SQL - グルーピング, 集約関数 の編集履歴

tomoda が 2015-08-29 16:12 に編集

--- Ver.6	2015-08-29 16:05:49+09:00
+++ Ver.7	2015-08-29 16:12:34+09:00
@@ -395,22 +395,23 @@
 ```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
+  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;
+ORDER BY area_id, item_id, sales_month;
 ```
 
 この SQL では、

tomoda が 2015-08-29 16:05 に編集

--- Ver.5	2015-08-29 16:04:55+09:00
+++ Ver.6	2015-08-29 16:05:49+09:00
@@ -372,7 +372,7 @@
 ## ウィンドウ関数の使用例
 
  area_id | item_id | sales_month | sales_amount 
----------+---------+-------------+--------------
+---------|---------|-------------|--------------
  001     | A       |           4 |          100
  001     | A       |           5 |          120
  001     | A       |           6 |          300

tomoda が 2015-08-29 16:04 に編集

--- Ver.4	2015-08-29 15:59:34+09:00
+++ Ver.5	2015-08-29 16:04:55+09:00
@@ -371,24 +371,24 @@
 
 ## ウィンドウ関数の使用例
 
- area_id | sales_month | item_id | sales_amount 
----------|-------------|---------|--------------
- 001     |           4 | A       |          100
- 001     |           5 | A       |          120
- 001     |           6 | A       |          300
- 001     |           7 | A       |           80
- 001     |           7 | B       |          110
- 001     |           4 | B       |          100
- 001     |           6 | B       |          250
- 001     |           5 | B       |          120
- 002     |           4 | A       |          150
- 002     |           5 | A       |          150
- 002     |           6 | A       |          100
- 002     |           7 | A       |          300
- 002     |           4 | B       |          150
- 002     |           7 | C       |          150
- 002     |           6 | C       |          250
- 002     |           5 | C       |          200
+ 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 を実行します。
 

tomoda が 2015-08-29 15:59 に編集

--- Ver.3	2015-08-29 13:05:19+09:00
+++ Ver.4	2015-08-29 15:59:34+09:00
@@ -214,7 +214,8 @@
 エリア・月毎の集計に加えて、月・エリア毎の集計も行われています。
 
 これも ROLLUP と同じく、「CUBE (area_id, sales_month)」の部分を「GROUPING SETS
-((area_id, sales_month), (area_id), ())」と置き換えても同じ結果が得られます。
+((area_id, sales_month), (area_id), (sales_month), ())」と置き換えても同じ結果が
+得られます。
 
 つまり、次の CUBE は、
 
@@ -269,6 +270,9 @@
 にパーティションに分割するだけで集約はしません。つまり、入力と出力の行数は同一と
 なります。
 
+また、GROUP BY 句と同じく省略した場合はテーブル全体 (抽出結果全体) が 1 つのパー
+ティションとして処理されます。
+
 
 ## ORDER BY 句
 
@@ -293,7 +297,7 @@
 ```
 
 UNBOUND PRECEDING を指定された場合、ウィンドウフレームの下限は必ずパーティション
-の先頭行となります。<window frame preceding> は、ウィンドウフレームの下限を何行前
+の先頭行となります。`<window frame preceding>` は、ウィンドウフレームの下限を何行前
 にするかの指定です。
 
 
@@ -321,6 +325,16 @@
 * EXCLUDE GROUP: 現在業と同じ値を持つ行を外す
 * EXCLUDE TIES: 現在業と同じ値を持つ行以外を外す
 * EXCLUDE NO OTHERS: いかなる行も外されないことを保証する
+
+ウィンドウフレーム句を省略した場合は、
+
+```sql
+ROWS BETWEEN UNBOUNDED PRECEDING
+         AND CURRENT ROW
+```
+
+が指定された場合と同様の動作となります。つまり、パーティションの先頭から現在行ま
+でがウィンドウフレームとして処理されます。
 
 
 ## ウィンドウ集約関数
@@ -380,44 +394,51 @@
 
 ```sql
 SELECT
-  area_id, item_id,
-  round(AVG(sales_amount)
+  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 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 area_id, item_id
+             ORDER BY sales_month
                  ROWS BETWEEN UNBOUNDED PRECEDING
-                          AND CURRENT ROW) AS moving_total
-FROM sales;
+                          AND CURRENT ROW
+                          ) AS moving_total
+FROM sales
+order by area_id, item_id, sales_month;
 ```
 
 この SQL では、
 
-* 2 ヵ月前からの平均売上
+* 2 ヵ月前からの移動平均売上
 * 累計の売上
 
-を求めます。次のような結果となります。
-
- area_id | item_id | moving_average | moving_total 
----------|---------|----------------|--------------
- 001     | A       |        100.000 |          100
- 001     | A       |        110.000 |          220
- 001     | A       |        173.333 |          520
- 001     | A       |        166.667 |          600
- 001     | B       |        110.000 |          110
- 001     | B       |        105.000 |          210
- 001     | B       |        153.333 |          460
- 001     | B       |        156.667 |          580
- 002     | A       |        150.000 |          150
- 002     | A       |        150.000 |          300
- 002     | A       |        133.333 |          400
- 002     | A       |        183.333 |          700
- 002     | B       |        150.000 |          150
- 002     | C       |        150.000 |          150
- 002     | C       |        200.000 |          400
- 002     | C       |        200.000 |          600
+を求めます。また、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
 
 
 # 参考文献

tomoda が 2015-08-29 13:05 に編集

--- Ver.2	2015-08-29 09:46:37+09:00
+++ Ver.3	2015-08-29 13:05:19+09:00
@@ -1,7 +1,7 @@
 SQL に関する話題は非常に多岐に渡りますが、本稿ではそのうちのグルーピング演算子、
 およびそれと組み合わせて使う集約関数について取り上げます。
 
-# ORDER BY 演算子
+# GROUP BY 演算子
 
 GROUP BY 演算子を使ってグループ化します。また、これと関連する機能として
 PARTITION BY 演算子があります。GROUP BY 演算子とは「複数の行を 1 つにまとめたうえ
@@ -264,8 +264,8 @@
 
 ## PARTITION BY 句
 
-GROUP BY 句と同じように、列名を使ってパーティションを定義します。ORDER BY 句と異
-なるのは、ORDER BY 句がグループを 1 行に集約するのに対して PARTITION BY 句では単
+GROUP BY 句と同じように、列名を使ってパーティションを定義します。GROUP BY 句と異
+なるのは、GROUP BY 句がグループを 1 行に集約するのに対して PARTITION BY 句では単
 にパーティションに分割するだけで集約はしません。つまり、入力と出力の行数は同一と
 なります。
 
@@ -283,8 +283,8 @@
 <window frame units>::= ROWS | RANGE
 ```
 
-ウィンドウ関数の処理対象 (ウィンドウ) として、行数では指定できない曖昧な値の範囲
-を指定する場合は RANGE を使います。行数で指定する場合は ROWS を使います。
+ウィンドウ関数の処理対象 (ウィンドウフレーム) として、行数では指定できない曖昧な
+値の範囲を指定する場合は RANGE を使います。行数で指定する場合は ROWS を使います。
 
 ```
 <window frame extent>::= <window frame start> | <window frame between>
@@ -292,9 +292,9 @@
 <window frame preceding>::= <unsigned value specification> PRECEDING
 ```
 
-UNBOUND PRECEDING を指定された場合、ウィンドウの下限は必ずパーティションの先頭行
-となります。<window frame preceding> は、ウィンドウの下限を何行前にするかの指定で
-す。
+UNBOUND PRECEDING を指定された場合、ウィンドウフレームの下限は必ずパーティション
+の先頭行となります。<window frame preceding> は、ウィンドウフレームの下限を何行前
+にするかの指定です。
 
 
 ```
@@ -305,19 +305,19 @@
 <window frame bound 2>::= <window frame bound>
 ```
 
-UNBOUND FOLLOWING を指定された場合、ウィンドウの上限は必ずパーティションの末尾行
-となります。<window frame following> は、ウィンドウの上限を何行先にするかの指定で
-す。
+UNBOUND FOLLOWING を指定された場合、ウィンドウフレームの上限は必ずパーティション
+の末尾行となります。<window frame following> は、ウィンドウフレームの上限を何行先
+にするかの指定です。
 
 
 ```
 <window frame exclusion>::= EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS
 ```
 
-<window frame exclusion> によって、行をウィンドウから外します。
-この機能をサポートしている実装は少ないようです。
-
-* EXCLUDE CURRENT ROW: ウィンドウから現在行を外す
+<window frame exclusion> によって、行をウィンドウフレームから外します。
+現状、この機能をサポートしている実装は少ないようです。
+
+* EXCLUDE CURRENT ROW: ウィンドウフレームから現在行を外す
 * EXCLUDE GROUP: 現在業と同じ値を持つ行を外す
 * EXCLUDE TIES: 現在業と同じ値を持つ行以外を外す
 * EXCLUDE NO OTHERS: いかなる行も外されないことを保証する
@@ -325,8 +325,8 @@
 
 ## ウィンドウ集約関数
 
-通常の集約関数もウィンドウ句を持つことができます。
-この場合、ウィンドウ句のない集約関数とは異なり、与えられたリストが集約されることはありません。
+通常の集約関数もウィンドウ句を持つことができます。この場合、ウィンドウ句のない集
+約関数とは異なり、与えられたリストが集約されることはありません。
 
 ```
 <aggregate function> OVER([PARTITION BY <column list>] [ORDER BY <sort column list>] [<window frame>])
@@ -341,12 +341,12 @@
 
 ## 順序関数
 
-* ROW_NUMBER: ORDER BY でソートされた順に、ウィンドウ内において行を一意に識別す
-  る連番を 1 から順に割り当てます。
-
-* RANK: ウィンドウ内の行に連番の順位を割り当てます。同位タイの行に対しては同じ番
-  号を割り当て、タイの下の順位には欠番が生じます。3 位が 2 行ある場合は、1, 2,
-  3, 3, 5, ... となります。
+* ROW_NUMBER: ORDER BY でソートされた順に、パーティション内において行を一意に識別
+  する連番を 1 から順に割り当てます。
+
+* RANK: パーティション内の行に連番の順位を割り当てます。同位タイの行に対しては同
+  じ番号を割り当て、タイの下の順位には欠番が生じます。3 位が 2 行ある場合は、1,
+  2, 3, 3, 5, ... となります。
 * DENSE_RANK: RANK とほぼ同じですが、タイの下の順位に欠番を生じさせません。3 位が
   2 行ある場合は、1, 2, 3, 3, 4, ... となります。
 

tomoda が 2015-08-29 09:46 に編集

--- Ver.1	2015-08-29 09:40:09+09:00
+++ Ver.2	2015-08-29 09:46:37+09:00
@@ -420,5 +420,7 @@
  002     | C       |        200.000 |          600
 
 
-参考文献: プログラマのための SQL 第 4 版
-
+# 参考文献
+
+* [プログラマのためのSQL 第4版, Joe Celko 著, ミック 監訳, 翔泳社](https://www.shoeisha.co.jp/book/detail/9784798128023)
+

tomoda が 2015-08-29 09:39 に投稿