ClickHouse: Windows関数のスクラッチから

チュートリアル
Pandas

ClickHouseは、分析ワークロードに最適化された高いスケーラビリティを持つ列指向リレーショナルデータベース管理システムです。これは、検索エンジン会社Yandexによって開発されたオープンソース製品であり、ClickHouseの主要な特徴の一つは、高度な分析機能、特にウィンドウ関数のサポートです。

ウィンドウ関数は、1990年代後半にSQL Serverに初めて導入され、その後、ClickHouseを含む多くのリレーショナルデータベースの標準機能となりました。現在では、ウィンドウ関数はデータアナリストや開発者にとって必須のツールであり、多くの業界で広く使用されています。

これらの関数は、分析関数とも呼ばれ、行のスライディングウィンドウに基づいて計算を行う関数の一種です。ランニングトータル、移動平均、ランキングの計算など、データセットに対してさまざまなタイプの分析を実行するために使用されます。ウィンドウ関数はデータ分析の強力なツールであり、複雑なクエリを書くことを大幅に簡素化します。

ClickHouseは、ランク、パーセントランク、累積分布、行番号、ランニングトータルなどの組み込み関数を含む幅広いウィンドウ関数をサポートしています。さらに、ユーザー定義のウィンドウ関数もサポートしており、ユーザーは特定のユースケースのためにカスタム関数を作成できます。

この記事では、ウィンドウ関数の概念を紹介し、ClickHouseで利用可能なウィンドウ関数について包括的に概観します。また、これらの関数を現実のシナリオでどのように使用するかの例を提供します。この記事は、SQLに精通しており、ClickHouseのウィンドウ関数について学びたい経験豊富な開発者向けです。

ウィンドウ関数の実世界例

ウィンドウ関数はデータ分析の強力なツールであり、金融、電子商取引、医療などさまざまな業界で広く使用されています。

金融分析

ウィンドウ関数の初期の応用の1つは金融分析でした。株式市場分析では、開発者はウィンドウ関数を使用して移動平均、累積合計、変動率を計算できます。例えば、株式の終値の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行（または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の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): このWindows関数は、商品名でパーティション分割され、日付で並べ替えられた各行の売上を合計することで、各商品の売上累積合計を計算します。ウィンドウフレームはPARTITION BY product_nameで商品名に基づいてデータをパーティション分割し、ORDER BY dateでWindows関数が行を処理する順序を指定します。
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): このWindows関数は、カテゴリ内で合計売上に基づいて各商品のランクを計算します。ROW_NUMBER関数は、パーティション内の各行に対して一意の番号を生成し、ウィンドウフレームはPARTITION BY product_categoryで商品カテゴリに基づいてデータをパーティション分割し、ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESCで各パーティション内のデータを売上累積合計に基づいて降順に並べ替えます。

クエリの出力は、商品名、商品カテゴリ、売上の累計、および各商品の商品カテゴリ内での総売上に基づくランクを返します。

ClickHouseのウィンドウ関数を使用することで、eコマースのアナリストはリアルタイムで高度な販売データ分析を実行し、結果に基づいて知識豊富な意思決定を行うことができます。

ヘルスケアアナリティクスとウィンドウ関数

ヘルスケア部門が患者の成果を改善し、患者のケアに関する賢明な決定を行うためには、患者データの分析が不可欠です。 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、年齢、性別、症状、および病院で過ごした日数のランニングトータルを示します。

2番目のより複雑な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