【目的】

ゆるい題材からクエリ、アルゴリズムを学ぶ。

【環境】

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 までの値を指定したりすることにより、特定のステートメントに許可される再帰レベルの数を制限します。