Сучасні дискусії про дані часто звучать як заїжджена платівка, безперервно повторюючи: "Вивчення та розуміння SQL — одна з найважливіших навичок для будь-якого спеціаліста з даних". Однак такі твердження не враховують, що SQL має свої підводні камені, як і слід очікувати від такої абстрактної мови. Один з таких моментів, який я хочу розглянути, — це створення набору даних, який буде підходити для аналізу часових рядів, де активні записи вважаються активними протягом усього свого життєвого циклу, а не лише в один момент часу.
Проблема
Уявімо, що у нас є таблиця в базі даних, яка вказує на дату початку та кінця контрактів клієнтів.
Ми хочемо дізнатися, скільки активних контрактів було в кожному місяці, щоб потім побудувати графік у вигляді часового ряду.
Ось таблиця контрактів:
Зверніть увагу: Ми будемо розглядати узагальнену версію цієї проблеми як приклад — метод, описаний у цій статті, працюватиме для будь-якого інтервалу часу та набору даних.
Запитати дані так, щоб побачити, скільки контрактів було розпочато або завершено за місяць, майже не становить труднощів. Але як порахувати контракти, які були активними кожного місяця між їхньою датою початку та завершення, ось так?
Крок 1: Створіть таблицю дат
Перше, що вам потрібно зробити, — це визначити періоди часу, що вас цікавлять, і визначити їхні початкові та кінцеві точки.
Оскільки ми хочемо відстежувати активність по місяцях, нам будуть потрібні дати початку та кінця кожного місяця. Нашою кінцевою метою є таблиця, схожа на цю, яку ми використовуватимемо як CTE в основному запиті:
Пам'ятаєте, як я казав, що SQL має свої підводні камені? Так от, створення такої таблиці дат, коли у вас є тільки права на читання в базі даних, зовсім не є легким завданням. Якщо ви не можете створювати нові таблиці, а всі ваші дати початку та кінця покривають кожен місяць, який ви хочете відстежувати, ви можете зробити ось так (mySQL):
Примітка: Я використовую DISTINCT тут для кращої читабельності.
Видаливши DISTINCT, використовуючи підзапити та групуючи за перші три колонки, теоретично це буде швидше.
SELECT DISTINCT
DATE_FORMAT(contracts.start_date, '%m-%Y') 'month_period',
-- Перший день місяця
DATE_ADD(
LAST_DAY(contracts.start_date),
INTERVAL - DAY(LAST_DAY(contracts.start_date)) + 1 DAY
) 'start_of_month',
-- Останній день місяця
LAST_DAY(contracts.start_date) 'end_of_month'
FROM contracts
-- Оскільки ми пропускаємо деякі місяці, розглядаючи лише дати початку,
-- потрібно також об'єднати дати завершення.
-- Якщо ваші дати початку та завершення покривають усі місяці, ви не потребуєте об'єднання.
UNION
SELECT DISTINCT
DATE_FORMAT(contracts.end_date, '%m-%Y'),
DATE_ADD(
LAST_DAY(contracts.end_date),
INTERVAL - DAY(LAST_DAY(contracts.end_date)) + 1 DAY
),
LAST_DAY(contracts.end_date)
FROM contracts
Ай-ай.
Це не найефективніше чи універсальне рішення, і воно не єдино можливе (пам'ятайте, ваша відповідь, ймовірно, вже десь у глибинах Інтернету!). Якщо вам важко зробити це в SQL для власних цілей, я рекомендую перейти на Python і вставити ваші значення у DataFrame з polars або pandas. Потім ви можете виконати наступний крок у pandasql або за допомогою SQL інтерфейсу polars.
Крок 2: З'єднання ваших даних з таблицею дат
Тепер вступає справжня магія SQL!
Принцип полягає в тому, що ми вибираємо з нашої таблиці дат, а потім об'єднуємо ваші дані з датами початку та завершення.
Якщо подумати, то якщо контракт починається до кінця місяця і завершується після початку місяця, він буде активним хоча б деякий час у цьому місяці.
SELECT [...]
FROM date_cte
INNER JOIN contracts_cte
ON contracts_cte.start_date <= date_cte.end_of_month
AND contracts_cte.end_date >= date_cte.start_of_month
При приєднанні за допомогою порівняння, а не рівності, ми створюємо запис для кожного контракту, який дублюється через активність у будь-який момент кожного місяця.
Стиль вашого скрипта залежить від вас, але я зазвичай використовую CTE для таблиці дат та інший CTE для таблиці даних з лише необхідними характеристиками.
На даний момент наш код виглядає ось так.
-- Крок 1: Створення таблиці дат із початком та кінцем
-- кожного періоду
WITH date_cte AS (
SELECT DISTINCT
DATE_FORMAT(contracts.start_date, ‘%m-%Y’) ‘month_period’,
DATE_ADD(
LAST_DAY(contracts.start_date),
INTERVAL - DAY(LAST_DAY(contracts.start_date)) + 1 DAY
) ‘start_of_month’,
LAST_DAY(contracts.start_date) ‘end_of_month’
FROM
contracts
UNION
SELECT DISTINCT
DATE_FORMAT(contracts.end_date, ‘%m-%Y’),
DATE_ADD(
LAST_DAY(contracts.end_date),
INTERVAL - DAY(LAST_DAY(contracts.end_date)) + 1 DAY
),
LAST_DAY(contracts.end_date)
FROM
contracts
),
-- (Опціональний, але рекомендований крок): Вибір тільки необхідних
-- характеристик із набору даних, який ви хочете відслідковувати в часі
contracts_cte AS (
SELECT
contracts.contract_id,
contracts.client_id,
contracts.start_date,
contracts.end_date
FROM
contracts
)
-- Крок 2: Приєднання ваших даних до таблиці дат
SELECT
date_cte.month_period,
contracts_cte.contract_id
-- Додайте будь-які додаткові характеристики з contracts_cte, якщо хочете
FROM
date_cte
INNER JOIN
contracts_cte
ON contracts_cte.start_date <= date_cte.end_of_month
AND contracts_cte.end_date >= date_cte.start_of_month
Це тепер поверне неагреговану таблицю, як ось ця:
Звісно, ви можете зупинитися на цьому кроці і додати це до вашого улюбленого інструменту візуалізації даних, якщо хочете (але не забудьте рахувати записи за місяць, а не підсумовувати їх!).
Перевага залишення цього набору даних неагрегованим полягає в тому, що ви можете додавати стільки характеристик, скільки хочете, наприклад, ID клієнта або регіон клієнта, для зручного фільтрування та інших операцій.
Якщо ви не турбуєтесь про додаткові характеристики і хочете виконати підрахунки прямо в SQL, не хвилюйтеся. Все, що потрібно зробити, це групувати за стовпцем month_period, а потім підрахувати кількість рядків.
SELECT
date_cte.month_period 'Місячний період',
COUNT(0) '# Активних контрактів'
FROM
date_cte
INNER JOIN
contracts_cte
ON contracts_cte.start_date <= date_cte.end_of_month
AND contracts_cte.end_date >= date_cte.start_of_month
GROUP BY
date_cte.month_period
Цей підхід залишає простір для гнучкості.
Якщо, наприклад, ви хочете підрахувати кількість унікальних клієнтів, активних у певному місяці, замість контрактів, просто додайте це до вашого групування.
SELECT
date_cte.month_period 'Місячний період',
COUNT(0) '# Активних унікальних контрактів клієнтів'
-- COUNT(DISTINCT contracts_cte.client_id) також можливо, якщо ви
-- не хочете додавати це до вашого group by.
FROM
date_cte
INNER JOIN
contracts_cte
ON contracts_cte.start_date <= date_cte.end_of_month
AND contracts_cte.end_date >= date_cte.start_of_month
GROUP BY
date_cte.month_period,
contracts_cte.client_id
І ось ми і маємо результат! Це, звісно, не найелегантніше рішення, але воно цілком можливе. Мені було б цікаво почути, чи допомогло це вам у ваших власних справах. Успіхів у SQL!
Перекладено з: Curating Data Trends Over Time in SQL with Active Records