RANK() OVER Раздел со сбросом в зависимости от другого столбца

У меня есть в таблице 2 разных идентификатора и временная метка, которую я хотел бы ранжировать. Но особенность в том, что я хочу ранжировать S_ID до тех пор, пока не будет записи по O_ID. Когда есть запись в O_ID, я хочу, чтобы следующий ранг в S_ID начинался с 1.

Вот пример:

select 
    S_ID,
    timestamp,
    O_ID,
    rank() OVER (PARTITION BY S_ID ORDER BY timestamp asc) AS RANK
from table
order by S_ID, timestamp;
S_IDОтметка времениO_IDКлассифицировать
2e114e9f2021-11-26 08:57:44.049НУЛЕВОЙ1
2e114e9f2021-12-26 17:07:26.272НУЛЕВОЙ2
2e114e9f2021-12-27 08:13:24.277НУЛЕВОЙ3
2e114e9f2021-12-29 11:32:56.95222875494
2e114e9f2021-12-30 13:41:28.821НУЛЕВОЙ5
2e114e9f2021-12-30 19:53:28.590НУЛЕВОЙ6
2e114e9f2022-02-05 09:50:54.10423330027
2e114e9f2022-02-19 10:14:31.389НУЛЕВОЙ8

Как мне теперь добавить еще один ранг в зависимости от записи в колонке O_ID? Итак, результат должен быть:

S_IDОтметка времениO_IDРанг S_IDОценить оба
2e114e9f2021-11-26 08:57:44.049НУЛЕВОЙ11
2e114e9f2021-12-26 17:07:26.272НУЛЕВОЙ22
2e114e9f2021-12-27 08:13:24.277НУЛЕВОЙ33
2e114e9f2021-12-29 11:32:56.952228754944
2e114e9f2021-12-30 13:41:28.821НУЛЕВОЙ51
2e114e9f2021-12-30 19:53:28.590НУЛЕВОЙ62
2e114e9f2022-02-05 09:50:54.104233300273
2e114e9f2022-02-19 10:14:31.389НУЛЕВОЙ81

Я рада любой пище для размышлений!!!!

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
0
17
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Похоже, что подход с пробелами и островами может быть здесь полезен — используйте lag для разделения данных на группы (на основе текущего и предыдущего равенства с некоторой обработкой нулей), а затем используйте значение группы в качестве раздела для функции rank().

-- sample data
WITH dataset (S_ID, Timestamp, O_ID) AS (
    VALUES ('2e114e9f', timestamp '2021-11-26 08:57:44.049',    NULL),
    ('2e114e9f',    timestamp '2021-12-26 17:07:26.272',    NULL),
    ('2e114e9f',    timestamp '2021-12-27 08:13:24.277',    NULL),
    ('2e114e9f',    timestamp '2021-12-29 11:32:56.952',    2287549),
    ('2e114e9f',    timestamp '2021-12-30 13:41:28.821',    NULL),
    ('2e114e9f',    timestamp '2021-12-30 19:53:28.590',    NULL),
    ('2e114e9f',    timestamp '2022-02-05 09:50:54.104',    2333002),
    ('2e114e9f',    timestamp '2022-02-19 10:14:31.389',    NULL)
) 

--query
select S_ID,
    Timestamp,
    O_ID,
    rank() OVER (PARTITION BY S_ID, grp ORDER BY timestamp asc) AS RANK
from(
        select *,
            sum(if (prev is not null and (O_ID is null or O_ID != prev), 1, 0)) 
                OVER (PARTITION BY S_ID ORDER BY timestamp asc) as grp
        from (
                select *,
                    lag(O_ID) OVER (PARTITION BY S_ID ORDER BY timestamp asc) AS prev
                from dataset
            )
    )

Выход:

S_IDОтметка времениO_IDКЛАССИФИЦИРОВАТЬ
2e114e9f2021-11-26 08:57:44.0491
2e114e9f2021-12-26 17:07:26.2722
2e114e9f2021-12-27 08:13:24.2773
2e114e9f2021-12-29 11:32:56.95222875494
2e114e9f2021-12-30 13:41:28.8211
2e114e9f2021-12-30 19:53:28.5902
2e114e9f2022-02-05 09:50:54.10423330023
2e114e9f2022-02-19 10:14:31.3891

Другие вопросы по теме