SQL подсчет кол-ва повторяющихся значений в колонке с обновлением счетчика
Есть массив с датой, идентификатором человека и неким показателем
| dt | tab_num | ger_sess |
|---|---|---|
| 28.08.2022 | 00ZP-1234 | 1 |
| 29.08.2022 | 00ZP-1234 | 1 |
| 30.08.2022 | 00ZP-1234 | 0 |
| 31.08.2022 | 00ZP-1234 | 1 |
| 01.09.2022 | 00ZP-1234 | 1 |
| 02.09.2022 | 00ZP-1234 | 1 |
| 03.09.2022 | 00ZP-1234 | 0 |
| 04.09.2022 | 00ZP-1234 | 0 |
Необходимо, чтобы sql-запрос считал кол-во повторяющихся значений в столбце ger_sess в разрезе одного идентификатора сотрудника, при этом ранжировать по дате от большей к меньшей
В итоге должно получиться следующее:
| dt | tab_num | ger_sess | count |
|---|---|---|---|
| 28.08.2022 | 00ZP-1234 | 1 | 1 |
| 29.08.2022 | 00ZP-1234 | 1 | 2 |
| 30.08.2022 | 00ZP-1234 | 0 | 0 |
| 31.08.2022 | 00ZP-1234 | 1 | 1 |
| 01.09.2022 | 00ZP-1234 | 1 | 2 |
| 02.09.2022 | 00ZP-1234 | 1 | 3 |
| 03.09.2022 | 00ZP-1234 | 0 | 0 |
| 04.09.2022 | 00ZP-1234 | 0 | 0 |
У меня получился скрипт ниже, но выводимый результат меня не устраивает:
Create table temp_table (
dt date,
tab_num text,
ger_sess int
);
insert into temp_table values
('2022-08-28', '00ZP-1234', 1),
('2022-08-29', '00ZP-1234', 1),
('2022-08-30', '00ZP-1234', 0),
('2022-08-31', '00ZP-1234', 1),
('2022-09-01', '00ZP-1234', 1),
('2022-09-02', '00ZP-1234', 1),
('2022-09-03', '00ZP-1234', 0),
('2022-09-03', '00ZP-1234', 0)
;
select
tab_num,
dt,
ger_sess,
case when ger_sess = 1 then rank () over (partition by tab_num, ger_sess order by dt) else 0 end
from temp_table
order by dt
| dt | tab_num | ger_sess | count |
|---|---|---|---|
| 28.08.2022 | 00ZP-1234 | 1 | 1 |
| 29.08.2022 | 00ZP-1234 | 1 | 2 |
| 30.08.2022 | 00ZP-1234 | 0 | 0 |
| 31.08.2022 | 00ZP-1234 | 1 | 3 |
| 01.09.2022 | 00ZP-1234 | 1 | 4 |
| 02.09.2022 | 00ZP-1234 | 1 | 5 |
| 03.09.2022 | 00ZP-1234 | 0 | 0 |
| 04.09.2022 | 00ZP-1234 | 0 | 0 |
Работаю в GreenPlum с ядром от PostgreSQL
Ответы (1 шт):
Наверное не самый красивый вариант. В подзапросе, через SUM попытался разметить строки которые идут по порядку CASE WHEN ger_sess = 1 THEN 0 ELSE 1 END. Тут для значений ger_sess = 1 значение sum не увеличивается, поэтому это будет одна группа значений до тех пор, пока не попадется ger_sess = 0. Затем использую получененное значение для определения PARTITION для rank() :
SELECT t.tab_num,
t.dt,
t.ger_sess,
CASE
WHEN t.ger_sess = 1 THEN RANK() OVER (PARTITION BY t.tab_num, t.order_sum, t.ger_sess ORDER BY dt)
ELSE 0 END
FROM (SELECT tab_num,
dt,
ger_sess,
SUM(CASE WHEN ger_sess = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY tab_num ORDER BY dt) AS order_sum
FROM temp_table
ORDER BY dt) t;