ClickHouse: оконные функции с нуля

Оконные функции в ClickHouse

ClickHouse  —  легко масштабируемая столбцовая, реляционная СУБД, оптимизированная под аналитические рабочие нагрузки. Это продукт с открытым исходным кодом, разработанный в «Яндексе». Одна из ключевых особенностей ClickHouse  —  поддержка продвинутых аналитических функций, в том числе оконных.

Оконные функции впервые появились в конце 1990-х годов в SQL Server и с тех пор стали стандартным функционалом многих реляционных баз данных, включая ClickHouse. Сегодня оконные функции  —  незаменимый инструмент аналитиков данных и разработчиков, широко применяемый во многих сферах.

Это класс называемых также аналитическими функций, в которых вычисления выполняются на основе скользящего окна строк. Они применяются в разного рода анализе наборов данных: расчете нарастающих итогов, скользящих средних и ранжирования. Оконные функции  —  мощный инструмент анализа данных, с которым значительно проще писать сложные запросы.

В ClickHouse поддерживаются разнообразные оконные функции, в том числе встроенные функции ранжирования, percent_rank, интегральная функция распределения, нумерации строк и нарастающих итогов. А еще пользовательские оконные функции, создаваемые под конкретные случаи применения.

В этой статье мы разберем концепцию оконных функций, предоставим исчерпывающий обзор тех, что доступны в ClickHouse, и приведем примеры их использования в реальных сценариях. Статья адресована опытным разработчикам, уже знакомым с SQL и желающим узнать больше об оконных функциях в ClickHouse.

Реальные примеры использования оконных функций

Оконные функции  —  это мощный инструмент анализа данных, широко применяемый во многих сферах: финансах, электронной коммерции, здравоохранении.

Финансовый анализ

Одна из первых сфер применения оконных функций  —  финансовый анализ, где с их помощью разработчики рассчитывают скользящие средние, нарастающие итоги и процентные изменения. Так, вычисление 50-дневной скользящей средней цены закрытия акции  —  это типичный пример использования оконных функций в финансах. Другой пример  —  расчет нарастающего итога прибыли компании за период времени.

Аналитика электронной коммерции

В электронной коммерции оконными функциями анализируют поведение покупателей и структуру продаж. Разработчики применяют оконные функции для расчета нарастающего итога продаж по каждому продукту, ранжирования продуктов по их продажам и процентного роста продаж с течением времени. Поведение покупателей анализируется вычислением в оконных функциях средней частоты покупок и средней стоимости покупки клиента за период времени.

Аналитика в здравоохранении

Оконными функциями в здравоохранении анализируют данные пациента: основные физиологические показатели, результаты лабораторных исследований, прием медикаментов. Например, рассчитывают скользящее среднее частоты сердечных сокращений, нарастающий итог принятых доз препарата, ранжирование пациентов по результатам лабораторных исследований.

Это лишь малая часть примеров применения разработчиками оконных функций в реальных сценариях. Ключевой вывод  —  с оконными функциями выполняется расширенный анализ разнообразных наборов данных и значительно упрощаются сложные запросы.

Синтаксис оконных функций в ClickHouse

В ClickHouse оконные функции применяются в вычислениях, выполняемых над набором строк, внутри предложения SELECT запроса. Вот базовый их синтаксис:

SELECT
  [column_list],
  [windows_function_name]([argument_list])
    OVER ([PARTITION BY [partition_column_list]]
         [ORDER BY [order_column_list]]
         [ROWS [BETWEEN [start_offset] AND [end_offset]]])
  AS [alias_name]
FROM [table_name];

Разберем каждую его часть:

1. [column_list]  —  список возвращаемых в запросе столбцов.
2. [windows_function_name]([argument_list])  —  имя оконной функции и список ее аргументов.
3. AS [alias_name]  —  необязательное предложение для присвоения псевдонима выводу оконной функции.
4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]])  —  спецификация фрейма окна для оконной функции.

• PARTITION BY [partition_column_list]  —  необязательное предложение для разделения результирующего набора на партиции, исходя из значений в указанных столбцах.
• ORDER BY [order_column_list]  —  обязательное предложение для указания, в каком порядке оконной функцией обрабатываются строки.
• ROWS [BETWEEN [start_offset] AND [end_offset]]  —  необязательное предложение с указанием диапазона строк для вычислений в оконной функции. start_offset и end_offset  —  положительные или отрицательные целые числа или специальные значения UNBOUNDED PRECEDING или CURRENT ROW.

Вот пример оконной функции в ClickHouse:

SELECT
  date,
  product_id,
  sales,
  SUM(sales) OVER (PARTITION BY product_id ORDER BY date) AS running_total
FROM sales_data;

Здесь оконной функцией SUM вычисляется нарастающий итог продаж по каждому продукту, сгруппированный по столбцу product_id. Фрейм окна задается с PARTITION BY product_id для разделения результирующего набора на партиции по product_id и с ORDER BY date для указания, в каком порядке оконной функцией обрабатываются строки. Выводу оконной функции присваивается псевдоним running_total.

В ClickHouse оконные функции используются не в WHERE и HAVING, а только в предложении SELECT запроса, а также с другими функциями, например агрегатными, для более сложного анализа данных.

Финансовый анализ с оконными функциями

В финансовой сфере отслеживание эффективности инвестиций по времени очень важно для принятия решений. Сложный анализ финансовых данных, например расчет скользящих средних и нарастающих итогов, выполняется в ClickHouse с помощью оконных функций.

Рассмотрим сценарий с таблицей ежедневных котировок акции. Рассчитаем 50-дневную скользящую среднюю цены закрытия и нарастающий итог по ежедневному доходу от инвестиций.

Формирование данных:

CREATE TABLE stock_prices (
  date Date,
  symbol String,
  open Float32,
  close Float32,
  high Float32,
  low Float32,
  volume UInt64
) ENGINE = MergeTree(date, (symbol, date), 8192);

INSERT INTO stock_prices
SELECT
  toDate('yyyy-MM-dd', d),
  'AAAA',
  rand(),
  rand(),
  rand(),
  rand(),
  rand() * 100000
FROM generateDates('2022-01-01', '2023-02-10') d;

Для хранения ежедневных котировок акции, представленной символом AAAA, создаем таблицу stock_prices и вставляем в нее случайно сгенерированные данные за 2022–2023 годы.

SQL-запрос:

SELECT
  date,
  symbol,
  close,
  AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS moving_average,
  SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100 AS running_return
FROM stock_prices
WHERE symbol = 'AAAA';

Финансовый анализ данных по котировкам выполняем оконными функциями:

1. AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)  —  рассчитывается 50-дневная скользящая средняя цены закрытия: берется среднее значение упорядоченных по дате 50 строк  —  или меньше, если дней менее 50,  —  до текущей строки, включая и ее. Фрейм окна задается с ORDER BY date для указания, в каком порядке оконной функцией обрабатываются строки, и с ROWS BETWEEN 49 PRECEDING AND CURRENT ROW, которым указывается диапазон строк для вычислений в оконной функции.
2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100  —  рассчитывается нарастающий итог по ежедневному доходу от инвестиций, а именно: суммируется доходность по каждому дню, то есть текущая цена закрытия, деленная на предыдущую, минус 1. Функцией lag извлекается значение предыдущей строки той же партиции. Чтобы возвращаемые значения вычислялись в корректном порядке, фрейм окна задается с ORDER BY date.

В выводе запроса возвращаются дата, символ, цена закрытия, 50-дневная скользящая средняя и нарастающий итог по ежедневному доходу от инвестиций для символа AAAA.

Используя оконные функции ClickHouse, финансовые аналитики в режиме реального времени выполняют сложный анализ финансовых данных и по его результатам принимают взвешенные решения.

Аналитика электронной коммерции с оконными функциями

В сфере электронной коммерции анализ данных о продажах очень важен для понимания поведения покупателей и принятия обоснованных бизнес-решений. Сложный анализ данных, например вычисление нарастающих итогов и ранжирование продуктов по продажам, тоже выполняется в ClickHouse с оконными функциями.

Возьмем таблицу с данными по ежедневным продажам интернет-магазина. Упорядочим продукты по объему продаж, рассчитав нарастающий итог.

Формирование данных:

CREATE TABLE sales_data (
  date Date,
  product_name String,
  product_category String,
  sales UInt64
) ENGINE = MergeTree(date, (product_name, date), 8192);

INSERT INTO sales_data
SELECT
  toDate('yyyy-MM-dd', d),
  'Product ' || toString(intDiv(rand() * 100, 1)),
  'Category ' || toString(intDiv(rand() * 5, 1)),
  rand() * 1000
FROM generateDates('2022-01-01', '2023-02-10') d;

Для хранения данных по ежедневным продажам интернет-магазина создаем таблицу sales_data и вставляем в нее случайно сгенерированные данные за 2022–2023 годы.

SQL-запрос:

SELECT
  product_name,
  product_category,
  SUM(sales) OVER (PARTITION BY product_name ORDER BY date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC) AS rank
FROM sales_data;

Анализ данных о продажах выполняем оконными функциями:

1. SUM(sales) OVER (PARTITION BY product_name ORDER BY date)  —  рассчитывается нарастающий итог продаж каждого продукта. Продажи суммируются по каждой строке, которые разделены по названию продукта на партиции и упорядочены по дате. Фрейм окна задается с PARTITION BY product_name для разделения данных на партиции по названию продукта и с ORDER BY date для указания, в каком порядке оконной функцией обрабатываются строки.
2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC)  —  выполняется ранжирование каждого продукта в его категории по объему продаж. Функцией ROW_NUMBER для каждой строки в партиции генерируется уникальный номер, а фрейм окна задается с PARTITION BY product_category для разделения данных на партиции по категории продукта и с ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC для сортировки данных каждой партиции в порядке убывания нарастающего итога продаж.

В выводе запроса возвращаются название и категория продукта, нарастающий итог продаж и положение каждого продукта в своей категории по объему продаж.

Используя оконные функции ClickHouse, аналитики электронной коммерции в режиме реального времени выполняют сложный анализ данных о продажах и по его результатам принимают взвешенные решения.

Оконные функции в аналитике здравоохранения

Чтобы повышать результаты лечения и принимать в этой связи разумные решения, необходим анализ данных о пациентах. С оконными функциями ClickHouse расширяются возможности анализа медицинских данных, в том числе оценки пациентов по различным критериям и вычисления нарастающих итогов.

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

Формирование данных:

CREATE TABLE patient_data (
  admission_date Date,
  discharge_date Date,
  patient_id String,
  age UInt16,
  gender String,
  condition String
) ENGINE = MergeTree(admission_date, (patient_id, admission_date), 8192);

INSERT INTO patient_data
SELECT
  toDate('yyyy-MM-dd', d1),
  toDate('yyyy-MM-dd', d2),
  'Patient ' || toString(intDiv(rand() * 10000, 1)),
  rand() % 90 + 10,
  if(rand() % 2 = 0, 'Male', 'Female'),
  'Condition ' || toString(intDiv(rand() * 100, 1))
FROM generateDates('2022-01-01', '2023-02-10') d1
JOIN generateDates('2022-01-01', '2023-02-10') d2 ON d1 <= d2;

Для хранения информации о пациентах создаем таблицу patient_data и вставляем в нее случайно сгенерированные данные за 2022–2023 годы. В каждой строке содержатся сведения о госпитализации: дата поступления/выписки, идентификатор пациента, возраст, пол и состояние здоровья.

SQL-запрос № 1:

SELECT
  patient_id,
  age,
  gender,
  condition,
  SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
FROM patient_data;

Нарастающий итог дней, проведенных в больнице каждым пациентом, вычисляем оконной функцией SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): в каждой строке суммируются дни между датами поступления и выписки, строки разделены по идентификатору пациента на партиции и упорядочены по дате поступления. Фрейм окна задается с PARTITION BY patient_id для разделения данных на партиции по идентификатору пациента и с ORDER BY admission_date для указания, в каком порядке оконной функцией обрабатываются строки.

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

В SQL-запросе посложнее, используя оконные функции, упорядочим пациентов по общему количеству дней госпитализации.

SQL-запрос № 2:

SELECT
  patient_id,
  age,
  gender,
  condition,
  running_total_days,
  ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank
FROM (
  SELECT
    patient_id,
    age,
    gender,
    condition,
    SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
  FROM patient_data
)

1. Функцией ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank каждому пациенту присваивается уникальная позиция в соответствии с убывающим порядком ORDER BY running_total_days DESC: чем больше нарастающий итог дней госпитализации, тем она ниже. ROW_NUMBER()  —  это встроенная функция ClickHouse, ею каждой строке в пределах задаваемого фрейма окна присваивается уникальный номер.
2. Функцией SUM внутреннего запроса (SELECT ...) вычисляется нарастающий итог дней госпитализации каждого пациента. Результат запроса затем становится входными данными внешнего запроса, где оконная функция ROW_NUMBER применяется для упорядочения пациентов по общему количеству дней госпитализации.

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

Вот пример данных, генерируемых этим запросом:

Результаты запроса визуализируются на Python с помощью Seaborn, Plotly и т. д. Вот их визуализация на Matplotlib:

import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import create_engine

# Устанавливаем подключение к ClickHouse
engine = create_engine("clickhouse://<host>:<port>/<database>")

# Выполняем SQL-запрос и сохраняем результаты во фрейме данных Pandas
df = pd.read_sql_query("<SQL query>", engine)

# Отображаем результаты на столбчатой диаграмме
plt.bar(df['patient_id'], df['running_total_days'], color=df['rank'])
plt.xlabel("Patient ID")
plt.ylabel("Running Total of Hospitalization Days")
plt.title("Healthcare Analytics with Windows Functions in ClickHouse")
plt.show()

В этом коде сначала устанавливаем подключение к ClickHouse функцией create_engine из библиотеки SQLAlchemy. Затем функцией read_sql_query выполняем SQL-запрос и сохраняем результаты во фрейме данных Pandas. Наконец, функцией bar библиотеки Matplotlib на оси x столбчатой диаграммы отображаем идентификатор пациента, на оси y  —  нарастающий итог дней госпитализации, а цвет каждого столбца  —  это позиция пациента.

В этих примерах оконными функциями ClickHouse оцениваются медицинские данные, по общему количеству дней госпитализации упорядочиваются пациенты. При таком анализе в данных пациента обнаруживаются закономерности и тенденции, используемые для принятия взвешенных решений и повышения результатов лечения.

Заключение

Оконные функции  —  это мощный инструмент для обработки операций анализа и агрегирования сложных данных в ClickHouse, компактный и эффективный способ вычисления внутри запроса нарастающих итогов, ранжирования, процентилей. Без оконных функций для таких расчетов требуется много запросов или даже предварительная обработка данных. С этим же инструментом процесс анализа и агрегирования данных намного проще.

Однако оконные функции бывают чреваты большими вычислительными затратами, особенно для больших наборов данных. Проблема решается применением индексов и тщательной проработкой запросов. Важно при этом помнить о влиянии оконных функций на производительность и применять их разумно.

Читайте также:

• ClickHouse + Kafka = ❤
• Конвейер данных в реальном времени с Kafka и ClickHouse
• Как выбрать СУБД для решения ваших задач?

Читайте нас в Telegram, VK и Дзен

---

Перевод статьи Taras Baranyuk: ClickHouse: windows functions from scratch