Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ROW_NUMBER с пропуском строк  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
Добрый день!
Есть таблица
CREATE TABLE t(
 tran_id bigint primary key
,prop_id bigint
,date datetime
,type smallint
)

Необходимо, выбрать с группировкой по prop_id все записи с максимальной date плюс следующую по date запись с type=2(при этом максимальная запись тоже может быть с type=2, тогда надо искать следующую)
Сделать надо за один проход, в реальной таблице 200 млн. записей. Как сделать не за один проход я знаю: первым проходом забрать максимальные, вторым проходом забрать максимальные с type=2, которых нет в результатах первого прохода.
Ситуация, когда максимальная по дате запись в рамках prop_id не с type=2 решается тривиально:
SELECT *
FROM(
SELECT 
tran_id
,prop_id
,date
,type
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY IIF(date DESC) rn_2
FROM t
) as m
WHERE m.rn = 1 OR (m.rn_2 = 1 AND m.rn <> 1 AND m.type = 2)

Но как в этот же проход уместить ситуацию, когда m.rn = 1 AND m.type = 2 и надо найти следующую запись с type = 2 уже час не могу придумать

Версия сервера
SELECT @@version
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)   Nov 30 2018 12:57:58   Copyright (C) 2017 Microsoft Corporation  Web Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor) 

Я отстал в развитии T-SQL, кроме ROW_NUMBER, RANK и DENSE_RANK толком ничего не знаю. Может есть новые конструкции языка, которые помогут решить проблему?
12 июл 19, 15:24    [21925807]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
court
Member

Откуда:
Сообщений: 1956
Oblom,

так вроде ...

...
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY case when type = 2 then 0 else 1 end, date DESC) rn_2
...
where rn=1 or (rn=1 and rn_2=2) or (rn=2 and rn_2=2)
12 июл 19, 16:02    [21925856]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
court
Member

Откуда:
Сообщений: 1956
fix
court
Oblom,

так вроде ...

...
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY case when type = 2 then 0 else 1 end, date DESC) rn_2
...
where rn=1 or (rn=2 and rn_2=1) or (rn=2 and rn_2=2)
12 июл 19, 16:08    [21925872]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
court
Member

Откуда:
Сообщений: 1956
court
fix
court
Oblom,

так вроде ...

...
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY case when type = 2 then 0 else 1 end, date DESC) rn_2
...
where rn=1 or (rn=2 and rn_2=1) or (rn=2 and rn_2=2)

нее, всё равно "плохо", если первая/вторая запись с type = 2 будет с rn>2
сори ...
12 июл 19, 16:35    [21925912]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
msLex
Member

Откуда:
Сообщений: 7730
Oblom
Сделать надо за один проход

А "один проход" это вы как меряете?
Oblom
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY IIF(date DESC) rn_2



вы план этого "одного прохода" смотрели?
12 июл 19, 16:39    [21925918]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
msLex
Oblom
Сделать надо за один проход

А "один проход" это вы как меряете?
Oblom
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY IIF(date DESC) rn_2



вы план этого "одного прохода" смотрели?


Справедливо, там будет не один проход. Но и не два полных скана таблицы
12 июл 19, 16:53    [21925930]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
msLex
Member

Откуда:
Сообщений: 7730
Oblom
msLex
пропущено...

А "один проход" это вы как меряете?
пропущено...



вы план этого "одного прохода" смотрели?


Справедливо, там будет не один проход. Но и не два полных скана таблицы

Материализуйте нужные данные из 200 миллионной таблицы в # таблицу.
Добавьте нужные индексы.

Вот вам и "за 1 проход по 200 млн-ой таблице" нужное решение.
12 июл 19, 17:02    [21925946]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
msLex
Oblom
пропущено...


Справедливо, там будет не один проход. Но и не два полных скана таблицы

Материализуйте нужные данные из 200 миллионной таблицы в # таблицу.
Добавьте нужные индексы.

Вот вам и "за 1 проход по 200 млн-ой таблице" нужное решение.


Спасибо, этот способ я знаю. Более того, я его в итоге и сделал.
Вопрос был в другом, ваш ответ на него "Нет, и вообще вам не это надо". Спасибо, может у кого-то будет положительный ответ.
12 июл 19, 17:08    [21925950]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
msLex
Member

Откуда:
Сообщений: 7730
Oblom
msLex
пропущено...

Материализуйте нужные данные из 200 миллионной таблицы в # таблицу.
Добавьте нужные индексы.

Вот вам и "за 1 проход по 200 млн-ой таблице" нужное решение.


Спасибо, этот способ я знаю. Более того, я его в итоге и сделал.
Вопрос был в другом, ваш ответ на него "Нет, и вообще вам не это надо". Спасибо, может у кого-то будет положительный ответ.


А с чего вы решили, что гирлянды row_number-ов с, с той же промежуточной материализацией, в той же tempdb, будут работать быстрее?
12 июл 19, 17:21    [21925960]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 624
msLex
Oblom
пропущено...


Спасибо, этот способ я знаю. Более того, я его в итоге и сделал.
Вопрос был в другом, ваш ответ на него "Нет, и вообще вам не это надо". Спасибо, может у кого-то будет положительный ответ.


А с чего вы решили, что гирлянды row_number-ов с, с той же промежуточной материализацией, в той же tempdb, будут работать быстрее?


Ок, переформулирую вопрос:
Как решить задачу "гирляндой ROW_NUMBER" без временных таблиц и подзапросов?
Вопрос на возможность такой реализации. Если кто-то кинет ссылкой вместо работающего кода - буду благодарен.
12 июл 19, 21:34    [21926044]     Ответить | Цитировать Сообщить модератору
 Re: ROW_NUMBER с пропуском строк  [new]
court
Member

Откуда:
Сообщений: 1956
вторая попытка :)

...
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY date DESC) rn
,ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY case when type = 2 then 0 else 1 end, date DESC) rn_2
,first_value(tran_id) OVER (PARTITION BY prop_id ORDER BY date DESC) fv
,first_value(tran_id) OVER (PARTITION BY prop_id ORDER BY case when type = 2 then 0 else 1 end, date DESC) fv_2
...
where rn=1 
    or (fv<>fv_2 and rn_2=1) or (fv=fv_2 and rn_2=2)
13 июл 19, 08:06    [21926100]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить