Ефективне маркування даних за допомогою віконних функцій в BigQuery

4 Березня 2024 в 19:21 44

Робота з великими обсягами даних часто вимагає не тільки здатності зберігати та обробляти інформацію, але й уміння ефективно аналізувати та отримувати з неї цінні інсайти. Однією з поширених задач є необхідність визначення певних моментів у часових рядах, наприклад, останнього події перед визначеною датою чи першої події за день. Рішення цієї задачі з використанням традиційних методів може бути не тільки трудомістким, але й неефективним. Тут на допомогу приходять віконні функції SQL, які дозволяють виконувати складні аналітичні операції без потреби в додаткових запитах чи складній логіці програмування.

BigQuery від Google є потужним інструментом для аналізу даних, що підтримує віконні функції, дозволяючи користувачам ефективно розв’язувати задачі, пов’язані з обробкою великих наборів даних. Одним з частих запитань в контексті роботи з датами є визначення останньої події до певної дати та першої події у визначений день. Розглянемо, як можна вирішити цю задачу, використовуючи віконні функції на прикладі таблиці з датами завантаження та прийому.

Представимо, що у нас є таблиця з наступними полями: RECEPTION_DATE (дата прийому) та LOAD_DATE (дата завантаження). Задача полягає в тому, щоб встановити мітку для останнього завантаження перед датою прийому та мітку для першого завантаження в день прийому.

WITH PreProcessed AS (
  SELECT
    *,
    LOAD_DATE &lt; RECEPTION_DATE AS BeforeReception,
    DATE(LOAD_DATE) = DATE(RECEPTION_DATE) AS SameDay
  FROM
    `your_dataset.your_table`
),
RankedLoads AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY RECEPTION_DATE, BeforeReception ORDER BY LOAD_DATE DESC) AS rn_desc,
    ROW_NUMBER() OVER(PARTITION BY RECEPTION_DATE, SameDay ORDER BY LOAD_DATE) AS rn_asc
  FROM
    PreProcessed
  WHERE
    BeforeReception OR SameDay
),
MarkedLoads AS (
  SELECT
    *,
    CASE WHEN rn_desc = 1 AND BeforeReception THEN 1 ELSE 0 END AS LastLoadBeforeDate,
    CASE WHEN rn_asc = 1 AND SameDay THEN 1 ELSE 0 END AS FirstLoadOnDate
  FROM
    RankedLoads
)
SELECT
  RECEPTION_DATE,
  LOAD_DATE,
  LastLoadBeforeDate,
  FirstLoadOnDate
FROM
  MarkedLoads
ORDER BY
  RECEPTION_DATE, LOAD_DATE;

WITH PreProcessed AS (

SELECT

LOAD_DATE < RECEPTION_DATE AS BeforeReception,

DATE(LOAD_DATE) = DATE(RECEPTION_DATE) AS SameDay

FROM

`your_dataset.your_table`

RankedLoads AS (

SELECT

ROW_NUMBER() OVER(PARTITION BY RECEPTION_DATE, BeforeReception ORDER BY LOAD_DATE DESC) AS rn_desc,

ROW_NUMBER() OVER(PARTITION BY RECEPTION_DATE, SameDay ORDER BY LOAD_DATE) AS rn_asc

FROM

PreProcessed

WHERE

BeforeReception OR SameDay

MarkedLoads AS (

SELECT

CASE WHEN rn_desc = 1 AND BeforeReception THEN 1 ELSE 0 END AS LastLoadBeforeDate,

CASE WHEN rn_asc = 1 AND SameDay THEN 1 ELSE 0 END AS FirstLoadOnDate

FROM

RankedLoads

)

SELECT

RECEPTION_DATE,

LOAD_DATE,

LastLoadBeforeDate,

FirstLoadOnDate

FROM

MarkedLoads

ORDER BY

RECEPTION_DATE, LOAD_DATE;

У цьому запиті спочатку визначаємо, які рядки відповідають умовам бути перед датою прийому (BeforeReception) та в той самий день (SameDay). Після цього використовуємо віконні функції ROW_NUMBER() для присвоєння унікальних номерів кожному рядку в межах партиту, визначеного за RECEPTION_DATE та статусом події (BeforeReception або SameDay), з відповідним сортуванням дати завантаження. В результаті, ми можемо ідентифікувати останнє завантаження перед датою прийому та перше завантаження в день прийому, присвоївши відповідні мітки.

Використання віконних функцій в SQL для аналізу часових рядів значно спрощує роботу з даними, дозволяючи вирішувати складні задачі без необхідності реалізації громіздких процедур обробки даних. Це не тільки підвищує продуктивність запитів, але й робить код більш читабельним і легшим для розуміння. В контексті BigQuery, де оптимізація запитів має критичне значення для ефективної роботи з великими обсягами даних, віконні функції надають потужний інструмент для дослідників даних, аналітиків та розробників.

Підсумовуючи, віконні функції в SQL пропонують елегантне рішення для вирішення низки задач аналізу даних, зокрема, для маркування специфічних подій у часових рядах. BigQuery як потужне сховище даних з високим рівнем підтримки SQL-стандартів, включаючи віконні функції, є ідеальним інструментом для розробки та виконання складних аналітичних запитів, що відкриває широкі можливості для отримання цінних інсайтів з даних.