Присвоение порядковых номеров датам в SQL

Я пытаюсь выделить даты, которые попадают в 30-дневное окно друг от друга в SQL. Для каждого идентификационного номера в приведенном ниже примере данных, как я могу назначить номер корзины, который увеличивается на 1 каждый раз, когда между соседними датами существует более чем 30-дневный разрыв?

Пример данных:

create table dates_bucket (ID integer, DATE date);
insert into dates_bucket (ID, DATE)
values
('123', '2019-04-04'),
('123', '2019-04-06'),
('123', '2019-09-09'),
('123', '2019-09-10'),
('123', '2019-10-01'),
('123', '2019-11-30'),
('345', '2013-03-12'),
('345', '2013-05-23'),
('345', '2014-09-03'),
('345', '2019-10-23'),
('345', '2019-10-25');

Мне нужно, чтобы вывод выглядел так:

ID    DATE           ROW_NUM
123   2019-04-04     1
123   2019-04-06     1
123   2019-09-09     2
123   2019-09-10     2
123   2019-10-01     2
123   2019-11-30     3
345   2013-03-12     1
345   2013-05-23     2
345   2014-09-03     3
345   2019-10-23     4
345   2019-10-25     4

Если это уместно, я работаю в Netezza.

sql datetime netezza gaps-and-islands date-arithmetic

14.12.2020 00:35

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

164

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

Это проблема пробелов и островов, когда острова представляют собой соседние даты с промежутком менее одного месяца. Я бы рекомендовал lag() получить предыдущую дату, а накопительный sum(), увеличивающий разрыв более чем на 30 дней, выполняется:

select id, date, 
    sum(case when date <= lag_date + interval '30 day' then 0 else 1 end)
        over(partition by id order by date) as grp
from (
    select d.*,
        lag(date) over(partition by id order by date) lag_date
    from dates_buckets d
) d

Блестяще, спасибо! Я думал, что сумма может сыграть свою роль, но не мог понять, как собрать части вместе.

— 14.12.2020 01:26

14.12.2020 00:57