分析関数（ウィンドウ関数）がよくわからんかったのでまとめた
投稿者：ソリューション担当
分析関数と呼ばれる関数があります。
調べたところPostgreSQLの公式ドキュメントにも説明があったり、Google検索で約1,240万件も検索結果が出るくらいに有名な単語らしいです。
どうやら分析する際に利用するSQLらしく、お恥ずかしながらProfessional Data Engineerの資格勉強中にはじめて知りました。
そのため「この分析関数、ウィンドウ関数が何か？」というものがわからず必死に調べて学習を行っていましたが、いかんせん自分の理解度が弱くよくわからなかった部分があるので個人的に理解した範囲をまとめようと思います。
同じ気持ちの人がいて本記事が役に立っていただけると幸いです。
そもそも「ウィンドウ」とは何？
まずはGoogle Cloudの公式ドキュメントに記載があったので見てみます
分析関数（ウィンドウ関数ともいいます）は、行のグループに対して値を計算し、各行に対して 1 つの結果を返します。これは、行のグループに対して 1 つの結果を返す集計関数とは異なります。
分析関数には、評価対象の行を囲む行のウィンドウを定義する OVER 句が含まれています。各行について、選択された行のウィンドウを入力として使用して分析関数の結果が計算され、場合によっては集計が行われます。
や、わからんが？？？
ウィンドウの記載が何もありません。
ウィンドウとは？が気になっているのに単語の説明されています。
勉強中の自分は「ウィンドウってなんやねん」とひたすら調べていました。
その結果としてなんとなくわかったことは下記の通りです。
- 既存のテーブル全体を指して「ウィンドウ」と呼ぶ
テーブルで表現すると以下の通りです。
サンプルテーブルとして、昔話に出てくる登場人物をテーブルとして考えてみます。
枠で囲った部分がウィンドウです。
どうやらウィンドウってのはこの全体を指すみたいです。
このウィンドウに対して分析をかけたりするんですね。
分析関数はどこで使うのか
では、この分析関数を使った結果はどうなるか？
例えば先程のサンプルテーブルに残業時間のカラムがあったとします。
青枠で追加した部分です。
各作業者が残業していますね。残業している人もいればしていない人もいます。
例えば DepartmentName ごとに所属している部署の平均残業時間を出して、個人の残業時間と比較したいとします。
よくあるのは平均値を出すavg関数がありますが、この値で計算すると単一行に出力されます。
何も考えずに実行するとこうなります
確かに平均値はでますが、これは全体の平均値です。
実際の利用を考えると、個人の残業時間と部署での残業時間が一緒に出る方がいいです。
イメージは、青と緑の枠が出力されるのが望ましいです
もう少し手を加えようとして、select句に DepartmentName を追加するとエラーがでます。
これはavg関数はovertime全体を対象とし一つにまとめるのに対して、DepartmentNameは「ももたろう」と「あかずきん」の2つを表示しようとしているので動きが噛み合わずエラーがでます。
このような用途のときに分析関数を利用するらしいです
GCP公式ドキュメントにも下記のような用途が記載されていますので割りとできそうです
分析関数を使用すると、移動平均の計算、項目のランク付け、累積合計の計算、その他の分析を行うことができます。
分析関数を利用したクエリの書き方
では実際にaverageを計算してみます。
計算にはOVER句を利用します。OVER句はselect内に記載します。
OVER句の説明はGoogle Cloud公式ドキュメントの分析関数の構文に記載があります。
analytic_function_name ( [ argument_list ] ) OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
window_frame_clause:
{ rows_range } { frame_start | frame_between }
rows_range:
{ ROWS | RANGE }
なんのこっちゃわからんです。
ここからは自分が把握したやり方の順番で計算してみます。
先程の表をベースに見てみます。
今回やりたいことをまとめると次のような流れで計算できます。
-
overtimeの平均値を -
DepartmentNameごとに計算して -
averageに結果として出力したい
OVER句も同じような流れで記載をします。
まずは1の平均値を計算します。
avg(overtime)
こちらの後ろにOVERをつけます
avg(overtime) OVER()
現時点でのイメージを表に記載するとこうなります。
まだこの時点では計算されません。
次に、2の平均値を出すカラムを指定します
指定するカラムは DepartmentName です。
avg(overtime) OVER(DepartmentName)
OVER句でカラム指定する際は PARTITION BY を利用します。
avg(overtime) OVER(PARTITION BY DepartmentName)
現時点でのイメージを表に記載するとこうなります。
OVER句で指定されたカラムをベースとして計算範囲が区切られるイメージです。つまり PARTITION BY で指定されたカラムを基準軸として計算をしています。
最後に3の計算を実施します。
ここはそのままクエリを実行すると結果が出力されます。
実際にクエリを実行すると以下の通りになります。
計算ができました。
まとめるとこうです
もちろんAVGの部分を変えても計算ができます。
まとめ
今回のまとめです
- ウィンドウ関数のウィンドウとは、テーブルのことを指す
- OVER句は1行にまとまる値を全体に表記したい際に利用する
- OVER句は計算するカラム、軸とするカラムを指定する必要がある
OVER句はこれ以外にも指定が可能なので、また別途ブログでまとめようと思います。
