1.分析関数
集合関数(SUM,AVG,MAX,MIN等)と同じ集計動作をそれぞれの行に制限範囲で実行する関数。集合関数と違い行がまとまらず、それぞれの行で集計結果が返される。
分析関数を使うメリットは、速度の向上、SQLの簡素化。
■使用方法
分析関数の後ろに、OVER句を指定する。（集合関数と分析関数は基本同じ名前）
例）SELECT SUM(カラム名) OVER() FROM ~~~
OVER句に以下を指定することで集計範囲を指定できる。
- PARTITION BY ・・・ グループ分け
- ORDER BY ・・・ 順序指定
- ROWS BETWEEN ・・・ レコード範囲
- 指定なし・・・全行が集計対象
例）SELECT SUM(カラム名) OVER(PARTITION BY カラム名,・・・)
■分析関数
- COUNT関数 ・・・ 件数
- SUM関数 ・・・ 合計
- AVG関数 ・・・ 平均
- MAX関数 ・・・ 最大
- MIN関数 ・・・ 最小
- RANK関数 ・・・ 順位
- DENSE_RANK関数 ・・・ 順位
- LAG関数 ・・・ n個前
- LEAD関数 ・・・ n個後
等
2.集合関数と分析関数の比較
使用テーブル
カラム：ORDER_ID、ITEM
①レコード数の取得
集合関数の場合
全レコードを1つにまとめて集計される。
分析関数の場合
各レコードで集計を行うため、集計結果が各行で確認できる。
②ITEMごとのレコード数の取得
集合関数の場合
GROUP BYでitemを指定することでitemごとに集計をおこなう。
Apple、Bananaの2レコードにまとめられる。
分析関数の場合
PARTITION BYでitemを指定することでitemごとに集計をおこなう。
各レコードで集計を行うため、集計結果が各行で確認できる。
まとめ
現在行っている案件では、仮想テーブルの作成で分析関数を使用しました。DISTINCTと併用することで、集合関数+GROUP BYで集計した場合と似たデータを、簡素化させて取得することができました。
今回は簡単な分析関数のみを紹介しました。OVER句以外にWINDOW句、FILTER句といった要素もあるため、より複雑な集計を行いたい場合は調べると良いです。
以上
コメント