ClickHouse: Функции Windows С нуля

Учебники
Pandas

ClickHouse — это высокомасштабируемая, столбцовая, реляционная система управления базами данных, оптимизированная для аналитических нагрузок. Это open-source продукт, разработанный компанией Yandex, известной как поисковая система. Одной из ключевых особенностей ClickHouse является поддержка продвинутых аналитических функций, включая функции окна.

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

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

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

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

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

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

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

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

E-commerce Analytics

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

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

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

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

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

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

SQL

  

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:

SQL

  

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

I use the SUM windows function to calculate the running total of sales for each product, grouped by the product_id column. The window frame is specified with PARTITION BY product_id to divide the result set into partitions based on the product_id and ORDER BY date to specify the order in which the windows function processes the rows. The output of the windows function is given an alias name running_total.

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

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

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

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

Генерация данных:

SQL

  

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;

I create a table stock_prices to store daily stock prices for the symbol AAAA. I then insert randomly generated data into the table for the years 2022–2023.

Запрос SQL:

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';

I use windows functions to perform financial analysis on the stock price data.

  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: Эта функция окна вычисляет накопленную сумму ежедневного возврата инвестиций, суммируя ежедневные возвраты, которые вычисляются как разница между текущей ценой закрытия и предыдущей ценой закрытия, деленная на предыдущую цену закрытия. Функция lag используется для получения значения предыдущей строки в той же паре, а окно задается с помощью ORDER BY date для обеспечения правильного порядка вычисления возвратов.

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

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

E-commerce Analytics With Windows Functions

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

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

Генерация данных:

SQL

  

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;

I create a table sales_data to store daily sales data for a single e-commerce store. I then insert randomly generated data into the table for the years 2022–2023.

Запрос SQL:

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;

I use windows functions to perform e-commerce analytics on 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 предлагают передовые возможности анализа данных в области здравоохранения, включая возможность оценивать пациентов по различным критериям и вычислять текущие суммы.

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

Генерация данных:

SQL

  

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;

I create a table patient_data to store patient data for a hospital. I then inserted randomly generated data into the table for the years 2022–2023. Each row represents a patient’s hospitalization, including the admission date, discharge date, patient ID, age, gender, and medical condition.

Запрос SQL #1:

SQL

  

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 Request #2:

SQL

  

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. (SELECT ...): Внутренний запрос вычисляет промежуточную сумму дней госпитализации для каждого пациента с использованием функции окна SUM. Результат внутреннего запроса затем используется в качестве входных данных для внешнего запроса, где функция окна ROW_NUMBER применяется для ранжирования пациентов по их общему количеству дней госпитализации.

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

Вот пример данных, которые может генерировать этот запрос:


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

Python

  

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

# Установка соединения с базой данных ClickHouse
engine = create_engine("clickhouse://:/")

# Выполнение SQL-запроса и сохранение результатов в DataFrame Pandas
df = pd.read_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()

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

I successfully used ClickHouse’s windows functions in those examples to evaluate healthcare data and rank patients based on their total hospitalization days. This analysis can uncover patterns and trends in patient data, which can help to inform clinical decision-making and improve patient outcomes.

Заключение

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

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

Source:
https://dzone.com/articles/clickhouse-windows-functions-from-scratch