У меня есть в таблице 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 | Классифицировать |
---|---|---|---|
2e114e9f | 2021-11-26 08:57:44.049 | НУЛЕВОЙ | 1 |
2e114e9f | 2021-12-26 17:07:26.272 | НУЛЕВОЙ | 2 |
2e114e9f | 2021-12-27 08:13:24.277 | НУЛЕВОЙ | 3 |
2e114e9f | 2021-12-29 11:32:56.952 | 2287549 | 4 |
2e114e9f | 2021-12-30 13:41:28.821 | НУЛЕВОЙ | 5 |
2e114e9f | 2021-12-30 19:53:28.590 | НУЛЕВОЙ | 6 |
2e114e9f | 2022-02-05 09:50:54.104 | 2333002 | 7 |
2e114e9f | 2022-02-19 10:14:31.389 | НУЛЕВОЙ | 8 |
Как мне теперь добавить еще один ранг в зависимости от записи в колонке O_ID? Итак, результат должен быть:
S_ID | Отметка времени | O_ID | Ранг S_ID | Оценить оба |
---|---|---|---|---|
2e114e9f | 2021-11-26 08:57:44.049 | НУЛЕВОЙ | 1 | 1 |
2e114e9f | 2021-12-26 17:07:26.272 | НУЛЕВОЙ | 2 | 2 |
2e114e9f | 2021-12-27 08:13:24.277 | НУЛЕВОЙ | 3 | 3 |
2e114e9f | 2021-12-29 11:32:56.952 | 2287549 | 4 | 4 |
2e114e9f | 2021-12-30 13:41:28.821 | НУЛЕВОЙ | 5 | 1 |
2e114e9f | 2021-12-30 19:53:28.590 | НУЛЕВОЙ | 6 | 2 |
2e114e9f | 2022-02-05 09:50:54.104 | 2333002 | 7 | 3 |
2e114e9f | 2022-02-19 10:14:31.389 | НУЛЕВОЙ | 8 | 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 | КЛАССИФИЦИРОВАТЬ |
---|---|---|---|
2e114e9f | 2021-11-26 08:57:44.049 | 1 | |
2e114e9f | 2021-12-26 17:07:26.272 | 2 | |
2e114e9f | 2021-12-27 08:13:24.277 | 3 | |
2e114e9f | 2021-12-29 11:32:56.952 | 2287549 | 4 |
2e114e9f | 2021-12-30 13:41:28.821 | 1 | |
2e114e9f | 2021-12-30 19:53:28.590 | 2 | |
2e114e9f | 2022-02-05 09:50:54.104 | 2333002 | 3 |
2e114e9f | 2022-02-19 10:14:31.389 | 1 |