【目的】
ゆるい題材からクエリ、アルゴリズムを学ぶ。
【環境】
| SQLServer | SQLCMD |
|---|---|
| 2017 | 14.0.1000.169 |
【着地点】
1～40 を列挙し、3の倍数と3が付く数字のときは 'アホ' とする。
出力イメージは以下。
| Seq | Status |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | アホ |
| 4 | NULL |
| ... | ... |
【連番の列挙】
CTE（共通テーブル式）、WITH句を使う。以下は 1～5 を列挙するサンプル。
;WITH [CTE_Seq]([Seq]) AS (
SELECT 1
UNION ALL
SELECT [Seq] + 1 FROM [CTE_Seq] WHERE [Seq] < 5
)
SELECT [Seq] FROM [CTE_Seq] ORDER BY [Seq]
;
GO
使わない人は全然使わない CTE。
まずこの説明から。（知っている方は飛ばして下さい）
-
UNION ALLで二つの SELECT文を連結が基本構文 - 一つ目の SELECT文は先頭レコードの定義
- 二つ目の SELECT文は二レコード目以降の定義
- 二つ目の SELECT文の FROM句は自分自身を指し、再帰のイメージ
- 二つ目の SELECT文は一つ前のレコードを選択し、そこからカレントのレコードを生成
実行結果は以下。
Seq
-----------
1
2
3
4
5
(5 行処理されました)
CTE は INSERT文と組み合わせれば登録処理にも使える。
検証用にダミーレコードを大量に登録する場合等に重宝し、実務でも使えるので覚えておきたい。
【3の倍数の判定】
これは簡単だろう。3 で割って余りが 0 なら 3 の倍数。
剰余を求める演算子は % や mod が多い。SQLServer の場合は前者。
確認する。
1> SELECT 1 % 3, 2 % 3, 3 % 3, 4 % 3;
2> GO
----------- ----------- ----------- -----------
1 2 0 1
(1 行処理されました)
いい余りっぷり。問題なし。
【3が付く数字の判定】
こちらは少々面倒。
例えば 12345 に 3 が含まれるかは、人間の目で見れば一目瞭然。
しかしこれをプログラムで判定しようとすると意外に面倒。
やり方は色々考えられるが、鉄板の一つが 剰余 と 商 で各桁を分解する方法。
① 12345
② ① ÷ 10 の剰余 = 5
③ ① ÷ 10 の商 = 1234
④ ③ ÷ 10 の剰余 = 4
⑤ ③ ÷ 10 の商 = 123
以下商が 0 になるまで繰り返し。
これで各桁の数字が分解できるので、それぞれ目的の数値であるか判定することになる。
単純な数値演算処理なのでコンピュータでの処理は高速であることが期待できる。
しかし理屈は単純だがコードを書くとなると地味に面倒。
な感じ。
別の切り口としては、文字列に変換し、目的の数字が含まれるか検索する方法。
人間が目視で確認する動作に近い。
文字列操作は数値演算処理に比べ重いことが欠点だが、今日日のプロセッサにとっては誤差の範囲だろう。コード量が少なく済みそうなので今回はこの方法を採用する。
【クエリ】
情報は揃ったのでクエリにまとめる。
;WITH [CTE_Base]([Seq]) AS (
SELECT 1
UNION ALL
SELECT [Seq] + 1 FROM [CTE_Base]
WHERE [Seq] < 40
)
SELECT
[Seq]
, IIF([Seq] % 3 = 0 OR CHARINDEX('3', STR([Seq])) > 0, 'アホ', NULL) [Status]
FROM [CTE_Base]
;
GO
数値を文字列に変換する方法は CAST や CONVERT があるが、今回は少ないコードで書ける STR関数を使用。
STR関数はデフォルトでは前スペースが入る等クセがあるので要注意。今回のケースではそれを含めても問題なしと判断した。
文字列の検索は定番の CHARINDEX関数。
条件判定分岐処理は SQLServer2012 から追加された IIF関数。便利だよね、これ。
実行結果は以下。
Seq Status
----------- ------
1 NULL
2 NULL
3 アホ
4 NULL
5 NULL
6 アホ
7 NULL
8 NULL
9 アホ
10 NULL
11 NULL
12 アホ
13 アホ
14 NULL
15 アホ
16 NULL
17 NULL
18 アホ
19 NULL
20 NULL
21 アホ
22 NULL
23 アホ
24 アホ
25 NULL
26 NULL
27 アホ
28 NULL
29 NULL
30 アホ
31 アホ
32 アホ
33 アホ
34 アホ
35 アホ
36 アホ
37 アホ
38 アホ
39 アホ
40 NULL
(40 行処理されました)
【答え合わせ】
上記結果の検証。簡単な問題なので目視でも十分だが、PowerShell でも書いて結果を突き合わせてみる。
PS C:\Users\user> 1..40 | %{"$_ $(if($_ % 3 -eq 0 -or ([String]$_).Indexof("3") -ne -1) {"アホ"})"}
1
2
3 アホ
4
5
6 アホ
7
8
9 アホ
10
11
12 アホ
13 アホ
14
15 アホ
16
17
18 アホ
19
20
21 アホ
22
23 アホ
24 アホ
25
26
27 アホ
28
29
30 アホ
31 アホ
32 アホ
33 アホ
34 アホ
35 アホ
36 アホ
37 アホ
38 アホ
39 アホ
40
どうやら結果は同じ。当たり前か。てか PowerShell で書くと簡単だよなぁ。
【余談】
CTE について。再帰と聞くと、スタックを喰い潰すケースを心配する人がいるかもしれない。
しかしそれはあり得ない。
何故なら、CTE構文は必ず 末尾再帰 になるから。
末尾再帰は単純な反復処理に機械的に置き換えることができるので、通常の処理系ならその最適化が行われることが期待できる。
SQLServer ではないが、PostgreSQLの公式にも以下の説明がある。
7.8. WITH問い合わせ（共通テーブル式）
厳密には、この手順は反復(iteration)であって再帰(recursion)ではありませんが、～
SQLServer の公式からこれに相当する解説は見つけられなかったが、それぐらいの最適化はやってるよね？ 普通。
それと SQLServer の CTE は無限ループを防ぐ為、デフォルトの上限ループ回数が100 に設けられている。
それを超えて回したい場合は OPTION (MAXRECURSION n) を指定すること。
WITH common_table_expression (Transact-SQL)
無限ループを防ぐには、MAXRECURSION ヒントを使用したり、INSERT、UPDATE、DELETE、または SELECT ステートメントの OPTION 句に 0 から 32,767 までの値を指定したりすることにより、特定のステートメントに許可される再帰レベルの数を制限します。