Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Itille Member Откуда: Волгоград Сообщений: 15 |
Простой пример. Имеется 2 таблицы, 1 ко многим T1 Id1 int primary key N1 varchar(500) T2 Id2 int primary key Id1 int Data datetime Rekv1, Rekv2 и т.д. На момент времени @Data1 необходимо иметь значения реквизитов T2 для всех ID по таблице T1 В лоб, без денормализации, это решается UDF с 3 Join T2 самой на себя Select * from T2 T JOIN (Select Id1, Max(Id2) as Id2 from T2 A JOIN ( Select Id1, Max(Data) as Data from T2 Where Data < @Data1 Group by id1) B On A.Id1 = B.Id1 and A.Data = B.Data) group by A.Id2 )M On T.Id1 = M.Id1 and T.Id2 = M.Id2 с индексом по T2: Id1 + Data Пример - договора и доп. соглашения. При большом количестве данных нагрузка может быть значительна. Может, у кого-нибудь есть альтернативные реализации структуры, пусть и ненормализованные? |
24 ноя 17, 10:52 [20980138] Ответить | Цитировать Сообщить модератору |
WarAnt Member Откуда: Питер Сообщений: 2423 |
Itille, Для какой версии сиквела? |
24 ноя 17, 11:02 [20980163] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
MS SQL 2012. Но если есть в 2016, то расскажите или киньте ссылку, лишним не будет. |
24 ноя 17, 11:07 [20980175] Ответить | Цитировать Сообщить модератору |
buser Member Откуда: Санкт-Петербург Сообщений: 4537 |
Itille,select top(1) with ties * from T2 where Data < @Data1 order by ROW_NUMBER() over(partition by Id1 order by Data desc) asc |
24 ноя 17, 11:08 [20980179] Ответить | Цитировать Сообщить модератору |
WarAnt Member Откуда: Питер Сообщений: 2423 |
тогда изайте LAST_VALUE |
||
24 ноя 17, 11:11 [20980189] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
buser, спасибо! |
24 ноя 17, 11:33 [20980277] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8330 |
Itille, без сортировки, похоже, не обойтись. Если селективность запроса низкая, т.е. T1 содержит большой объем данных, то Вам поможет только прокачка "железа". Кластерный индекс по t2.Data тоже бы помог, но это не подходит, судя по структуре. Попробуйте составить календарь заранее просчитать суммы на каждый день (та самая "денормализация"). Отчет летать будет. |
24 ноя 17, 14:26 [20981067] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
buser, посмотрел, немного не так, правильно: select top(1) with ties * from T2 where Data < @Data1 order by ROW_NUMBER() over(partition by Id1 order by Data desc, Id2 desc) asc в Вашем запросе не учтена возможность 2 записей с одинаковой датой. Всё равно, спасибо! |
6 дек 17, 13:52 [21010835] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47051 |
Думаю, вам надо заменить ROW_NUMBER() на RANK() |
||
6 дек 17, 14:17 [21011001] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
iap, согласен,что не указал, что ID2 identity(1б1) |
6 дек 17, 14:59 [21011238] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
iap,p, согласен,что не указал, что ID2 identity(1,1) Кстати, поигрался на реальных данных, есть ощущение, что конструкция из 3 join работает несколько быстрее... |
6 дек 17, 15:03 [21011258] Ответить | Цитировать Сообщить модератору |
buser Member Откуда: Санкт-Петербург Сообщений: 4537 |
Itille, да, очень даже может быть... а вот еще вопрос - назачем Max(Id2) в купе с Max(Data)? БардачЁк в данных или бизнеспроцессах? Или просто перестраховка? |
6 дек 17, 22:54 [21013031] Ответить | Цитировать Сообщить модератору |
982183 Member Откуда: VL Сообщений: 3374 |
А зачем нам в данном случае T1? Там нет интересующих вас данных. Почему нельзя взять ID из T2.Id1 |
||
7 дек 17, 04:23 [21013355] Ответить | Цитировать Сообщить модератору |
982183 Member Откуда: VL Сообщений: 3374 |
Дошло. "Доп соглашений" может и не быть... Но в Т1 нет и даты... |
7 дек 17, 04:25 [21013356] Ответить | Цитировать Сообщить модератору |
982183 Member Откуда: VL Сообщений: 3374 |
Всё. Совсем дошло. Вывести все допсоглашения по всем договорам в которых есть допсоглашения до @Data1 |
7 дек 17, 08:13 [21013443] Ответить | Цитировать Сообщить модератору |
982183 Member Откуда: VL Сообщений: 3374 |
В качестве ненормализованного бреда. Заведи в T1 дополнительное поле "Дата последнего допсоглашения" И получится один Update и один Select c одним join |
7 дек 17, 08:17 [21013451] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
buser, данные ведутся от царя гороха, плюс пользователи бывают замечательные, есть ограничения на редактирование записей ( в течение суток от создания), после чего вменяется для корректировки создавать новую запись. |
7 дек 17, 08:50 [21013528] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Select * from T2 T JOIN (Select Id1, Max(Id2) as Id2 from T2 A JOIN ( Select Id1, Max(Data) as Data from T2 Where Data < @Data1 Group by id1) B On A.Id1 = B.Id1 and A.Data = B.Data) group by A.Id2 ) M On T.Id1 = M.Id1 and T.Id2 = M.Id2 -- эквивалент Select m.* from ( select distinct Id1, Id2 from T2 ) as T cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 and Id2 = T.Id2 order by Data desc, Id2 desc ) as m -- из которого видно, насколько идиотичен исходный запрос (обработка ВСЕЙ таблицы, когда реально надо только часть ) -- для ускорения -- select distinct Id1, Id2 from T2 -- можно материализовать (Indexed View) |
7 дек 17, 09:23 [21013591] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
aleks222, во - первых, Ваш запрос неправильный. Правильно - Select m.* from ( select distinct Id1 from T2 ) as T cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m А во-вторых, мне не очень нравится order by внутри функции. Вариант buser больше по душе |
7 дек 17, 15:46 [21015514] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47051 |
Вижу не ORDER BY, а TOP(1) ... ORDER BY. |
||
7 дек 17, 16:06 [21015612] Ответить | Цитировать Сообщить модератору |
Cammomile Member Откуда: Сообщений: 1214 |
А оно быстрее аплая с TOP (1) ORDER... DESC ? Ни разу не пользовался такой штукой. |
||||
7 дек 17, 16:14 [21015646] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
iap, я имел в виду подзапрос. Сейчас поэксперементировал. Могу сказать, что на реальных данных время выполнения хуже, чем даже у запроса в первом посте. Где-то в 2-3 раза на 1 млн. записей. Но это на конкретном массиве записей с конкретными значениями. В принципе, сам вариант имеет право на существование. |
7 дек 17, 16:21 [21015683] Ответить | Цитировать Сообщить модератору |
Itille Member Откуда: Волгоград Сообщений: 15 |
На самом деле, есть подозрение, что все вышеприведенные варианты по эффективности зависят от среднего количества строк в таблице t2 на каждое значение t1. Вышенаписанное (в предыдущем моем топике) верно для среднего значения около 2. |
7 дек 17, 16:30 [21015734] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
Дык, страдалец, ты меня пугаешь своей замороченностью. Select m.* from T1 as T cross apply ( select top(1) * from T2 Where Data < @Data1 and Id1 = T.Id1 order by Data desc, Id2 desc ) as m + индекс T2(Id1, Data, Id2) |
||
7 дек 17, 18:22 [21016149] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |