SQL操作マニュアル9（ウィンドウ関数、GROPING）

10725 ワード

ウィンドウ関数
ウィンドウ関数はOLPAP関数とも呼ばれ、Online Analytical Processingと呼ばれ、データベースをリアルタイムで分析処理することを意味します。

ウィンドウ関数のシンタックス文法:

 OVER ([PARTITION BY ]
                    ORDER BY )

その中で重要なキーワードはPARTTIONとORDER BYであり、この二つのキーワードの役割を理解することがウィンドウ関数の理解を助ける鍵となります。
ウィンドウ関数として使用できる関数ウィンドウ関数は、大きく分けて以下の2つに分類されます。1.ウィンドウ関数としての集計関数（SUM、AVG、COUNT、MAX、MIN）。2.RANK、DENSE_RANK、ROW_NUMBERなどの専用ウィンドウ関数。

文法の基本的な使用方法は、RANK関数RANK関数を使用して名前の通り記録順序を計算するための関数である。

--                
SELECT shop_name,shop_type,sell_price,
RANK () OVER (PARTITION BY shop_type ORDER BY sell_price) AS ranking
FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
|         |       |       2000 |       1 |
|         |       |       6000 |       2 |
|        |         |        600 |       1 |
|         |         |       3000 |       2 |
| T       |         |       2000 |       1 |
|   T    |         |       3000 |       2 |
+-----------+-----------+------------+---------+

PATION BYは並べ替えの対象範囲を設定できます。例として商品の種類によって並べ替えられます。ORDER BYはどの列、どの順番で並べられますか？販売単価の上昇順に並べるために、sell uを指定しました。priceウィンドウ関数のORDER BYは、末尾にキーワードASC/DESCを付加することで昇順と降順を指定できます。このキーワードを省略します。デフォルトは昇順です。

PARTTION BYを指定しなくても、ウィンドウ関数を使用してキーとなるのはPARTTION BYとORDER BYです。この中で、PARTTION BYは必須ではなく、指定しなくても正常にウィンドウを使用することができます。

--   PARTITION BY      
 SELECT shop_name,shop_type,sell_price,
 RANK () OVER (ORDER BY sell_price) AS ranking
 FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
|        |         |        600 |       1 |
| T       |         |       2000 |       2 |
|         |       |       2000 |       2 |
|   T    |         |       3000 |       4 |
|         |         |       3000 |       4 |
|         |       |       6000 |       6 |
+-----------+-----------+------------+---------+

PARTTION BY指定列を使用しないで、全部の商品の並べ替えになりました。

専用のウィンドウ関数の種類は、上の並べ替え結果のように2番目であるため、次のビットが直接3番目をスキップして4番目になる。いくつかの特殊な場合には、1、2、3、4のようなスキップする必要がない順序が必要であり、この場合にはRANK以外の関数が必要となります。RANK関数：並べ替えを計算する時、同じ順位の記録があると、順位をスキップします。DENSE_RANK関数：並べ替えを計算する場合、同じ順位の記録があっても同じ順位はスキップされません。ROW_NUMBER関数：一意の連続順位を与える。

--         
SELECT shop_name,shop_type,sell_price,
RANK () OVER (ORDER BY sell_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sell_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sell_price) AS row_num
FROM shop_list;
+-----------+-----------+------------+---------+---------------+---------+
| shop_name | shop_type | sell_price | ranking | dense_ranking | row_num |
+-----------+-----------+------------+---------+---------------+---------+
|        |         |        600 |       1 |             1 |       1 |
| T       |         |       2000 |       2 |             2 |       2 |
|         |       |       2000 |       2 |             2 |       3 |
|   T    |         |       3000 |       4 |             3 |       4 |
|         |         |       3000 |       4 |             3 |       5 |
|         |       |       6000 |       6 |             4 |       6 |
+-----------+-----------+------------+---------+---------------+---------+

ウィンドウ関数の適用範囲は、ウィンドウ関数はSELECT子文でのみ使用できます。また、これらの関数はWHERE子文またはGROUTP BY子文では使用できません。ウィンドウ関数の目的は並べ替えですので、WHEREを使って記録を除去したり、GROUT BYを使って記録を集約したりして、並べ替えの結果が間違っていますので、何の意味もありません。だから、SELECT以外でウィンドウ関数を使うのは意味がありません。

は、ウィンドウ関数として、集計関数を使用するすべての集計関数を、ウィンドウ関数に使用することができ、その文法および専用ウィンドウ関数は、完全に同じです。

-- SUM        
SELECT shop_id,shop_name,sell_price,
SUM(sell_price) OVER (ORDER BY shop_id)AS current_sum
FROM shop_list;
+---------+-----------+------------+-------------+
| shop_id | shop_name | sell_price | current_sum |
+---------+-----------+------------+-------------+
| 0001    | T       |       2000 | 2000        |
| 0002    |        |        600 | 2600        |
| 0003    |   T    |       3000 | 5600        |
| 0004    |         |       6000 | 11600       |
| 0005    |         |       2000 | 13600       |
| 0006    |         |       3000 | 16600       |
+---------+-----------+------------+-------------+

は、移動平均ウィンドウ関数を計算すると、テーブルをウィンドウ単位で分割し、並べ替えを行う関数です。ウィンドウでより詳細な統計範囲を指定する代替機能も含まれています。この代替機能の統計範囲を「枠」と呼びます。

--  "    3 "      
SELECT shop_id,shop_name,sell_price,
    -> AVG(sell_price) OVER (ORDER BY shop_id ROWS 2 PRECEDING)AS moving_avg
    -> FROM shop_list;
+---------+-----------+------------+------------+
| shop_id | shop_name | sell_price | moving_avg |
+---------+-----------+------------+------------+
| 0001    | T       |       2000 | 2000.0000  |
| 0002    |        |        600 | 1300.0000  |
| 0003    |   T    |       3000 | 1866.6667  |
| 0004    |         |       6000 | 3200.0000  |
| 0005    |         |       2000 | 3666.6667  |
| 0006    |         |       3000 | 3666.6667  |
+---------+-----------+------------+------------+

番号0003のデータからデータが異なります。統計対象として「一番近い3行」を指定するフレームを作りました。ここではROWS（行）とPRECEDING（前）の2つのキーワードを使って、枠を「前～行」に指定します。したがって、ROWS 2 PRECEDINGは、前の2行までという意味です。フレームは現在の記録によって決められています。固定されているウィンドウとは異なり、その範囲は現在の記録によって変わります。自身（現在の記録）→前の1行の記録→前の2行の記録という統計方法を移動平均と呼びます。この方法は最近の状態をリアルタイムで把握したい時に便利なので、常に株式市場の動向をリアルタイムでフォローしています。キーワードFOLLOWINGを使ってPRECEDIINGを交換すると、「後～行」を枠として指定できます。

つのORDER BYウィンドウ関数の中のORDER BYは、並べ替えられた番号だけを並べていますが、どのように並べ替えられていますか？簡単です。末尾にORDER BY子文をつければいいです。

。

 SELECT shop_id,shop_name,sell_price,
 RANK() OVER (ORDER BY sell_price)AS ranking
 FROM shop_list
 ORDER BY ranking DESC;
+---------+-----------+------------+---------+
| shop_id | shop_name | sell_price | ranking |
+---------+-----------+------------+---------+
| 0004    |         |       6000 |       6 |
| 0003    |   T    |       3000 |       4 |
| 0006    |         |       3000 |       4 |
| 0001    | T       |       2000 |       2 |
| 0005    |         |       2000 |       2 |
| 0002    |        |        600 |       1 |
+---------+-----------+------------+---------+

GROPING演算子

合計値を同時に計算します。私達はGROUT BY子文を使って各種類の総価を合計すると、次のような表が得られます。

+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
|         | 5000            |
|         | 3600            |
|       | 8000            |
+-----------+-----------------+

これらの種類を合計するにはユニオンALLが必要です。

SELECT '  ' AS shop_type,SUM(sell_price)
FROM shop_list
UNION ALL
SELECT shop_type,SUM(sell_price)
FROM shop_list
GROUP BY shop_type;
+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
|         | 16600           |
|         | 5000            |
|         | 3600            |
|       | 8000            |
+-----------+-----------------+

これは私達が欲しい結果を計算することができますが、同じSELECT文を二回実行して、その結果を接続します。煩雑なだけでなく、DBMS内部処理コストも非常に高いです。だから、他のより効率的な方法を探してみます。

ROLLUP――合計値と小計値を同時に算出し、上述のような要求を満たすために、標準SQLはGROPING演算子を導入しています。GROPING演算子は主に以下の種類があります。1.ROLLUP 2.CUBE 3.GROPING SETS

ROLLUPの使い方

SELECT shop_type,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type WITH ROLLUP;
+-----------+-----------+
| shop_type | sum_price |
+-----------+-----------+
|       | 8000      |
|         | 3600      |
|         | 5000      |
| NULL      | 16600     |
+-----------+-----------+

この演算子の役割は、「異なる集合キーの組み合わせを一度に計算した結果」であり、その計算結果は、デフォルトではNULLを集約キーとして使用している。
「登録日」を集約キーに追加します。

--   ROLLUP   
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
|         | 2009-09-20    | 5000      |
|         | 2009-09-20    | 3600      |
|       | 2009-09-20    | 8000      |
+-----------+---------------+-----------+

--  ROLLUP   
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
|       | 2009-09-20    | 8000      |
|       | NULL          | 8000      |    ←  
|         | 2009-09-20    | 3600      |
|         | NULL          | 3600      |
|         | 2009-09-20    | 5000      |
|         | NULL          | 5000      |
| NULL      | NULL          | 16600     | ←  
+-----------+---------------+-----------+

二回の結果を比較したところ、ROLLUPを使って各カテゴリに対する小計が多く出ていることが分かりました。

GROUTPING関数——NULLをより分かりやすくするためにROLLUPを使用すると、NULLがいくつか出現します。日付自体がNULLである場合、NULLが表す意味はよく分かりません。混淆を避けるために、SQLは、スーパーパケットがNULLに記録されていると判断するための特定の関数、GROUTPING関数を提供します。この関数は、パラメータ列の値がスーパーパケットに記録されます。発生したNULLは1を返し、他の場合は0を返します。

 SELECT GROUPING(shop_type)AS shop_type,GROUPING(register_date)AS  register_date,SUM(sell_price)AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
|         0 |             0 | 8000      |
|         0 |             1 | 8000      |
|         0 |             0 | 3600      |
|         0 |             1 | 3600      |
|         0 |             0 | 5000      |
|         0 |             1 | 5000      |
|         1 |             1 | 16600     |   ←       NULL  1
+-----------+---------------+-----------+

この表は上のROLLUPで発生したテーブルに対応しており、NULLの位置を調べるとスーパーパケットが発生したかどうかが分かります。
GROUTPING関数を使用すると、スーパーパケット記録のキーに文字列を挿入することができます。つまり、GROPING関数の戻り値が1の場合は、「合計」と「小計」などの文字列を指定します。他の場合は通常の列の値を返します。

SELECT CASE WHEN GROUPING(shop_type)=1
            THEN '      '
            ELSE shop_type END AS shop_type,
            CASE WHEN GROUPING(register_date)=1
            THEN '      '
            ELSE register_date END AS register_date,
            SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+--------------+---------------+-----------+
| shop_type    | register_date | sum_price |
+--------------+---------------+-----------+
|          | 2009-09-20     | 8000      |
|          |           | 8000      |
|            | 2009-09-20    | 3600      |
|            |          | 3600      |
|            | 2009-09-20    | 5000      |
|            |          | 5000      |
|        |           | 16600     |
+--------------+---------------+-----------+

CUBE――データで積み木CUBEを積み重ねるのはキューブという意味で、ROLLUPと同じように、その役割を生き生きと説明しました。CUBEはROLLUPと同じ文法で、ROLLUPをCUBEで代用すればいいです。

GROUTPING SETS――所望の積木を取得する演算子は、ROLLUPとCUBEの結果から一部の記録（つまり、GROPINGの戻り値が1のデータ）を取得することができる。

androidイベント伝達メカニズム及びオンインテットタッチイベント（）とOnTouchEvent（）詳細解三のACT ION_CANCEL事件とイベントフィードバック

elasticsearchは重合後の2つのフィールドの相殺、加算、相乗、演算を実現します。