Оконные функции ClickHouse
С помощью оконных функций 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];
Рассмотрим каждую часть этого синтаксиса по отдельности:
-
[column_list]: список столбцов, возвращаемых в запросе; - [windows_function_name]([argument_list]): имя оконной функции, которую Вы хотите использовать, и список ее аргументов.AS
- [alias_name]: предложение для присвоения псевдонима выводу оконной функции, применяемое по желанию (не является обязательной частью).
-
OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]])- спецификация фрейма окна для оконной функции.. -
PARTITIONBY[partition_column_list]: предложение для разделения результирующего набора на партиции исходя из значений в указанных столбцах, применяемое по желанию (не является обязательной частью). -
ORDERBY[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';
Финансовый анализ данных по котировкам акции выполнено с помощью оконных функций.
- 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, которым указывается диапазон строк для вычисления искомого значения.
- 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;
Проанализируем данные о продажах с помощью оконных функций:
- SUM(sales) OVER (PARTITION BY product_name ORDER BY date): рассчитываем нарастающий итог продаж по каждому продукту. Продажи суммируются по каждой строке, которые разделены на партиции по названию продукта и упорядочены по дате. Фрейм окна задается с помощью PARTITION BY product_name, что позволяет разделить данные на партиции по названию продукта. ORDER BY date указывает порядок, в каком оконная функция должна обработать строки.
- 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 годы. В каждой строке сохраним сведения о госпитализации пациентов: дата поступления/выписки, id пациента, возраст, пол и состояние здоровья в целом.
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): в каждой строке суммируются дни, начиная с даты поступления и заканчивая днем выписки. Строки разделены на партиции по id пациента и упорядочены по дате поступления. Фрейм окна задается с помощью PARTITION BY patient_id, что позволяет разделить данные на партиции по id пациента, а также с помощью ORDER BY admission_date для указания, в каком порядка, в котором оконная функция должна обработать строки.
В результате получаем данные по каждому пациенту: id, возраст, пол, состояние здоровья и общее количество дней, проведенных в больнице.
Во втором более сложном 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 )
- Функция ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank присваивает каждому пациенту уникальную позицию в соответствии с убывающим порядком ORDER BY running_total_days: чем больше нарастающий итог дней госпитализации, тем она ниже. ROW_NUMBER() — это встроенная функция ClickHouse, которая присваивает каждой строке уникальный номер.
- Функцией SUM внутреннего запроса (SELECT ...) вычисляется нарастающий итог дней госпитализации каждого пациента. Чуть позже результат этого запроса станет входными данными внешнего запроса, где для упорядочения пациентов по общему количеству дней госпитализации будет применена оконная функция ROW_NUMBER.
В результате мы получим данные по каждому пациенту: id, возраст, пол, состояние здоровья и нарастающий итог дней, проведенных в больнице.
Пример таблицы, содержащей данные, полученные в результате нашего запроса:
Результаты запроса можно визуализировать на Python с помощью Seaborn, Plotly и т. д. На Matplotlib визуализация будет выглядеть следующим образом:
import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import create_engine
# Establish a connection to the ClickHouse database
engine = create_engine("clickhouse://<host>:<port>/<database>")
# Execute the SQL query and store the results in a Pandas DataFrame
df = pd.read_sql_query("<SQL query>", engine)
# Plot the results using a bar plot
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()
В этом коде функцией create_engine из библиотеки SQLAlchemy в первую очередь устанавливается подключение к ClickHouse. Затем функцией read_sql_query выполняем SQL-запрос и сохраняем результаты во фрейме данных Pandas. Наконец, функцией bar библиотеки Matplotlib на оси x столбчатой диаграммы отображаем идентификатор пациента, на оси y — нарастающий итог дней госпитализации, цвет каждого столбца — это положение пациента.
В данном случае оконные функции ClickHouse помогают оценить медицинские данные пациентов исходя из общего количества дней, проведенных ими в больнице. Результаты такого анализа позволяют эффективно скорректировать терапию пациентов и сделать все возможное для достижения наилучших результатов лечения.
Заключение
Оконные функции — незаменимый инструмент для проведения сложного анализа данных в ClickHouse. Если бы их не было, нужно было бы писать огромное количество запросов или даже предварительно обрабатывать данные. С этими же функциями задача анализа и агрегирования данных становится намного проще.
При этом важно понимать, что оконные функции могут быть сопряжены с большими вычислительными затратами, особенно при обработке больших наборов данных. В этом случае на помощь придут индексы. В любом случае помните о том, что оконные функции влияют на производительность работы системы в целом, поэтому старайтесь применять их разумно.
