はじめに
最近、BigQueryを使う非エンジニアの方から、分析関数の書き方も使う理由もよく理解できないというご意見をいただきましたので、分析関数について公式ドキュメントとUdemyのBigQuery講習のみの知識をまとめたいと思います。
分析関数とは
- 公式ドキュメントの説明
分析関数は、行のグループに対して値を計算し、各行に対して 1 つの結果を返します。これは、行のグループに対して 1 つの結果を返す集計関数とは異なります。
- Udemy講座(BigQuery で学ぶ非エンジニアのための SQL データ分析入門)の説明
グループ化されつつ集計しない括りについては、パーティションと呼ばれ、パーティションを使った関数を分析関数と言います。行のグループに対して単一の結果を返すGROUP BY(集計関数)とは異なり、行のグループの値を計算し行ごとに一つの結果を返します。
上記をまとめると、カテゴリごとに複数のレコードをまとめた計算をする集計関数に対して、カテゴリごとに分けつつも返す結果は元のテーブルと同じレコード数だけ返す関数を分析関数と言います。
例えば、以下のテーブル"sample.purchases"を題材に考えます。
| id | category | sales_amount |
|---|---|---|
| 1 | 野菜 | 3000 |
| 2 | 肉 | 2000 |
| 3 | 肉 | 6000 |
| 4 | 野菜 | 1500 |
| 5 | 肉 | 2500 |
このテーブルに対し、カテゴリごとの総売上を計算する場合には、集計関数(group by)を使って以下のように記述できます。
SELECT
category,
SUM(sales_amount) AS total_sales
FROM sample.purchases
GROUP BY category
これを実行すると、元のテーブルが5行あるのに対して、結果をまとめた2行のみとなります。
| category | total_sales |
|---|---|
| 野菜 | 4500 |
| 肉 | 10500 |
一方、カテゴリごとに売上を時系列順に並べて「その行までの累計売上はどれくらいか」を知りたい場合には、分析関数(over partition by)を使って以下のように記述できます。
SELECT
id,
category,
sales_amount,
SUM(sales_amount)
OVER ( -- OVER句を伴うことで、分析関数であることが明示的になる
PARTITION BY category -- PARTITON BY句: 指定したカラム名でパーティションを作成する
ORDER BY id -- ORDER BY句: パーティションの中で並び替えを行う
ROWS BETWEEN unbounded preceding AND current row -- WINDOW FRAME句: パーティションの中でどのレコードを計算対象のフレームとするか宣言する
) AS cumulative_sales
FROM sample.purchases
ORDER BY category, id
これを実行すると、元のテーブルと同じ5行の結果が返ってきます。
| id | category | sales_amount | cumulative_sales |
|---|---|---|---|
| 1 | 野菜 | 3000 | 3000 |
| 4 | 野菜 | 1500 | 4500 |
| 2 | 肉 | 2000 | 2000 |
| 3 | 肉 | 6000 | 8000 |
| 5 | 肉 | 2500 | 10500 |
この結果の返す意味は、以下の通りです。
1. categoryごとに結果が整理されているが、一つのcategoryに対して複数の結果が表示される。
2. 特定のcategoryの中では、id順になっている。
3. cumulative_salesでは、同じカテゴリに対してのsales_amountを現在の行まで累積するように計算されている。
WINDOW FRAME句について
最初に指定するのはrowsです。
次にフレーム(分析関数で計算させる対象範囲)を指定する方法として、基本的にbetween句が利用されます。
すなわち、
ROWS BETWEEN [フレーム開始行設定] AND [フレーム終了行設定]
となります。
フレーム開始行設定、フレーム終了行設定に使えるキーワードは以下の通りです。
| フレーム開始行/終了行設定キーワード | 説明 |
|---|---|
| unbounded preceding | パーティションで定義された境界の、最も上の行 |
| unbounded following | パーティションで定義された境界の、最も下の行 |
| current row | 現在の行 |
| [正の整数] preceding | 現在の行から[正の整数行]だけ上の行 |
| [正の整数] following | 現在の行から[正の整数行]だけ下の行 |
まとめ
集計関数: カテゴリごとにまとめて統計値を出したい場合に使う
分析関数: カテゴリごとに区切って統計値を出したい場合に使う
集計関数に対してカテゴリごとに異なるレコードを持つ結果を返したい場合に使う分析関数は、柔軟性に優れているといえます。
参考
https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts
BigQuery で学ぶ非エンジニアのための SQL データ分析入門(https://www.udemy.com/course/bigquerysql/)