So-net無料ブログ作成
検索選択
SQLポケリ ブログトップ
前の10件 | -

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

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

みなさん、こんにちは。本日もSQLポケットリファレンスの話題である。
前にも紹介したと思うが、最近では書籍の電子化が当たり前になってきている。SQLポケットリファレンスも改訂第3版を電子化するかどうか、といった話があったが、見送られていた。当時は、電子出版したら不正コピーが横行するのではないか、という心配があったからである。

そんな心配もあったが、時代の流れは電子化される方向に動いている。今回の改訂で、電子化されることになったのである。以下のURLから購入することができる。


[改訂第4版]SQLポケットリファレンス
https://gihyo.jp/dp/ebook/2016/978-4-7741-8826-3



技術評論社では、電子版と紙の本とで価格に差がない。どちらも同じ1980円。
電子版だと、印税率が高い。なので、私としては、電子版を購入してもらった方が嬉しいのであるが、電子版の売れ行きはあまり「よろしくない」らしい。

電子版は、PCやタブレットで閲覧することになる(PDFでダウンロードできる)。リファレンス的な内容の本は「ネット検索した方が早い」という場合が多いので、わざわざ、電子版の書籍を購入しなくても...

ということみたいである。
PDFを開いて検索するっていう方法もあるのだけれど...


電子機器持ち込み禁止

システム開発の現場によっては、「電子機器持ち込み禁止」というところがあるらしい。個人情報の流出が問題になるような、データセンターに入って開発や保守作業するのであれば、当然かも知れない。
PCはもちろんのこと、携帯電話、タブレット、デジカメ、USBメモリなどなど、今やちょっとしたデジタル機器でも数ギガのデータを入れることができるので、簡単に大量のデータを持ち出すことができてしまう。

そんな時は、紙媒体の資料を持ち込んで作業する、ということをするらしい(聞いた話なので)。

ふーん、なるほどね。それならSQLポケットリファレンスは「もってこい」じゃないですか。

でも、「電子機器持ち込み禁止」な現場で働いている開発者って、そう何人も居ないよね。あまり、期待はできないが、そんな辛い立場で開発している方は、紙媒体の「改訂第4版SQLポケットリファレンス」を宜しくお願いします。

マルチディスプレイ

普段システム開発している時は、Visual StudioやEclipseなんかのIDEを立ち上げてますよね。多分、ディスプレイを占有しているはず。
SQLの文法が分からないぞ、というときにSQLポケリを取り出して見ていると思う。電子版なら、PDFを開いてディスプレイ上で閲覧できる。サブディスプレイに出しておけば、とっても便利じゃない?サブディスプレイがない?それなら、スマホやタブレットに入れて見たらどう。

DSC_0287.JPG

どちらでもお好きな方をどうぞ。


関連記事
SQLポケットリファレンスが第4版になりました
SQLポケットリファレンスの歴史
SQLポケットリファレンスの歴史その2


nice!(0)  コメント(0) 

みなさんこんにちは、本日は、重大発表があります。 [SQLポケリ]

なんと、SQLポケットリファレンスが第4版になりました。
じゃーん。

DSC_0278.JPG

例によって、歴代のSQLポケットリファレンスを並べてみると。

DSC_0277.JPG

なかなか壮観である。

えーと、なんか自慢みたいでいやなのだが、増刷になると1冊だけだが、本を出版社からもらえる。増刷する際に、誤記を訂正できるので、その確認のためにくれるのだが、SQLポケットリファレンスの場合、けっこう増刷しているので、何冊もある。
机の上にあってもじゃまなので、古い版のやつは、押し入れにしまってあったりする。

刷数が違うやつも並べてみたら、机の上に乗りきらなかった。うーん、歴史を感じるなぁ。最初が1999年(平成11年)だから、もう17年か。

初版  1999年
第2版 2003年
第3版 2009年
第4版 2017年

今回の改訂では、対応データベースがひとつ増えた。SQLiteが増えている。日々、AndroidやiOSなどの携帯端末上でアプリを開発している、という開発者も多いことであろう。そんな、方々にもSQLポケットリファレンスを使ってもらおう。というのが今回の改訂の目論見のひとつでもあった。

組み込み系では、SQLなんて使わなくても、配列で十分、とか... サイズ的に重たいんじゃない?といった意見も多い。というか、「多かった」かなぁ。しかし、AndroidやiPhoneには標準で入っているし。これを使わない手はない。

それに、今や組み込みにしても、ギガ単位でストレージを持つことも珍しくない。流石にOracleをフルセットで導入、とかは無理かも知れないが、SQLiteなら数百キロくらいでOK。raspberry piなんか、メモリ1ギガも積んでるし。5000円なのに。
そういえば、昔Oracle Liteっていうのがあったなぁ。

ということで、進化を続けるSQLポケットリファレンスなのであるが、改訂する度にページ数は増えている。当然、内容を充実させてきた結果ではあるが。ちょっとまとめてみよう。

ページ数対応DB
初版  416 Oracle、SQL Server、MS Access
第2版 600 Oracle、SQL Server、DB2、PostgreSQL、MySQL、MS Access
第3版 640 Oracle、SQL Server、DB2、PostgreSQL、MySQL、MS Access
第4版 656 Oracle、SQL Server、DB2、PostgreSQL、MySQL、SQLite、MS Access


そんな感じで進化を続けてきたSQLポケットリファレンスであるが、変わっていないことがひとつある。

お気づきであろうか。
カバーの色が「緑」っていうことも、変化していないうちのひとつではあるが、それより決定的に変化していないものがある。


それは、


なんと、お値段がそのまま1980円なのである。

ページ数が増えればそれだけ、本の値段は高くなるのが普通。印刷するのにコストがかかるから。しかしながら、お値段は変わらず1980円なのである。
消費税が5%から8%になり、税込みでいうと、2079円から2138円に値上げされてしまった感があるが、本体価格は発売以来ずーと1980円なのである。

気になって、他のポケットリファレンスシリーズの価格も調べてみると、2000円台のものが多い。最近発行されたものは、2500円くらい。他の出版社さんでも2000円くらいが多いか。

いいんですかねぇ、こんなにお安くて。

まぁ、戦略としてはよいと思う。内容が充実しているのに、1980円なら「安い」と思えるでしょう。Web関係の開発者が、JavaScriptリファレンスが必要という状況で、SQLもあった方がよいか... ということになり、1980円なら、ついでに購入。っていうことが期待できそう。

なんにしても、新しくなった「改訂第4版SQLポケットリファレンス」をどうぞよろしくお願い致します。



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

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

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



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

nice!(0)  コメント(0) 

onclick事件 [SQLポケリ]

先日、ネタになりそうな話を聞いたので、久しぶりにブログを更新してみる。

ハンディターミナルを扱っている販売代理店の方と食事をする機会があり、その中で聞いた話である。
工場が中国にあるそうで、そことのやりとりも多いそうである。やりとりは英語が中心になるそうなのであるが、たまに日本語も入ってくるらしい。

とある日のメールに「onclick事件」がどうのこうのと書いてあったらしい。担当者は、何か工場で事件があったのか?とビビったようである。

何かのボタンをクリックしたら火を噴いたとか...

オチは、どうということはない。どうやら、eventのことは「事件」と訳されているというだけの話。
向こうの担当者は普通に、OnClickEventの話をしていたようなのだが、中途半端に訳してしまったものだから、混乱してしまったようである。しかし、

onclick事件

って書かれたらなんか意味深である。
気になって、「onclick事件」で検索してみたら、出てくる出てくる。これは結構笑える。

以前に、SQLポケットリファレンスの台湾版を紹介しているが、ここにも「何たら事件」がないかパラパラとめくってみた。

DSC_0331.JPG


GUI系ではないので、イベントは出てこないか...
残念。

目次を見ていくと、面白いキーワードを発見。「物件資料表」て何だ。
物件、物件?、物件??、不動産関係の本じゃないんだけど、物件って何?

対応するページを見てみると、わかった。どうも「オブジェクト」が「物件」に訳されている。
ふーん。

「物件資料表」=「オブジェクトテーブル」

ということらしい。

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



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

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

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



nice!(0)  コメント(0) 

CROSS APPLY LATERAL [SQLポケリ]

本日も、SQLネタである。
少し前に、結合方法について書いてみた(INNER JOINとかの記事のことです)。
NATURAL JOINが最新の結合方法、と書いてみたりしたのだが、これよりもっと新しそうなのを発見してしまった。

CROSS APPLY」というやつである。以前からその存在は知っていた(*1)のだが、Oracle12cで採用になったのでちょっと調べてみることにした。

*1
SQL Serverのマニュアルでxml列のメソッドnodes()を使うときに、CROSS APPLYを使っている。

CROSS APPLYはMS SQL Serverでは、2005からできることになっている。マニュアルには、テーブルを戻す関数をCROSS APPLYすると便利ですよ"的な"例が載っている。
そうなのか、じゃあやってみるか。

CREATE TABLE foo (
 a INTEGER,
 b VARCHAR(10)
);

INSERT INTO foo VALUES(1,'one');
INSERT INTO foo VALUES(2,'two');
INSERT INTO foo VALUES(3,'three');


まずは、CROSS APPLYの左側に指定するテーブルを作成してみた。このテーブルfooに対してCROSS APPLYで交差適用(とでもいうのだろうか)、をやってみたいと思う。
CROSS APPLYの右側には、テーブル値を戻す関数を指定するといいよ、とのことなので、関数を作成してみる。引数でもらった値の行数分だけ連番を戻すような関数を作成する。

CREATE FUNCTION func_bar(@no int)
 RETURNS @result TABLE (a int)
AS
BEGIN
  DECLARE @i int
  SET @i = 0
  WHILE @i < @no
  BEGIN
    INSERT INTO @result SELECT @i
    SET @i = @i + 1
  END
  RETURN
END


説明しなくてもいいよね。
この関数を実行したら、以下のような結果を戻す。

SELECT * FROM func_bar(2);
a
------
0
1


これで、CROSS APPLYの左右が揃った。fooとfun_barを結合してみるわけであるが、テーブルfooと関数func_barを単純に結合するのではなく、foo.aをfunc_barの引数に渡して結合したいのである。
そんな時に、CROSS APPLYを使うとよいらしい。違うかなぁ...まぁ、やってみよう。

SELECT * FROM foo CROSS APPLY func_bar(foo.a) AS FB

a   b     a_1
---------------
1   one   0
2   two   0
2   two   1
3   three 0
3   three 1
3   three 2


func_barに渡した引数で戻りの行数が異なる。これを考慮して、交差結合した感じになった。
a=1の行 func_barの戻す行=1 (0)
a=2の行 func_barの戻す行=2 (0,1)
a=3の行 func_barの戻す行=3 (0,1,2)

まぁ、そういうもんでしょう。

CROSS APPLYの左右を逆にすることはできない。

SELECT * FROM func_bar(foo.a) AS FB CROSS APPLY foo


はエラーになる。foo.aが最初に出現するので、これがわからん、と文句をいわれる。

マニュアルには関数なら便利だよ的なことが書いてあるので、関数じゃなければいけないかというと、そうでもない。テーブル値を戻せばよいだけなので、サブクエリでも良い。
もうひとつテーブルを作成してみよう。

CREATE TABLE bar (
 a INTEGER,
 b VARCHAR(20)
)

INSERT INTO bar VALUES(1,'uno');
INSERT INTO bar VALUES(1,'一');
INSERT INTO bar VALUES(2,'due');
INSERT INTO bar VALUES(2,'二');


barテーブルを作成した。これをSELECTするサブクエリをCROSS APPLYしてみよう。

SELECT * FROM foo CROSS APPLY (
  SELECT * FROM bar WHERE foo.a = bar.a
) AS SB

a   b     a_1  a_2
--------------------
1   one   1    uno
1   one   1    一
2   two   2    due
2   two   2    二


できた。
a=3の行がなくなってしまっているが、「OUTER APPLY」にすれば取得可能となる。

SELECT * FROM foo OUTER APPLY (
  SELECT * FROM bar WHERE foo.a = bar.a
) AS SB

a   b     a_1  a_2
--------------------
1   one   1    uno
1   one   1    一
2   two   2    due
2   two   2    二
3   three null null



えーと、これって普通のINNER JOINやLEFT JOINと「どう違うのだろう?」と思いません?
INNER JOINで書くとするのなら、以下のようにすればよいと思うのだが...

SELECT * FROM foo INNER JOIN (
  SELECT * FROM bar
) AS SB ON foo.a = SB.a


結合条件をサブクエリのWHEREで書いているか、FROMのONで書いているかの違いはあるのか。
CROSS APPLYを単にINNER JOINには変更できない。以下のようにすると、エラーになる。

SELECT * FROM foo INNER JOIN (
  SELECT * FROM bar WHERE foo.a = bar.a
) AS SB 


ONで条件式を書いていない、というのは横に置いておくとしても、サブクエリの中で、fooを使うことはできない。fooはわかりません、というエラーになる。

ああ、そうか、こういう場合、SELECT句にサブクエリ書いて逃げてきたかも。でも、SELECT句に書くとスカラ値を返さなくてはいけなくなって、散々苦労した覚えが...


LATERAL

LATERALインラインビューというものがある。これを使えば、INNER JOINでもCROSS APPLYのようなことができてしまうのである。
LATERALインラインビューにするには、サブクエリの前にLATERALキーワードを付けるだけ。

SELECT * FROM foo CROSS JOIN LATERAL (
  SELECT * FROM bar WHERE foo.a = bar.a
) SB 


SQL Serverでは、LATERALをサポートしていないので、エラーになってしまったが、Oracle12c、PostgreSQLでは実行できる。

OUTER APPLYにしたい場合は、LEFT JOINを使えば良い。

SELECT * FROM foo LEFT JOIN LATERAL (
  SELECT * FROM bar WHERE foo.a = bar.a
) SB ON foo.a = SB.a


結合条件を冗長に記述しないといけないのが、ちょっと美しくないが、しょうがない。
ちなみに、LATERALはSQL標準である。CROSS APPLYばベンダ拡張。



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

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

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




nice!(1)  コメント(0) 

テーブルのパーティッション分割 [SQLポケリ]

本日のネタもデータベースである。

最近のデータベースでは、テーブルを「パーティッションに区切る」ということが可能。
最近といっても、もうかなり前からかも。えーと、Oracleだと、10gくらいからか?
しかし、Enterprise Editionじゃないと使えないっぽい。ケチだよね、Oracleって。
DB2はV9から。なんだ、やっぱり割と最近じゃない?
SQL Serverは、2005。ということは、約10年前。10年ひと昔、というから割と古いのかも。
MySQLでは、5.1からか。PostgreSQLは一応は対応しているのか。

でもってパーティッションに分割すると、どんないいことがあるかというと。

 ・パフォーマンスアップ
 ・データの管理がしやすくなる

といったところか。

テーブルをパーティッションに区切ると効率的なのは、ログデータのように、履歴を保存しておくような場合。

パーティッションに分割するといっても、けっこういろいろなパターンがあるようなので、ここで一回整理しよう。

・レンジパーティッション
テーブルを時系列で分割するような分割方法。
 2000年のデータ
 2001年のデータ
 2002年のデータ
のように、時間や数値の範囲で分割していく手法。

・リストパーティッション
テーブルのある列をキーにして分割するような分割方法。
 男性のデータ
 女性のデータ
のように、データの取る値によって、保存先のパーティッションを決定する方法。

・ハッシュパーティッション
テーブルのある列から、ハッシュ値を計算して、その値から分割する方法。
 分割したいのだけど、どの値で分割してよいかわからない場合に、ハッシュパーティッションが有効。

・コンポジットパーティッション
なんかよくわからないが、リストとハッシュの組み合わせ、とかでやるものか。

まぁ、メジャーなのは、以下の3つか。

 ・レンジパーティッション
 ・リストパーティッション
 ・ハッシュパーティッション

ひとつひとつみていこうか。

レンジパーティッション

これは、紹介した通り、値の範囲で分割しましょう、という考えやすい分割方法である。
パーティッションの定義は、CREATE TABLEでテーブルを作成する際にする。後で変更する場合は、ALTER TABLEを使う。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)


上記のようなテーブルがあったとする。普通に実行すれば、テーブルが作成されるが、パーティッション分割は行われない。

これをパーティッションに分割してみよう。

パーティッションに分割するためには、PARTITION句が必要になる。PARTITION句では、どの列で分割を行うのか、それに加え、どのような条件で分割を行うのかを指定する。
partition_testでは、日付のd列を使って、レンジパーティッションで分割するようにしたいと思う。レンジパーティッションでは、PARTITION BY RANGEを使用する。ここまでをSQLにしてみると、以下のようになる。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d)


まだ十分でないため、実行してもエラーになる。
PARTITION BYには、分割のための条件式が必要になる。分割したい数分だけパーティッションの名前と条件を列挙する。
partition_testでは、年を単位にしてパーティッションを分割しようと思う。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d) (
 PARTITION year1 VALUES LESS THAN(TO_DATE('2016-01-01', 'YYYY-MM-DD')),
 PARTITION year2 VALUES LESS THAN(TO_DATE('2017-01-01', 'YYYY-MM-DD'))
)


これで、ふたつのパーティッションに分割されることになる。必要なら、TABLESPACEを指定して、保存先のストレージを分けるということもできる。

日付で分割する場合、パーティッションのメンテナンスが必要になる。つまり2017年になったら、パーティッションを増やしてやる必要がある。保存しておく必要がなくなった古いデータをパーティッションごと削除する、ということも可能である。既存テーブルへのパーティッションの追加、削除は、ALTER TABLE命令で行う。

パーティッションの追加

ALTER TABLE partition_test
 ADD PARTITION year3 VALUES LESS THAN(TO_DATE('2018-01-01', 'YYYY-MM-DD'))


パーティッションの削除

ALTER TABLE partition_test
 DROP PARTITION year1


パーティッション内のレコードは全部なくなってしまうので注意。
この他、パーティッションのメンテナンスとしては、既存のパーティッションをさらに分割(SPLIT)したり、ふたつのパーティッションを統合(MERGE)したりすることができる。

パーティッションを作成したからといって、INSERTやUPDATEを行う際に、パーティッションを意識する必要はない。普通にINSERTすればよい。

INSERT INTO partition_test VALUES(1, '2015-01-01', 10);
INSERT INTO partition_test VALUES(2, '2015-12-31', 20);
INSERT INTO partition_test VALUES(3, '2016-01-01', 30);
INSERT INTO partition_test VALUES(4, '2016-12-31', 40);


2016-01-01より前のレコードは、パーティッション year1 に、2017-01-01より前のレコードは、パーティッション year2 に自動的に記録される。
しかしである。記録すべきパーティッションが見つからない場合は、エラーになるので注意したい。こういった場合、最大値までのパーティッションを作成しておくと、間違いがない。最大値は、MAXVALUEで指定可能。

CREATE TABLE partition_test (
 no INTEGER,
 d DATE,
 amount INTEGER
)
PARTITION BY RANGE (d) (
 PARTITION year1 VALUES LESS THAN(TO_DATE('2016-01-01', 'YYYY-MM-DD')),
 PARTITION year2 VALUES LESS THAN(TO_DATE('2017-01-01', 'YYYY-MM-DD')),
 PARTITION yearx VALUES LESS THAN(MAXVALUE)
)


SELECTで問い合わせを行う際には、パーティションを意識した方が、パフォーマンス向上が期待できる(かも知れない)。
データの検索を2015年のデータに限って行いたいのであれば、パーティッションyear1のみからデータを検索すれば、高速。パーティッションを指定するには、以下のようにする。

SELECT * FROM partition_test PARTITION(year1)


だいたい、こんな感じだろうか。
では、次に、リストパーティッションについてみていこう。

リストパーティッション

レンジパーティッションは、値の範囲でパーティッション分割を行っている。リストパーティッションでは、キーとなる列の値をリストで定義する。
ユーザ管理が行われているテーブルを例にしてみる。以下のように、レコードデータにユーザIDが付けられている。多くのWebアプリケーションシステムでユーザ管理が行われていると思う。今回は、そういったシステムでユーザIDをキーにして、パーティッション分割を行ってみる想定である。
まずは、テーブルの定義を見てみよう。

CREATE TABLE partition_test2 (
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY(userId, no)
)


userId列が、ユーザIDを示す列である。通常、ユーザ間のデータ共有ってまず行われない。ユーザ間のコミュニケーションが目的のシステムであれば話は別であるが、ブログとか、ショッピングサイトなら、他ユーザの情報は見えないし、見せてはいけないものとなる。
ユーザごとに、パーティッション分割してしまえば、何かと便利なんじゃないか、という発想である。

こういった用途で、リストパーティッションを使うことができる。リストパーティッションは、PARTITION BY LISTで定義する。partition_test2では、userIdをキーに分割したいので、PARTITION BY LIST (userId)とする。レンジパーティッション同様に、分割するパーティッションを列挙していくが、分割の条件がLESS THANではなく、単にVALUESに値を列挙するだけとなる。

CREATE TABLE partition_test2 (
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (userId, no)
)
PARTITION BY LIST (userId) (
 PARTITION list1 VALUES ('user1', 'user2'),
 PARTITION list2 VALUES ('user3'),
 PARTITION listx VALUES (DEFAULT)
)


じゃーん。こんな感じでパーティッション分割してみた。
ユーザuser1とuser2は同じパーティッションlist1に含まれることになる。user3は単独でパーティッションlist2に記録される。その他のユーザは、listxに入ることになる。

データの追加は通常のINSERTでOK。

INSERT INTO partition_test2 VALUES('user1', 1, '2016-04-01', 100);


パーティッションを指定することも可能ではあるが、あまりメリットはない。

INSERT INTO partition_test2 PARTITION(list1) VALUES('user1', 2, '2016-04-02', 101);


間違えたパーティッションにINSERTしようとするとエラーになる。

INSERT INTO partition_test2 PARTITION(list2) VALUES('user1', 2, '2016-04-02', 101);


レコードを検索する際に、パーティッション指定をすると、便利かも知れない。

SELECT * FROM partition_test2 PARTITION(list1);


パーティッション分割していないテーブルだとWHERE条件でユーザIDを指定しないといけない。

SELECT * FROM partition_test2 WHERE userId = 'user1';


上記の例では、user1とuser2が同じパーティッションに入るので、やはりWHERE条件は必要になるかも知れないが、ユーザひとりについてひとつのパーティッションを割り当てます、というルールにすれば、そのテーブルをそのユーザ専用にすることができる。うまいことキーを設定してやれば、めんどうな結合条件を省略できるのでは、という話である。

これ開発中のシステムでも使いたいな、でもEnterprise Editionじゃないとだめなのか。


ハッシュパーティッション

レンジ、リストとふたつのパーティッションをみてきた。どちらもパーティッションに分割するためのキーが必要であったが、キーに対してひとつの列しか与えることができない。PARTITION BY RANGE (a,b,c) ということはできないのである。PARTITION BY LISTでも同様。

複数の列からパーティッション分割の条件を設定したい場合は、ハッシュパーティッションを使用すればよい。

前の例では、ユーザIDでリストパーティッション分割を行った。大規模なシステムでは、ユーザーIDだけではなく、ドメインとユーザIDでユーザ管理できることが往々にしてある。その場合、以下のようなテーブルになるであろう。

CREATE TABLE partition_test3 (
 domain VARCHAR2(10) NOT NULL,
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (domain, userId, no)
)


domainとuserIdでユニークになるようなデータ構造となる。パーティッション分割する際に、domainだけでは不十分。userIdも付けたい。しかし、複数列を許してくれない。ハッシュパーティッションにすれば、この問題を解決できる。

CREATE TABLE partition_test3 (
 domain VARCHAR2(10) NOT NULL,
 userId VARCHAR2(10) NOT NULL,
 no INTEGER NOT NULL,
 d DATE,
 amount INTEGER,
 PRIMARY KEY (domain, userId, no)
)
PARTITION BY HASH (domain, userId) (
 PARTITION hash1,
 PARTITION hash2,
 PARTITION hash3
)


ハッシュパーティッションでは、複数の値からひとつの「ハッシュ値」を計算し、それを元にパーティッション分割する。この値のときは、このパーティッションに記録する、という条件を指定することはできない。データベースシステムの方で勝手に記録先が決定してしまう。
ハッシュ値の特性から、同じ値を与えれば、同じ記録先になる。例えば、キー'domain1','user1'のレコードがパーティッションhash1に記録されたとする。以降、パーティッション分割数を変更しない限り、キー'domain1','user1'の記録先は、常にパーティッションhash1に記録される。
どのパーティッションに記録されたかを調べるには、USER_OBJECTSテーブルの、SUBOBJECT_NAMEを参照するとわかる。
USER_OBJECTSテーブルのOBJECT_IDを調べるには、DBMS_ROWID.ROWID_OBJECT(rowid)を使用する。rowidは、レコードの疑似列。

よくわからないでしょうから、SELECT命令にします。

SELECT *
  FROM (
    SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) object_id,
      domain, userId
      FROM partition_test3
  )
  NATURAL JOIN USER_OBJECTS
  ;


今回はここまでとする。



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

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

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





nice!(0)  コメント(0) 

SQL NATURAL JOIN [SQLポケリ]

SQLの基本はテーブル。テーブル同士を結合するようなSELECT命令を作成して、帳票出力なり、画面表示なりを行っていくことが基本である。結合は基本なわけであるが、その当たりの詳細は、「[データベースの気持ちがわかる]SQLはじめの一歩」を是非読んでみて欲しい。

さて、結合の方法には、歴史がある。RDBMSは、古くからある。最初のレガシーな結合は、というと以下のような感じで行っていた。何せ、SQLには歴史があるからねぇ。

SELECT * FROM foo, bar WHERE foo.a = bar.a


FROM句には、テーブルを列挙するが、ここにはアクセスするテーブル名だけを書く。結合条件は書かない。
WHEREに行を選択する条件式を書くのだが、レガシーなSQLでは結合条件もここに書くのである。
結合の基本は「交差結合」。なので基本は、交差結合で得られる「直積」の中から「条件を付けて選び出す」というのが、結合の基本であるわけである。

この当たりのことも「[データベースの気持ちがわかる]SQLはじめの一歩」に書いてあるので、是非読んでみて欲しい。

外部結合

普通の結合は、普通の画面や帳票を作る上では、普通に便利であった。直積から、条件に一致する行だけを抽出する、といった単純な処理方法であるため、扱いやすかった。

しかし、である。

「例外」というのは、いつの世の世界でも存在するもので「データがまだ揃っていない状況でも画面表示したい」とか、バグで「データがない状態でもなんとか表示させたい」というあまり「美しくない理由」で"外部結合"という方法が編み出された(と思う。あくまで推測です)。

考え方自体はどれも同じであったが、実装方法が異なっていた。
例えば、Oracleでは、外部結合を以下のように行っていた。

SELECT * FROM foo, bar WHERE foo.a = bar.a(+)


「(+)」っていうなんか得体の知れない、独特の「演算子みたいなもの」を作ってしまったのである。
これに対して、MS SQL Serverでは、以下のように外部結合することができた。

SELECT * FROM foo, bar WHERE foo.a *= bar.a


演算子を追加することで、外部結合ができるようにしたのだが... Oracleとは違う。
そう、いわゆる方言なわけです。

この当たりのことは「[データベースの気持ちがわかる]SQLはじめの一歩」にはあまり書いてない。SQLポケットリファレンスには書いてあるか。

どちらかというと、Oracleより、SQL Serverの方が素直な感じ、というか、自分の考えに近いので、受け入れ易いと思う。開発者としては、演算子が増えるのには、意外と抵抗がない。しかし、Oracleの外部結合のように、式のわけわからない位置に、わけわからない、記号が来ると「パニック」になるわけです。(+)っていう演算子なんて「Oracle以外で見たことないし!」。

どちらが良いかは置いておいて、同じことをするのに、データベースごとにやり方が異なると、開発者は困るわけです。どっちにも対応できるように作っておけ、と言われてしまうと、#ifdefの山になってしまうわけで... これはソースを見ると「読み難い」んです。とっても。

でもって、標準化団体が動き出したわけです。

結合は、「JOIN」を使ってやりましょう。ということになったのである。JOINはFROM句に書く。結合には、交差結合、内部結合、外部結合などいろいろな種類がある。JOINでは、その前に種類を指定することができる。

CROSS JOIN なら、交差結合。
INNER JOIN なら、内部結合。
LEFT OUTER JOIN なら、左側のテーブルを残す外部結合。
RIGHT OUTER JOIN なら、右側。
FULL OUTER JOIN なら、両方残す。

と決めたわけです。
交差結合を除いて、結合条件を指定しなければならない。WHEREに書いてきたやつですね。JOINの左右にはテーブル名を書くのだが、その後に「ONを付けて、結合条件式を書く」

SELECT * FROM foo, bar WHERE foo.a = bar.a


という内部結合なら、以下のようにJOINで書くことができる。

SELECT * FROM foo INNER JOIN bar ON foo.a = bar.a


外部結合でもやってみるか。

SELECT * FROM foo, bar WHERE foo.a = bar.a(+)
SELECT * FROM foo, bar WHERE foo.a *= bar.a


どちらもLEFT JOINを使って、

SELECT * FROM foo LEFT JOIN bar ON foo.a = bar.a


とすればOK。
OUTERは省略できる。

FULL OUTER JOINは、(+)や*=では、できない。
Oracleの(+)方式でやろうと思ったら、以下のようになる。

SELECT * FROM foo, bar WHERE foo.a(+) = bar.a(+)


SQL Server方式なら以下

SELECT * FROM foo, bar WHERE foo.a *=* bar.a


(+)の連続や、*=*演算子は使えないので、どちらもエラーになる。
FULL OUTER JOINはサポートしているデータベースなら実行できる。

SELECT * FROM foo FULL OUTER JOIN bar ON foo.a = bar.a


ついでに、CROSS JOINもやってみようか

SELECT * FROM foo CROSS JOIN bar


CROSS JOINでは結合条件がない。


USING

時代が進むと、USINGを使った結合条件を書くことも可能になってきた。
普通にテーブルを作成したら、プライマリキーとなっている列は、列名に「気を配って」作成される。商品マスタの商品コード列は、syohin_cdという名前を付けておいて、別のテーブルで商品マスタを参照する場合は、syohin_cdという同じ名前の列を作成することでしょう。
なので、結合条件は、以下のような感じになることが多い。

syohin INNER JOIN uriage ON syohin.syohin_cd = uriage.syohin_cd


結構式が長いよね。
結合条件が同じ列名であるのなら、USINGを使うことができる。

syohin INNER JOIN uriage USING(syohin_cd)


おお、これなら書く量が少なくて済む。いいね。
USINGだと、括弧の中には、列名しか書けない。結合の両方のテーブルで「同じ列名であることが前提」となる。

同じ列名であっても、型が微妙に違っていたりすると問題が発生するかも知れない。
普通のテーブル設計では、データの紐付けに使用する列は、列名、型とも一致させておくことがセオリーであろう。USINGはそのようなスキーマになっていれば、問題なくUSINGを使うことができる。

USINGでは、条件式で使われる演算子は、=に限定される。BETWEENやら>を使うときは、ONにしないといけない。まぁ、普通の結合では、=しか使わないので、この点については問題ないかと。

外部結合でもUSINGが使えることもある(データベース依存?)。
SQL Server、DB2、AccessではUSINGは使えない。

NATURAL JOIN

長々と説明してきたが、今回紹介したかったのはNATURAL JOINである。NATURAL JOINは「一番新しい結合の方法」かもしれない。
NATUAL JOINを使うと条件式を記述しなくてもよい。結合するテーブル同士で同じ名前の列をすべて等しい条件に自動的になる。
テーブルfooとbarが以下のようにスキーマ定義されていたとする。

CREATE TABLE foo (
 a INTEGER,
 b INTEGER
)

CREATE TABLE bar (
 a INTEGER,
 c VARCHAR(20)
)


NATURAL JOINすると結合条件は、暗黙的に「foo.a = bar.a」となる。

SELECT * FROM foo NATUAL JOIN bar


NATURAL JOINは、Oracle、MySQL、PostgreSQL、SQLiteで使用できる。



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

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

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





nice!(0)  コメント(0) 

WITH 再帰クエリ その2 [SQLポケリ]

さて、今回もWITH再帰クエリである。

なんとか再帰クエリを書いて、実行させることができたが、なんか「ありがたみ」がない。

なぜかといえば、再帰的にデータを検索しているが、検索するだけでなんの演算もしていないから。今回は再帰ならではの演算をさせてみよう。
ツリー構造には再帰、ということを前回も紹介したが「ツリー構造ならでは」の情報を計算させることにしてみる。表形式のデータは、行と列を指定すれば、一つのセルが決定して、その中に入っているデータを参照できる。
ツリー構造のデータでは、何階層目に位置するデータなのか、といった情報も結構重要であったりする。

 1
 ┣ 2
 ┃ ┗ 4
 ┗ 3

例にしている、データは上のようになっていた。
ルートノードとなっている、1のデータは「最初の階層にある」と言って良いと思う。最初を0とするか1とするかの決め事があると思うが、ここでは、最初は0ということにしよう。1の子である、2と3のノードは、階層1になる。2の子である4のノードはさらに下の階層2。

TREE_DATAテーブルの行を見てみると、以下のようになっている。

SELECT * FROM TREE_DATA

NODE_NO PARENT_NO NODE_DATA  階層
---------------------------
1       NULL      100        -- 0
2       1         200        -- 1
3       1         300        -- 1
4       2         100        -- 2


階層をコメントで記載した。このような結果を計算したいのである。
普通のSELECT命令だけではこのような計算はできない。集計関数を使っても無理かも。ユーザ定義関数ならできるか。

そこで、WITH再帰クエリの登場となる。
初期化ブランチで検索できる行は、すべてルートノードと言って良い。なので、初期化ブランチに引っかかった行は、階層0として良い。
WITH句では、列を決定できるが、計算によって生成されるデータであっても良い。まずは、列LVLを作って、初期化ブランチで0を返してみよう。UNION ALL後の再帰クエリではとりあえず、NULLを戻しておく。

WITH td(NNO, PNO, NDATA, LVL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0   -- LVL 0を戻す
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, NULL  -- とりあえずNULL
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

NNO   PNO   NDATA  LVL
-------------------------
1     NULL  100    0
2     1     200    NULL
3     1     300    NULL
4     2     100    NULL


とりあえずは、ルートノードのLVLは0になった。コメントで示した階層に一つ近づいた。
では、次の段階へ進もう。
UNION ALLの後の再帰クエリで、とりあえずNULLとしたところを変更していけば、良いことはなんとなくわかるが、どうしたら良いものか。

TREE_DATAテーブルには、LVLの情報はないので、計算するしかない。tdは、一時テーブルなので、前回の再帰処理の結果がわかるはず。そうか、td.LVLを見ればOKかも?

WITH td(NNO, PNO, NDATA, LVL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL  -- tdのLVLを戻してみる
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

NNO   PNO   NDATA  LVL
-------------------------
1     NULL  100    0
2     1     200    0
3     1     300    0
4     2     100    0


おっと、全部0になってしまった。
そりゃ、そうか。どっかでインクリメントしないとね。
td.LVL + 1で戻せばOKだろうか。やってみよう。

WITH td(NNO, PNO, NDATA, LVL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1 -- これでどうだ
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

NNO   PNO   NDATA  LVL
-------------------------
1     NULL  100    0
2     1     200    1
3     1     300    1
4     2     100    2


いいね。
NNO=1はルートノードなので0。
NNO=2と3は、1の子ノードなので、階層は1。OKです。
NNO=4は、1->2->4といった「パス」を経由することになるので、階層は2でOK。2回遷移した(矢印が二つ)。

前回、解説していった「再帰のn回目」みたいなことが、LVLで計算できた!っていうことなんです。

TREE_DATAとtdを結合することで、再帰の前後のデータの両方を参照することができる。というのが、再帰クエリの一番の特徴なのかな。

パスから値を計算する

見事に階層を計算することができた。
最後に、NODE_DATAの合計を計算する再帰クエリを紹介してみたいと思う。

WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
    TD2.NODE_DATA + td.NDATA_TOTAL
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

NNO   PNO   NDATA  LVL  NDATA_TOTAL
-----------------------------------
1     NULL  100    0    100
2     1     200    1    300
3     1     300    1    400
4     2     100    2    400


ノード4のNDATA_TOTALに注目して欲しい。計算された値は400となっている。これは、以下の計算式で計算されたものである。

100 + 200 + 100

ノード1 -> ノード2 -> ノード4と再帰のパスを通ってくることになるのだが、その時のNDATAの値を順番に足し算していった結果がNDATA_TOTALとなる。
SUMは、グループの合計を計算する。仮にノード1,2,4と言うグループ分けができれば、SUM集計関数を使用してNDATA_TOTALを計算できなくもない。そんな変なグループ化ができないので、SUM集計関数では計算できないのである。おっと、OracleにはCONNECT BYみたいな文法があるんだっけか?これは要調査か。


再帰の循環

何も考えずに、自分自身を呼び出すと無限ループに陥る、といった話をしたかと思う。WITHの再帰クエリでは、再帰呼び出しできる場所が限定されているので、無限ループしてしまうことが少ないようになっている。
それでも、データの作り方によっては、「再帰の循環」が発生してしまうこともある。

ツリー構造では、ルートから子が生えていって、いずれは子を持たないノードになる。末端のノードは、リーフノードとも呼ばれる。
また、通常は、子ノードが祖先のノードに先祖返りしてしまうこともない。そのようになっていると、タイムリープが発生することになり、親子関係が時系列順に並ばなくなってしまう。なんか、難しい?図にしたら以下のようなことである。

 1
 ┣ 2
 ┃ ┗ 4
 ┗ 3
   ┗ 1

3の子に1がある。3の親も1である。
親ノードへのポインタを持つツリー構造では、こう言ったデータを作成することはできない。上の図は無理やり描いただけ。
と思ったが、TREE_DATAテーブルでNODE_NOがプライマリキーになっていなければ可能か。TREE_DATAテーブルにもその行をプライマリキーを外して追加してみた。

SELECT * FROM TREE_DATA

NODE_NO PARENT_NO NODE_DATA
---------------------------
1       NULL      100
2       1         200
3       1         300
4       2         100
1       3          90


これを先ほどの再帰クエリにかけるとどうなるのか。やってみよう。

WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
    TD2.NODE_DATA + td.NDATA_TOTAL
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

 ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました


エラーになりました。
無限ループになっちゃったわけである。

CYCLE

さて、これを回避する方法もちゃんとあります。
WITH句のオプションでCYCLEというものがある。これを使って循環を検出できるような列を書いておく。CYCLEに続けて、SET なになに、と式を書くがここはあまり重要ではなく、フラグの名前とフラグの値を0/1にします程度のこと。

WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
    TD2.NODE_DATA + td.NDATA_TOTAL
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
CYCLE NNO SET LOOP_FLAG TO 1 DEFAULT 0   -- CYCLEで循環を検出させる
SELECT * FROM td

NNO   PNO   NDATA  LVL  NDATA_TOTAL LOOP_FLAG
---------------------------------------------
1     NULL  100    0    100         0
2     1     200    1    300         0
3     1     300    1    400         0
4     2     100    2    400         0
1     3      90    2    490         1


CYCLEに続けて、再帰の過程で記録しておくべき列を指定する。記録しておいた値が再度出現したら循環とみなし、再帰を停止する。複数であっても良いが、WITH句のパラメータにふくまれている仮想な列を指定する必要がある。
例で言うのなら、TREE_DATAテーブルのNODE_NOは、指定不可で、NNOならOK。

CYCLE 列名 の後のSET LOOP_FLAG TO 1 DEFAULT 0は、循環の検出に使用するフラグの名前と値。
WITHで定義される仮想表に自動的にこの列が追加される。面倒な指定だが省略することはできない。
フラグの値は、数値でなくてもよく、1文字の文字列でも可。SET LOOP_FLAG TO 'Y' DEFAULT 'N'とすることもできる。数値の場合でも桁数が1。
数値、文字のどちらの場合でも、ふたつ指定する値がどちらも同じであってはいけない。SET LOOP_FLAG TO 0 DEFAULT 0はダメ。違いがわからないもんね。

CYCLE指定する列は、例の場合は、NNOが適切。PNOでも循環を検出できるが、以下のように1段階、再帰が深いところまで進んで行われる。

WITH td(NNO, PNO, NDATA, LVL, NDATA_TOTAL) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA, 0, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA, td.LVL + 1,
    TD2.NODE_DATA + td.NDATA_TOTAL
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
CYCLE PNO SET LOOP_FLAG TO 1 DEFAULT 0   -- PNOで循環検出
SELECT * FROM td

NNO   PNO   NDATA  LVL  NDATA_TOTAL LOOP_FLAG
---------------------------------------------
1     NULL  100    0    100         0
2     1     200    1    300         0
3     1     300    1    400         0
4     2     100    2    400         0
1     3      90    2    490         0
2     1     200    3    690         1
3     1     300    3    790         1


用語や方言について

WITH再帰クエリについて、解説してきた。すべて、Oracle11gで実行して検証している。SQL ServerでもWITHで再帰させることが可能ではあるものの、CYCLEが使えなかったりするので注意が必要。
PostgreSQLでは、WITH RECURSIVE ...と再帰クエリとする場合は、RECURSIVEを明示しないとダメかも。
Oracle11gではRECURSIVE指定はできない。もーまた方言作っちゃって。まぁ、CONNECT BYよりはいいか。

初期化ブランチという用語も「Oracleならでは」なのかも。UNION ALLの前後のSELECT命令をなんと呼ぶかについては結構DBによってバラバラ?
SQL標準だと、「非再帰項」と「再帰項」となっている。非再帰項は最初の初期化の時だけしか評価されないので、名前としては初期化項の方がわかりやすいと思うが。「再帰を含まない初期化クエリ」とでもしておくか。
UNION ALLの前後にどちらを書いても良い、というのが非常に嫌な感じ。説明しにくいじゃないか。

再帰についてはここまで。

WITH 再帰クエリ



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

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

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





nice!(0)  コメント(0) 

WITH 再帰クエリ [SQLポケリ]

前回は、WITH句と再帰クエリについて紹介した。今回は、その続きである。

再帰処理とツリー構造
再帰処理は、ツリー構造のデータ構造を扱う際に、よく使用される。XMLの要素はツリー構造になっている。各要素をくまなく回ってみていく際に、再帰処理を使うと簡単に書けたりする。

SQLでは表形式のデータを扱うことが多いので、再帰処理はあまり得意ではない。SELECT命令にしても、ループ処理をするための制御命令を便利にしただけという印象。

最近拡張された、WITHによって「再帰クエリ」が可能になった、というわけである。

表形式のデータでもツリー構造にすることができる。簡単に実装する方法に、親へのポインタを持つ方法がある。C
言語ならポインタとなるが、テーブルなら行番号を持たせれば良いであろう。テーブルを作成するとしたら、以下のようになる。

CREATE TABLE TREE_DATA (
 NODE_NO INTEGER NOT NULL PRIMARY KEY,
 PARENT_NO INTEGER,
 NODE_DATA INTEGER
);

INSERT INTO TREE_DATA VALUES(1, NULL, 100);
INSERT INTO TREE_DATA VALUES(2, 1, 200);
INSERT INTO TREE_DATA VALUES(3, 1, 300);
INSERT INTO TREE_DATA VALUES(4, 2, 100);


図にしたら以下のような感じ。

 1
 ┣ 2
 ┃ ┗ 4
 ┗ 3

NODE_NO=1の行には親が存在しない。従って、PARENT_NOの列はNULLとなっている。
NODE_NO=1の行には、子のノードとして、NODE_NO=2と3の二つがぶら下がっている。PARENT_NOの列がどちらも1になっている。
NODE_NO=2の行には、子ノードNODE_NO=4がある。
NODE_NO=3と4の行には、子ノードが存在しない。

PARENT_NOがNULLである行は、子ノードを持たない、いわゆるルートノードである。これは以下のようなSELECT命令で取得できる。

SELECT * FROM TREE_DATA WHERE PARENT_NO IS NULL

NODE_NO  PARENT_NO  NODE_DATA
-----------------------------
1        NULL       100


ルートノードは、NODE_NO=1の行が一つだけということがわかる。
ノード1の子ノードを列挙しなさい、と言われたら、以下のSELECT命令で取得できる。

SELECT * FROM TREE_DATA WHERE PARENT_NO = 1

NODE_NO  PARENT_NO  NODE_DATA
-----------------------------
2        1          200
3        1          300


NODE_NO=2と3の二つの子ノードがある。
さらに、ノード2の子ノードを列挙するなら以下とすればOK

SELECT * FROM TREE_DATA WHERE PARENT_NO = 2

NODE_NO  PARENT_NO  NODE_DATA
-----------------------------
4        2          100


そんなに難しくない。
ノード3なら以下。

SELECT * FROM TREE_DATA WHERE PARENT_NO = 3

NODE_NO  PARENT_NO  NODE_DATA
-----------------------------


ノード3には子ノードが存在しないので、結果は空となる。

これらのSELECT命令を順に実行できれば、ツリー構造を「舐めた」と言えるのであるが、PARENT_NO=1とか、2とかの条件はツリー構造の図から人力で拾ってきた。ここをSELECT命令でなんとか書きたいっていうのが再帰クエリの主な目的。
WITHを使うとできるようになっちゃうのだが、この書き方がちょっと慣れが必要。

まず、WITH内のSELECT命令に、UNION ALLが必要。それに、初期化ブランチというものが必要であることは、前でも述べた。
初期化ブランチというのは、初期化を行うためのクエリで、「ツリー構造を舐める」クエリの場合、ルートノードを見つけるというのが初期化ブランチになる。ブランチ、と呼んでいるが、UNION ALLで繋げたクエリのどちらか、と考えるとスッキリするかも知れない。
初期化ブランチでは、再帰呼び出しをしてはいけない。再帰呼び出しをすると無限ループになるからね。

前記事でのWITH再帰クエリをもう一度見てみよう。

WITH vfoo(a) AS (
  SELECT a FROM vfoo
  UNION ALL
  SELECT a FROM vfoo
)
SELECT * FROM vfoo
 
 ORA-32043: 再帰的WITH句には初期化ブランチが必要です


エラーとなってしまうのは、UNION ALLで繋げた両方のクエリで再帰呼び出しになってしまっているから。以下のように変更して実行してみると、エラーの様子が変化する。

WITH vfoo(a) AS (
  SELECT a FROM foo WHERE a = 'A' -- 再帰しない
  UNION ALL
  SELECT a FROM vfoo
)
SELECT * FROM vfoo

 ORA-32044: 再帰的WITH問合せの実行中にサイクルが検出されました


しかし、これでもエラーになってしまっている。「サイクルが検出」というのは、無限ループに陥ってますよ、と言うこと。UNION ALLで繋げた後の方のクエリで再帰している。
ここの再帰の様子を変更すれば、実行可能な再帰クエリとなる。TREE_DATAの例でやってみよう。

WITH td(NNO, PNO, NDATA) AS (
  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO
)
SELECT * FROM td

  NNO  PNO   NDATA
  -----------------
  1    NULL  100
  2    1     200
  3    1     300
  4    2     100


UNION ALLの前のクエリは、初期化ブランチである。切り出してよく見てみよう。

  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL


PARENT_NOがNULLである、ルートノードを取り出すためのクエリである。再帰となっていないため、単独で実行できる。
以下は、UNION ALLの後のクエリである。

  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
    FROM td INNER JOIN TREE_DATA TD2
     ON td.NNO = TD2.PARENT_NO


tdは、WITHで定義したインラインビューである。自分を再帰的に呼び出している部分でもある。そのtdとTREE_DATAを結合している。TD2はTREE_DATAに付けた別名である。
tdを定義中の一部分であるため、取り出して単独で実行することはできない。

tdの定義で、列名を指定している。そのため、tdは以下の列を持っているようになる。

NNO
PNO
NDATA

仮想的な表、tdとTREE_DATAテーブルを結合している。tdの元は、TREE_DATAテーブルなので、自己結合しているようなものだが、単純に一つの同じテーブルを横に並べただけではなく、「再帰呼び出しで得られた結果のテーブルを結合」している感じになる。
結合条件が、td.NNO = TD2.PARENT_NOとなっていることが最大のポイントなわけである。

なんとなくわかってきたと思うが、まだしっかり把握できたわけではないと思うので、再帰処理を順を追ってみていくことにしよう。

再帰の1回目

WITH句の外で、tdを参照する。その最初の参照で、どうなるかというと、初期化ブランチだけの実行結果が一時表に格納される。初期化ブランチは以下のようになっていた。

  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL

  NNO  PNO   NDATA
  -----------------
  1    NULL  100


NODE_NO=1だけの行が一時表に格納される。初期化だからなんとなく納得頂けるのではないかと思う。

再帰の2回目

で、この一時表の結果をさらに、WITH内のクエリにかけるのである。この時、tdの内容が仮に、一時表のものとなる。

  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
    FROM td INNER JOIN TREE_DATA TD2                -- tdの内容は再帰の1回目の実行結果となる
     ON td.NNO = TD2.PARENT_NO

  NNO  PNO   NDATA    td.NNO td.PNO td.NDATA
  -----------------
  1    NULL  100     -- 初期化ブランチで得られる
  2    1     200      1      NULL   100
  3    1     300      1      NULL   100


td.NNO、td.PNO、td.NDATAは、参考情報である。クエリで取得していないため、戻されることはない。
UNION ALLの後のクエリでPARENT_NO=1となっている行が結合条件に引っかかり、NNO=2と3の二つの行が増えている。一時表には、増えた分の行だけが追加されていくことになる。UNION ALLだからと言って、重複して結果が戻されることはない。

再帰の3回目

前回の呼び出しと、今回の呼び出しで、行が増えている限り、再帰呼び出しが継続する。3回目の再帰処理も行われる。

  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
    FROM td INNER JOIN TREE_DATA TD2                -- tdの内容は再帰の2回目の実行結果となる
     ON td.NNO = TD2.PARENT_NO

  NNO  PNO   NDATA    td.NNO td.PNO td.NDATA
  -----------------
  1    NULL  100     -- 初期化ブランチで得られる
  2    1     200      1      NULL   100    -- 2回目の実行で得られる
  3    1     300      1      NULL   100    -- 2回目の実行で得られる
  4    2     100      2      1      200


NNO=4の行が増えた。
増えたので、4回目の再帰も行われる。

再帰の4回目

  SELECT NODE_NO, PARENT_NO, NODE_DATA
    FROM TREE_DATA WHERE PARENT_NO IS NULL
  UNION ALL
  SELECT TD2.NODE_NO, TD2.PARENT_NO, TD2.NODE_DATA
    FROM td INNER JOIN TREE_DATA TD2                -- tdの内容は再帰の3回目の実行結果となる
     ON td.NNO = TD2.PARENT_NO

  NNO  PNO   NDATA    td.NNO td.PNO td.NDATA
  -----------------
  1    NULL  100     -- 初期化ブランチで得られる
  2    1     200      1      NULL   100    -- 2回目の実行で得られる
  3    1     300      1      NULL   100    -- 2回目の実行で得られる
  4    2     100      2      1      200    -- 3回目の実行で得られる


3回目でNNO=4の行が増えたが、PARENT_NO=4である行が存在しないので、結果として行が増えなかった。そのため、再帰呼び出しは、4回目で終了となる。

わかって頂けたであろうか。
これが、再帰クエリの基本である。

もう、「こういうパターンで再帰クエリは書くもの」と覚えてしまった方が良いかも知れない。
ポイントとしては、以下のようになる。

再帰クエリ

 1 WITH内には、UNION ALLで二つのSELECT命令を書く
 2 二つのSELECT命令のうち、一つは初期化ブランチなので、再帰呼び出しをしてはいけない
 3 二つのSELECT命令のうち、もう一つは再帰呼び出しをして良いが、呼び出し前の一時表結果と結合する
 4 呼び出し前の一時表結果との結合条件は、親子関係を示す「PARENT_NO = NODE_NO」のようなものとなる


しかしながら、得られた結果にどう言った意味があるのか?
少々疑問ではないだろうか。
単に、全レコードを取得できただけ?それならば、SELECT * FROM TREE_DATAで十分では?

 はい、このクエリではその通りです。

  えー、じゃあ再帰クエリを使う意味って...

大丈夫、再帰の場合は、計算方法が異なる。再帰処理の特徴を使えば、再帰処理ならではの計算ができるようになる。

まぁ、急ぐこともあるまい。次回に期待?して欲しい。


WITH 再帰クエリ その2



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

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

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





nice!(0)  コメント(0) 

SQL WITH句 再帰クエリー [SQLポケリ]

最近、SQLネタをブログにアップしている。本日もSQLネタである。

WITH句

少し前から、WITH句というものが使えるようになっている。
Oracleだと9iから使えるのか。割と昔からあるじゃない。SQLポケリには載っていない。

WITHを使うと、長ったらしいサブクエリを何回も書かなくてよくなる。
これは便利。

CREATE VIEWをSELECT命令内でやってしまうようなもの。

具体例を示しながら解説してみる。

SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a


といったSELECT命令をサブクエリで何回も使いたいとする。
ビューを定義してしまえば、話は簡単。

CREATE VIEW vfoobar AS
 SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a


vfoobarという名前のビューを定義した。
vfoobarをテーブルのように使用できるから、長ったらしいfooとbarをa列で結合して...といったSELECT命令をvfoobarという名前だけで使用できるようになる。

SELECT * FROM vfoobar
 WHERE a = (SELECT MIN(a) FROM vfoobar)
   OR  a = (SELECT MAX(a) FROM vfoobar)


ほらね。
vfoobarのところを、SELECT foo.a, bar.b FROM foo INNER...というSELECT命令に脳内で置換してみてください。
なんだかよくわからない、複雑なクエリになってしまうことがわかる。

WITH句を使えば、こういったことを、ビューを作らないで一気にやってしまうことができる。

WITH foobar AS (
 SELECT foo.a, bar.b FROM foo INNER JOIN bar ON foo.a = bar.a
)
SELECT * FROM foobar
 WHERE a = (SELECT MIN(a) FROM foobar)
   OR  a = (SELECT MAX(a) FROM foobar)


なるほど。やっぱりこれは便利。


WITH RECURSIVE

便利なWITH句ではあるが、本来は「再帰クエリー」というものを想定して作られたものらしい。再帰クエリーとはいったいどういったものなのであろうか。

と偉そうに書いてみたが、再帰という概念は説明するのがけっこう難しい。自分の中に自分があるような感じ。プログラミングの場合、関数の再帰呼び出し、ということをよく耳にするかと思う。関数の再帰呼び出しなら、ある関数の内部処理で、自分自身を「再帰的に」呼び出す、というもの。

よく考えずに、関数の再帰呼び出しを行うと、無限ループに陥ることになる。なぜかというと、自分が自分を呼び出して、さらに呼び出された自分も自分を呼び出すので...
通常の関数は、引数をスタックに積む。無限ループといっても、スタックが足りなくなった時点で異常終了する。また、よく考えられた再帰呼び出しは、ちゃんといつかは終了するようにして作成する。

さて、SQLに戻って再帰クエリーである。

ビューを作成する際に、SELECT命令を記述することになるのだが、そのSELECT命令に定義中のビューを使用することはできない。
なぜなら、そのビューはまだ作成されていないから。
CREATE VIEWでviewxxを作成中に、ビューのSELECT命令でviewxxを使うことはできない。

CREATE VIEW vfoo AS
 SELECT * FROM vfoo


まぁ、そりゃそうだよね。再帰関数呼び出しで、無限ループに陥ってしまうことと同じ問題である。
試しに、上記のCREATE VIEWをOracleでやってみたら、エラーになった。

 SQLエラー: ORA-01731: 循環ビュー定義が検出されました
 01731. 00000 - "circular view definition encountered"

へー、「循環ビュー」っていうのか。

ビューでは、エラーになるものの、WITH句では、これが許されているのである。
マジか?無限ループにならないのか?
やってみよう。

WITH vfoo AS (
  SELECT a FROM vfoo
)
SELECT * FROM vfoo

 ORA-32039: 再帰的WITH句には列別名リストが必要です
 32039. 00000 -  "recursive WITH clause must have column alias list"


えー、そうなの。めんどくさー

WITH vfoo(a) AS (
  SELECT a FROM vfoo
)
SELECT * FROM vfoo

 ORA-32040: 再帰的WITH句にはUNION ALL操作を使用する必要があります
 32040. 00000 -  "recursive WITH clause must use a UNION ALL operation"


また、エラーになってしまった。
UNION ALLが必要?

WITH vfoo(a) AS (
  SELECT a FROM vfoo
  UNION ALL
  SELECT a FROM vfoo
)
SELECT * FROM vfoo

 ORA-32043: 再帰的WITH句には初期化ブランチが必要です
 32043. 00000 -  "recursive WITH clause needs an initialization branch"


ふえーん。初期化ブランチとはいったい。

要は、再帰呼び出しで無限ループに陥らないように、ちゃんと中のクエリを定義しなさい、ということ。WITHの中で、自分自身を使っても循環ビューのエラーにはならない。

再帰クエリーにする場合は、列指定が必要。再帰クエリーの中には、自分自身を含めてよいが、UNION ALLと初期化ブランチが必要。というところまでわかった。

本日は、ここまでとする。

続き WITH 再帰クエリー
WITH 再帰クエリ その2



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

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

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





nice!(0)  コメント(0) 

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句を使って分析関数のように使用することができる。

本日はここまで。




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

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

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





nice!(1)  コメント(0) 
前の10件 | - SQLポケリ ブログトップ



AREarthroid
AREarthroidPro ARで地球を表示するアプリ
AREarthroid ARで地球を表示するアプリ(無料)
AREarthライブ壁紙(無料)
Copyright Atsushi Asai Google+朝井淳

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

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

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

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

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

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

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

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

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