SQLite3 で FizzBuzz（CASE句は未使用）

CASE 句を使わずに FizzBuzz している記事がなかったので、書いてみました。

実際やってみると、結局 CASE 句使った方がキレイにできるのですが、
せっかく考えたので SQL や気づきの部分を残しておきます。

成果物

今回、書いた SQL はこちらです。
（FizzBuzz とは思えない長さ）

WITH RECURSIVE
  -- どの倍数でどのワードに置き換えるかを定義
  fizzbuzz(multiple, word) AS (
    VALUES (3, 'fizz'), (5, 'buzz')
  ),
  -- 3 or 5 の倍数であれば fizz と buzz にそれぞれ置き換える。
  results(seq_no, might_be_replaced, multiple_opt) AS (
    VALUES (1, 1, NULL)
     UNION
    SELECT seq_no + 1, COALESCE(word, seq_no + 1), multiple
      FROM results
      LEFT JOIN fizzbuzz
        ON (seq_no + 1) % multiple = 0
     WHERE seq_no < 100
  )
-- seq_no が 15 の倍数であれば、fizz, buzz の 2 行になっているため、
-- fizz → buzz の順に結合する。
SELECT GROUP_CONCAT(
           GROUP_CONCAT(might_be_replaced, ''), ''
       ) OVER (
           PARTITION BY seq_no
           ORDER BY multiple_opt ASC
       ) AS answer
  FROM results
 GROUP BY seq_no
 ORDER BY seq_no
;

特にテーブル作成も必要ないため、上の SQL は手元の SQLite でそのまま実行できます。
オンラインでの確認は以下のサイトなどが便利です。

この記事では SQLite 中心に記載していますが、
Oracle, MariaDB, PostgreSQL も同じように書けましたので、 gist に載せています。
（SQL Server はダメでした。理由は後述。。）

気づき

1点目： 再帰 WITH 句内に集計関数が使えない

当初以下のように書いてしまえば楽かと思ってましたが、これがそもそも無理だったようです。

実行不可

...
  -- 3 or 5 の倍数であれば fizz と buzz にそれぞれ置き換える。
  results(seq_no, might_be_replaced, multiple_opt) AS (
    VALUES (1, 1, NULL)
     UNION
    SELECT seq_no + 1, GROUP_CONCAT(COALESCE(word, seq_no + 1), ''), multiple
      FROM results
      LEFT JOIN fizzbuzz
        ON (seq_no + 1) % multiple = 0
     WHERE seq_no < 100
  )
...

上記の書き方ですと、以下のエラーが出ます。
[SQLITE_ERROR] SQL error or missing database (recursive aggregate queries not supported)

どうやら SQLite 固有の制約というより、そもそも再帰 WITH 句内では集計関数が使えないのが一般的なようです。

また、DISTINCT や GROUP BY での重複行削除も再帰 WITH 句内では機能しないですが、
UNION 句に ALL を指定しなければそこは問題なかったです。
ただ、 Oracle の場合ですと再帰 WITH 句内では UNION ALL しかサポートされていないため、
15 の倍数に達するごとに x2 で行数が指数関数的に増えることになります。¹

SQL Server ではそもそも、再帰 WITH 句内で外部結合ができないようになっていました。
こういうケースでレコードが想定以上に多くなることを防止するための制約なんですかね。

（商用 DB の方がこの辺の柔軟性が効かないのはなぜ。）

2点目： ウィンドウ関数と GROUP BY の併用は可能

最後の箇所で集計関数を入れ子に使っているところ。

SELECT GROUP_CONCAT(
           GROUP_CONCAT(might_be_replaced, ''), ''
       ) OVER (
           PARTITION BY seq_no
           ORDER BY multiple_opt ASC
       ) AS answer
...

単純に GROUP_CONCAT を 1 回だけ使っただけでは、なぜか上手く機能しないそうです。
調べてみると、GROUP BY 用の集計関数とウィンドウ関数用の集計関数を
入れ子にする形で指定することで併用できるとのこと。

あまり腹落ちした感が持てないですが、そういうものなんですね。

3点目： SQLite だと論理的なミスが気づきづらい

SQLite では細かいところのエラーが出ずに実行できてしまうので、
論理的にミスしている場合、どこが原因なのか特定するのに時間がかかりました。

SQLite で新たにサポートされる構文は PostgreSQL の構文を参考に加えられているケースをよく目にするので、
（おそらく機会は稀ですが）こういう複雑なクエリ書く場合は、 PostgreSQL をコーディング場所にするのもありですね。

番外編： CASE句あり版

せっかくなので、CASE句使用版も載せておきます。

WITH RECURSIVE numbers(number) AS (
    SELECT 1
    UNION ALL
    SELECT number + 1 FROM numbers WHERE number < 100
)
SELECT 
    CASE
        WHEN number % 3 = 0 AND number % 5 = 0 THEN 'FizzBuzz'
        WHEN number % 3 = 0 THEN 'Fizz'
        WHEN number % 5 = 0 THEN 'Buzz'
        ELSE CAST(number AS TEXT)
    END AS result
FROM numbers;

1. 生成される行数 - ユニークな行数 で一般化すると、 $\sum_{k=1}^{n} 2^{\lfloor{k/15}\rfloor} - (n + \lfloor{n/15}\rfloor)$ で無駄な行数が増えます。 ↩