четверг, 7 марта 2024 г.

Пробелы и острова, или Gaps and islands. Часть II

Это продолжение поста Пробелы и острова, или Gaps and islands. Часть I

Напомню, что задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

Часто последовательность данных – это хронологическая последовательность, то есть, развернутая во времени, например, по датам. Тогда "острова" – это непрерывные периоды, когда некое событие происходит ежедневно, а "пробелы" – это непрерывные периоды, в течение которых это событие не происходит.

Рассмотрим решение задачи "пробелы и острова" на хронологической последовательности данных.

Пусть в таблице daily_sales хранятся данные ежедневных продаж товаров A, B и C за неделю с 2024-01-05 по 2024-01-11:

create table daily_sales (
    article varchar(10),
    sales_date date,
    sales int
);

insert into daily_sales
values
('A', date '2024-01-05', 15),
('B', date '2024-01-05', 10),
('A', date '2024-01-06', 12),
('B', date '2024-01-06', 12),
('A', date '2024-01-07', 12),
('B', date '2024-01-07', 15),
('С', date '2024-01-07',  3),
('B', date '2024-01-08', 11),
('С', date '2024-01-08',  5),
('A', date '2024-01-09', 14),
('B', date '2024-01-09', 15),
('С', date '2024-01-09',  2),
('B', date '2024-01-10', 10),
('A', date '2024-01-11', 15)
;

select * from daily_sales;
articlesales_datesales
A2024-01-0515
B2024-01-0510
A2024-01-0612
B2024-01-0612
A2024-01-0712
B2024-01-0715
С2024-01-073
B2024-01-0811
С2024-01-085
A2024-01-0914
B2024-01-0915
С2024-01-092
B2024-01-1010
A2024-01-1115

Нужно получить периоды ежедневных продаж для каждого товара – то есть, нужно решить задачу выделения нормальных островов. Воспользуемся ранее приведенным рецептом, модифицировав его для хронологической последовательности (для дат) и для нескольких последовательностей (для каждого товара):

with q as (
    select article,
        sales_date,
        sales_date - date '1970-01-01' - row_number() over (partition by article order by sales_date) grp
    from daily_sales
)
select
    article,
    min(sales_date) first_date,
    max(sales_date) last_date
from q
group by article, grp
order by 1, 2
;

Здесь вычитание date '1970-01-01' из sales_date дает число, из которого мы вычитаем результат оконной функции row_numbr() over (partition by article order by sales_date) и получаем значение, идентифицирующее нормальный "остров" в последовательности дат для каждого товара:

articlesales_dategrp
A2024-01-0519726
A2024-01-0619726
A2024-01-0719726
A2024-01-0919727
A2024-01-1119728
B2024-01-0519726
B2024-01-0619726
B2024-01-0719726
B2024-01-0819726
B2024-01-0919726
B2024-01-1019726
С2024-01-0719728
С2024-01-0819728
С2024-01-0919728

А внешний запрос группирует строки по товару и идентификатору "острова" и выводит для кадого товара минимальное и максимальное значение каждого "острова":

articlefirst_datelast_date
A2024-01-052023-01-07
A2024-01-092023-01-09
A2024-01-112023-01-11
B2024-01-052023-01-10
C2024-01-072023-01-09

Теперь найдем "пробелы" для каждого товара, адаптировав ранее приведенный рецепт для "пробелов" для таблицы daily_dales:

with q as (
    select article, 
        sales_date cur,
        lead(sales_date) over (partition by article order by sales_date) nxt
    from daily_sales
)
select
    article,
    cur + 1 first_date,
    nxt - 1 last_date
from q
where nxt - cur > 1
;
articlefirst_datelast_date
A2024-01-082024-01-08
A2024-01-102024-01-10

Видим, что, хотя данные в таблице представляют неделю продаж, запрос находит только периоды без продаж ("пробелы"), ограниченные периодами продаж ("островами") и не находит периоды без продаж, примыкающие к началу или концу недели. Как же их получить?

Предлагаю рассмотреть универсальное решение для выделения и "пробелов" и "островов".

Для этого вначале дополним данные таблицы daily_sales данными о нулевых продажах товаров в те дни, когда продаж не было. Создам соответствущее вью:

create view daily_sales_vw as
with prod as (
    select article, date '2024-01-05' + n sales_date
    from generate_series(0, 6) as d(n),
        (select distinct article from daily_sales) a
)
select prod.article,
    prod.sales_date,
    coalesce(sales, 0) sales
from prod
    left join daily_sales ds 
        on prod.article = ds.article 
        and prod.sales_date = ds.sales_date
;

select * from daily_sales_vw order by article, sales_date;
articlesales_datesales
A2024-01-0515
B2024-01-0510
C2024-01-050
A2024-01-0612
B2024-01-0612
C2024-01-060
A2024-01-0712
B2024-01-0715
С2024-01-073
A2024-01-080
B2024-01-0811
С2024-01-085
A2024-01-0914
B2024-01-0915
С2024-01-092
A2024-01-100
B2024-01-1010
С2024-01-100
A2024-01-1115
B2024-01-110
С2024-01-110

Запрос к daily_sales_vw возвращает данные продаж каждого из трех товаров в каждый из дней периода 2024-01-05 - 2024-01-11.

На этих данных решим задачу выделения "пробелов" и "островов", используя три группировки:

  1. группировка по товарам,
  2. группировка по товарам и событию продажи,
  3. группировка по товарам, событию продажи и критерию группировки, рассчитанному с помощью двух первых группировок.

Посмотрим вначале на две первые группировки:

with q as (
    select
        article,
        sales_date,
        sign(sales) sale_event,
        row_number() over (partition by article order by sales_date) rn1,
        row_number() over (partition by article, sign(sales) order by sales_date) rn2
    from daily_sales_vw
)
select q.*, rn1 - rn2 grp
from q
;
articlesales_datesale_eventrn1rn2grp
A2024-01-051110
A2024-01-061220
A2024-01-071330
A2024-01-080413
A2024-01-091541
A2024-01-100624
A2024-01-111752
B2024-01-051110
B2024-01-061220
B2024-01-071330
B2024-01-081440
B2024-01-091550
B2024-01-101660
B2024-01-110716
C2024-01-050110
C2024-01-060220
С2024-01-071312
С2024-01-081422
С2024-01-091532
С2024-01-100633
С2024-01-110743

Здесь группировка по товару и группировка по товару и событию продажи используются для последовательной нумерации строк внутри групп, см. столбцы rn1 и rn2, и получения разности этих номеров, см. столбец grp.

Сочетание значений article, sales_event, grp и позволяет выделить "острова" и "пробелы". Так, для товара A указанное сочетание одинаково для дат с 2024-01-05 по 2024-01-07, что соответствует первому "острову" товара A. Далее идет однодневный "пробел" 2024-01-08, однодневный "остров" 2024-01-09 и т.д.

Итак, получим непрерывные периоды продаж и периоды без продаж:

with q as (
    select
        article,
        sales_date,
        sign(sales) sales_event,
        row_number() over (partition by article order by sales_date)
            - row_number() over (partition by article, sign(sales) order by sales_date) grp
    from daily_sales
)
select article,
    sales_event,
    min(sales_date) first_date,
    max(sales_date) last_date
from q
group by article, sales_event, grp
order by article, first_date
;
articlesales_eventfirst_datelast_date
A12024-01-052024-01-07
A02024-01-082024-01-08
A12024-01-092024-01-09
A02024-01-102024-01-10
A12024-01-112024-01-11
B12024-01-052024-01-10
B02024-01-112024-01-11
C02024-01-052024-01-06
С12024-01-072024-01-09
С02024-01-102024-01-11

Если нас интересуют только периоды без продаж – "пробелы" – или только периоды продаж – "острова", – то достаточно добавить во внешний запрос условие where sales_event = 0 (нет продаж) или where sales_event = 1 (есть продажи).

Итак, мы рассмотрелии решение задачи "пробелы и острова" на хронологической последовательности данных.

На этом заканчиваю работу с демонстрационными данными:

drop viw daily_sales_vw;
drop table daily_sales;

В заключение замечу, что "пробелы" и "острова" можно с равным успехом выделять на последовательностях, члены которых представляют собой диапазоны. Например, интервалы времени или периоды дат, в течение (или в конце или начале) которых произошло некоторое событие. Тогда "пробелы" и "острова" образуются слиянием смежных диапазонов, отвечающих соответствующим условиям.

Ярлыки: , , , ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)