SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

本日もSQL分析関数である。

本日は、予定していた通り、CUME_DISTを紹介したい。
マニュアルを見ると、CUME_DISTは、累積分布を計算する分析関数、と書いてある。

はーん、わかったようなわからないような。

また、CUME_DISTは0〜1.0の範囲で値を戻す、とも書いてある。

なるほど、パーセンタイル値と同じか。
じゃあ、PERCENT_RANKと同じじゃない？とも思ってみたが、そうでもないみたい。
比較してみた。

-- PERCENT_RANK

SELECT val, PERCENT_RANK() OVER (ORDER BY val)
 FROM test_analyze3;

val  PRNK
---- ----
   1    0   -- {1}          0/4 0.0
   2 0.25   -- {1,2}        1/4 0.25
   3  0.5   -- {1,2,3}      2/4 0.5
   3  0.5   -- {1,2,3,3}    2/4 0.5
   4    1   -- {1,2,3,3,4}  4/4 1.0


-- CUME_DIST

SELECT val, CUME_DIST() OVER (ORDER BY val)
 FROM test_analyze3;

val  CDST
---- ----
   1  0.2   -- {1}          1/5 0.2
   2  0.4   -- {1,2}        2/5 0.4
   3  0.8   -- {1,2,3}      4/5 0.8
   3  0.8   -- {1,2,3,3}    4/5 0.8
   4    1   -- {1,2,3,3,4}  5/5 1.0

計算式の違いは、以下のようになる。
PERCENT_RANKは、RANK-1 / 全体の個数-1
CUME_DISTは、集合の個数 / 全体の個数

分子の方は微妙に異なる。分母の方は、全体の個数を-1するかどうかの違いである。

これだけでCUME_DISTの解説はおしまい。というのも寂しいので、解説していないPARTITION BYについても書いてみたいと思う。

PARTITION BY

分析関数は、OVER句で順序を指定する必要がある。これまでの例では、ORDER BYで順序のみを指定していたのだが、ここにPARTITION BYでグループ化の方法を指定できる。
グループ分けできるサンプルデータを作成して、PARTITION BYをやってみよう。

CREATE TABLE test_analyze5 (
 type INTEGER,
 val INTEGER
);

INSERT INTO test_analyze5 VALUES(1, 1);
INSERT INTO test_analyze5 VALUES(1, 2);
INSERT INTO test_analyze5 VALUES(1, 3);
INSERT INTO test_analyze5 VALUES(1, 4);
INSERT INTO test_analyze5 VALUES(2, 1);
INSERT INTO test_analyze5 VALUES(2, 1);
INSERT INTO test_analyze5 VALUES(2, 2);
INSERT INTO test_analyze5 VALUES(2, 3);
INSERT INTO test_analyze5 VALUES(2, 4);

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME
---- ---- ----
   1    1 0.25
   1    2  0.5
   1    3 0.75
   1    4    1
   2    1  0.4
   2    1  0.4
   2    2  0.6
   2    3  0.8
   2    4    1

PARTITION BYは、OVER句のカッコの中に記述する。ORDER BYは必須だが、PARTITION BYは任意。順番は、PARTITION BY、ORDER BYの順に書く必要がある。SELECT命令でのGROUP BYとORDER BYの関係と同じなので覚えやすいと思う。
PARTITION BYのグループ化指定方法も、GROUP BYと同じ。例では、type列の値別にグループ化してみた。

type val  CUME
---- ---- ----
   1    1 0.25  -+
   1    2  0.5   +- type=1のグループ
   1    3 0.75   |
   1    4    1  -+
   2    1  0.4  -+
   2    1  0.4   |
   2    2  0.6   +- type=2のグループ
   2    3  0.8   |
   2    4    1  -+

PARTITION BYを省略すると、全体が一つのグループになる。
例では、type列の値別にPARTITION BYしている。type=1とtype=2の二つのグループに分けられ、その中でvalの値が昇順で並び替えられていることがわかる。

COUNT OVER

さて、ここまでは分析関数に対してOVER句を指定して、どういった計算が行われるのかを紹介してきた。実は、従来からある、集計関数もOVER句を付けて分析関数のように使うこともできる。
その例として、COUNT関数をOVER句付きで使ってみよう。上記のCUME_DISTの例では、type=1のグループとtype=2のグループで行数が異なっていた。これをCOUNT関数で計算してみよう。

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME,
  COUNT(*) OVER (PARTITION BY type) CONT
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME CONT
---- ---- ---- ----
   1    1 0.25    4
   1    2  0.5    4
   1    3 0.75    4
   1    4    1    4
   2    1  0.4    5
   2    1  0.4    5
   2    2  0.6    5
   2    3  0.8    5
   2    4    1    5

COUNTは集計関数である。OVERを付けなければ、集計関数なので、SELECT命令にGROUP BYを付けて使用することが多いと思う。しかし、OVER句を付けて、分析関数として使えば、グループ化の方法は、OVER句のPARTITION BYの内容で決定できる。
COUNTで個数を集計するにあたり、データの並び順は重要ではなく、必要のない情報であるため、「ORDER BYを省略できる」。一方、順番に並べることで計算を行っていた分析関数では、ORDER BYを省略することはできない。
PARTITION BYはグループ化の必要がなければ、COUNTでも省略可能である。

では、COUNTのOVER句でORDER BYを指定しても意味のないものとなり、無視されるのであろうか？ちょっとやってみることにする。

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME,
  COUNT(*) OVER (PARTITION BY type ORDER BY val) CNTO,
  COUNT(*) OVER (PARTITION BY type) CONT
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME CNTO CONT
---- ---- ---- ---- ----
   1    1 0.25    1    4
   1    2  0.5    2    4
   1    3 0.75    3    4
   1    4    1    4    4
   2    1  0.4    2    5
   2    1  0.4    2    5
   2    2  0.6    3    5
   2    3  0.8    4    5
   2    4    1    5    5

おー、予想に反して「無視されることはない」ようである。CNTOの列が、ORDER BY付きである。ORDER BYを付けることで、分析関数に渡される集合が、処理しているカレント行までとなる。
CNTO/CONTでCUME_DISTが計算できている。あー、そういうことか。

ここでは、COUNTの例を紹介したが、集計関数のSUM、AVG、MIN、MAXでもOVER句を使って分析関数のように使用することができる。

本日はここまで。

関連記事

　SQL分析関数 ROW_NUMBER RANK DENSE_RANK
　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD
　SQL分析関数 PERCENT_RANK パーセンタイル
　SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC
　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：分析関数 SQL

SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC 中央値(MEDIAN)

本日の話題もSQL分析関数である。

本日紹介したいのは、PERCENTILEである。パーセンタイル。あまり聞かない単語ではある。ちょっと検索してみたら、母子手帳にはパーセンタイルでグラフが載っているらしい。ふーん。ということは結構メジャーなものなのか。
前回紹介した、PERCENT_RANKは順位をパーセンテージで計算しました、っていうだけなので、パーセンタイルとはちょっと違う気もしないでもないが、同じかも？

どうも、順番に並べ替えて、パーセンテージでそのデータの位置を示したもの。というのがパーセンタイルらしいが...
まさに、PERCENT_RANKで計算できるような感じがするが。

分析関数としては、PERCENTILE_CONTとPERCENTILE_DISCの二つがある。どちらもパーセンタイル値を計算できるようなのだが、使い方が他の分析関数と比べて微妙に違う。
まず、OVER句ではなく、WITHIN GROUPでソート順を指定しないといけない。OVER句も付けることは可能ではあるが、PARTITION BYしか記述できない。さらに、関数に引数が必要なのである。PERCENT_RANKでは必要なかったのに... どうも勝手が違うなぁ、と思っていたら「やっぱり勘違い」していた。

PERCENTILE_CONT関数は、パーセンタイル値を計算してくれるのではなく、パーセンタイル値を引数で与えると、元の値が戻されるという関数なのだ。要は、PERCENT_RANKの逆関数みたいなものか。

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val)

とすると、val列でソートして計算したパーセンタイル値0.5に相当するval列の値を戻してくれる。
パーセンタイル値は0〜1.0の範囲外になることはない(試しに入れてみたらエラーになった)。
引数で0を与えれば、PERCENTILE_CONTは一番優秀とされたvalの値を戻す。1を与えれば逆に最低の成績を戻す。0.5ならちょうど真ん中の値を戻してくれる。という理屈である。

0.5を与えた際に戻ってくる値を「中央値」と呼ぶ。データ分析をする上では、中央値という言葉をよく耳にすると思う。
PERCENTILE_CONTの引数に0.5を入れることで中央値を計算できる。と覚えておこう。

SELECT val, PERCENT_RANK() OVER (ORDER BY val) RANK
 FROM test_analyze2

val  RANK
---- ----
   1    0
   2 0.25
   3  0.5
   3  0.5
   4    1

SELECT 
 PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY val) hi,
 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) mid,
 PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY val) lo
FROM test_analyze2

hi   mid  lo
---- ---- ----
   1    3    4

関連記事
　分析関数percentile_cont 中央値でノイズ除去

蛇足ながら、データ分析で使われる「なんとか値」をまとめてみたいと思う。

・最大値・最小値
まぁね。これは、わかるでしょう。SQLでは集計関数のMAXとMINで計算できる。

・合計値
集合のデータをすべて足し合わせた値。SQLではSUM。

・平均値
データの合計値を個数で割り算した値。SQLではAVGで計算可能。

・パーセンタイル値
あるデータのそれを含む集合の中での位置をパーセンテージで示した値。百分位。
SQLでは、PERCENT_RANKまたは、CUME_DISTで計算できる。

・中央値
パーセンタイル値が、0.5となるデータの値。集合の中でちょうど真ん中に位置するデータの値。
SQLでは、PERCENTILE_CONT、PERCENTILE_DISC、MEDIAN(メジアン)で計算できる。

・最頻値
集合の中で、最も頻繁に出現する数値。
これは、専用の関数は存在しないか...

PERCENTILE_DISC MEDIAN

中央値を計算するのに、PERCENTILE_CONTとPERCENTILE_DISC、さらにはMEDIANの3つが存在する。前の例では、PERCENTILE_CONTを使ったのだが、PERCENTILE_DISCの方が良かったかも知れない。PERCENTILE_CONTは補間された値を計算して戻す可能性がある。
MEDIANは、PERCENTILE_CONTの引数が0.5に固定された、いわばシンタックスシュガーな分析関数である。中央値の計算がしばしば行われるということの裏返しかも知れない。メジアンフィルターとかよく聞きませんか。メジアンフィルターはノイズ除去に使われる。photoshopなんかにもあるよねメジアンフィルター。

真ん中

PERCENTILE_CONTがどうやって補間をするのかを解説していこう。
そもそも、集合の中の真ん中って「いったいどこ」ということを考えていく。
補間の方法が集合全体の要素数が奇数か偶数かで変化する。

集合の要素数が、奇数のとき。例えば、以下の集合があったとして

　{1,2,3,4,5}

真ん中の値って、3ですわな。
集合の要素数が、偶数のとき。例えば、以下の集合だと

　{1,2,3,4}

真ん中って？

PERCENTILE_CONTでは、集合の全体数が奇数である場合と偶数である場合で、処理方法が異なる。奇数のときは、真ん中に対する値が存在するので、その値をそのまま戻す。
偶数のときは、真ん中に位置するふたつの値が存在するので、ふたつの値の真ん中を補間して戻す。

　{1,[2,3],4}

[2,3]が真ん中のふたつの値になる。2と3の中間を線形補間して、2.5を戻す。

PERCENTILE_DISCでも奇数と偶数で処理が異なるのは同じではある。偶数の場合真ん中のふたつの値が存在することになるが、補間をせず、最初の値をそのまま戻す。

　{1,[2,3],4}

[2,3]が真ん中のふたつの値になる。最初の値2を戻す。

サンプルを作って、検証してみよう。

CREATE TABLE test_analyze4 (
 val INTEGER
);

INSERT INTO test_analyze4 VALUES(1);
INSERT INTO test_analyze4 VALUES(2);
INSERT INTO test_analyze4 VALUES(3);
INSERT INTO test_analyze4 VALUES(4);

SELECT
 PERCENTILE_CONT(0.5) OVER (ORDER BY val) CONT,
 PERCENTILE_DISC(0.5) OVER (ORDER BY val) DISC
 FROM test_analyze4

CONT DISC
---- ----
 2.5    2

-- もう一行作って奇数にしてみる
INSERT INTO test_analyze4 VALUES(5);

SELECT
 PERCENTILE_CONT(0.5) OVER (ORDER BY val) CONT,
 PERCENTILE_DISC(0.5) OVER (ORDER BY val) DISC
 FROM test_analyze4;

CONT DISC
---- ----
   3    3

ばっちり。

関連記事

　分析関数percentile_cont 中央値でノイズ除去
　SQL分析関数 ROW_NUMBER RANK DENSE_RANK
　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD
　SQL分析関数 PERCENT_RANK パーセンタイル
　SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC
　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ
　WITH 再帰クエリ その２

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

イラストで理解　SQL　はじめて入門

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2019/05/16
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：分析関数 SQL

SQL分析関数 PERCENT_RANK パーセントで順位を計算する

最近マイブームとなっているSQLネタ(分析関数)を続けていこうと思う。

分析関数は「ウインドウ関数」と呼ばれる場合もある。レガシーな集計(COUNTとかSUMとか)とは違い、かなり色々なことができるように拡張されている、結構ハイテク(そうでもないか)な関数なのである。
しかし「WINDOWって何」といったことを本ブログで解説していない気もする...LEADのところで少しやったか。集計関数に引き渡す集合の範囲を決めるのがWINDOW。

それはさて置き。

SQLポケリにも分析関数を書いてはあるものの、何せ、いまいち「バラバラで決まっていない感」」があったので、見送っていた機能なのである。
最近になって、「やっぱり便利なんじゃない分析関数」ということで、このブログに"セコセコ"記事をアップしている次第である。で、今回は、PERCENT_RANKである。


PERCENT_RANK

RANKとDENSE_RANKは「順位」を整数値で計算してくれる関数であった。


「しかし」である。


唐突に「あなたの順位は、54位でした」と言われても、全体数がわからなければ、成績が良いのか悪いのか、中間くらいなのか、よくわかりませんよね。
全体で1000のうちの54位なら、まぁ上位の方だけれども、全体で100なら「中くらいの成績」でしかないわけである(一人中の一人だから1位と言われても...)。

そういった場合は、「パーセントで順位を示してあげる」ということをするわけなのだ！(うっ、なんか偉そう)。
パーセントなら、全体が100であることは「世界中で決定」しているので、50%ならちょうど中間だし、10%位なら、ベスト10入り。90%とかだとお尻の方、といった判断が簡単にできる。

「PERCENT_RANK」を使えば、全体で何個中の何位といった「パーセンテージ」で順位を計算してくれる。やってみよう。

SELECT val, PERCENT_RANK() OVER (ORDER BY val ASC) PRANK
FROM test_analyze2

val  PRANK
---- -----
   1     0
   2  0.25
   3   0.5
   3   0.5
   4     1

PERCENT_RANKの計算結果は、パーセンテージと言っても0〜100の数値ではなく、0〜1.0となる。本当に百分率(単位を%)にしたければ、100を掛け算すれば良い。
一番成績が優秀なデータは、順位0.0となる。逆に、一番お尻のデータは、1.0である。

ここでふと思った、1件しかデータがなかったら？0.0だろうか、1.0かも？
やってみた。

SELECT PERCENT_RANK() OVER (ORDER BY dummy ASC) PRANK FROM DUAL

PRANK
-----
    0

どうやら、0になる。


　　そうか、バッチリ理解できた！


とはならないと思うので、もう少し解説してみたい。
PERCENT_RANKとRANKの両方を計算させてみた。
横っちょに集合と、計算式も書いてある。

SELECT val,
 PERCENT_RANK() OVER (ORDER BY val ASC) PRANK,
 RANK() OVER (ORDER BY val ASC) RANK
FROM test_analyze2

val  PRANK RANK
---- ----- ----
   1     0    1    {1}          0/4 = 0
   2  0.25    2    {1,2}        1/4 = 0.25
   3   0.5    3    {1,2,3}      2/4 = 0.5
   3   0.5    3    {1,2,3,3}    2/4 = 0.5
   4     1    5    {1,2,3,3,4}  4/4 = 1.0

RANKの場合は「全体の」データの個数を気にしなくてもよかったことに対して、PERCENT_RANKでは「全体の個数が必要」になってくる。そう、全体で1000あるのか、100なのか、といったことなのである。

まぁ、落ち着いて、1レコード毎に見ていこう。

最初の行は、値1である。このデータの順位は、最高位なのだが、PERCENT_RANKでの計算式は以下のようになる。

(カレント行の値からRANKで順位を計算 - 1) / (全体のデータ数 - 1)

RANK関数では、集合から要素数を計算すればよかったのに対して、PERCENT_RANKでは、事前に全体のデータ数が必要になってくるのが最大の違い。
この例での全体のデータ数は、5になる。5レコードあるからね。これは間違いない。割り算の分母になる数値は-1しているから、4になる。5-1で4。

分子の方はというと、val=1のレコードでは、1-1=0になり、0/4で0になる。そう、一番優秀なのが0になる。

次に、val=2のレコードは、2-1=1で、1/4で0.25。

さらに、val=3は、3-1=2で、2/4で0.5。

最後は、val=4は、4/4で1.0。ビリは1.0。

どうだろうか。


まぁ、難しいことは置いといて、順位を総数に関係なく表現できるのが、PERSENT_RANKということで理解していただければ良いと思う。

データ分析をするにあたっては、尺度というか、基準というか、「全体のうちのどのくらいの位置にあるのか」ということが重要になってくると思う。パーセンテージにすれば、0から100までの数値に換算されるわけで、数量を把握するためには非常に便利な訳である。

PERCENT_RANKを使えば、パーセンテージで順位を計算してくれるので、単位変換しなくてもいいので、楽チン。くらいに覚えておくと良いであろう。「単位を揃える」っていう基本的なことなのかも知れない。

えーと、なんか偉そうになってしまった。スミマセン。

本当は、CUME_DISTまでいけるか、と思ったが、本日はここまでとする。

関連記事

　SQL分析関数 ROW_NUMBER RANK DENSE_RANK
　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD
　SQL分析関数 PERCENT_RANK パーセンタイル
　SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC
　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：分析関数 SQL

SQL分析関数 ROW_NUMBER・RANK・DENSE_RANKついでにNTILE

本日も前回に引き続き、SQLのお話である。

前回は、FIRST_VALUEやLAGといった分析関数の基本を紹介した。
今回はというと、RANK、ROW_NUMBERといった順位計算系の分析関数を紹介したい。

ROW_NUMBER

ROW_NUMBERは、OVER句を必要とする分析関数となる。
OracleのROWNUMは、WHERE句を処理する際に割り当てられるため、ORDER BYでソートされた順にはならない、といったことは結構有名である。
なので、ソートした結果で連番としたい場合や、行制限をかけたい場合には、一度サブクエリを作ってやらないといけなかったりして不便であった。

というわけで、ROW_NUMBERはソートした結果で行制限を行う際によく使用されるのだが、実際は分析関数であるので、その他の用途でも、結構便利に使えるわけである。

ROW_NUMBERの機能は、引数で与えられた集合内の「要素数＋1を計算して戻す」だけである。集合を明示的に引数で指定する必要はないので、ROW_NUMBER関数の引数は存在しない。

早速やってみるのだが、その前に、サンプルのテーブルを作成しよう。

CREATE TABLE test_analyze2 (
 val INTEGER
);

INSERT INTO test_analyze2 VALUES(1);
INSERT INTO test_analyze2 VALUES(2);
INSERT INTO test_analyze2 VALUES(3);
INSERT INTO test_analyze2 VALUES(3);
INSERT INTO test_analyze2 VALUES(4);

SELECT * FROM test_analyze2;

val
----
   1
   2
   3
   3
   4

ROW_NUMBERで順番を計算してみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO
FROM test_analyze2

val  NO
---- ----
   1    1    {1}
   2    2    {1,2}
   3    3    {1,2,3}
   3    4    {1,2,3,3}
   4    5    {1,2,3,3,4}

{}で囲まれている数値は、引数で渡される集合を"仮想的に"表現している。実際のクエリの結果には出現しないものなので、注意して欲しい。同時に、集合の要素数と一致することにも着目して欲しい。

番号は、ORDER BYで指定された順序に従って振られていく。ASCをDESCに変更すると違った結果になってくる。

SELECT val, ROW_NUMBER() OVER (ORDER BY val DESC) NO
FROM test_analyze2

val  NO
---- ----
   4    1    {4}
   3    2    {4,3}
   3    3    {4,3,3}
   2    4    {4,3,3,2}
   1    5    {4,3,3,2,1}

集合の個数を計算することに変化はないが、要素の並びが降順となっていることに注目して欲しい。SELECT命令にORDER BYを付けていないので、全体のソート順もOracleの都合の良いように降順で出力されるようになった。


RANKとDENSE_RANK

RANKとDENSE_RANKも分析関数である。ROW_NUMBERは、順に連番を振っていくものだが、RANK、DENSE_RANKは順位を計算することができるというシロモノ。
連番と順位はどう違うかと申しますと、連番は、重なる番号が出現しないことに対して、順位は、同じ数値のものが、同じ順位になる、という点。
{1,2,3,3,4}とういう集合を例にして解説しているわけだが、この集合の中には数値3が二つ重複してある。数値が小さい方が優秀ということで話を進めると、1の順位が1位、2の順位が2。3の順位は、両方とも3になる。次の4はというと、ふた通りの考え方があるので、ちょっと後回し。
まずは、ROW_NUMBERとRANK関数を使って行番号と順位を比較してみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO,
 RANK() OVER (ORDER BY val ASC) RANK
FROM test_analyze2

val  NO   RANK
---- ---- ----
   1    1    1   {1}
   2    2    2   {1,2}
   3    3    3   {1,2,3}
   3    4    3   {1,2,3,3}
   4    5    5   {1,2,3,3,4}

ROW_NUMBERは、集合の要素数を計算する。一方、RANK関数は、カレント行の値より前の順位となる要素数を計算して、その値に+1した数値を戻す。
最初の3の行では、{1,2,3}が集合として関数に渡り、3より順位が高い1と2の二つがあることがわかる。2+1を計算して3が結果として戻される。
次の3の行では、{1,2,3,3}が集合として関数に渡り、3より順位が高い1と2の二つがあることがわかる。2+1を計算して3が結果として戻される。
これで、データ3の順位はどちらも3位となリ、ROW_NUMBERとの差が発生する。

DENSE_RANK

ここで、後回しにしていた、次の4まで考えてみよう。3については、同値の行が2行あるので「どちらも3位になる」ことがわかった。
次の4の行では、{1,2,3,3,4}が集合として関数に渡り、4より順位が高い1,2,3,3の4つがあることがわかる。4+1を計算して5が結果として戻される。
3位の次だから4位とはならず「同値であった行数分だけ順位が飛ぶ」のがRANK関数の考え方。順位が飛んでしまう現象を「ギャップ」と呼ぶこともある。

RANK関数ではなく、DENSE_RANK関数を使うと少し違った様子になる。やってみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO,
 RANK() OVER (ORDER BY val ASC) RANK,
 DENSE_RANK() OVER (ORDER BY val ASC) DRNK
FROM test_analyze2

val  NO   RANK DRNK
---- ---- ---- ----
   1    1    1    1  {1}
   2    2    2    2  {1,2}
   3    3    3    3  {1,2,3}
   3    4    3    3  {1,2,3}
   4    5    5    4  {1,2,3,4}

DENSE_RANKでも、より順位が高い要素の数＋1を計算する。異なるのは、集合から重複が取り除かれている点。つまり、DISTINCTされた状態で個数を計算するということ。なので、DENSE_RANKでは3位の次だから4位となり「同値である行があっても順位は飛ばない」ギャップが発生しない。

NTILE

さて、分析関数を使うことで、順位付けすることが簡単にできることがわかって頂けたかと思う。

分析関数が存在しなかった時代では、サブクエリを使って行番号や順位を計算していた。サブクエリとすると、SELECT命令が長くて複雑なものとなりやすい。分析関数を使えば、サブクエリよりも簡潔に分かりやすく記述できる。是非とも有益に使っていきたいものである。

サブクエリでなく、ユーザー定義関数を作ってしまう、という手もあり、自分でも結構関数を作ってきた。しかし、関数を作る際には、どうしても、スカラ値を返さなくてはならず、なんか面倒なことになりがち。分析関数で済むのなら、そっちの方が楽。

単純な順位付けなら、RANK関数で十分かも知れない。しかし、データ分析を実際にやろうとすると、データが大量にある場合などは、4つくらいのグループに分けて全体の傾向をつかみたい、ということもあるかと思う。
NTILEを使用すれば、そう言った順位付けを行うことが可能となる。
一つの例として、ヒストグラムを計算する際のSQLを例にしてみたい。ヒストグラムっていうのは、以下のようなデータの分布状態を視覚化したグラフ。デジイチユーザなら見たことがあるはず。



デジタルカメラの場合は、R,G,Bの階調データをそれぞれの色で3つのグラフで表示したりする。階調も256くらいあるので、例としてはデータが大き過ぎる。なので、白黒で8階調ということにしよう。
8階調なので、数値は0〜7までの値となる。画素数は4x4=16個にしようか。

0 1 0 4
0 1 7 0
6 2 7 1
0 3 7 2

こんな感じの画像データがあったとする。画像に直すと、どうなるかちょっとよく分からないが、まぁなんとなくわかるでしょう。
実際のテーブルには、以下のような感じで行を作った。

SELECT * FROM test_analyze3

val
----
   0
   1
   0
   4
   0
   1
   7
   0
   6
以下略

集計を行うにあたっては、ピクセルの位置は関係ないので、ピクセルに出力している階調データのみを行のデータとして記録しているだけの単純な構造にした。
ここから、階調0を出力しているデータの個数、階調1を出力している個数、階調2と、順に7まで集計すれば、ヒストグラムを表示することができる。数の集計なので、COUNT使って、GROUP BYすればOKかも。まぁ、そうですね。分析関数使うまでもないか。

SELECT val X, COUNT(val) Y
 FROM test_analyze3
 GROUP BY val
 ORDER BY val

X    Y
---- ----
   0    5
   1    3
   2    2
   3    1
   4    1
   6    1
   7    3

これを、そのままグラフにすると、以下のようになる。

5 *
4 *
3 * *           *
2 * * *         *
1 * * * * *   * *
  0 1 2 3 4 5 6 7

テキストで作成したのでショボイが、なんとなく、ヒストグラムっぽいでしょ？

しかし、分析関数使ってませんね。普通にGROUP BYとCOUNTで済んでしまった。まぁ、SQLの潜在能力を「思い知らされただけ」っていう話もあるが。そうは言っても、分析関数である。

ヒストグラムを表示すると言っても、実際は、8階調っていうことはないわけで、フルカラーを表現するには、24ビット整数の精度くらいは必要、実際はもっと大きい数値になる。さらに、画面の制約があるので幅は100ドットくらいでグラフを表示したい...

で、分析関数を使えば、そう言ったことに柔軟に対応できるようになるのである。

ここで、ビューを作成する。一度は集計処理はしないといけないので、そのためのビューを定義してしまう。

CREATE VIEW v_test_analyze3 AS
SELECT val X, COUNT(val) Y
 FROM test_analyze3
 GROUP BY val

SELECT * FROM v_test_analyze3 ORDER BY X

X    Y
---- ----
   0    5
   1    3
   2    2
   3    1
   4    1
   6    1
   7    3

前のクエリをそのままビューにしただけなので、結果も変わらない。このビューの結果を元にさらに集計していく。
まず、画面に収めるため、Xが最大7であるところを半分の3にしてみたい。8階調であるところを、半分の4にしたいのである。そうすれば、小さい画面でもヒストグラムを表示できる。

いろいろなやり方はあると思うが、分析関数のNTILEを使ってやってみることにする(やっと出てきました分析関数)。
NTILEには、グループをいくつのグループに分割するのかを引数で指定できる。現段階での課題は、4つのグループに分割して、ヒストグラムを表示させたいので、NTILEの引数には4を指定する。
NTILEで分割したグループは「バケット」と呼ばれる。

SELECT X, Y, NTILE(4) OVER (ORDER BY X ASC) BUCT
FROM v_test_analyze3
ORDER BY X

X    Y    BUCT
---- ---- ----
   0    5    1
   1    3    1
   2    2    2
   3    1    2
   4    1    3
   6    1    3
   7    3    4

BUCTが1となっている部分が、1のバケットに含まれているデータ行になる。同様に2なら2のバケットと分割数に指定した4まで続く。
この段階では、全体を4つのバケットに分割して連番を振っただけに過ぎない。BUCTの列がその連番になっていると理解して欲しい。
前回の8階調ヒストグラムでは、X毎にデータの個数を計算したのだが、今回はこれを、BUCT毎に計算するのである。Yの値は、バケット内のデータを合計する。BUCTは4分割したのだから、4階調に変換できるはず。

SELECT BUCT - 1 X, SUM(Y) Y
 FROM (SELECT Y, NTILE(4) OVER (ORDER BY X ASC) BUCT
   FROM v_test_analyze3) SUB
 GROUP BY SUB.BUCT
 ORDER BY X

X    Y
---- ----
   0    8
   1    3
   2    2
   3    3

グラフにしたら

8 *
7 *
6 *
5 *
4 *
3 *   *       *   
2 *   *   *   *
1 *   *   *   *
  0   1   2   3

こうなる。前の8階調のグラフと比較してみる。

5 *
4 *
3 * *           *
2 * * *         *
1 * * * * *   * *
  0 1 2 3 4 5 6 7

なるほど、Xが0と1がバケット0なので、Yの合計がバケット0の数値8になっている。Xが2と3でバケット1だから、2＋1で3なのか。ちなみに、グラフの*の数はどちらも画素数の16個である。

なかなか良さそうではある、がしかし。

階調5のデータは0件なので、X={4,5}のバケットとX={6,7}のバケットが怪しい感じになっている。ちゃんとしたヒストグラムにしたい時は、5のところに0が入ってこないといけないことがわかった。まぁ、サンプルなので目をつむってもらうとしよう。

本日は、ここまで。

関連記事

　SQL分析関数 ROW_NUMBER RANK DENSE_RANK
　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD
　SQL分析関数 PERCENT_RANK パーセンタイル
　SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC
　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

タグ：分析関数 SQL

SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD

本日は、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

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

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

関連記事

　SQL分析関数 ROW_NUMBER RANK DENSE_RANK
　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD
　SQL分析関数 PERCENT_RANK パーセンタイル
　SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC
　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

---

サイト内を検索

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus) が発売されます

SQLポケットリファレンスの記事を書いた効果があったか、なかったかよくわからないが、どうにかGoogle検索で上位にランクされるようになった模様。
でも、油断すると、すぐに落ちていってしまうので、地道に更新したいと思う。

ということで、本日もSQLポケットリファレンス関連の話題である。

第三版から、もう5年も経過している、という話は前回したと思う。

5年もあれば、世の中変わっている。システム業界はけっこうすぐに廃れたりする。
なので、けっこう変わっているか、というと…



うーん。あんまり変わってないかな。

もう、SQLって、C言語みたいに、「枯れた技術」になっているような気がする。

枯れた技術は、良い悪いは別にして、あまり変えてはいけない、というしがらみというか、しばりというか、なんというか、いろいろあって、大きく変更できないのである。

考えて見て下さい。開発用の言語が、頻繁に更新されていたら、どうなります。
めんどくさいでしょ？

例えて言うなら、C言語で、intじゃなくてintegerを使うようにしましょう。
ってなったら、困りますよね(多分そんなことは滅多にないと思うが…)。想像してみて下さい。めんどくさいでしょ？

ソースを全部変更しなければならない。int -> integerなら、テキストエディタの「リプレイス機能」で一発変換できるかも知れないが…
まぁ、それだけではどうも不安だし、ちゃんと試験しないとね。
変数名に、intXXXっていうのがあって、単にテキストの文字列置換だけでやったとすると、intXXXがintegerXXXに一律に置換されて、コンパイルすら通らない、っていう状況になってしまうかも。

ということで、一度「これって決めたら」、なかなか変更はされないものなのです。
基本的なものについては余計に。

使っているユーザが多ければ多いほど、インパクトは大きいので、余計に変更できなくなります。

SQLについては、もうそのくらいに良い意味で「レガシー」になったのであろう。

にしても、いろいろ新しい技術は登場する。
最近では、「no-sql」かな。

no-sql。SQLを真っ向から批判しているような、用語ではあるが、困ったことになんか、流行っているっぽい。

私が、SQLと出会ったのは、そう、MSのSQL Serverが最初かな。バージョンはというと4とかだったかも。当時、某総合研究所に派遣でプログラマとして働く日々であった。お世話になった方々は、どうしているであろうか。
SQL Serverは今や201x？
当時は、SQLのSの字も知らなかった。教科書的なものはあまり「多くなく」洋書を翻訳したものが、いくつかあったかも。しかし、一番の教科書は「マニュアル」であったと思う。
Informixの本があったかなぁ。Infomixって今ないよねぇ...

当時の私としては、C言語やVBはお馴染みの言語になっていたので、最初にSQLを見た時はビビった。何がなんやらわからん。大体、大文字で命令を書くなんて、なんかFORTRANくさくて実は「いや」だった。

C言語は、予約語がみな小文字。ifとかforとか。それに大文字使うとシフトキー使わないといけないので、操作がめんどうだし。C言語で大文字で書くとマクロの#defineなので。小文字が基本。こんな感じでしょ？

#define MAX_LINE 1024

char line[MAX_LINE];

for( i = 0; i < MAX_LINE; i++ ){
    line[i] = 0;
}

MAX_LINEは何かの配列の要素数になっていたりする。

それはさておき、SQLである。別に小文字でselectって書いてもいいのだけど、なんか大文字の方が主流な感じ。C言語のソースに、埋め込むなら、大文字の方がいいかも、でもなぁと違和感を感じつつも、コードを書いていたという遠い記憶がある。こんな感じか。

strcpy(sql_cmd, “SELECT * FROM name=‘%s’;”, name);
rc = SQLExecute(sql_cmd);
if ( rc == …

ううん。セミコロンで終わっているのは似ているといえば、そうだけど… SQLではセミコロンいらないんだっけか。
プログラミング言語のソースファイルの中に、他のプログラミング言語の命令があると、どう考えても「見ずらい」。まぁ、しょうがないことなんだけどね。

話はSQLに戻る、SQLをやり始めたら習得は速かったと思う。なにせ、基本的なコマンドは、４つしかない。SELECTとINSERT、UPDATE、DELETE。最近では、MERGEとか、なんやらと増えたみたいではあるが、基本はこの４つ。
CREATE TABLEは覚えなかったなぁ...あんまり。最初は、ALTER TABLEってコマンドはなかったと思うのだが、後になって登場してくる。
CREATE TABLE命令を覚えなかったのは、どうしてかというと、SQL Serverには、Access的なGUIツールが付いていたから、だったっけ。単に、クライアントのプログラムを作る分には、テーブル作ったり、削除したりする必要がなかったからかも。テーブルの設計とかは、初心者だったから、やってなかったからね。

SELECT命令でWHERE句が使えます。

というのは、簡単に理解できたと思う。条件式もC言語やっていれば、なんなくクリアでしょう。ANDとかORとかね。思わず&&って書いちゃいそうになっていたが。
値が等価である、っていう比較演算子は、C言語なら==とイコールがふたつだけど、SQLでは=(イコール)のみ。
比較演算の==か=か、という問題はC言語の方が変。なので、SQLの方が自然。C言語のときに一度はハマる問題なので、SQLでは関係ないか。

「句」っていうのも、SQLならではの用語かも。命令文でもなく、演算子でも変数でもない、句っていう「くくり」がある。まぁ、これは慣れればどうってことないかな。命令文がいくつかの句に細分されている、っていうだけ。

しかし、NULLの扱いが？だった。

NULLは、値ではありません(とかマニュアルに書いてあったかな)。

C言語でいえば、NULLは0なんだけど、SQLでNULLは、0でもなければ、9999でもないし、""(空の文字列)でもない。おまけに、NULLとNULLを比較しても結果がわかならいっていうこと。

「わからない」ってなんだよ。
まぁ、SQL的には、UNKNOWNっていう値が返ってくるということになっている。
どういうことかというと、

WHERE NULL = NULL

って書いたとする。NULLはNULLと等しいですか、とデータベースに聞く。するとデータベースは「UNKNOWN(わからない)」といった答えを出す。え〜、コンピュータなのに「わからない」って何。何。謎。何。謎。

もっと妙なのは、テーブルのある行のある列がNULLであるかどうかを調べるのにも「=NULLではだめ」ということ。

SELECT * FROM テーブル WHERE 列 = NULL

上記のようにして、列がNULL値であるものをSELECTで検索することはできない。
じゃあどうすればよいかというと、「IS NULL」を使う。

SELECT * FROM テーブル WHERE 列 IS NULL

オーマイガー。
ここが、SQLの一番の「変なところ」だったりするかも知れない。
C言語とか普通のプログラミング言語に比較演算っていうのは付きものである。しかし、その答えはyesかnoか、trueかfalseかのふたつ。yesでもnoでもなく、UNKNOWNっていう「第三の答え」があるのは珍しい。

実は、そんなSQLの変なところや、初心者がはまりやすい部分をまとめた「SQLの入門書」を書籍にしてみた。
もうそろそろ、発売になる、予定である。

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2015/03/03
• メディア: 単行本（ソフトカバー）

みなさんどうぞよろしく。

---

サイト内を検索

SQLポケットリファレンスの歴史その2 XMLやらOODBの話

SQLポケットリファレンスの記事を書いて、このブログにアップしているのではあるが、どうも検索で一番にヒットしてくれない(技術評論社のページでもなく、Amazonが一番っているのもどうか)。著者なのにぃ...と悔しい思いをしているので、しつこく書いてみることにする。

え〜と前回、SQLポケットリファレンスの歴史について、少し書いてみたわけではあるが、どこまで話を進めたか、というと... 何々(過去の記事を読み返し中)...



AmazonでSQLポケットリファレンスを検索

おお、改訂第三版が出たところまでか。第三版を出したのも結構、前な気がしてきたゾ。ちょっと思い出してみよう。出版日は、2009年の4月か。え、もう、5年も前なの。そうか、年取ったなぁ。時間が経つのが早いねぇ。しみじみ。
当時は、何してたかなぁ。たぶん、システム開発していたと思うけど... MySQLでシステム組むことが多くなっていた時期かも。
第三版を書くにあたって、Oracleは11gに対応、SQL Serverは、2005か？DB2とかも調べたなぁ。そうそう、目新しい機能に、XML対応、というのが結構あった。

XMLデータベース

SQLというのは、RDBMS用のデータベース操作言語。RDBMSはデータベースの一種であり、他にもいろいろなデータベースがある。いや、あったし、現在でも新しいものが考案されている。移ろいやすい、諸行無常の世界なわけである。
XMLが流行りだすと、データベースにXML形式のデータを入れちゃえば「いいじゃん」的な考えで、RDBMSにXMLデータベース的な機能を持ち込んできたのだ。

まぁ、XMLっていってもテキストデータなので、普通にCLOB的な大きな列にXMLデータをぶち込んでしまえば、それでOK。後は、XQueryとかを使えるようにライブラリをリンクしちゃえば、なんとなく、XML対応のRDBMSができちゃう。みたいな(そんなこというと、OracleとかIBMに怒られそうではあるが)。

オブジェクト指向データベース

XMLが流行る前には、オブジェクト指向データベースっていうのも流行りかけた。OODBってやつですよ。OODBにはSQLがない。ちょっとはあるっぽいが、基本、オブジェクトをそのまま永続化する、っていうのがOODBのよいところだから、データの操作用の専用命令なんていらない。メモリにnewする感覚で、永続化されるオブジェクトインスタンスを生成できる。
細かい話はやめておくとして、OODBの機能もRDBMSに取り込もうと頑張った感がある。しかし、OODBはデータベースの主流にはならなかったぽい。もし、OODBがデータベースの主流になっていたら、SQLポケットリファレンスも用無しになっていたことであろう。

というか、RDBMSは世の中に広まり過ぎた感がある。そこから抜け出せない感じ。まぁねぇ、いきなり明日からOODBで行きます。っていわれたらシステム屋さんは困るだろうなぁ。というか現実的に無理。というしがらみでRDBMSが主流なんだろうな。喜ぶべきなのか、どうなのか。私の立場なら喜ぶべきなんだろうなぁ。

一方、RDBMS陣営は、OODBのいいとことか、XMLのいいとこをちょいちょい、取り入れていってうまいことやってる。SQLポケリ第三版でも、XMLのことやOODBっぽいところを書き足したなぁ。
システム全体を見れば、クラスター構成とか、パラレルクエリーとかスケーラブルな方向に進化していっているような感じもあったが、SQLの文法については、それほど変化はなかったかも。

Oracle12cのc

最近(でもないか)になって、Oracleは12cを出したらしい。どんなものかまだよくは見ていないが、どんなものなのであろうか。Oracleは9iの時からバージョン番号の後に、なにかその時代を象徴するような1文字を付けてきた。9iのときのiはInternetのiだったと思う。10g、11gでは、グリッドのg。12cのcは、何？という感じだが... クラスターか?コンシューマーか?。あー、クラウドか。

No-SQLとか言われている現在なので、その対抗策がいろいろあるんだろうね。
JSONとか使えるし...

なにか、他人事のようになってしまったが、本日はこれにて失礼。




SQLポケットリファレンスが第４版になりました。
Oracle12cにも対応しています。

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

関連記事
SQLポケットリファレンスが第4版になりました
第４版は電子版もあります。
改訂第4版 SQLポケットリファレンス電子版

---

サイト内を検索

SQLポケットリファレンスの歴史(初版〜第３版)

本日は、SQLポケットリファレンスの話題。

SQLポケットリファレンスで、Google検索すると、Amazonのページとか、技術評論社のページがヒットする。
著者である私のこのブログのページはというと、なんとページを送って3ページ目まで行かないと表示されない。これは、なんとも「マーケティング的にはどうか」ということで、SQLポケットリファレンスな記事を書いてみようと思う。

どういう記事にしようかと思ったが、これは著者じゃないと無理。っていう感じのものにしようと考えた。
ちょっと前に、台湾版のSQLポケットリファレンスを紹介したが、これの続編というわけでもないが、同じ系統で、歴代のSQLポケットリファレンスを揃えてみることにした。なかなか、3種類持っている人は少ないでしょう。



どれも、みな緑色。並べて見ると微妙に色が違う。タイトルのSQLもフォントが微妙に違う。

ということで、第一版から振り返ってみることにしよう。
まずは、最初のSQLポケットリファレンスである。

SQLポケットリファレンス (Pocket reference)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2000/01
• メディア: 単行本

なんと、初版は1999年(出版日は2000年)。21世紀じゃないし、今から13年も前か。
当時は、Oracleは7、SQL Serverは6とかだったかなぁ... どちらのデータベースも使ったことがあり、現役でSQLをバリバリ書いていた(今でも書いているが)。
SQL Serverは、4.21の時代から使っている。クライアントはVBで書いてたかなぁ... Windows3.1とかの16ビットOSで。なんか懐かしい感じかも。
この時は、SQL ServerとOracleだけだなぁ。まぁ、それしか知らなかったからね。他にもInfomixとかあったけど、やったことなかったし。

詳細なイキサツは、技術評論社のインタビュー記事にかいてある。

初版のSQLポケットリファレンス、けっこう売れた。当時は、RDBMS全盛期っていう感じだったからなぁ...
その後、私の実際の仕事は、データベースから離れるが、MySQLとか、Postgresなんかのいわゆる「フリーなオープンソースのデータベース」が出てきた。
これは、ビックリである。現場では、まだ、Oracle、SQL Serverを使っていた。そりゃねぇ。いきなり無料っていわれてもねぇ。「安かろう悪かろう」っていう感じ。

こういったオープンソースのフリーソフト関係は、UNIX畑であった私にとっては、けっこう馴染みのあるものであったし、無料で使えるのなら、そっちの方がいいじゃん。と思ったものである。

実は、この私「客」としてOracleなどを購入したことはない。基本、開発者はそういった、開発ツールは購入しない。開発を依頼する側が購入して提供する、といったパターンが多い。
なんていったて、Oracle買ったら、すげー高いので... Enterpriseなんて年収くらいの値段がするし。
SQL Serverなら、10万とかだったかも。

それが、MySQLやPostgresなら「無料」なのである。
まぁ、さすがに、最初は「えー、うそくさい」って思うよね。OracleやMSにしたって、そう面白くはないであろう。MSはOS売ってなんぼ、だったからそうでもなかったかも知れないが... Oracleは大変だっただろうに。

そういった中で生まれたのが、改訂第二版のSQLポケットリファレンスである。

改訂新版 SQLポケットリファレンス (Pocket reference)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2003/06/10
• メディア: 単行本

これも、おかげさまで売れた。
なんでか。やはり、MySQLやPostgreSQLの台頭であろう。改訂するとともに、サポートするデータベースサーバーを増やしたのである。

SQLの方言

SQL命令の文法ってそれほど多くない。基本的なコマンドは、4つしかないし。CREATE なんちゃら、とか権限関係のGRANTとかはけっこう細かく文法があるが、データを操作するっていう基本的な命令は、以下の4つ。

INSERT
DELETE
UPDATE
SELECT

まあねぇ、データの操作なんだからねぇ。最近では、MERGEとか、REPLACEっている基本命令も加わっているが...

でもね。SQLって、割とあちこちで開発、拡張されちゃったみたいなので、変な方言ができてしまったのである。
よく、言われているのが、外部結合の方法。LEFT JOINやら、*=やら(+)やらのことです。
それに、まぁ、プログラミング言語ではありがちなんだけど、同じ機能なんだけど名前が微妙に異なっていたり、引数が少し違っていたり、という感じ。

SQLポケットリファレンス改訂新版では、どのデータベースで使える関数、コマンドがアイコンで記載されている。このあたりが受けた模様。どのデータベースでどういった関数やコマンドが使えるのか、といったことがわかるので。



でも、大変だったなぁ。Oracleのマニュアルなんか印刷すると5cmくらいの厚さになるし。MySQLやPostgresはバージョンアップが頻繁に細かく行われるので、「あれ、この関数いつの間に使えるようになった？」とか。

SQLの方言問題は、ISOなどの標準化組織が頑張って標準化してくれたおかげで、沈静化していった。SQL92、SQL99、SQL2003といった規格が発表され、「標準のSQL」が確立してきた。そんな中で、SQLポケットリファレンス改訂第３版が誕生したのである。

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2009/04/29
• メディア: 単行本（ソフトカバー）

ちょっと話が長くなってしまったので、続きはまた今度にしたいと思う。

AmazonでSQLポケットリファレンスを検索

関連記事

SQLポケットリファレンスの歴史その2

SQLポケットリファレンスが第4版になりました

第４版は電子版もあります。
改訂第4版 SQLポケットリファレンス電子版

SQLポケットリファレンス (Pocket reference)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2000/01
• メディア: 単行本

改訂新版 SQLポケットリファレンス (Pocket reference)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2003/06/10
• メディア: 単行本

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2009/04/29
• メディア: 単行本（ソフトカバー）

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

続きを読む

---

サイト内を検索

SQLポケットリファレンス 台湾版「SQL語法範例辭典」

本日は、レアなアイテムが手に入ったので紹介したいと思う。
台湾版の「SQLポケットリファレンス」である。



表示は黒字にオレンジ。横に並べているのが、日本で発売されているSQLポケリ。表紙の色もサイズもちょっと違う。結構違っているので面白い。他のポケットリファレンスシリーズも同じように訳されて出版されているようである。
タイトルのSQLはわかるが、後は漢字だけ。理解できそうなできなさそうな...。

「語法」はシンタックスっていうことかなぁ？
「範例」はサンプルぽい。日本語の範例と同じか。語法範例で、リファレンスなのか？
漢字が異なるが多分「辞典」はそのまま辞典だろうなぁ。もしかすると、語法範例辞典まででリファレンスか？
ポケットはどこに？

まぁ、いいか。「ここが違う」って文句を言ったら直してくれるのかなぁ... でも中国語わからないから、違っているところを指摘できない。
パラパラとめくってみただけであるが、割とオリジナルに忠実に訳されている模様。

「SQL語法範例」で検索すると、台湾のサイトのページがヒットした。台湾のAmazonはないのか？...あればリンクしたかったが。

検索してみたら、台湾の本屋のサイトであると思われるが、見つけた。

http://www.books.com.tw/exep/prod/booksfile.php?item=0010356474

台湾のみなさん、「SQL語法範例辭典」をどうぞよろしく。

日本の方は、「SQLポケットリファレンス」をよろしく。

関連記事

SQLポケットリファレンスが第4版になりました
SQLポケットリファレンス 台湾版「最新SQL語法範例辭典」

[改訂第4版]SQLポケットリファレンス

• 作者: 朝井 淳
• 出版社/メーカー: 技術評論社
• 発売日: 2017/02/18
• メディア: 単行本（ソフトカバー）

続きを読む

---

サイト内を検索