SQLφ(..)メモメモその7 分析関数（Window関数）の登場

これ今勉強してる講座で最も難しいらしいけど、これは知っておきたい。

その後で色々調べたけど、どのブログ見てもわかりにくい。
だから、木田さんのこの講座がやっぱりおすすめ。

分析関数とはPARTITION BY でデータベースを特定の条件で２つに分けること。GROUP BYと違うのはGROUP BYは集計とか平均とか値を加工してしまうけど、PARTITION BYはまとめない。だからその後で色々計算ができる。

だからGROUP BYだと出した値を個別に分析できないが

• 何番目かをとう番号つけ関数 rank関数
• 最初の値を問う ナビゲーション関数　first_value関数
• 合計値の大きい順番など、集計して分析をする　集計分析関数

といった計算ができる柔軟性の高い関数。仮想的にグループ化することなのね。

OVER(分析対象のカラム名,パーティションの中で並べ替えを行うために使うカラム名 ASC|DESC, window_frame句）AS　別名

WINDOW FRAME句とは？

最初に指定するのはROWSもしくはRANGEになる
このあとに、フレームを指定する方法としてBETWEEN句をつかうことも使わないこともできる。

ROWS|RANGE BETWEEEN フレーム開始行 AND フレーム終了行設定
という書式。(|は正規表現関数のorね）
ROWSから書かないとエラーになる。カンマとか不要。なんか長ったらしいから気をつけて。

フレーム開始行とフレーム終了行は以下で設定する

UNBOUNDED PRECEDING パーティションで定義された境界の最も上の行
UNBOUNDED FOLLOWING パーティションで定義された境界の最も下の行
CURRENT ROW 現在の行
正の整数 PRECEDING 現在の行から正の整数だけ上の行
正の整数 FOLLOWING 現在の行辛い正の整数だけ下の行

3行前の値から、という場合は2 PRECEDINGね。

ROWS BETWEEN UNBOUNDED 何行 PRECEDING AND CURRENT ROW
のように設定する

番号付け関数

番号付け関数には
CUME_DISt()
DENSE_RANK()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()
がある

RANK()

同じ値に同じランク値を返し、次の値がスキップされる。戻り値は正の整数(INT64型）

RANK（）
OVER(
PARTITION BY パーティションを宣言する列 ORDER BY 並べる列 ASC|DESC)

PARTITION BYは任意ですが、ORDER BYは必須。

分析関数では、WHEREが使えないことに注意。WHEREは集計関数より先に実行されるため、WHERE句で分析関数で取得した値は利用できない。

ROW_NUMBER

ROW_NUMBER（）

パーティションの中の行番号を返します。各レコードにユニークな整数が戻る。

ROW_NUMBER（）
OVER(
PARTITION BY パーティションを宣言する列 ORDER BY 並べる列 ASC|DESC)

ナビゲーション関数

FIRST_VALUE()
LAST_VALUE()
LEAD()
LAG()
NTH_VALUE()
PERCENTILE_CONT()
PERCENTILE_DISC()
がある

FIRST_VALUE()とLAST_VALUE()

FIRST_VALUEもしくはLAST_VALUE(値を求める列)
OVER(PARTITION BY パーティション宣言する列 ORDER BY 順序を決めるカラム WINDOW FRAME
)
PARTITION BY とWINDOW FRAMEはオプションで、ORDER BYは必須

ただし注意したいのは、WINDOW関数、WINDOW FRAMEを指定していないと、ディフォルトは
LOWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
（CURRENT ROWとは現在の行ね）になってしまうので、LAST_VALUEは計算しているその行のLASTになり、PARTITION BYで区切ったデータの中の中LASTにはならない。だからPARTITION BYで区切ったデータの最後を見たいときはWINDOWS関数を加えなければいけない。

LEAD()
LAG()

LEAD関数は自分の今いる行の一行下
LAG関数は自分の今いる行の一行上
ただし行数はオプションで設定できる。

書式は
LEADもしくはLAG(列,行数,値がない場合の値)
OVER(
PARTITION BY 分けたい列 ORDER BY 順序を決める列
WINDOWS FRAME
)
;

集計分析関数

集計関数を分析関数でも利用できる。
COUNT() ,SUM(),AVG(),MAX(),MIN()など。

COUNT|SUM|AGV(取得したい列)
OVER(
PARTITION BY わけたい列
ORDER BY 並べたい列
WINDOWSFRAME関数例えば ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 命名したい名前

なんて書き方になるね。

移動平均とかとるときはPRECEDINGをつかうといいね。