分析関数と呼ばれる関数があります。
調べたところPostgreSQLの公式ドキュメントにも説明があったり、Google検索で約1,240万件も検索結果が出るくらいに有名な単語らしいです。

ただ自分自身はあまりわからなかったので、ひたすら調べて理解した範囲をまとめようと思い社内ブログを書いています。
前回ブログリンクはこちらです。

前回の話

前回は分析関数とは？といった部分から記載しました。
ざっくりまとめは以下の通りです。

• 分析関数（ウィンドウ関数）は名前の通り分析用途とかに利用するらしい
• ウィンドウとは、対象となるテーブル全体を指すらしい
• OVER句を指定してクエリを記載する

前回はOVER句を利用して平均値のみ指定をしていたため、他の記載方法に関してもまとめていきます

公式ドキュメントには何が書いてあるか

公式ドキュメントに記載されている内容が基本的に正しいことが多いです。
そのため今回もGoogle Cloudの公式ドキュメントを見ていきます

分析関数を使用すると、移動平均の計算、項目のランク付け、累積合計の計算、その他の分析を行うことができます。

なんか色々とできそうですね
それぞれ見ていきます

項目のランク付け

前回も利用した昔話に出てくる登場人物をサンプルテーブルとして利用します。

残業時間カラムとしてovertimeも入れています。
ランク付けとは特定項目を基準として1番、2番と順番付けをしていきます。

例えば今回のサンプルテーブルだと、部署ごとの残業時間が一番多い人がだれか？といった確認をしたいとします。
流れとしては以下の通りです

1. overtime の値の順番を
2. DepartmentName ごとに
3. 昇順に判定して
4. ranking に結果として出力したい

今回は結果を昇順に出力したいとします。もちろん降順でも問題ないです。

イメージは次の通りです

まずは1の overtime を軸とした順番を決めます。
番号をつけるのは rank 関数を利用します。

rank()

もちろんこちらもOVER句を利用するのでこうなります

rank() OVER()

ただし今回は overtime を軸とした順番付けをしたいです。順序付けする際は ORDER BY を指定します。
通常のSQLクエリでもORDER BY指定したりするのでこのあたりは同じっぽいです。

rank() OVER(ORDER BY overtime)

現時点のイメージを表に記載するとこうなります。

overtimeを軸とするため、overtimeに対して順番付けがされます。
しかし今回は全体の順番付けではなく、DepartmentNameごとに順番付けをしたいです。

次に、2のランク付けする範囲を決定するカラムを指定します。
指定するカラムは DepartmentName です。

こちらも前回同様、PARTITION BYで指定します
ただし場所はORDER BYの前に指定します

rank() OVER(PARTITION BY DepartmentName ORDER BY overtime)

現時点でのイメージを表に記載するとこうなります。

これで何を軸として、どこの値ごとに順番付をするかといった部分ができました。

次に昇順降順を指定します。
昇順降順はASC、DESCで指定します。
ASC、DESCはORDER BYの後に指定します。デフォルトはASCのため指定しなくても問題ないですが今回はわかりやすく指定します。

rank() OVER(PARTITION BY DepartmentName ORDER BY overtime ASC)

最後に4の結果出力を実施します。
このままクエリを実施すると結果が出力されます。

もちろんDESCを指定するとrankingが降順となった出力結果となります。
注意していただきたいのは、この降順は軸として指定した値（ORDER BYで指定しているカラム）を降順に計算した場合として出力されます。
実際の出力結果はこのようになります。

累積合計の計算

次に累積合計の計算を分析関数でやってみます。
その前に謎の単語があります。

累積計算とは

累積計算とは？
普通に調べてみると「累計」と出てきます
累計は特定の値を順次加えて合計を出す算出用法です。

例えば先ほどとは別のサンプルテーブルとして、日ごとの残業時間を計算できるようにします。
下記のような6/1 ~ 6/3の作業時間を記録したサンプルテーブルがあったとします

このテーブルに対して累積計算を行います。
とはいえ難しい部分はあまりないです。
軸となるカラムをどうするか？といっただけです。

今回確認したいことを整理すると下記の通りになります

1. OvertimeHour の値を
2. EmployeeID ごとに累積計算を行い
3. Date の順番で増加させて
4. 昇順に判定して
5. Cumulative に結果として出力したい

イメージは次の通りです。

1つ目からやっていきます
まずは OvertimeHour を指定します。累積計算の場合は sum を指定します
そして後ろにOVER句を指定します

SUM(OvertimeHour) OVER()

次に区分け対象となるカラムを選択します。
今回は EmployeeID のため、OVER() 内に記載します
区分けで利用するのは PARTITION BY なので、 EmployeeID の前に指定します。

SUM(OvertimeHour) OVER(PARTITION BY EmployeeID)

次に順番を指定します。今回は Date を順番通りに指定したいため、ORDER BY 句に Date を指定します

SUM(OvertimeHour) OVER(PARTITION BY EmployeeID ORDER BY Date)

次に昇順降順です。
rankで説明した通り、ASCを指定します

SUM(OvertimeHour) OVER(PARTITION BY EmployeeID ORDER BY Date ASC)

最後に出力するカラム名を指定で完成です

SUM(OvertimeHour) OVER(PARTITION BY EmployeeID ORDER BY Date ASC) as Cumulative

このままクエリを実施すると結果が出力されます。

日毎に残業時間の合計が出ていますね。これで累積計算も完了となります。
前回紹介したSUM関数のカラム指定を変更するだけで累積計算になるのでかなり便利です。

まとめ

今回のまとめです

• 順番付けはrank関数を利用する。ORDER BYの指定で昇順降順の指定が可能
• 累積計算はsum関数を利用する。PARTITION BYの指定やORDER BYの指定を変えるだけで累積計算として利用可能

次は移動平均と、今まで紹介したやつをどう使うかってのをブログとしてまとめたいです。