SQL に関する話題は非常に多岐に渡りますが、本稿ではそのうちのグルーピング演算子、 およびそれと組み合わせて使う集約関数について取り上げます。

GROUP BY 演算子

GROUP BY 演算子を使ってグループ化します。また、これと関連する機能として PARTITION BY 演算子があります。GROUP BY 演算子とは「複数の行を 1 つにまとめたうえ で、各グループについて 1 行を返す」ものです。それに対し、PARTITION BY 演算子とは 「もとのテーブルを複数の部分集合に切り分ける」ものです。まずは、GROUP BY 演算子に ついて取り上げます。

例えば次のような「sales」テーブルがあるとき、

area_id sales_month item_id sales_amount
001 4 A 100
001 4 B 100
001 5 A 120
001 5 B 120
002 4 A 150
002 4 B 150
002 5 A 150
002 5 C 200
003 4 A 100
003 4 B 100
003 5 A 120
003 5 B 120

次の SQL でエリア別、月別の売上を集計できます。

SELECT area_id, sales_month, SUM(sales_amount) total_amount
FROM sales
GROUP BY area_id, sales_month
;
area_id sales_month total_amount
001 4 200
001 5 240
002 4 300
002 5 350
003 4 200
003 5 240

また、次のように HAVING 句を使うことにより「合計売上が 200 より大きい」集計結果 だけを抽出できます。

SELECT area_id, sales_month, SUM(sales_amount) total_amount
FROM sales
GROUP BY area_id, sales_month
HAVING SUM(sales_amount) > 200
;
area_id sales_month total_amount
001 5 240
002 4 300
002 5 350
003 5 240

このとき、グルーピングキーでない item_id は SELECT 句、および HAVING 句で参照する ことはできません1

GROUP BY 句の指定により area_id と sales_month で一意になるようにレコードがグルー プ化されている (グループ毎に 1 行の情報として集約されている) ためです。ただし、集 約関数を適用した列についてはその限りではありません。この場合は SUM を適用した sales_amount があてはまります。

集約関数

GROUP BY が使われていない (グループ化されていない) 場合、集約関数は WHERE 句を経 て抽出されたレコード全体に対して作用します (レコード全体を一つのグループとして作 用する) が、グループ化されている場合は各グループに対して作用します。

集約関数の処理対象は「グループの値のリスト」です。このとき、NULL はリストから予め 除外されて集約関数に渡されます。

集約関数には次のようなものがあります。

  • 数学的演算: 平均 (AVERAGE), 合計 (SUM)。数値型に対して適用できる
  • 極値関数: 最小 (MIN), 最大 (MAX)。順序を持つ全てのデータ型に対して適用できる
  • カウント: テーブルの (抽出結果の) 行数を数える (COUNT)

集約関数には、ALL, DISTINCT のオプションを指定できます。 ALL を指定した場合は、値のリストに重複があった場合も全て渡されます。 DISTINCT を指定した場合は重複を除外したリストが渡されます。 省略した場合は ALL を指定した場合の振る舞いをします。

例えば、

SELECT COUNT(a.name)
FROM (
    SELECT 'foo' name FROM dual UNION ALL
    SELECT 'bar' name FROM dual UNION ALL
    SELECT 'foo' name FROM dual
  ) a;

の結果 は 3 となりますが、COUNT(DISTINCT a.name) とした場合は 2 となります。

また、空集合を渡された場合の振る舞いが関数によって異なります。COUNT 関数は 0 を返 しますが、これ以外の関数は空集合を返します。このとき、(COUNT 以外の) 集約関数が使 われている SELECT 句に他の列が指定されている場合は、空集合は NULL に変換されます2

GROUPING 演算子

SQL-99 では、GROUP BY に ROLLUP と CUBE オプションの拡張が行われました。これによっ て作られるグループは超集合 (super-group) と呼ばれます。これは、従来の SQL でも GROUP BY と UNION を使って書くことができましたが、より簡素な SQL で同じことを実現 できるようになっています。

ROLLUP

例えば、次の SQL によってエリア・月での小計を求めつつ、最後に合計を出力することが できます。

SELECT
  GROUPING(area_id) grouping_area,
  GROUPING(sales_month) grouping_month,
  area_id, sales_month, SUM(sales_amount) total_amount
FROM sales
GROUP BY ROLLUP (area_id, sales_month)
ORDER BY area_id, sales_month;
grouping_area grouping_month area_id sales_month total_amount
0 0 001 4 200
0 0 001 5 240
0 1 001 440
0 0 002 4 300
0 0 002 5 350
0 1 002 650
0 0 003 4 200
0 0 003 5 240
0 1 003 440
1 1 1530

「ROLLUP (area_id, sales_month)」の部分は「GROUPING SETS ((area_id, sales_month), (area_id), ())」と置き換えても同じ結果が得られます。

つまり、次の ROLLUP は、

ROLLUP (a, b, c)

次のように GROUPING SETS を繰り返すことで定義されます。

GROUPING SETS (
  (a, b, c),
  (a, b),
  (a),
  ()
  )

また、SELECT 句に「GROUPING(area_id)」のような指定がありますが、これは、指定され た列がスーパーグループの対象であるか (スーパーグループとして集計さた結果、指定さ れた列が NULL となっているか) どうかを返します。スーパーグループとして集計されて いる場合は 1, そうでない場合は 0 となります。

CUBE

CUBE は、ROLLUP で 生成されたスーパーグループ行に加えて「クロス集計表」を生成しま す。例えば、次のような出力結果となります。

SELECT
  GROUPING(area_id) grouping_area,
  GROUPING(sales_month) grouping_month,
  area_id, sales_month, SUM(sales_amount) total_amount
FROM sales
GROUP BY CUBE (area_id, sales_month)
ORDER BY area_id, sales_month;
grouping_area grouping_month area_id sales_month total_amount
0 0 001 4 200
0 0 001 5 240
0 1 001 440
0 0 002 4 300
0 0 002 5 350
0 1 002 650
0 0 003 4 200
0 0 003 5 240
0 1 003 440
1 0 4 700
1 0 5 830
1 1 1530

エリア・月毎の集計に加えて、月・エリア毎の集計も行われています。

これも ROLLUP と同じく、「CUBE (area_id, sales_month)」の部分を「GROUPING SETS ((area_id, sales_month), (area_id), (sales_month), ())」と置き換えても同じ結果が 得られます。

つまり、次の CUBE は、

CUBE (a, b, c)

次のように GROUPING SETS を繰り返すことで定義されます。

GROUPING SETS (
  (a, b, c),
  (a, b),
  (a, c),
  (b, c),
  (a),
  (b),
  (c),
  ()
  )

ウィンドウ句

ウィンドウ句は、ウィンドウ関数、OVER 句とも呼ばれます。 次のような順序で処理されます。

  • テーブル (抽出結果) を PARTITION BY でパーティションに分割する
  • パーティション内の行を ORDER BY でソートする
  • ウィンドウ関数が呼び出されたカレント行に仮想的なカーソルがセットされ、ウィンド ウフレーム句で処理対象の行を選択する
  • 選択された行 (部分集合) が集約関数、または順序関数に渡され、スカラ値を返す

ウィンドウ関数は関数ですが、その構文は他の関数のそれとは大きく異なります。 BNF は次の通りです。

<window function>::= <window function type> OVER <window name OR speccification>
<window function type>::= <rank function type> | ROW_NUMBER() | <aggregate function>
<rank function type>::= RANK() | DENSE_RANK() | PERCENT_RANK() | CUME_DIST()
<window name OR speccification>::= <window name> | <in-line window specification>
<in-line window specification>::= <window specification>

ウィンドウ句は PARTITION BY 句、ORDER BY 句、ウィンドウフレーム句から構成されま す。

PARTITION BY 句

GROUP BY 句と同じように、列名を使ってパーティションを定義します。GROUP BY 句と異 なるのは、GROUP BY 句がグループを 1 行に集約するのに対して PARTITION BY 句では単 にパーティションに分割するだけで集約はしません。つまり、入力と出力の行数は同一と なります。

また、GROUP BY 句と同じく省略した場合はテーブル全体 (抽出結果全体) が 1 つのパー ティションとして処理されます。

ORDER BY 句

通常の ORDER BY 句と同じように列名を指定してソートします。ただし、ウィンドウ句に おいてはパーティション内においてソートされます。

ウィンドウフレーム句

<window frame clause>::= <window frame units> <window frame extent> [<window frame exclusion>]
<window frame units>::= ROWS | RANGE

ウィンドウ関数の処理対象 (ウィンドウフレーム) として、行数では指定できない曖昧な 値の範囲を指定する場合は RANGE を使います。行数で指定する場合は ROWS を使います。

<window frame extent>::= <window frame start> | <window frame between>
<window frame start>::= UNBOUND PRECEDING | <window frame preceding> | CURRENT ROW
<window frame preceding>::= <unsigned value specification> PRECEDING

UNBOUND PRECEDING を指定された場合、ウィンドウフレームの下限は必ずパーティション の先頭行となります。<window frame preceding> は、ウィンドウフレームの下限を何行前 にするかの指定です。

<window frame bound>::= <window frame start> | UNBOUND FOLLOWING | <window frame following>
<window frame following>::= <unsigned value specification> FOLLOWING
<window frame between>::= BETWEEN <window frame bound 1> AND <window frame bound 2>
<window frame bound 1>::= <window frame bound>
<window frame bound 2>::= <window frame bound>

UNBOUND FOLLOWING を指定された場合、ウィンドウフレームの上限は必ずパーティション の末尾行となります。 は、ウィンドウフレームの上限を何行先 にするかの指定です。

<window frame exclusion>::= EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS

によって、行をウィンドウフレームから外します。 現状、この機能をサポートしている実装は少ないようです。

  • EXCLUDE CURRENT ROW: ウィンドウフレームから現在行を外す
  • EXCLUDE GROUP: 現在業と同じ値を持つ行を外す
  • EXCLUDE TIES: 現在業と同じ値を持つ行以外を外す
  • EXCLUDE NO OTHERS: いかなる行も外されないことを保証する

ウィンドウフレーム句を省略した場合は、

ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW

が指定された場合と同様の動作となります。つまり、パーティションの先頭から現在行ま でがウィンドウフレームとして処理されます。

ウィンドウ集約関数

通常の集約関数もウィンドウ句を持つことができます。この場合、ウィンドウ句のない集 約関数とは異なり、与えられたリストが集約されることはありません。

<aggregate function> OVER([PARTITION BY <column list>] [ORDER BY <sort column list>] [<window frame>])
<aggregate function>::=
  MIN([DISTINCT | ALL] <value exp>) | 
  MAX([DISTINCT | ALL] <value exp>) | 
  SUM([DISTINCT | ALL] <value exp>) | 
  AVG([DISTINCT | ALL] <value exp>) | 
  COUNT([DISTINCT | ALL] [<value exp> | *])

順序関数

  • ROW_NUMBER: ORDER BY でソートされた順に、パーティション内において行を一意に識別 する連番を 1 から順に割り当てます。

  • RANK: パーティション内の行に連番の順位を割り当てます。同位タイの行に対しては同 じ番号を割り当て、タイの下の順位には欠番が生じます。3 位が 2 行ある場合は、1, 2, 3, 3, 5, ... となります。

  • DENSE_RANK: RANK とほぼ同じですが、タイの下の順位に欠番を生じさせません。3 位が 2 行ある場合は、1, 2, 3, 3, 4, ... となります。

  • PERCENT_RANK: 百分率を使った相対順位を 0 〜 1 の範囲内で割り当てます。

  • CUME_DIST: PERCENT_RANK と似ていますが、パーセンタイル値を 0 〜 1 の範囲内で割 り当てます。

ウィンドウ関数の使用例

area_id item_id sales_month sales_amount
001 A 4 100
001 A 5 120
001 A 6 300
001 A 7 8000
001 B 4 100
001 B 5 120
001 B 6 250
001 B 7 110
002 A 4 150
002 A 5 150
002 A 6 100
002 A 7 300
002 B 4 150
002 C 5 200
002 C 6 250
002 C 7 150

上記の表に対して次の SQL を実行します。

SELECT
  area_id, item_id, sales_month, sales_amount,
  ROW_NUMBER() OVER (
    PARTITION BY area_id, item_id
        ORDER BY sales_month
    ) AS rownum,
  ROUND(AVG(sales_amount) OVER(
    PARTITION BY area_id, item_id
        ORDER BY sales_month
            ROWS 2 PRECEDING
    ), 3) AS moving_average,
  SUM(sales_amount) OVER(
    PARTITION BY area_id, item_id
        ORDER BY sales_month
            ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW
    ) AS moving_total
FROM sales
ORDER BY area_id, item_id, sales_month;

この SQL では、

  • 2 ヵ月前からの移動平均売上
  • 累計の売上

を求めます。また、ROW_NUMBER によってパーティション内の連番を割り振ります。次のよ うな結果となります。

area_id item_id sales_month sales_amount rownum moving_average moving_total
001 A 4 100 1 100.000 100
001 A 5 120 2 110.000 220
001 A 6 300 3 173.333 520
001 A 7 8000 4 2806.667 8520
001 B 4 100 1 100.000 100
001 B 5 120 2 110.000 220
001 B 6 250 3 156.667 470
001 B 7 110 4 160.000 580
002 A 4 150 1 150.000 150
002 A 5 150 2 150.000 300
002 A 6 100 3 133.333 400
002 A 7 300 4 183.333 700
002 B 4 150 1 150.000 150
002 C 5 200 1 200.000 200
002 C 6 250 2 225.000 450
002 C 7 150 3 200.000 600

参考文献


  1. ただし、DBMS によってはグルーピングキーでない列も参照できるようです。MySQL 5.6 では参照できました。。 

  2. ただし、MySQL では、単独で (空集合を引数に取る) 集約関数を使った場合にも NULL が返されました。。