SQLを利用する人であれば、ウィンドウ（Window）関数という概念を聞いたことがあると思います。
ウィンドウ関数を使用することで、複雑な処理が必要だったSQL文の操作が簡単になり、ウィンドウ関数を使わなかったときと比べてシンプルな記述で高度な抽出・集計ができるようになります。
この記事では、ウィンドウ関数について説明し、その使い方や効果的なシチュエーションを解説します。
※なお、本記事で使用している関数の仕様はGoogle Bigqueryにもとづいており、他のデータベースでは異なる場合があります。
ウィンドウ関数の基本的な使い方
ウィンドウ関数は、以下のような構文で記述します。
関数名() OVER (PARTITION BY 列名1 ORDER BY 列名2)
この構文では、関数名の部分に使用する関数を指定し、OVER句でデータをグループ化します。
その後、PARTITION BY句でグループ分けをする列名を指定し、ORDER BY句でデータの並び順を指定します。
ウィンドウ関数には集計関数やランキング関数を使用できます。
代表的なウィンドウ関数としては、以下のものがあります。
- SUM
- AVG
- COUNT
- ROW_NUMBER
- RANK
- DENSE_RANK
- NTILE
ウィンドウ関数の構文と使い方
ウィンドウ関数の基本的な構文をもう少し詳しく見ていきましょう。
OVER句には、次の3つの節が含まれています。
- PARTITION BY：集計するために、行をグループ化するための式を指定します。
- ORDER BY：グループ内の行の順序を指定します。
- ROWS：フレーム仕様を指定します。フレーム仕様は、グループ内の行の範囲を定義します。
ウィンドウ関数は、OVER句の中でパーティション化や並び替えを指定できます。
この指定によって、集計や順位付けをする範囲を指定できます。
パーティション化は、複数のグループに分割して集計やランキングつけするための指定であり、並び替えは、集計やランキングの基準となる列の指定です。
Partition ByとOrder Byについて説明
PARTITION BY節は、ウィンドウ関数で使用される行をグループ化するための式を指定します。
グループ化された行に対して、ウィンドウ関数は個別に実行されます。
例えば、以下のクエリを考えてみましょう。
|
1 2 3 4 5 |
SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1) FROM table_name; |
このクエリでは、column1の値に基づいて、table_nameテーブルのデータをグループ化し、各グループのcolumn3の合計値を計算します。
つまり、column1の値ごとに、個別にSUM関数が実行されます。
ORDER BY節は、グループ内の行の順序を指定します。
これにより、ウィンドウ関数がフレームを設定できます。
例えば、このクエリを考えてみましょう。
|
1 2 3 4 5 |
SELECT column1, column2, SUM(column3) OVER (ORDER BY column2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM table_name; |
このクエリでは、column2の値に基づいて、table_nameテーブルのデータをソートし、各行に対してcolumn3の合計値を計算します。
ROWS BETWEEN句は、フレームを設定し、計算に使用する行の範囲を指定します。
この場合、UNBOUNDED PRECEDING（最初の行）から現在の行までの範囲で計算されます。
ウィンドウ関数の例題と実行結果
それでは、ウィンドウ関数を使った具体的な事例を見ていきましょう。
以下のようなsalesテーブルがあるとします。
| sales_id | sales_date | sales_amount | product_id |
| 1 | 2022-01-01 | 1000 | 1 |
| 2 | 2022-01-02 | 2000 | 1 |
| 3 | 2022-01-03 | 3000 | 2 |
| 4 | 2022-01-04 | 4000 | 2 |
| 5 | 2022-01-05 | 5000 | 3 |
このテーブルから、各商品の売上累計を計算するクエリを考えてみましょう。
|
1 2 3 4 5 6 7 |
SELECT sales_id, sales_date, sales_amount, product_id, SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales; |
このクエリでは、salesテーブルからsales_amount列の合計値を計算し、商品ごとにグループ化します。
さらに、各グループ内でsales_date列の昇順に並べ替えて、running_totalという名前の列に計算結果を格納します。
実行結果は以下のようになります。
| sales_id | sales_date | sales_amount | product_id | running_total |
| 1 | 2022-01-01 | 1000 | 1 | 1000 |
| 2 | 2022-01-02 | 2000 | 1 | 3000 |
| 3 | 2022-01-03 | 3000 | 2 | 3000 |
| 4 | 2022-01-04 | 4000 | 2 | 7000 |
| 5 | 2022-01-05 | 5000 | 3 | 5000 |
この結果から、各商品の売上累計が計算されていることが分かります
ウィンドウ関数を活用できるシチュエーション
ウィンドウ関数を使用することで、SQLの複雑な処理を簡単に実行できます。
例えばランキングをつける場合、ウィンドウ関数を使用することで、グループ内でランキングを計算できます。
また、移動平均を求める場合やグループごとの比率を算出する場合にも、ウィンドウ関数を活用できます。
ランキング処理での使用例
例えば、先程のsalesテーブルから商品の売り上げランキングを求めたい場合、以下のようなクエリになります。
|
1 2 3 4 5 6 7 |
SELECT sales_id, sales_date, sales_amount, product_id, RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank FROM sales; |
このクエリではsalesテーブルの中から売り上げ金額が高い順にランキングをつけています。
RANK関数により、ランク付けの処理が行われます。
また、OVER句により、ランク付けの対象となる列（この例ではsales_amount）と順序（この例では降順）を指定し、sales_rankという名前でランクを表す新しい列が追加されます。
実行結果は以下のようになります。
| sales_id | sales_date | sales_amount | product_id | sales_rank |
| 1 | 2022-01-01 | 1000 | 1 | 5 |
| 2 | 2022-01-02 | 2000 | 1 | 4 |
| 3 | 2022-01-03 | 3000 | 2 | 3 |
| 4 | 2022-01-04 | 4000 | 2 | 2 |
| 5 | 2022-01-05 | 5000 | 3 | 1 |
移動平均を求める場合の例
時系列データを扱う場合、移動平均を求めることがあります。
例えば、ある商品の1週間ごとの平均売り上げを求める場合、以下のようなクエリになります。
|
1 2 3 4 5 6 7 |
SELECT sales_id, sales_date, sales_amount, product_id, AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_average FROM sales; |
上記のクエリでは、sales_dataテーブルの中から日付順に並べ、AVG関数により直近3日間の平均売り上げを計算しています。
また、OVER句により、移動平均の対象となる列（この例ではsales_amount）と範囲（この例では直近3日間）を指定しています。
実行結果は以下のようになります。
| sales_id | sales_date | sales_amount | product_id | moving_average |
| 1 | 2022-01-01 | 1000 | 1 | 1000 |
| 2 | 2022-01-02 | 2000 | 1 | 1500 |
| 3 | 2022-01-03 | 3000 | 2 | 2000 |
| 4 | 2022-01-04 | 4000 | 2 | 3000 |
| 5 | 2022-01-05 | 5000 | 3 | 4000 |
グループごとの比率を算出する場合の例
グループごとにデータを集計する場合、各グループの中での割合を求めることがあります。例えば、ある商品の売り上げが全体の売り上げに占める割合を求める場合、以下のようなクエリになります。
|
1 2 3 4 5 6 7 |
SELECT sales_id, sales_date, sales_amount, product_id, SUM(sales_amount) OVER (PARTITION BY product_id) / SUM(sales_amount) OVER () AS sales_ratio FROM sales; |
上記のクエリでは、sales_dataテーブルの中から商品ごとに売り上げを集計し、全体の売り上げに占める割合を計算しています。
また、OVER句により、集計の対象となる列（この例ではsales_amount）とグループの指定（この例ではproduct_id）を指定しています。
実行結果は以下のようになります。
| sales_id | sales_date | sales_amount | product_id | sales_ratio |
| 1 | 2022-01-01 | 1000 | 1 | 0.3 |
| 2 | 2022-01-02 | 2000 | 1 | 0.3 |
| 3 | 2022-01-03 | 3000 | 2 | 0.4 |
| 4 | 2022-01-04 | 4000 | 2 | 0.4 |
| 5 | 2022-01-05 | 5000 | 3 | 0.3 |
ウィンドウ関数を使用することでの効率アップ
ウィンドウ関数でできることは、基本的にはサブクエリでも実現できます。
しかし、サブクエリを使う場合と比較して、SQL文がシンプルになるケースが多くあります。
また、複数の処理を1つのSQL文で行えるため、処理速度が向上しやすくなります。
サブクエリを使う場合との比較
ウィンドウ関数を使わない場合でも、サブクエリを使用して同じ結果が返ってきます。
しかしサブクエリを使用する場合は、処理速度が遅くなることがあり、SQLのコードが冗長になり可読性が低下することがあります。
一方、ウィンドウ関数を使用することで、複数の処理を1つのSQL文で実行でき、コードの可読性が向上します。
商品(product_id)ごとのsales_amountの合計を求める場合を考えてみます。サブクエリを使った場合は、以下のようなSQL文になります。
|
1 2 3 4 5 6 |
SELECT product_id, (SELECT SUM(sales_amount) FROM sales s2 WHERE s1.product_id = s2.product_id) AS total_sales_amount FROM sales s1 GROUP BY product_id; |
一方、ウィンドウ関数を使う場合は、以下のようになります。
|
1 2 3 4 5 |
SELECT product_id, SUM(sales_amount) OVER (PARTITION BY product_id) AS total_sales_amount FROM sales; |
どちらも結果は同じ結果が出力されます。
| product_id | total_sales_amount |
| 1 | 3000 |
| 1 | 3000 |
| 2 | 7000 |
| 2 | 7000 |
| 3 | 5000 |
結果として、ウィンドウ関数を使うことでSQL文がシンプルになりました。
サブクエリを使った場合と比較して、ウィンドウ関数を使った方が可読性が高まり、SQL文の修正や保守作業がしやすくなります。
複数の処理を1つのSQL文で行える
ウィンドウ関数を使うことで、複数の処理を1つのSQL文で実行できます。
商品(product_id)ごとに、sales_amountが最大値となるレコードを取得し、そのレコードのsales_dateとsales_amountを取得したいとします。以下のようにウィンドウ関数を使って実現できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT sales_id, sales_date, sales_amount, product_id FROM ( SELECT sales_id, sales_date, sales_amount, product_id, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS row_num FROM sales ) s WHERE s.row_num = 1; |
実行結果は以下の通りです。
| sales_id | sales_date | sales_amount | product_id |
| 2 | 2022-01-02 | 2000 | 1 |
| 4 | 2022-01-04 | 4000 | 2 |
| 5 | 2022-01-05 | 5000 | 3 |
ROW_NUMBER()を使うことで、商品ごとにsales_amountが最大値となるレコードに対して、1から順にナンバリングされたrow_numを取得できます。
そして、外側のSELECT文でrow_numが1のレコードを取得することで、商品ごとのsales_amountが最大値となるレコードを取得できます。
Google BigQueryでウィンドウ関数を利用する際の、Google BigQueryに特有の注意点
Google BigQueryは、クラウド上で大規模なデータ処理を実施できるデータウェアハウスサービスです。
BigQueryは、高速なデータ処理や、スケーラブルなストレージ、SQLのサポートなど、様々な機能を提供しています。
当然Google BigQueryを使う際にもウィンドウ関数は活躍できますが、BigQueryにはウィンドウ関数を利用する際に注意すべき点がいくつかあります。
列の値がnullの場合の扱いに注意
ウィンドウ関数を使ってデータを分析する際、null値を扱う場合に注意が必要です。
例えば、以下のようなテーブルがあったとします。
| sales_id | sales_date | sales_amount | product_id |
| 1 | 2022-01-01 | 1000 | 1 |
| 2 | 2022-01-02 | 2000 | 1 |
| 3 | 2022-01-03 | null | 2 |
| 4 | 2022-01-04 | null | 2 |
| 5 | 2022-01-05 | 5000 | 3 |
このテーブルに対して、以下のような売上データテーブルから、商品ごとの累積売上を計算するためにウィンドウ関数を実行したとします。
|
1 2 3 4 5 |
SELECT sales_date, product_id, SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date) AS cumulative_sales FROM sales; |
実行結果は以下の通りです。
| sales_date | product_id | cumulative_sales |
| 2022-01-01 | 1 | 1000 |
| 2022-01-02 | 1 | 3000 |
| 2022-01-03 | 2 | null |
| 2022-01-04 | 2 | null |
| 2022-01-05 | 3 | 5000 |
ウィンドウ関数で計算される累積売上額にnullが含まれているため、結果もnullとなってしまっています。このような場合は、nullを扱う方法を考慮する必要があります。例えば、以下のようにnullを0で置換できます。
|
1 2 3 4 5 |
SELECT sales_date, product_id, COALESCE(SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date), 0) AS cumulative_sales FROM sales; |
実行結果は以下の通りです。
| sales_date | product_id | cumulative_sales |
| 2022-01-01 | 1 | 1000 |
| 2022-01-02 | 1 | 3000 |
| 2022-01-03 | 2 | 3000 |
| 2022-01-04 | 2 | 7000 |
| 2022-01-05 | 3 | 5000 |
コストに注意する必要がある
Google BigQueryでは、クエリを実行するたびにコストがかかるため、クエリの実行に必要なリソースを最小限に抑えることが重要です。
ウィンドウ関数を使用する際には計算される値の数が多くなるため、その分だけコストも増加することがあります。
そのため、クエリの実行前に必要なリソースの量を見積もり、必要なリソースを最小限に抑えるようにクエリを設計することが重要です。
また、ウィンドウ関数を使う場合には、PARTITION BY句やORDER BY句などを使用して、不要な計算を回避することもできます。不要な計算を回避することで、コストを削減できます。
まとめ
ウィンドウ関数は、SQL文の中で集計関数をグループに分けて個別に計算する機能であり、より高度な分析や複雑なクエリが可能になります。
ウィンドウ関数を使うことでクエリのパフォーマンス向上が期待できるケースがあります。
例えば、GROUP BY句を使用した場合、データをグループ化するためにソートが必要ですが、ウィンドウ関数を使用する場合、ソートは必要ありません。
ウィンドウ関数を使えば、特定の範囲内のデータを取得できることもみてきました。
本記事で扱っていないことも含めれば、特定の期間の移動平均、累積和、ランキング、割合、増減率などを求められます。
しかしながら、ウィンドウ関数も万能ではなく、クエリのパフォーマンスに影響を与える可能性があるため、使用する際にはリソース管理に注意が必要なこともあるのでご注意ください。