ClickHouse：从零开始的Windows函数

教程
Pandas

ClickHouse是一款高扩展性、面向列的、针对分析型工作负载优化的关系型数据库管理系统。作为Yandex这家搜索引擎公司开发的开放源码产品，其核心特点之一便是支持高级分析功能，包括窗口函数。

窗口函数最初由SQL Server在20世纪90年代末引入，并逐渐成为众多关系型数据库的标准特性，其中也包括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)：此窗口函数通过计算当前行及其前49行（若不足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

在电商行业中，分析销售数据对于理解客户行为和做出明智的商业决策至关重要。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)`: 此窗口函数通过累加每行中入院日期与出院日期之间的天数，计算每个患者的住院天数累计总和，按患者ID分区，并按入院日期排序。窗口框架通过`PARTITION BY patient_id`指定，将数据根据患者ID分割成多个分区，而`ORDER BY admission_date`则规定了窗口函数处理行的顺序。

查询结果展示每位患者的患者ID、年龄、性别、病情以及在医院度过的累计天数。

在第二个更复杂的SQL请求中，我将使用窗口函数根据患者的总住院天数对患者进行排名。

SQL请求#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窗口函数，根据患者的总住院天数对患者进行排名。

查询结果提供了每位患者的排名、年龄、性别、病情以及累计住院天数。

以下是此查询可能生成的数据样本：


为可视化查询结果，可以使用Python中的多种数据可视化工具，如Matplotlib、Seaborn、Plotly等。以下是使用Matplotlib可视化查询结果的示例：

Python

  

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

# 建立与ClickHouse数据库的连接
engine = create_engine("clickhouse://:/")

# 执行SQL查询并将结果存储在Pandas DataFrame中
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()

在此代码中，我首先使用SQLAlchemy库的create_engine函数建立与ClickHouse数据库的连接。接着，使用read_sql_query函数执行SQL查询，并将结果存储在Pandas DataFrame中。最后，通过Matplotlib库的bar函数创建条形图，其中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