分析関数学習.md

分析関数学習

http://codezine.jp/article/detail/1269

http://www.atmarkit.co.jp/ait/articles/0509/27/news125.html

http://www.atmarkit.co.jp/ait/articles/0510/29/news012.html

http://www.atmarkit.co.jp/ait/articles/0511/26/news010.html

分析関数の概念

分析関数とは

分析関数は集合内の指定された範囲で集計を行った結果を、各行に付与する。 group by で集計して行数を減らすことなく、集計関数(などの)の結果を得られる。

順番的には、グループ化、having条件が適用された後で処理される。 さらにその後で order by の並び替えが行われる。

SQL:2003 以降の標準SQLで規定されている。

基本構文

select ename
     , sal
     , RANK() over(order by sal desc) as rank
  from emp;

pertition構文

通常のSQLの group by にあたる、グループ化単位を指定する。 例えば、rank()で利用すると、指定されたpertitionごとに順位がリセットされる。

select ename
     , job
     , sal
     , rank() over(partition by job  order by sal desc) rank
  from emp;

分析関数の処理順序

1. 結合処理（表を結合する必要がある場合）、WHERE句、GROUP BY句、HAVING句の実行

2. 1で実行された結果セットをグループに分割、各グループの各行で計算を実行

3. ORDER BY句が存在する場合、適切な出力順序処理を実行

ウインドウ関数

ウインドウ関数

ウインドウ関数とは、rows between 書式で指定された集計範囲（＝ウインドウ）に対する 集計結果を戻す分析関数である。

ウインドウ関数を利用すると以下のことができる。

• 累積集計
• 移動集計
• 集中集計

分析関数として利用する、 SUM()、AVG()、MAX()、MIN()、COUNT()、STDDEV()、FIRST_VALUE()、LAST_VALUE() などがウインドウ関数にあたる。

例

select JOB
     , ENAME
     , SAL
     , sum(SAL) over(partition by JOB  -- ●A
                         order by SAL  -- ●B
                          rows between UNBOUNDED PRECEDING and CURRENT ROW   -- ●C
                    ) as AMOUNT_SAL
       from EMP
      order by JOB, SAL, ENAME;

A.集合（EMP表全体）をJOBごとにグループ分けするように指定

B.グループ内のデータをどのような順番で分析するかを指定

C.グループ内のデータをどのように集計するかウィンドウを指定

ウィンドウ指定の書式について

rows

rows between ウィンドウ開始点 and ウィンドウ終了点

rows between ウィンドウ開始点

という書式を利用して、行数でウインドウ範囲を指定する。

ウィンドウ開始点、終了点には以下のいずれかを利用することで、ウインドウを範囲を指定する。 ウインドウ終了点を省略した場合、ウインドウ終了点には CURRENT ROW が利用される。

• UNBOUNDED PRECEDING - グループの最初の行

• UNBOUNDED FOLLOWING - グループの最後の行

• PRECEDING - 現在行から行前の行

• FOLLOWING - 現在行から行後の行

• CURRENT ROW - 現在行

例えば、

rows between UNBOUNDED PRECEDING and CURRENT ROW

は、グループ開始～現在行が集計範囲となるため、グループごとに累計集計がされることになる。

range

range between ウィンドウ開始点 and ウィンドウ終了点

range between ウィンドウ開始点

という書式を利用して、値でウインドウ範囲を指定する。 ウィンドウ開始点、終了点には、rowsと同様の指定を行う。

row と range の違い。

rowsとの違いは以下である。 rowsは「取得結果の行数」によってウインドウを決定する。 rangeは「order by で指定したキーの値」によってウインドウを決定する。

• rows between 2 PRECEDING and CURRENT ROW

「2行前」～「現在行」までが集計範囲

|                   |集計範囲  |           |          |
|key                |1行目     |2行目      |3行目     |4行目
|                   |(1～1行)  |(1～2行)   |(1～3行)  |(2～4行)
|-------------------|----------|-----------|----------|----------
|1                  |◯        |◯         |◯        |
|2                  |          |◯         |◯        |◯
|(3の行は未存在)    |          |           |          |
|4                  |          |           |◯        |◯
|5                  |          |           |          |◯

 * range between 2 PRECEDING and CURRENT ROW

「-2の値」～「現在の値」までが集計範囲
   
|                   |集計範囲  |           |          |
|key                |1行目     |2行目      |3行目     |4行目
|                   |(-1～1)   |(0～2)     |(2～4)    |(3～5)
|-------------------|----------|-----------|----------|----------
|1                  |◯        |◯         |          |
|2                  |          |◯         |◯        |
|(3の行は未存在)    |          |           |          |
|4                  |          |           |◯        |◯
|5                  |          |           |          |◯

レポート関数

概要

総売り上げに対する各店舗の売り上げ比率などを求める際に利用する。

パーティション合計に対する割合を求めているだけなので、 ウインドウ関数に比べてかなり概念として単純。

例

以下のSQLで、SAL(給与)合計に対する、一人ひとりのSALが占める割合を出している。 レポート関数は算出に順番が必要とされないため、order by は指定しない。 (下の例では全体を対象とするため、partition by も省略しており over 句に何も指定が無い)

select ENAME
     , SAL
     , sum(SAL)             over() as TOTAL_SAL
     , ratio_to_report(SAL) over() as RATIO_SAL
  from EMP
 order by SAL;

分析関数リファレンス

通常関数

• rank

RANK () OVER ( [query_partition_clause]order_by_clause )

順番をつける。同じ値は同じ順位になり、行番号抜けが発生する。 例：1→2→2→4→5

• dense_rank

DENSE_RANK () OVER( [query_partition_clause] order_by_clause )

同じ値があっても番号抜けがおきないRANK。 例：1→2→2→3→4

• row_number

ROW_NUMBER () OVER( [query_partition_clause] order_by_clause )

行番号をつける。必ず連番になる。 例：1→2→3→4→5

• lag

LAG ( value_expr ,offset [, default] ) OVER ( [query_partition_clause] order_by_clause )

offset 行前の value_expr を取得する。 default は、offset 行前に行が存在しない場合に利用される値で、規定値はnull。

• lead

LEAD ( value_expr ,offset [, default] ) OVER ( [query_partition_clause] order_by_clause )

offset 行後の value_expr を取得する。 オプションは LAG と同じ。

ウインドウ関数

• sum

SUM (expr) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

合計を取得する。

• count

COUNT (expr) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

件数を取得する。 exprに「*」が指定されると純粋な行数、列が指定されると指定列がnullでない行数が取得される。 (通常のcountと同じ)

• max

MAX (expr) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

指定列の中で最大値を取得する。

• min

MIN (expr) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

指定列の中で最小値を取得する。

• avg

AVG (expr) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

指定列の中で最小値を取得する。

• first_value

FIRST_VALUE (expr [ IGNORE NULLS ]) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

最初の値を取得する。 IGNORE NULLSを指定すると、最初のNULLでない行の値が返される。

• last_value

LAST_VALUE (expr [ IGNORE NULLS ]) OVER( [query_partition_clause] order_by_clause ) [rows between [and ]]

最後の値を取得する。 IGNORE NULLSを指定すると、最初のNULLでない行の値が返される。

レポート関数

• RATIO_TO_REPORT

RATIO_TO_REPORT(expr) OVER( [query_partition_clause] )

パーティションの合計に対する値（各行）の割合を求める。

単語

• ウインドウ

分析関数が分析を行う範囲のこと。 たとえば orver (partition JOB order by SAL) は、JOBごとのウインドウに対して 分析が行われる。