SQL文を組み立てるには？SQLが書ける様になるには？

　SQL文ができない人は、どこまでも文法で考えようとしている。

　私が、SQLに違和感を感じずに理解することができたのは、文法を気にしてなかったからで、私の勉強法というか、どうやって習得してきたのかを整理してみました。

例えば IN と EXISTS の違い

　カラオケで説明します。
　カラオケ10万曲から、とある曲リストと一致する曲を抽出するとします。

　手続き型では、次のいずれかになるでしょう。（細かいところは端折るよ）

Aパターン

	foreach (string 曲名 in 曲リスト){
		結果セット.add(カラオケ.find(曲名));
	}

Bパターン

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　Bパターンなんて使わないと思いますか？
　「とある曲リスト」が「男性ヴォーカリストの曲」でも？

　COBOL世代の人は処理時間が

　　　　　カラオケ.find(曲名) >> 曲リスト.exists(曲.曲名)

　となると理解しているので、「とある曲リスト」の件数が、母数に比べてどれぐらいの割合か（インデックスがあるなら25％以上、インデックスがないなら数件以上ならBパターンにすべき）考えてプログラムを変えます。
　※ ただ、COBOL世代の人は .find のロジックをそこにベタ書きする人が……（苦笑）

　この使い分けが普段からできてない人は、「SQL以前に手続き型でも全然できてない」ってことになってしまうので、もうちょっと修行すべきですね。

Aパターンのプログラムが欲しい

　Aパターンのプログラムが欲しいと思って仕様書を書こうとするとどうなるでしょう。

　　　　何から取ってくるか書く場所（つまりFROM句）
　　　　どんな条件で取るか書く場所（つまりWHERE句）
　　　　何が欲しいか書く場所（つまりSELECT句）

　結果セットとして欲しいモノは、あくまでもカラオケの曲ですから、何から取るかは「カラオケ」になります。
　何が欲しいかは特に指定してない（曲全体）ですから * で良いでしょう。

　問題はどんな条件かしかありませんが、Aパターンでは、まず、「曲リスト」が必要になります。

　これは外付けでもできますね。

　(SELECT 曲名 FROM 曲リスト) = ('逢いたくて', '愛のメモリー', ……)

	SELECT *
	FROM カラオケ
	WHERE 曲名 IN ('逢いたくて', '愛のメモリー',  ……);

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '逢いたくて' 
		OR 曲名 = '愛のメモリー' 
		OR ……;

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '逢いたくて'
	UNION ALL
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '愛のメモリー' 
	UNION ALL
	……;

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 IN (SELECT 曲名 FROM 曲リスト);

　無茶苦茶、書いていますが、どのパターンで仕様書（SQL）を書いても、実行計画（＝プログラム）は同じになります。（むしろ、実行前に UNION ALL の SQL に展開することが多いはず）

Bパターンのプログラムが欲しい

　FROM句まではAパターンと同じですね。

　抽出条件としては、FROM句のカラオケをループしながら「曲リスト」に存在するかチェックして欲しい。

　これを表現する手段としてサブクエリの中に、FROM句のカラムを入れ込むのが分かり易いと考えて、その表現を決めました。

	SELECT *
	FROM カラオケ
	WHERE
	EXISTS (SELECT * FROM 曲リスト WHERE カラオケ.曲名 = 曲リスト.曲名)

　（INで書いてもFROM句のカラムを指定したら同じですが）

　サブクエリの中にFROM句のカラムが入っていますから、サブクエリ単独では実行することができないのが特徴です。

　一般的な仕様書ならどう表現しても良いので、「Bパターンのプログラムが欲しい」と考えて出来の悪いプログラマ向けに仕様書を書いてみたら良い。その仕様書を、SQLの文法に当て嵌めれば、EXISTSを使ったSQLになるハズです。

　文法から考えるのではなく、まずは、どんなプログラムが欲しいか、じっくり考えてみたらどうでしょう。

逆にSQL（仕様書）からプログラムを書く

　逆にも考えてみましょう。
　あなたが下のSQL（仕様書）を受け取ったとしましょう。

	SELECT *
	FROM カラオケ
	WHERE 曲名 IN (SELECT 曲名 FROM 曲リスト);

　あなたのプログラムはこうなりますか？

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　SQLに限らず、カッコの中から先に解析・実行するのが普通で、カッコの中を先に解析・実行すれば、Bパターンのプログラムは出てこないでしょう。
　逆に、こちらのSQLでは、

	SELECT *
	FROM カラオケ
	WHERE
	EXISTS (SELECT * FROM 曲リスト WHERE カラオケ.曲名 = 曲リスト.曲名)

　カッコの中を解析しようとしても単独では実行できないので、普通にプログラミングすれば、Bパターンのプログラムになるでしょう。

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　私は実行計画の見方すら分からない初心者の頃（インターネットもほとんどない時代ね）から、このSQLならプログラムはこうなるはずで、そうじゃないプログラムを返すDBエンジンは創れない。実行速度から間違いない。と常に考えてコーディングしていました。
　その後、実行計画の見方を覚えて確認したら、思った通りで安心したという経緯を辿りました。

　スタートからSQLは仕様書で自動でプログラムを書いてくれるモノ、と考えて来たからSQLを書くことに何の違和感も感じないだけなのです。
　（後は経験の問題ですけれど……）

　逆に、嫌な言い方をすれば、「IN と EXISTS の違い」が分かってないという人は、SQL文が（直接実行される）プログラミング言語と思っているから中々理解できないし、現実のプログラムと全く繋がってないし、「文法を覚えて何とかしよう」と思ってしまうのでしょう。

　余談ですが、私がHTMLを覚えたときも同じやり方。
　「自分がブラウザを作るとしたら、HTMLはこんな言語仕様になってないとブラウザが作れない。だから、言語仕様はこんな感じだろう」と荒く考えて、細かい仕様はリファレンスを引く訳です。

SQLが仕様書なら手続き型と等価

　ちょっとしたプロジェクトに入ったことがある人なら、何度も経験があるはずですが、プログラミングができない人が書いた仕様書は、何が書いてあるか分からないことが多々ある。逆に考えれば、プログラミングができるなら、「仕様書であるSQLは効率的に簡単に書けるはず」なんです。

　ところが、プログラミングができるのに仕様書であるSQLが書けない人が沢山いるのは、SQLとプログラムを別物と考えているからです。（くどいけどSQLを魔法か呪文と勘違いしている）

　良いSQLを書くには、Aパターンのプログラムが欲しいのか、Bパターンのプログラムが欲しいのかが、まず、あなたの頭の中になければ書けない。これは大前提。
　裏を返せば、SQLがまともにできる人は、同時にプログラムもできているはずです。

　自分が欲しいプログラムが出てきそうな仕様書（SQL）を書き終わったら、それを貰ったプログラマ（DBエンジン）の立場になってプログラミングしてみる。更に、確認として、実行計画が問題ないか見るのです。

　これを繰り返していれば、自然に良いSQLが書ける様になる。DBエンジンが変わっても、ある程度予想できるし、RDBMS毎の文法の違いなんて、プロジェクト毎の仕様書のフォーマットや、お作法の違い程度の差しか感じなくなります。（いちいち、リファレンスを引いても大したことない）

　しかし、文法から入っていると、RDBMS毎の文法の違いが気になって仕方がないでしょうし、ちょっと変わればできなくなる。そういう人は、本質が分かってないのです。

　この規模のSQLになれば、書くのも、読むのも、SQLと手続き型を行ったり来たりしないと無理です。逆に行ったり来たりできるレベルになれば、30分もあればできる様になります。

　また、SQLと手続き型を行ったり来たりできるレベルになれば、「小さなSQLに分割した方がDBサーバの負荷が減る」なんて勘違いは絶対に起きないし、逆に、システム全体を俯瞰して本当に分割すべきポイントも見えてきます。

　しかし、現実的には、多くのプロジェクトで、「小さなSQLに分割した方がDBサーバの負荷が減る」なんて勘違いする連中がクラスタ構成までやっている訳で……、私には悪い冗談としか思えない。彼らは「できる」つもりになっているけれど、彼らのいう「できる」は、「クラスタを構成するツールを使うことができる」であって、「本質を分かって構成することができる」では決してないのですから。

　とにかく、SQLは仕様書ですから、手続き型プログラムと等価でなければおかしい。

　そこが分かってない人は、本質的に何も分かってないのです。

RDBMSを使う以上、SQLを使いこなさなければいけない

　何度も何度も言ってるけれど、「処理を分割した方がDBサーバの負荷が減る」と感じるのは勘違い。
http://d.hatena.ne.jp/Sikushima/20110809/
http://d.hatena.ne.jp/Sikushima/20110810/
http://d.hatena.ne.jp/Sikushima/20110811/
http://d.hatena.ne.jp/Sikushima/20110815/

落ちるのはピークしかない

　http://d.hatena.ne.jp/Sikushima/20110815/ にある通り、SQLでDBサーバでできることをAPサーバで行ってもDBサーバの全体の処理は減らない訳です。
　サーバに掛かる負荷というのはピークではなく面積で見なければいけないけれど、処理が減らない以上、DBサーバの1回の処理の面積は分割した方が大きくなる。

　この状態でアクセスが輻輳すれば下のようになる。

　SQLの処理を避ければ、DBサーバの負荷は高くなるわけです。

　もっと重要なことは、SQLで処理を行ったときのAPサーバの負荷に比べ、SQLを避けて処理したときのAPサーバの負荷は何倍も高くなります。

　ですから、APサーバが複数あったとしても、パンクするのは大抵APサーバの方でしょう。それで、「もし、DBサーバで処理してたら……」って言い訳してる場面を何度も見てきました。

　ところが、本来はDBサーバで処理していたら、APサーバがパンクする時期も随分ズラせてた訳です。（もちろん、Webサーバのパンクはどうにも出来ないけどね）

　すぐパンクするAPサーバをパンクさせないためにも、レスポンスを早くするためにも、DBサーバのパンクを防ぐためにも、SQLを使った方が良いわけですから、何度も言ってるけど、SQLを避けて得られるものは、「下手くそに合わせることができる」の一点しかないのです。

APサーバで行った方がよい処理もある

　とはいえ、もちろん例外は存在する。

　例えばこれとか。元々、メモリー上で処理が完結するようなモノを、わざわざDBサーバを使う必要がないのは当たり前。

　もう一つ、パターンがあって、前回の勉強会で出た問題。

というテーブルから、以下の様に一番多い連勝と連敗の数を出力する。

　何となくできそうな気はするんだけれど、一晩考えて（といっても徹夜するわけではなく夢の中で整理するんですが……）やっぱり答えが出なかった。

　勉強会で出た答えはこれ。

SELECT   勝敗値,
         max(cnt)
FROM     (SELECT   勝敗値,
                   count(*) AS cnt
          FROM     (SELECT 勝敗値,
                           row_number() OVER (ORDER BY 日付) - row_number() OVER (PARTITION BY 勝敗値 ORDER BY 日付) AS cnt
                    FROM   試合結果 AS s) AS a
          GROUP BY 勝敗値, cnt) AS a
GROUP BY 勝敗値
ORDER BY 勝敗値;

　まあ、正しいのですが応用は効かないし、どう考えてもAPサーバでやった方が効率的。

　再帰SQLでやるこちらはすぐに思いついたけれど、内部的にレコード数回SQLを発行するというとんでもないSQLで、こちらも、私としては答えとは認められない。

WITH
m(ID, 日付, 勝敗値)
AS(
	SELECT 
		ROW_NUMBER()OVER(ORDER BY 日付), 日付, 勝敗値
	FROM 試合結果
)
, cte(ID ,開始日付, 日付, 勝敗値, 連勝数, 連敗数)
AS(
	SELECT
		ID 
		, 日付 AS 開始日付
		, 日付
		, 勝敗値
		, CASE WHEN 勝敗値 = 1 THEN 1 ELSE 0 END AS 連勝数
		, CASE WHEN 勝敗値 = 0 THEN 1 ELSE 0 END AS 連敗数
	FROM m 
	WHERE ID = 1
 	UNION ALL
	SELECT 
		m.ID
		, CASE WHEN cte.勝敗値 = m.勝敗値 THEN cte.開始日付 ELSE m.日付 END AS 開始日付
		, m.日付
		, m.勝敗値
		, CASE WHEN m.勝敗値 = 1 THEN cte.連勝数 + 1 ELSE 0 END AS 連勝数
		, CASE WHEN cte.勝敗値 = 0 THEN cte.連敗数 + 1 ELSE 0 END AS 連敗数
	FROM m INNER JOIN cte ON m.ID = cte.ID + 1
)
SELECT * FROM cte; -- 後は好きにサマリーを取る

　要するに RDBMS はシーケンシャルにデータを保たない。という考えから、シーケンシャルな処理を非常に苦手にしている。
　　　　# 分析関数でLAGなどを使えばできるけどSQLServerは未実装なので……。

　このような処理は、必要なデータを転送して素直にAPサーバでやるか、ストアドプロシージャを組んだ方がよろしい。

　何でも例外はあるけれど、それはあくまで例外であって基本は理解するべき。

明日、勉強会の席が空いてます！

　まあ、何が言いたかったかというと宣伝。
　明日、勉強会の席が空いていますので、お時間がある方はいらっしゃってください。

http://sqlworld.org/event/20130827/

SQLWorld★大阪#16 開催情報
【日時】
2013年8月27日（火曜日）　19:00~21:00

【イベント概要】
SQLWorld 3回目の平日夜開催〜。今回も、みんなで SQL を書いてみようというハンズオン企画です！ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0001 大阪府大阪市北区梅田 2-4-9 ブリーゼタワー 12F
http://info.fenrir-inc.com/jp/profile/overview.html
ＪＲ大阪駅、ＪＲ北新地駅、地下鉄梅田駅・東梅田駅・西梅田駅と各駅からアクセス可能です

【参加費】
無料

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)

【参加可能人数】
13 人

【Twitterハッシュタグ】
#sqlworld

CODE VS 2.1 をSQLでやるとどうなる７

　前回、こんな「落ちゲー」の

　ブロックを一つ一つ加算していって、合計が10になる組合せをSQLで探しました。

　今回は消すお邪魔ブロック（最初から10以上（＝ 11）のブロック）を探すSQLを書きます。

繋げるか、一旦テーブルに書き出すか。

　前回でも充分な長さのある SQL になりました。
　更に繋げるのが良いか悪いかは微妙で、当初の設計では、Deletedテーブルに一旦保存するつもりでした。
　しかし、繋げた方がパフォーマンスが出そうなので繋げることにします。
　嫌な人は、前回の結果をテーブルに書き出してから続けましょう。

　前回は4方向に加算していきましたが、今回は消えるブロックの周りにあるお邪魔ブロックを探すことになりますので、1回だけですみます（再帰なし）また、お邪魔ブロックからは逆向きに探してこないため、4方向ではなく8方向を調査することになります。

　4方向用のDirection4テーブルと、8方向用のDirection8テーブルの2つ作っているのは、単純にサブクエリーが増えるのを避けたかったからだけで、サブクエリーでも良いと思う人は、Direction8に区分を追加してサブクエリーでやってください。

　ちと長い……。

WITH
	cb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 4方向に合算された結果
	AS(
	SELECT
		GameID
		, SimulationID
		, Turn
		, Row
		, Col
		, Row AS ERow
		, Col AS ECol
		, Num
		, Num AS amount
		, 1 AS cnt
		, 0 --調べている位置
		, CAST(N'調査' AS nvarchar(8)) AS Memo
	FROM Board
	WHERE GameID =-1 AND Turn = 0	AND Num < 10 
	UNION ALL
	SELECT 
		cb.GameID
		, cb.SimulationID
		, cb.Turn
		, cb.Row			-- 調査元の行
		, cb.Col		-- 調査元の列
		, bd.Row		-- 調査先の行
		, bd.Col		-- 調査先の列
		, bd.Num
		, cb.Amount + bd.Num
		, Cnt + 1 
		, dr.Angle
		, dr.Memo
	FROM 
		Board AS bd
		INNER JOIN (cb
		CROSS JOIN Direction4 AS dr)
			ON bd.Row = cb.Row + (dr.RowOffset * Cnt)
			AND bd.Col = cb.Col + (dr.ColOffset * Cnt)
			AND bd.GameID = cb.GameID
			AND bd.SimulationID = cb.SimulationID
			AND bd.turn = cb.turn
	WHERE
		cb.amount + bd.num <= 10
	)
-- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる
	, eb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 消すブロック
	AS(
	SELECT * 
	FROM cb AS bd
		WHERE EXISTS (
			SELECT * FROM cb 
			WHERE bd.row = cb.row 
				AND bd.col = cb.col
				AND (bd.angle = 0 OR bd.angle = cb.angle)
				AND cb.amount = 10
			)
	) -- WITHの終わり
-- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる
-- SELECT * FROM eb;  -- として、以下をコメントアウトすれば消すべきブロックを確認できる
	, ob (Row, Col, ERow, ECol, Num, Angle, Memo) -- お邪魔ブロック
	AS(
	SELECT 
		eb.ERow			-- 調査元の行
		, eb.ECol		-- 調査元の列
		, bd.Row		-- 調査先の行
		, bd.Col		-- 調査先の列
		, bd.Num
		, dr.Angle
		, dr.Memo
	FROM
		eb -- 上のSQLで作成した消すべきブロックの一覧
		INNER JOIN (Board AS bd
		CROSS JOIN Direction8 AS dr)
			ON eb.ERow + dr.RowOffset = bd.Row
			AND eb.ECol + dr.ColOffset = bd.Col
	WHERE
		bd.GameID = -1 AND bd.Turn = 0
		AND bd.Num > 10
	)
-- SELECT * FROM cb;  --として、以下をコメントアウトすれば4方向に合計していったときの結果が確認できる
-- SELECT * FROM eb;  --として、以下をコメントアウトすれば消すべきブロックを確認できる
-- SELECT * FROM ob;  --として、以下をコメントアウトすれば消すべきお邪魔ブロックが確認できる
	, curEb (ERow, ECol) -- このチェインで消すブロック（重複あり）
	AS (
		SELECT ERow, ECol FROM eb -- 通常のブロックは重複あり
		UNION ALL 
		SELECT ERow, ECol FROM ob GROUP BY ERow, ECol -- お邪魔ブロックは重複なし
	)
-- SELECT * FROM cb;  --として、以下をコメントアウトすれば4方向に合計していったときの結果が確認できる
-- SELECT * FROM eb;  --として、以下をコメントアウトすれば消すべきブロックが確認できる
-- SELECT * FROM ob;  --として、以下をコメントアウトすれば消すべきお邪魔ブロックが確認できる
SELECT * FROM curEb; --今回のチェインで消すべきブロックの数が確認出来る。

つづく

CODE VS 2.1 をSQLでやるとどうなる６

　前回の続きです。今回は消すブロックを選択するところまで作ります。

　作るのは http://codevswc.jp/jpn/rule.html これです。

　是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
　「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
　と想像しながら読んでください。

　多分、多くの人が、「『消すブロックを選択するところ』が一番、SQLでできない」と考えるところじゃないかと思っています。が、この辺の処理は極めてSQL的です。

　最初の方で作った4方向、8方向へオフセットするテーブルを使いますから、勘のいい人はアレを観た時点で分かったと思いますが……。

現状のデータでは分かりにくいので新たなデータでやります

　このSQL http://www.g1sys.co.jp/TestB.txt を流して確認すると、

-- 確認
SELECT * FROM DispBoard10(-1, 0, 0, 0)
ORDER BY Row DESC;

　図の様に返ってきます。

　以降、これをベースに考えます。
　色が変わっているブロックを選択出来れば成功です。

4方向か8方向か

　まずは消すブロックをカウントするのですけれど、今、落としたブロックは分かるわけですから、落としたブロックだけを8方向に合算して行き、合計が条件と一致する（現在のゲームでは10）になれば消すブロックと考えれば良いことです。

　しかし、図の様に赤枠の4つのブロックが投下されたとすると、

（1, 0)の位置にある[5]から右下方向に合算して行って(0, 1)の[5]を見つけ、「消すブロック」と決定することができます。
　さらに、(0, 0)の位置の[1]、(1, 1)の位置の[11]はどの方向合算していっても消せませんが、(0, 1)の[5]を左上に合算をして行ったとき、既に選択済みの(1, 0)の[5]が二重に選択されます。単純な個数なら重複して抽出し、後から重複を消せばすむ話ですが、ルールからオレンジの(0, 1)の[5]は二重計上しなければ行けません。

　この複雑さを回避するために、全部の数字を4方向にチェックしていくのが一般的と思います。（ちなみに8方向に合算していくと、必ず二重計上され無駄です）

　いくつかフラグを足せばSQLでも、落としたブロックだけをチェックするだけでできなくはないのですが、解説が大変なので4方向に合算していくやり方にしました（苦笑）

　恐らく、パフォーマンス的にはそんなに変わらないと思います。

　と言うわけで、再帰SQLを使って4方向に合算していきます。

再帰SQLとCROSS JOIN

WITH
	cb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 4方向に合算された結果
	AS(
	SELECT
		GameID
		, SimulationID
		, Turn
		, Row
		, Col
		, Row AS ERow
		, Col AS ECol
		, Num
		, Num AS amount
		, 1 AS cnt
		, 0 --調べている位置
		, CAST(N'調査' AS nvarchar(8)) AS Memo
	FROM Board
	WHERE GameID =-1 AND Turn = 0	AND Num < 10 
	UNION ALL
	SELECT 
		cb.GameID
		, cb.SimulationID
		, cb.Turn
		, cb.Row			-- 調査元の行
		, cb.Col		-- 調査元の列
		, bd.Row		-- 調査先の行
		, bd.Col		-- 調査先の列
		, bd.Num
		, cb.Amount + bd.Num
		, Cnt + 1 
		, dr.Angle
		, dr.Memo
	FROM 
		Board AS bd
		INNER JOIN (cb
		CROSS JOIN Direction4 AS dr)
			ON bd.Row = cb.Row + (dr.RowOffset * Cnt)
			AND bd.Col = cb.Col + (dr.ColOffset * Cnt)
			AND bd.GameID = cb.GameID
			AND bd.SimulationID = cb.SimulationID
			AND bd.turn = cb.turn
	WHERE
		cb.amount + bd.num <= 10
	)
-- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる
	, eb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 消すブロック
	AS(
	SELECT * 
	FROM cb AS bd
		WHERE EXISTS (
			SELECT * FROM cb 
			WHERE bd.row = cb.row 
				AND bd.col = cb.col
				AND (bd.angle = 0 OR bd.angle = cb.angle)
				AND cb.amount = 10
			)
	) -- WITHの終わり
-- SELECT * FROM eb; -- でより詳しい情報が表示されます。
SELECT ERow, ECol, Num FROM eb;	

　実行すれば、(0, 1)の[5]が重複して、都合5つのブロックが選択されるはずです。

　つづく

CODE VS 2.1 をSQLでやるとどうなる５

　前回の続きです。今回は、パックを投下するところまでを作ります。

　作るのは http://codevswc.jp/jpn/rule.html これです。データは、与えられるInputのサンプルとします。

　是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
　「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」

　と想像しながら読んでください。

　プログラムを作るよりも解説の方がはるかに難しい……。だんだん手抜きになってきていますがご容赦。

Turnテーブル（シミュレーション用）から全通りのパックを投下する

　前回作ったTurnテーブル（シミュレーション用）から、1つ目のパックが取り得る全通りのパターンで投下します。

　まず、現在、盤面に残っているブロック（現状は空）に、新しい SimulationID 次のターンにインサートします。

INSERT INTO Board
	(GameID, SimulationID, Turn, Row, Col, Num) 
SELECT 
	t.GameID
	, t.SimulationID
	, t.Turn 
	, b.Row
	, b.Col
	, b.Num
FROM
	Turn t
	LEFT JOIN board b
		ON t.GameID = b.GameID
		AND 0 = b.SimulationID -- 採用したパターン
		AND t.Turn = b.Turn -1
WHERE
	t.GameID = 0
	AND t.Turn = 1;

　続いて、パックデータを投下します
　尚、上のSQLのSELECT部分と、下のSQLのSELECT部分を、UNION ALLで繋いで1回にしても同じです。

INSERT INTO Board
	(GameID, SimulationID, Turn, Row, Col, Num) 
SELECT 
	tp.GameID
	, tp.SimulationID
	, tp.Turn
	, ISNULL(b.MaxRow, -1) -- ゼロオリジンなのでブロックがないときは-1
		+ RANK() OVER(PARTITION BY tp.SimulationID, tp.tCol ORDER BY tp.tRow)
	, tp.tCol
	, tp.Num
FROM
	(SELECT 
		t.GameID, t.Turn, t.SimulationID, t.Rot
		, dbo.ChangeRow(t.Rot, p.Row, p.Col, 4) AS tRow -- 回転後のRow
		, dbo.ChangeCol(t.Rot, p.Row, p.Col, 4) + t.XPos 
				AS tCol   -- 回転後落とす位置のCol  
		, p.Num
		FROM 
			Turn t
			INNER JOIN Pack p --参照されるPackテーブル
				ON t.GameID = p.GameID
				AND t.Turn = p.Turn + 1 -- Turnテーブルのターンはカウントアップ済み
		WHERE
			t.Turn = 1
	) AS tp
	LEFT JOIN (
			SELECT MAX(Row) As MaxRow, Col 
			FROM  Board 
			WHERE Turn = 0 -- コピー元を観た方が効率的
			GROUP BY Col) As b
		ON tp.tCol = b.Col
;

-- 確認 xx に1〜52までの数字を入れて、パックを回転させて
-- 狙った位置に投下できているか確認しましょう。
SELECT * FROM DispBoard10(0, xx, 1, 0)
ORDER BY Row DESC;

CODE VS 2.1 をSQLでやるとどうなる４

　前回の続きです。今回はパックを回転するするところまでを作ります。

　作るのは http://codevswc.jp/jpn/rule.html これです。データは、与えられるInputのサンプルとします。

　是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
　「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」

　と想像しながら読んでください。

パックを回転します

　回転するには図の様に、Row、Colを変更する必要があります。これは他の言語でもほぼ同じでしょう。もちろん、左上を (0, 0) としたときは変更の必要があるので、お気を付けください。

　具体的には図の通りになります。

　これは他の言語でもされるように関数にしましょう。
　……ですが、SQLでは一つずつ作ることになります。

CREATE FUNCTION ChangeRow
(@Rot int, @Row int, @Col int, @Size int)
RETURNS int
BEGIN
	DECLARE @Ret int;
	
	SET @Ret = 
		CASE @Rot 
			WHEN 1 THEN (@Size - 1) - @Col 
			WHEN 2 THEN (@Size - 1) - @Row 
			WHEN 3 THEN @Col
			ELSE @Row 
		END;
	
	RETURN @Ret;
END;

CREATE FUNCTION ChangeCol
(@Rot int, @Row int, @Col int, @Size int)
RETURNS int
BEGIN
	DECLARE @Ret int;
	
	SET @Ret = 
		CASE @Rot 
			WHEN 1 THEN @Row
			WHEN 2 THEN (@Size - 1) - @Col
			WHEN 3 THEN (@Size - 1) - @Row
			WHEN @Col
		END;
	
	RETURN @Ret;
END;

パックを投下するリストを作る

　ルールhttp://codevswc.jp/jpn/rule.html を読むと、パックは横方向（X軸）はマイナスの位置にも落とせます。

　ゼロオリジンになっていますので、取り得るパックを落とす位置の一番右端は、
　　　　0 - パックのサイズ - 1
　となります。

　左端は、
　　　　盤の幅 - 1
　となります。

　パックは90°ずつ回した4パターンがあります。

　今回のゲーム
　　　　パックのサイズ = 4
　　　　盤の幅 = 10
　ですから、パックを投下するパターンは、

　　　　-3 〜 9 = 13通り × 4つの角度 = 52通り
　になります。

SELECT 
	xp.Seq AS XPos 
	, rt.Seq AS Rot
FROM tf_SequentialNums(-3, 10) AS xp
	CROSS JOIN tf_SequentialNums(0, 3) AS rt
	

　1つめのパックを落とせる全てのパターンを作ることができます。
　SimulationID は 1から採番して 0を実際に採用したアウトプットとします。
　投下した時点でTurnはカウントアップされます。

INSERT INTO Turn
	(GameID, SimulationID, Turn, XPos, Rot)
SELECT 
	0, Rank() OVER(ORDER BY xp.Seq, rt.Seq)
	, 0 + 1, xp.Seq, rt.Seq 
FROM tf_SequentialNums(-3, 9) AS xp
	CROSS JOIN tf_SequentialNums(0, 3) AS rt;
	

回転したリスト

　作成したTurn（シミュレーション用）のデータとパックを結合して、回転後のRow、Colの一覧を見る。

SELECT 
	t.SimulationID, t.Rot
	, p.Row, p.Col, p.Num
	, dbo.ChangeRow(t.Rot, p.Row, p.Col, 4) AS tRow -- 回転後のRow
	, dbo.ChangeCol(t.Rot, p.Row, p.Col, 4) AS tCol -- 回転後のCol  
FROM 
	Turn t
	INNER JOIN Pack p --参照されるPackテーブル
		ON t.GameID = p.GameID -- TurnテーブルのキーのSimulationIDと結合して
		AND t.Turn = p.Turn    -- ないのでSimulationIDの種類数倍になる。

つづく

CODE VS 2.1 をSQLでやるとどうなる３

　前回の続きです。

　作るのは http://codevswc.jp/jpn/rule.html これです。所謂、「落ちゲー」もある程度、SQLで作れます。

　是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」

　「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」

　と想像しながら読んでください。

パックを登録

　前回説明したとおり、左下を(0, 0) として、(行, 列)の組合せで登録してきます。

　例えば、Sampleにあるように、Javaでは以下の様に登録することが多いと思います。

	int[][][] pack = new int[step][size][size];
	String endstr = null;

	for(int i=0;i<step;i++){
		for(int j=0;j<size;j++){
			for(int k=0;k<size;k++){
				pack[i][j][k] = scan.nextInt();  -- 0以外の数字のとき、INSERTする。
			}
		}
		endstr = scan.next();
	}

　この pack[i][j][k] = scan.nextInt(); の j が Row にあたるのですが、左下を（0, 0）としているため、Row = size - j - 1 となります。好みで変えてください。

　0は不要ですので、0のときは INSERT しません。

連番テーブルファンクションを作る

　よく使うので、共通関数としてDBに一つ作っておいても良いかも知れません。

CREATE FUNCTION tf_SequentialNums
(@From int, @To int)
RETURNS TABLE
AS
	RETURN
	(
		WITH SequentialNums (Seq) 
			AS (
			SELECT @From AS Seq
			UNION ALL
			SELECT Seq + 1 FROM SequentialNums
			WHERE Seq + 1 <= @To
			)
		SELECT Seq FROM SequentialNums
	);

　使い方

SELECT * FROM tf_SequentialNums(-3, 10); 

　とすれば、-3から10までの連番を作ることができます。

盤面を確認する

　盤面（Boardテーブル）にパック（Packテーブル）の内容を落としていくゲームですが、Boardテーブルも数値の羅列で何のことか分かりません。しかし、以下のSQLで確認することが可能です。

　残念なことに、SQLでは列を動的に指定することはできません。そのため、盤面の種類毎（今回の大会では、盤の幅が10、15、20）に作るか、一番大きな盤面で代替するかになります。

　よく使うことになるので、これもテーブルファンクションにしておくと良いでしょう。
　個人的に PIVOT は嫌いで、CASEで書くことが多いのですが、今回はPIVOTにしておきました。
　DispBoard15、DispBoard20 は列数が多いだけなので、ご自身で作ってください。

CREATE FUNCTION DispBoard10
(@GameID int, @SimulationID money, @Turn int, @Chain int)
RETURNS TABLE
AS
	RETURN
	(
		SELECT 
			pvt.Seq AS Row
			, [00], [01], [02], [03], [04], [05], [06], [07], [08], [09]
		FROM tf_SequentialNums(0, 19) AS l -- 盤の高さ＋Packサイズで良いと思う
			LEFT JOIN Board AS b
				ON l.Seq = b.Row
				AND @GameID = b.GameID -- WHERE句ではなくFROM句に書くのがミソ
				AND @SimulationID = b.SimulationID -- WHERE句ではなくFROM句に書くのがミソ
				AND @Turn = b.Turn -- WHERE句ではなくFROM句に書くのがミソ
				AND @Chain = b.Chain -- WHERE句ではなくFROM句に書くのがミソ
				PIVOT (
					MAX(b.Num) FOR b.Col 
					IN([00], [01], [02], [03], [04], [05], [06], [07], [08], [09])
					) AS pvt
	)
go

　使い方

SELECT * FROM DispBoard10(0, 1, 1, 0)
ORDER BY Row DESC

　現状は空なので、NULLが並んだ盤面が再現されるはずです。

　与えられるInputのサンプル。
　Packテーブルにデータを入れるInsert文のサンプル。
　（SQLServer2005向けで、2008なら列リストは要らないのですが……）

　（つづく）