はじめに
ちょっととっつきにくいけどとっても便利な分析関数について、なるべく分かりやすく説明してみようと思います。Oracleを対象にしていますが、他のDBでもたぶん似たようなものでしょう（無責任）。
まず分析関数とは何をするものか、ですが、一言で言うと集合関数と同じ集計動作をそれぞれの行に制限範囲で実行するものです。ここでいう集合関数とは、MAXやSUMやAVG等、GROUP BYと共に使い行をまとめるて集計計算する関数ですね。分析関数は集合関数と同様の計算をしますが、集合関数と違い行をまとめません。それぞれの行で集計計算し結果を返します。ここが集合関数との大きな違いです。
また、集合関数ではGROUP BYの同じカラム値をもつ全行を一つに集計しますが、分析関数では集計対象となる行の範囲を任意で指定できます。関数に続くOVER句でこの範囲指定を行います。集合関数と分析関数は基本同じ名前なので、よく使う一般的な集合関数は後ろにOVER句をつけれれば、分析関数になると考えてください（集合関数だけの関数や分析関数だけの関数もあります）。
別の見方をすると、それぞれの行ので分析関数によって計算された集計値というものは、その行以外の行を参照して得た値の集計結果です。たとえば、ある行を処理している時に、一つ前の行と一つ後の行から得た値の合計をだす等の処理が自己結合することなく可能になります。つまり、分析関数は行間参照する（他の行を参照できる）関数ということになります。
それでは、クエリを使って説明します。
テストデータ
CREATE TABLE test_orders (order_id NUMBER, item VARCHAR2(10), qty NUMBER, order_date DATE);
insert into test_orders values (1001, 'Apple', 4, date'2018-1-10');
insert into test_orders values (1005, 'Banana', 8, date'2018-1-20');
insert into test_orders values (1010, 'Banana', 2, date'2018-2-1');
insert into test_orders values (1021, 'Apple', 10, date'2018-2-15');
insert into test_orders values (1025, 'Apple', 6, date'2018-2-22');
insert into test_orders values (1026, 'Apple', 5, date'2018-2-23');
ORDER_ID ITEM QTY ORDER_DATE
---------- ---------- ---------- ----------
1001 Apple 4 2018/01/10
1005 Banana 8 2018/01/20
1010 Banana 2 2018/02/01
1021 Apple 10 2018/02/15
1025 Apple 6 2018/02/22
1026 Apple 5 2018/02/23
集合関数と分析関数の比較
一番簡単な例として、集合関数のCOUNTと分析関数のCOUNTを実行して比較してみます。以下の通り違いは一目瞭然ですね。
分析関数は、行をまとめずそれぞれの行で集計結果を計算します。またOVER句内に範囲指定がないためテーブルにあるすべての行が分析関数の集計対象となります。したがって、それぞれの行で結果が6になっています。「行をまとめないのに集合関数のように集計する」これが分析関数の基本的動作です。
集合関数
SELECT COUNT(*) FROM test_orders;
COUNT(*)
----------------
6
分析関数
SELECT order_id, item, COUNT(*) OVER () FROM test_orders;
ORDER_ID ITEM COUNT(*)OVER()
---------- ---------- --------------
1001 Apple 6
1005 Banana 6
1010 Banana 6
1021 Apple 6
1025 Apple 6
1026 Apple 6
OVER句
さて、それではOVER句の中をみていきましょう。OVER句では以下の３つの方法で集計対象の範囲指定ができます。そうこれ全部、範囲指定なのです。そして上記のクエリの通りOVER句になにも指定しなければ全行が集計対象になります。
- Partition By指定
- Order By指定
- Window (Frame)指定
PARTITION BY
最初にPARTITION BYです。これは名前の通り対象を区切るものです。集合関数でのGROUP BYの動きと思ってください。以下のクエリでは、ITEMカラムの値で区切っていますので、AppleとBananaで別々の集計になっています。まさにGROUP BY付きの集合関数がそれぞれの行で実行されたイメージです。
集合関数
SELECT item, COUNT(*) FROM test_orders t GROUP BY item;
ITEM COUNT(*)
---------- ----------------
Apple 4
Banana 2
分析関数
SELECT order_id, item, COUNT(*) OVER (PARTITION BY item) FROM test_orders ORDER BY order_id;
ORDER_ID ITEM COUNT(*)OVER(PARTITIONBYITEM)
---------- ---------- -----------------------------
1001 Apple 4
1005 Banana 2
1010 Banana 2
1021 Apple 4
1025 Apple 4
1026 Apple 4
ORDER BY
PARTITION BYがわかったところで、次はORDER BYです。実はこれが意外と曲者です。普通の集合関数では、対象行はまとめられて一つの結果に計算されますので、順番は本来意味をもちません。従って分析関数のORDER BYには少し特殊な意味があります。
それは、 「行を順番に並べた上で、最初の行から現在行までのみを集計の対象にする」 ことです。これを理解していないと分析関数が意味不明になります。
簡単な例を示します。
先程のCOUNT関数ですが、OVER句になにも指定しない結果はすべての行でテーブル行数の6でした。ここで、OVER句にORDER BY ORDER_IDを指定してみます。すると下記の様に計算結果が変わります。なぜでしょうか？ って先程の説明通り、それぞれの行で集計対象となる行の数が違っているからですね。一行目は、一行目のみ。二行目は、一行目から二行目まで。三行目は、一行目から三行目までが集計対象だからです。
select order_id, item, count(*) over (order by order_id) from test_orders;
ORDER_ID ITEM COUNT(*)OVER(ORDERBYORDER_ID)
---------- ---------- -----------------------------
1001 Apple 1
1005 Banana 2
1010 Banana 3
1021 Apple 4
1025 Apple 5
1026 Apple 6
この集計方法で一番良くつかわれるSUMでみてみましょう。下のクエリでは上から順番にQTYが加算されていっているのがわかります。これは、ランニング集計/合計(Running SUM)と呼ばれ、分析関数が使われる理由の大きな一角を占めています。これだけでもぜひ覚えておいてください。
SELECT order_id, item, qty, SUM(qty) over (ORDER BY order_id) FROM test_orders;
ORDER_ID ITEM QTY SUM(QTY)OVER(ORDERBYORDER_ID) # 内訳 ()は現在行
---------- ---------- ---------- -----------------------------
1001 Apple 4 4 # (4)
1005 Banana 8 12 # 4 + (8)
1010 Banana 2 14 # 4 + 8 + (2)
1021 Apple 10 24 # 4 + 8 + 2 + (10)
1025 Apple 6 30 # 4 + 8 + 2 + 10 + (6)
1026 Apple 5 35 # 4 + 8 + 2 + 10 + 6 + (5)
PARTITION BYとORDER BYを同時に使用すると、PARTITION BYで区切られた中でORDER BYを使用した集計が行われます。イメージできますか？ 以下の例ではITEM毎にORDER_IDの順でQTYがランニング集計されています。
SELECT order_id, item, qty,
SUM(qty) over (PARTITION BY item ORDER BY order_id)
FROM test_orders;
ORDER_ID ITEM QTY SUM(QTY)OVER(PARTITIONBYITEMORDERBYORDER_ID) # 内訳 ()は現在行
---------- ---------- ---------- --------------------------------------------
1001 Apple 4 4 # (4)
1021 Apple 10 14 # 4 + (10)
1025 Apple 6 20 # 4 + 10 + (6)
1026 Apple 5 25 # 4 + 10 + 6 + (5)
1005 Banana 8 8 # (8)
1010 Banana 2 10 # 8 + (2)
WINDOW (FRAME)
さて、最後のWINDOWですが、これはORDER BY句のオプションみたいなもので、行をソートした後それぞれの行でどこからどこまでを集計対象にするのかを指定するものです。したがってWINDOW指定するには必ずORDER BY句が必要です。
先程、ORDER BYでは先頭行から現在行までが対象になると説明しましたが、これは実はWINDOW指定を省略したときのデフォルト動作であったわけです。WINDOW指定では、この対象範囲を自由に設定できます。また分析関数によってはウインドウ指定が使えないものもあるので、詳しくはマニュアルを参照してください。
集計対象行の範囲指定方法ですが、まずROWSかRANGEかを選びます。ROWSは行数指定で、RANGEはORDER BYで指定しているカラムの値への指定です。
- ROWS
- RANGE
ROWSまたはRANGEを選んだら、次は対象行の開始点のみを指定するか、開始点と終了点の両方をしてするか選びます。前者を選んだ場合は、終了点は常に現在行になります。後者の場合はBETWEENを使います。
- ROWS/RANGE <開始点>
- ROWS/RANGE BETWEEN <開始点> AND <終了点>
ROWS
まずは簡単な方のROWSで指定範囲指定の説明します。
以下は、開始点を指定した例です。ORDER_IDでソート後、それぞれの行で集計対象の開始点を「２つ前の行（2 PRECEDING）」としています。終了点指定がないので終了点は「現在行まで」です。たとえば、4行目のORADER_ID 1021の処理では、2行前の8および1行前の2と現在行の10を合計して、20となっていますね。また集計対象行が存在しない場合は単純に無視されるので、1行目は、1行目だけ。2行目は、1行目と2行目だけの合計になっています。
SELECT order_id, item, qty,
SUM(qty) OVER (ORDER BY order_id ROWS 2 PRECEDING) result
FROM test_orders;
ORDER_ID ITEM QTY RESULT # ２行前から現在行まで
---------- ---------- ---------- ----------
1001 Apple 4 4 # (4)
1005 Banana 8 12 # 4 + (8)
1010 Banana 2 14 # 4 + 8 + (2)
1021 Apple 10 20 # 8 + 2 + (10)
1025 Apple 6 18 # 2 + 10 + (6)
1026 Apple 5 21 # 10 + 6 + (5)
ここまで分かれば、いわゆる移動平均が簡単に作れるのがイメージできると思います。一日一行のデータがあれば、７日移動平均はAVG()でROWS 6 PRECEDINGとするだけですね。
次にBETWEENを使って開始点と終了点を両方指定する方法もみてみます。この例では、一つ前の行から一つ後の行（BETWEEN 1 PRECEDING AND 1 FOLLOWING）の３行を常に集計対象としています。例えは3行目、8 + 2 + 10 = 20になってますね。
SELECT order_id, item, qty,
SUM(qty) OVER (ORDER BY order_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) result
FROM test_orders;
ORDER_ID ITEM QTY RESULT # １行前から１行後まで
---------- ---------- ---------- ----------
1001 Apple 4 12 # (4) + 8
1005 Banana 8 14 # 4 + (8) + 2
1010 Banana 2 20 # 8 + (2) + 10
1021 Apple 10 18 # 2 + (10) + 6
1025 Apple 6 21 # 10 + (6) + 5
1026 Apple 5 11 # 10 + (5)
ここで使える開始点、終了点の指定方法は、以下の５つのみです。これ以外はありません。
| 指定方法 | 説明 |
|---|---|
| UNBOUNDED PRECEDING | 先頭の行（終了点では使えない） |
| UNBOUNDED FOLLOWING | 末尾の行（開始点では使えない） |
| CURRENT ROW | 現在行 |
| <n> PRECEDING | 現在行より<n>行前、RANGEの場合は<n>値前 |
| <n> FOLLOWING | 現在行より<n>行後、RANGEの場合は<n>値後 |
ORDER BY指定のみでWINDOW指定を省略した場合は、先頭行から現在行までが集計対象でしたが、これを上記で記述すると、ROWS UNBOUNDED PRECEDING または ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW だということになります。
<n>部分には、実数値以外にも、テーブルの他のカラム、計算式、サブクエリなど結構自由に使用できます。たとえば下記の例ではORDER_IDで並べた後に、ITEMが"Banana"の時だけ前後を含めた３行を集計対象とし、それ以外(Apple)は現在行のみを集計対象にしています。
SELECT order_id, item, qty,
SUM(qty) OVER (ORDER BY order_id
ROWS BETWEEN DECODE(item, 'Banana', 1, 0) PRECEDING
AND DECODE(item, 'Banana', 1, 0) FOLLOWING) result
FROM test_orders;
ORDER_ID ITEM QTY RESULT # Bananaの時だけ前後行を含む
---------- ---------- ---------- ----------
1001 Apple 4 4 # (4)
1005 Banana 8 14 # 4 + (8) + 2
1010 Banana 2 20 # 8 + (2) + 10
1021 Apple 10 10 # (10)
1025 Apple 6 6 # (6)
1026 Apple 5 5 # (5)
RANGE
では、RANGEもみてみましょう。
RANGEは、行数ではなくORDER BYで指定したカラムの値で範囲指定します。例を見てもらったほうがつかみやすいでしょう。この例では、ORDER_DATEカラムでソートしています。DATEタイプですので、10 PRECEDINGの意味は、10日前になります。つまり、現在行のオーダー日から遡って10日前までの行を集計対象にします。10日前以内にオーダーがあるのは、2, 5, 6行目ですので、それぞれ10日前以内の行のQTYが加算されています。何行分が合計されているかは、CNTカラムを参照してください。
SELECT order_id, item, qty, order_date,
SUM(qty) OVER (ORDER BY order_date RANGE 10 PRECEDING) result,
COUNT(*) OVER (ORDER BY order_date RANGE 10 PRECEDING) cnt
FROM test_orders;
ORDER_ID ITEM QTY ORDER_DATE RESULT CNT # １０日前から現在行まで
---------- ---------- ---------- ---------- ---------- ----------
1001 Apple 4 2018/01/10 4 1 # (4)
1005 Banana 8 2018/01/20 12 2 # 4 + (8)
1010 Banana 2 2018/02/01 2 1 # (2)
1021 Apple 10 2018/02/15 10 1 # (10)
1025 Apple 6 2018/02/22 16 2 # 10 + (6)
1026 Apple 5 2018/02/23 21 3 # 10 + 6 + (5)
RANGE指定は、集計対象行数が不特定の場合に効果を発揮します。３日間の全てのオーダーの平均とか、または遡る日数を可変にして別カラムに用意しておけば非稼働日を含む５日稼働日間の集計なんかも簡単にできますね。
あとRANGE指定には、いくつかの制限があります。使用できるカラムは数値または日付のみです。従ってORDER BYのカラムが文字列の場合は、RANGEは使用できません。また、ORDER BYの並べ替えに複数のカラムを指定した場合、<n> PRECEDINGと <n> FOLLOWINGが使えなくなります。他の３つ（UNBOUNDED PRECEDING/FOLLOWING, CURRENT ROW）のみ使用可能です。ROWS指定には、それらの制限はありません。
分析関数がよく使われる例
最大値を持つ行の抜き出し
分析関数を使えば、自己結合することなくあるグループ内の特定の行（最大値や最小値など）をそのまま抜き出すことができます。以下のクエリでは、PARTITION BYとORDER BYを両方使って ITEM毎にQTYの大きい順に順番を付けています。このクエリの結果から一番だけを抜き出せば、「各ITEMで最も大きいQTYをもつ行を取得する」クエリの出来上がりです。
SELECT order_id, item, qty,
ROW_NUMBER() OVER (PARTITION BY item ORDER BY qty DESC)
FROM test_orders;
ORDER_ID ITEM QTY ROW_NUMBER()OVER(PARTITIONBYITEMORDERBYQTYDESC)
---------- ---------- ---------- -----------------------------------------------
1021 Apple 10 1
1025 Apple 6 2
1026 Apple 5 3
1001 Apple 4 4
1005 Banana 8 1
1010 Banana 2 2
SELECT * FROM (
SELECT order_id, item, qty,
ROW_NUMBER() OVER (PARTITION BY item ORDER BY qty DESC) rn
FROM test_orders)
WHERE rn = 1;
ORDER_ID ITEM QTY RN
---------- ---------- ---------- ----------
1021 Apple 10 1
1005 Banana 8 1
行間参照によるグループ化
行間参照グループ化でも分析関数がよく使われます。普通のグループ化ではなく他行を参照してその結果グループ化するものです。例えば、上からQTYを足していって合計が20を超えない行で一つのグループにするとかですね。
ここでは、ORDER_IDで並べて、ひとつ前の行が同じITEMであればグループとしてまとめるSQLを作ってみます。出力結果は、以下の通り2,3行目および、4,5,6行目がまとめられて一つの行になります。
元データ
ORDER_ID ITEM QTY ORDER_DATE
---------- ---------- ---------- ----------
1001 Apple 4 2018/01/10
1005 Banana 8 2018/01/20
1010 Banana 2 2018/02/01
1021 Apple 10 2018/02/15
1025 Apple 6 2018/02/22
1026 Apple 5 2018/02/23
行間参照グループ化後
GRP ITEM LAST_ORDER SUM_QTY ORDERS
---------- ---------- ---------- ---------- --------------------
1 Apple 2018/01/10 4 1001
2 Banana 2018/02/01 10 1005,1010
3 Apple 2018/02/23 21 1021,1025,1026
SELECT grp,
MAX (item) item,
MAX (order_date) last_order_date,
SUM (qty) sum_qty,
LISTAGG(order_id, ',') WITHIN GROUP (ORDER BY order_id) orders
FROM (SELECT order_id, item, order_date, qty,
SUM (flg) OVER (ORDER BY order_id) grp
FROM (SELECT order_id, item, order_date, qty,
DECODE (LAG (item) OVER (ORDER BY order_id), item, 0, 1) flg
FROM test_orders)
)
GROUP BY grp;
簡単に説明します。
まず、LAGを使って一つ前の行のITEMを参照し、現在行のITEMが一行前のITEMと異なっていれば、グループを変えるためにフラグを立てます。（LAGでなくFIRST_VALUE等でも可能です）
select order_id, item,
LAG (item) OVER (ORDER BY order_id) previous,
DECODE(LAG (item) OVER (ORDER BY order_id), item, 0, 1) flag
from test_orders;
ORDER_ID ITEM PREVIOUS FLAG
---------- ---------- ---------- ----------
1001 Apple 1
1005 Banana Apple 1
1010 Banana Banana 0
1021 Apple Banana 1
1025 Apple Apple 0
1026 Apple Apple 0
このフラグをランニング集計します。グループが変わる点で１が加算されるので、見事にグループ化できました。あとはGROUP BYと集合関数を使って欲しいデータを作ればいいわけです。
SELECT order_id, item,
SUM (flag) OVER (ORDER BY order_id) grp
FROM (SELECT order_id, item,
DECODE (LAG (item) OVER (ORDER BY order_id), item, 0, 1) flag
FROM test_orders);
ORDER_ID ITEM GRP
---------- ---------- ----------
1001 Apple 1
1005 Banana 2
1010 Banana 2
1021 Apple 3
1025 Apple 3
1026 Apple 3
ま、12cからはパターンマッチ検索ができるようになったので、MATCH_RECOGNIZEのほうが簡単ですけどね。オラクル限定ですが。
SELECT order_id, item, grp
FROM test_orders MATCH_RECOGNIZE (ORDER BY order_id
MEASURES MATCH_NUMBER () grp
ALL ROWS PER MATCH
PATTERN (s+)
DEFINE s AS item = FIRST (item));
ORDER_ID ITEM GRP
---------- ---------- ----------
1001 Apple 1
1005 Banana 2
1010 Banana 2
1021 Apple 3
1025 Apple 3
1026 Apple 3
追記：行間参照によるグループ化の演習問題を作ってみました。３つの演習を分析関数等で解いています。
行間比較(参照)によるグループ化SQL演習 (問題編)
分析関数リスト
最後によく使う分析関数とWINDOW指定ができるかどうかを軽くリストアップしておきます（オラクルのマニュアルより）。
| 分析関数名 | 集合関数 | WINDOW指定 | 説明 |
|---|---|---|---|
| AVG | 有 | ○ | 平均値 |
| COUNT | 有 | ○ | 行カウント |
| FIRST_VALUE LAST_VALUE NTH_VALUE |
○ | 最初、最後、<N>番目の行 | |
| LAG LEAD |
X | 前の行、後ろの行 | |
| MAX MIN |
有 | ○ | 最大値、最小値 |
| MEDIAN | 有 | X | 中間値 |
| NTILE | X | <n>個のグループ分け | |
| RANK DENSE_RANK |
有 | X | 同着後飛ばす順位 同着後飛ばさない順位 |
| ROW_NUMBER | X | 行番号 | |
| SUM | 有 | ○ | 合計 |