ClickHouse：從零開始的Windows函數

教學
Pandas

ClickHouse 是一個高度可擴展、面向列的關係型資料庫管理系統，專為分析型工作負載優化。這是一個由搜索引擎公司Yandex開發的開源產品。ClickHouse的一個關鍵特性是其對高級分析功能的支援，包括窗口函數。

窗口函數最初由SQL Server在1990年代末引入，自那時起，已成為許多關係型資料庫的標準功能，包括ClickHouse。如今，窗口函數是數據分析師和開發者不可或缺的工具，並在多個行業中廣泛使用。

這些函數，也被稱為分析函數，是一類基於滑動窗口的行進行計算的函數。它們用於對數據集進行各種類型的分析，如計算累計總和、移動平均和排名。窗口函數是數據分析的強大工具，並能顯著簡化編寫複雜查詢的過程。

ClickHouse支援多種窗口函數，包括用於排名、百分位排名、累積分布、行號和累計總和的內置函數。此外，它還支援用戶定義的窗口函數，允許用戶為特定用例創建自定義函數。

在本文中，我將介紹窗口函數的概念，並全面概述ClickHouse中可用的窗口函數。我還將提供如何在實際場景中使用這些函數的示例。本文適用於已熟悉SQL並希望深入了解ClickHouse中窗口函數的經驗豐富的開發人員。

實際應用窗口函數的例子

窗口函數是數據分析的強大工具，廣泛應用於金融、電子商務和醫療保健等行業。

財務分析

窗口函數最早的應用之一是在財務分析中。在股市分析中，開發人員可以使用窗口函數來計算移動平均值、運行總和及百分比變化。例如，計算股票收盤價的50天移動平均是一個常見的財務窗口函數應用案例。另一個例子是計算公司在一段時間內的收益總和。

E-commerce Analytics

在電子商務中，窗口函數可用於分析客戶行為和銷售模式。開發人員可以使用窗口函數來計算每個產品的銷售總和、基於銷售的產品排名以及隨時間的銷售增長百分比。此外，窗口函數還可用於通過計算客戶在一段時間內的平均購買頻率和平均購買價值來分析客戶行為。

醫療保健分析

在醫療保健領域，Windows 功能可用於分析患者數據，例如生命體徵、實驗室結果和藥物使用情況。例如，開發人員可以利用Windows功能來計算患者心率的移動平均值、患者藥物劑量的累計總量，以及基於實驗室結果的患者排名。

這些僅是開發人員在眾多實際場景中使用Windows功能的幾個例子。關鍵在於，Windows功能可用於對各種數據集進行高級分析，並能大幅簡化複雜查詢的編寫。

ClickHouse中Windows函數的語法

在ClickHouse中，Windows函數用於查詢的SELECT子句中，以對一組行進行計算。在ClickHouse中使用Windows函數的基本語法如下：

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])：這是您想要使用的Windows函數的名稱及其參數列表。
  3. AS [alias_name]：此子句為可選，用於為Windows函數的輸出指定別名。
  4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]])：這是為Windows函數指定窗口框架的規範。
  • 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日均線，方法是取當前行及其前49行（若天數不足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的Windows函數可用於進行複雜的電商數據分析，例如計算累計總額和根據銷售量對產品進行排名。

假設我們有一個僅包含單一電商網站每日銷售資訊的表格。為了根據總銷售量對產品進行排名，我們將計算銷售的累計總額。

數據生成：

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