MySQL
SQL
oracle
ウィンドウ関数
分析関数

分析関数(ウインドウ関数)をわかりやすく説明してみた

はじめに

ちょっととっつきにくいけどとっても便利な分析関数について、なるべく分かりやすく説明してみようと思います。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句では以下の3つの方法で集計対象の範囲指定ができます。そうこれ全部、範囲指定なのです。そして上記のクエリの通り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つ前の行(2 PRECEDING)」としています。終了点指定がないので終了点は「現在行まで」です。たとえば、4行目のORADER_ID 1021の処理では、2行前の8および1行前の2と現在行の10を合計して、20となっていますね。また集計対象行が存在しない場合は単純に無視されるので、1行目は、1行目だけ。2行目は、1行目と2行目だけの合計になっています。

ROWS開始行
SELECT order_id, item, qty,
       SUM(qty) OVER (ORDER BY order_id ROWS 2 PRECEDING) result
FROM test_orders;

  ORDER_ID ITEM              QTY     RESULT             # 2行前から現在行まで
---------- ---------- ---------- ----------
      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)

ここまで分かれば、いわゆる移動平均が簡単に作れるのがイメージできると思います。一日一行のデータがあれば、7日移動平均はAVG()でROWS 6 PRECEDINGとするだけですね。

次にBETWEENを使って開始点と終了点を両方指定する方法もみてみます。この例では、一つ前の行から一つ後の行(BETWEEN 1 PRECEDING AND 1 FOLLOWING)の3行を常に集計対象としています。例えは3行目、8 + 2 + 10 = 20になってますね。

ROWS開始行と終了行
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             #  1行前から1行後まで
---------- ---------- ---------- ----------
      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)

ここで使える開始点、終了点の指定方法は、以下の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"の時だけ前後を含めた3行を集計対象とし、それ以外(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カラムを参照してください。

RANGE指定
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             #  10日前から現在行まで
---------- ---------- ---------- ---------- ---------- ----------
      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指定は、集計対象行数が不特定の場合に効果を発揮します。3日間の全てのオーダーの平均とか、または遡る日数を可変にして別カラムに用意しておけば非稼働日を含む5日稼働日間の集計なんかも簡単にできますね。

あとRANGE指定には、いくつかの制限があります。使用できるカラムは数値または日付のみです。従ってORDER BYのカラムが文字列の場合は、RANGEは使用できません。また、ORDER BYの並べ替えに複数のカラムを指定した場合、<n> PRECEDING<n> FOLLOWINGが使えなくなります。他の3つ(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

このフラグをランニング集計します。グループが変わる点で1が加算されるので、見事にグループ化できました。あとは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

追記:行間参照によるグループ化の演習問題を作ってみました。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 合計