集約関数はGROUP句と一緒に使うことで、特定の条件(グループ)ごとの集計結果を出力することができる。
一方で、例えば部署に所属している人数を各階層(係、課、部、全社など)に抽出したいことなどがあった場合、集約関数を使うとそれぞれの階層ごとに計算して、結合する必要がある。
こういった問題を解決するのが、OVER句だ。
GROUP化することなく、指定した条件での集約結果を抽出することができる。
ここに関して、調べてみると分析関数、ウィンドウ関数、OVER句など様々な言葉が出てきますが、Microsoftのドキュメントを意訳すると、
- 集約関数:COUNT,SUMなどのGROUP句と一緒に使うもの(OVER句とも一緒に使える)
- 分析関数:OVER句と一緒に使うもの(OVER句で指定された条件での相対的な結果を出す)
- ウィンドウ関数:集約関数/分析関数の部分の総称。
- OVER句:集約/分析関数をどの条件で計算するかを指定する。
書き方
SELECT SUM(a) OVER(PARTITION b) FROM DUAL
SUM(a)がウィンドウ関数でSUMは他の関数に変更が可能です。
PARTITION bは省略可能で省略すると全行のSUM(a)を計算します。
OVER句の条件
OVER句で利用可能な条件は以下のとおり
記述 | 説明 |
---|---|
PARTITION BY column | 集計する行セットを指定します。GROUP句と同じかと思います。 カンマ区切りで複数指定することが可能です。 |
ORDER BY column [ASC / DESC] | 行の並び順を指定します。 カンマ区切りで複数指定することが可能です。 指定した条件の後方にASC(昇順・デフォルト)、DESC(降順)を指定することができます。 |
ROWS [start / BETWEEN start and end] | PARTITIONで区切った領域からウィンドウ関数で計算する範囲をさらに絞り込む条件です。 現在の行を基準に指定することができます。(指定方法は後述) |
RANGE [start / BETWEEN start and end] | ROWS同様に、PARTITIONで区切った領域からウィンドウ関数で計算する範囲をさらに絞り込む条件です。 現在の行を基準に指定することができます。 |
ROW/RANGEで指定する範囲は以下の条件を指定することができます。
(上記のstrat、endに入れる値)
記述 | 説明 |
---|---|
CURRENT ROW | 現在の行 |
[UNBOUNDED / number] PRECEDING | 行セットの最初の行(UNBOUNDED)または、現在の行からnumberの指定分前の行。 |
[UNBOUNDED / number] FOLLOWING | 行セットの最後の行(UNBOUNDED)または、現在の行からnumberの指定分後の行。 |
注意点
GROUP句と同じような感覚で使って私がよく間違えたのは、OVER句の場合は出力される行は集約されないということです。
つまり、社員名簿のデータをもとに部署/階層ごとの所属人数を計算すると社員数の行だけ出力されます。
そのため、必要に応じてGROUP句やDISTINCTを使う必要があります。
悪い例
この書き方だと、社員数分の行が出力されてしまします。
SELECT UNIT, COUNT(*) OVER (PARTITION UNIT) -- 係の所属人数
, DIV, COUNT(*) OVER (PARTITION DIV) -- 課の所属人数
, DEPT, COUNT(*) OVER (PARTITION DEPT) -- 部の所属人数
, 'ALL', COUNT(*) -- 全社員数
FROM EMPLOYEE_LIST
良い例
DISTIONCTすることで出力対象で最小単位となる係の数だけの行数になります。
SELECT DITINCT
UNIT, COUNT(*) OVER (PARTITION UNIT) -- 係の所属人数
, DIV, COUNT(*) OVER (PARTITION DIV) -- 課の所属人数
, DEPT, COUNT(*) OVER (PARTITION DEPT) -- 部の所属人数
, 'ALL', COUNT(*) -- 全社員数
FROM EMPLOYEE_LIST
例文少なめですが、とりあえずこんなところ、気が向いたら追記していきます。