ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / ROW_NUMBER с пропуском строк
11 сообщений из 11, страница 1 из 1
ROW_NUMBER с пропуском строк
    #39836882
Oblom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oblom
Участник
Добрый день!
Есть таблица
Код: sql
1.
2.
3.
4.
5.
6.
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 решается тривиально:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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 уже час не могу придумать

Версия сервера
Код: sql
1.
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 толком ничего не знаю. Может есть новые конструкции языка, которые помогут решить проблему?
...
Рейтинг: 0 / 0
12.07.2019, 15:24
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836902
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Oblom,

так вроде ...

Код: sql
1.
2.
3.
4.
5.
...
,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)
...
Рейтинг: 0 / 0
12.07.2019, 16:02
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836907
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
fix
courtOblom,

так вроде ...

Код: sql
1.
2.
3.
4.
5.
...
,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)
...
Рейтинг: 0 / 0
12.07.2019, 16:08
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836934
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
courtfix
courtOblom,

так вроде ...

Код: sql
1.
2.
3.
4.
5.
...
,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
сори ...
...
Рейтинг: 0 / 0
12.07.2019, 16:35
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836937
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
OblomСделать надо за один проход
А "один проход" это вы как меряете?
Oblom
Код: sql
1.
2.
,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




вы план этого "одного прохода" смотрели?
...
Рейтинг: 0 / 0
12.07.2019, 16:39
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836946
Oblom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oblom
Участник
msLexOblomСделать надо за один проход
А "один проход" это вы как меряете?
Oblom
Код: sql
1.
2.
,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




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

Справедливо, там будет не один проход. Но и не два полных скана таблицы
...
Рейтинг: 0 / 0
12.07.2019, 16:53
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836960
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
OblommsLexпропущено...

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



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

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

Вот вам и "за 1 проход по 200 млн-ой таблице" нужное решение.
...
Рейтинг: 0 / 0
12.07.2019, 17:02
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836962
Oblom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oblom
Участник
msLexOblomпропущено...


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

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

Спасибо, этот способ я знаю. Более того, я его в итоге и сделал.
Вопрос был в другом, ваш ответ на него "Нет, и вообще вам не это надо". Спасибо, может у кого-то будет положительный ответ.
...
Рейтинг: 0 / 0
12.07.2019, 17:08
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39836968
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
OblommsLexпропущено...

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

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

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

А с чего вы решили, что гирлянды row_number-ов с, с той же промежуточной материализацией, в той же tempdb, будут работать быстрее?
...
Рейтинг: 0 / 0
12.07.2019, 17:21
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39837012
Oblom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oblom
Участник
msLexOblomпропущено...


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

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

Ок, переформулирую вопрос:
Как решить задачу "гирляндой ROW_NUMBER" без временных таблиц и подзапросов?
Вопрос на возможность такой реализации. Если кто-то кинет ссылкой вместо работающего кода - буду благодарен.
...
Рейтинг: 0 / 0
12.07.2019, 21:34
| Ответить | Цитировать | Написать  
ROW_NUMBER с пропуском строк
    #39837051
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
вторая попытка :)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
...
,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)
...
Рейтинг: 0 / 0
13.07.2019, 08:06
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / ROW_NUMBER с пропуском строк
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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