Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать быстрый запрос / 9 сообщений из 9, страница 1 из 1
20.07.2018, 10:20
    #39676662
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
Коллеги, приветствую!
Есть вот такой список:
ИД, номер событие, класс события (0,1), состояние ().
Нужно вывести событие с максимальным номером, у которого класс = 1, либо если такового события нет - то с максимальным номером класса 0, и номер максимального события для данного ИД вообще (ну и само состояние и класс).
Номера - монотонно возрастающие.

Вот - модель:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
drop table if exists #t;

Create table #t
(ID bigint not NULL,
N int not NULL,
isChangeState bit,
isOnOff bit
Constraint PK_t primary key Clustered (ID, N)
)

; With N as ( 
Select Top 1000000
ROW_NUMBER() over (order by (select 1/0)) Num
from
	master..spt_values a 
		cross join master..spt_values b
		cross join master..spt_values c
)
insert into #t
Select 
Num % 220000 + 1 ID
,Row_Number() over (partition by Num % 220000 + 1 order by Num) N 
,(Cast(Rand(Num) * 100000000 as int) % 10) % 2 isChangeState
,(Cast(Rand(Num) * 10000000 as int) % 10) % 2 isOnOff
from N
-- очевидное решение
;With s as
(
Select
*,
ROW_NUMBER() over (partition by ID order by isChangeState DESC, N DESC) ord
,max(N) over (partition by ID) NN
from #t
)
Select ID, N, isChangeState, isOnOff, NN from s
	where ord = 1



Модель не очень хорошая, т.к. событий у одного id может быть от 1 до 100, примерно, но среднее - около 5, я просто не знаю, как нормально засеять модель.

Очевидное решение - у меня есть.
Но имеется проблема, оно конкретно подтормаживает.
Дело в том, что в списке порядка 10 млрд. записей, и порядка 100 млн. уникальных ID.
Сервер - 16SP2.

Посоветуйте, что можно сделать?
Если нужно, могу на список построить колумнстор или, теоретически, любой индекс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.07.2018, 11:53
    #39676779
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
uaggsterПосоветуйте, что можно сделать?Завести служебную таблицу для хранения агрегированных данных и поддерживать ее триггером.

Если же хочется все время выковыривать требуемое запросом, то можно, например, так:
Код: sql
1.
2.
create unique index IX_#t__1 on #t (ID, N desc) include (isChangeState, IsOnOff) where isChangeState = 1;
create unique index IX_#t__0 on #t (ID, N desc) include (isChangeState, IsOnOff) where isChangeState = 0;

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
with a as
(
 select
  *, row_number() over (partition by ID order by N desc) as rn
 from
  #t
 where
  isChangeState = 1
),
b as
(
 select
  *, row_number() over (partition by ID order by N desc) as rn
 from
  #t
 where
  isChangeState = 0
)
select
 isnull(a.ID, b.ID), isnull(a.N, b.N), isnull(a.isChangeState, b.isChangeState), isnull(a.isOnOff, b.isOnOff),
 case when isnull(a.N, 0) > isnull(b.N, 0) then a.N else b.N end
from
 (select * from a where rn = 1) a full join
 (select * from b where rn = 1) b on b.ID = a.ID;


В конечном итоге, затраты на индексы и запрос с их участием окажутся выше затрат на поддержание таблицы с агрегатами.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.07.2018, 15:10
    #39676964
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
invm,
Код: sql
1.
2.
3.
from
 (select * from a where rn = 1) a full join
 (select * from b where rn = 1) b on b.ID = a.ID;


Что-то я сомневаюсь, что самосоединение на миллиарде то записей нормально будет работать.
Хотя я попробую, конечно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.07.2018, 15:17
    #39676966
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
uaggster,

автор100 млн. уникальных ID.
т.е. вам в каждий момент времени(запрос) нужно выдать 100кк записей?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.07.2018, 16:04
    #39676999
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
TaPaK, нет.
Просто запрос на формирование некой вторичной выборки из хранилища, где регистрируются события от приборов.
Это хранилище живет своей жизнью, секции обновляются, удаляются и т.д.
Время от времени мне нужно материализовать такую выборку в отдельную базу.
И с ней уже в дальнейшем работают... ну, дофига кто.

Я ее выгружаю в отдельную табличку, а потом переключением контекста убиваю предыдущую (не совсем так, там еще архивные таблицы есть, но не суть).
Проблема в том, что обрабатывается она долго.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2018, 16:52
    #39678421
ATI.HeNRy
ATI.HeNRy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
uaggsterДело в том, что в списке порядка 10 млрд. записей, и порядка 100 млн. уникальных ID.
Проблема в том, что обрабатывается она долго.
Вы же говорите среднее 5, а если поделить одно на другое то 100 получается.
И долго - это сколько минут.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2018, 02:29
    #39678654
982183
982183
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
uaggsterПросто запрос на формирование некой вторичной выборки из хранилища, где регистрируются события от приборов.

Довольно распространенная задача.
Только "Вторичню выборку" надо материализовать не "Время от времени" а постоянно/периодически.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 08:54
    #39679392
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
ATI.HeNRy, около 20 минут.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.07.2018, 13:15
    #39679597
ATI.HeNRy
ATI.HeNRy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать быстрый запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
With s as (
		Select 
		ID
		,isChangeState
		,N=max(N)
		from #t
		GROUP BY ID,isChangeState
		)
,s2 as (
		Select 
		ID
		,ismax=max(cast (isChangeState as tinyint))
		,NN=max(N)
		from #t
		GROUP BY ID
		)
Select 
t.*
,nn 
--INTO #rz
FROM #t t
INNER JOIN s2
	on t.id=s2.id
INNER JOIN s
	on t.id=s.id
		and t.n = s.n
		and t.isChangeState= s2.ismax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать быстрый запрос / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]