SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD：AABlog：So-netブログ

	ブログをはじめるログイン

Android 久ぶりにアンドロイドの記.. ｜SQL分析関数 ROW_NUMBER・R.. ブログトップ

SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD　[SQLポケリ] [編集]

本日は、SQLの話である。ブログに登場するのは唐突かも知れないが...

分析関数

最近のSQLには分析関数という新しいジャンルの関数が存在する。できることは、集計関数とそれほど違いはないのだが、分析関数の特徴は、グループ化の方法や、集計する際の順番を関数毎に指定できるということ。

集計関数では、グループ化の具合は、SELECT命令のGROUP BYで指定する。集計するにあたり「処理順序は気にしない」。例えば、合計値を集計する時に、{1,2,3}といった集合があったとして、1+2+3=6という計算をしてもいいし、3+2+1=6としてもOK。

ところが、分析関数になるとそうもいかない。なぜなら、ソートして順に並んでいる状態で、先頭のデータとか、中央の値とか色々な集計というか、分析のための計算を行うことができるのである。

先ほどの例{1,2,3}で説明すると、昇順に並べた状態で、先頭なら1になるが、降順なら3となるであろう。

SQLで解説していくことにする。

まずは、テーブルを作ってみる。

CREATE TABLE test_analyze (
 val INTEGER
);

INSERT INTO test_analyze VALUES(1);
INSERT INTO test_analyze VALUES(2);
INSERT INTO test_analyze VALUES(3);

SELECT * FROM test_analyze;

val
----
   1
   2
   3

FIRST_VALUE と LAST_VALUE

先頭のデータを取得する分析関数は、FIRST_VALUEである。分析関数を使用するにあたっては「OVER句が必要になる」。OVER句は従来からある集計関数では必要のないものである。OVER句では計算の順序を指定する。指定方法は、ORDER BYでの列指定となる。ORDER BYのやり方は、SELECT命令でソート方法を指定することと同じ感覚でできる。ASC、DESCでソート方法を変更可能である。
では、やってみよう。まずは、昇順で並べて、先頭データを参照してみる。

SELECT val, FIRST_VALUE(val) OVER (ORDER BY val ASC) FV
FROM test_analyze

val  FV
---- ----
   1    1
   2    1
   3    1

昇順で並べているから、先頭データは最小値になる。1,2,3のうち最初の1が計算される。
では、降順にしたら、3が計算されるのであろうか。やってみよう。

SELECT val, FIRST_VALUE(val) OVER (ORDER BY val DESC) FV
FROM test_analyze

val  FV
---- ----
   1    3
   2    3
   3    3

なりました。
サブクエリで、最小値をMINで取ってくると「同じ値が重複していて、スカラ値にならないで困る」ということが往々にしてある。FIRST_VALUEなら「必ずスカラ値が戻る」のでこう言った面倒がない。

FIRST_VALUEは「グループの先頭データを取得できる分析関数」であることがわかった。

　ちょっと待て、GROUP BYしていないからグループなんてないのでは？

そうです。SELECT命令と同じように、グループ化する必要がなければ、グループ化の方法を省略することができる。省略した場合は、全体が一つのグループとみなされる。
分析関数でのグループ化は、PARTITION BY句で指定する。GROUP BYではないので、注意したいところ。PARTITION BYを省略した場合は、全体が一つのグループになる。

作成した例が良くなかったので、PARTITION BYの例は、また後でやることにしよう。

LAST_VALUEをやってみる

FIRST_VALUEは先頭だが、LAST_VALUEというのもある。こちらは、末尾を参照することができるのだが、ちょっと曲者です。まぁ、やってみましょうか。

SELECT val, FIRST_VALUE(val) OVER (ORDER BY val ASC) FV,
 LAST_VALUE(val) OVER (ORDER BY val ASC) LV
FROM test_analyze

val  FV   LV
---- ---- ----
   1    1    1
   2    1    2
   3    1    3

昇順ソートなので、FVは1、LVは3になってくれると思ったのだが...
その行と同じ値になっちゃっている。これは？

と最初は、思うと思います。普通に考えれば、3になってくれることを期待する。

「どうしてか」というと、分析関数に計算を依頼する際、デフォルトでは、SELECT命令で計算を行っているカレントの行までが、渡されるようになっているから。つまり、val=1の行を処理している際は、集合{1}が分析関数に渡される。次の行val=2の時点では、{1,2}となり、さらに、3では{1,2,3}になる。
FIRST_VALUEでは、先頭しか参照しないので良かったが、LAST_VALUEは末尾になる。分析関数に与える集合が、その行を処理している中間結果となるため、末尾のデータと言っても、最終結果での末尾とは一致しない。

分析関数に与える集合をどの範囲までにするかを変更することができる。
デフォルトでは、次のような指定になっている。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW FOLLOWING)

「カレント行まで」のところを「全部」に変更すれば、最終結果での末尾を参照することが可能になるはず。
やってみよう。

SELECT val, FIRST_VALUE(val) OVER (ORDER BY val ASC) FV,
 LAST_VALUE(val) OVER (ORDER BY val ASC
  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LV
FROM test_analyze

val  FV   LV
---- ---- ----
   1    1    3
   2    1    3
   3    1    3

できた。
「デフォルトでカレント行まで」となっているのは、単にパフォーマンス的に有利だから。先頭データだけを参照するのであれば、カレント行より上のデータがわかれば良いので。並列処理でパイプライン的に処理することも可能と思われる。

NTH_VALUE

Oracle限定になってしまうが、「任意の位置のデータ」を参照することも可能である。NTH_VALUEには、引数で何番目の位置にあるデータが欲しいのかを指定する。
NTH_VALUE(val,1) なら FIRST_VALUE(val)と同じことになる。

以下は、2番目の位置にあるデータを参照する例である。

SELECT val, FIRST_VALUE(val) OVER (ORDER BY val ASC) FV,
 NTH_VALUE(val, 2) OVER (ORDER BY val ASC
  RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SV,
 LAST_VALUE(val) OVER (ORDER BY val ASC
  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LV
FROM test_analyze

val  FV   SV   LV
---- ---- ---- ----
   1    1    2    3
   2    1    2    3
   3    1    2    3

LAST_VALUEと同様に、カレント行より後に参照したい場所がある場合に備え、範囲を指定する必要がある。「1 FOLLOWING」を指定して、カレント行から1行後までを「分析関数に与える範囲」としている点に着目して欲しい。

LAGとLEAD

NTH_VALUEでは、集合のどの位置にあるデータなのかを絶対値で指定しなければならなかった。LAG、LEADを使うことで、カレント行からの相対位置指定が可能となる。
LAGは、前方への参照、LEADは、後方への参照となる。NTH_VALUEと同様に、位置指定のための引数を与える必要がある。
以下の例を見て頂ければ、すんなりと理解できると思われる。

SELECT val, LAG(val,1) OVER (ORDER BY val ASC) LAG,
 LEAD(val, 1) OVER (ORDER BY val ASC) LEAD
FROM test_analyze

val  LAG  LEAD
---- ---- ----
   1 null    2
   2    1    3
   3    2 null

LAGでは、一つ前のレコードの値。LEADでは、一つ後のレコードの値を参照できます。
いやはや、これは便利ですよ。例えば、年間の実績値を集計して画面表示していたとしましょう。

年     実績値
2013   32400
2014   33500
2015   34200

前年比を横に計算してよ、という要望は多くあるでしょう。

SELECT 年, 実績値, LAG(実績値, 1) OVER (ORDER BY 年) 前年,
 ROUND(実績値 / LAG(実績値, 1) OVER (ORDER BY 年) * 100,2) 前年比
 FROM 実績集計

年     実績値  前年    前年比
------ ------ ------ ------
2013   32400  null   null
2014   33500  32400  103.4
2015   34200  33500  102.09

あっという間に出来ちゃいました。

えーと話が長くなってきたので、本日は終了。本当は、最頻値や中央値の話がしたかったのではあるが、また次回ということで。