Агрегирование перекрывающихся дат вступления в силу

Я пытаюсь агрегировать перекрывающиеся даты вступления в силу. Любые промежутки между датами следует рассматривать как отдельные строки. Я использую min и max, и я получаю результат ниже, но хотел бы увидеть ожидаемый результат.

Мой запрос

WITH test_data AS (
    SELECT '2020-01-01' AS date_from,
           '2020-01-03' AS date_to,
           '1'          AS product
    UNION ALL
    SELECT '2020-01-05' AS date_from,
           '2020-01-07' AS date_to,
           '1'          AS product
    UNION ALL
    SELECT '2020-01-06' AS date_from,
           '2020-01-10' AS date_to,
           '1'          AS product
)
SELECT product,
       MIN(date_from) AS date_from,
       MAX(date_to)   AS date_to
FROM test_data
GROUP BY 1;

Исходные данные

date_from date_to product
2020-01-01 2020-01-03 1
2020-01-05 2020-01-07 1
2020-01-06 2020-01-10 1

Выходная таблица

date_from date_to product
2020-01-01 2020-01-10 1

Ожидаемый результат

date_from date_to product
2020-01-01 2020-01-03 1
2020-01-05 2020-01-10 1

Заранее спасибо !


sql snowflake-cloud-data-platform snowflake-schema
person Dominik Golebiewski    schedule 06.05.2021    source источник
comment
Не могли бы вы добавить к своему вопросу запрос, который вы придумали? Это поможет другим увидеть, что вы пробовали и что именно нужно исправить.   -  person ginkul    schedule 06.05.2021
comment
Я думаю, вы ищете это   -  person ginkul    schedule 06.05.2021
comment
@ДоминикГолебевски . . . Отметьте свой вопрос с помощью базы данных, которую вы используете.   -  person Gordon Linoff    schedule 06.05.2021

Ответы (2)


arrow_upward
2
arrow_downward

Объединение диапазонов дат может быть достигнуто с помощью MATCH_RECOGNIZE.

Подготовка данных:

CREATE OR REPLACE TABLE test_data AS
SELECT '2020-01-01'::DATE AS date_from, '2020-01-03'::DATE AS date_to, '1'  AS product
UNION ALL
SELECT '2020-01-05'::DATE AS date_from, '2020-01-07'::DATE AS date_to, '1'  AS product
UNION ALL
SELECT '2020-01-06'::DATE AS date_from, '2020-01-10'::DATE AS date_to, '1' AS product;

Запрос:

SELECT * 
FROM test_data t
MATCH_RECOGNIZE(
  PARTITION BY product
  ORDER BY date_from, date_to
  MEASURES FIRST(date_from) date_from, MAX(date_to) date_to
  PATTERN(a* b)
  DEFINE a AS MAX(date_to) OVER() >= NEXT(date_from)
) mr;

демонстрация db‹›fiddle — Oracle

Связанное чтение: Объединение перекрывающихся диапазонов дат с помощью MATCH_RECOGNIZE от Стивастон

person Lukasz Szozda    schedule 06.05.2021
comment
Это выглядит аккуратно и чисто! Мне нравится это решение. Спасибо ! - person Dominik Golebiewski; 06.05.2021

arrow_upward
3
arrow_downward

Это тип проблемы пробелов и островов. Я рекомендую такой подход:

SELECT product,
       MIN(date_from) AS date_from,
       MAX(date_to)   AS date_to
FROM (SELECT td.*,
             SUM(CASE WHEN prev_date_to >= date_from THEN 0 ELSE 1 END) OVER (PARTITION BY product ORDER BY date_to) as grp
      FROM (SELECT td.*,
                   MAX(date_to) OVER (PARTITION BY product ORDER BY date_from ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as prev_date_to
            FROM test_data td
           ) td
     ) td
GROUP BY grp, product
ORDER BY product, MIN(date_from);

Вот скрипт db‹›.

Что это делает? Самый внутренний подзапрос получает последние date_to в предыдущих строках. Это используется для определения того, соединена ли каждая строка с предыдущей строкой или начинается новая группировка.

Средний подзапрос имеет логику, которая представляет собой кумулятивную сумму, когда строки начинают новую группу. Затем внешний запрос агрегируется по этой группировке.

person Gordon Linoff    schedule 06.05.2021
comment
Я никогда раньше не использовал оконные рамы! Это работает как очарование! Спасибо! - person Dominik Golebiewski; 06.05.2021