SQLite3 で FizzBuzz（CASE句は未使用）

8615 ワード
SQLite3 FizzBuzz SQL FizzBuzz テキストリンク

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 で行数が指数関数的に増えることになります。1

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 をコーディング場所にするのもありですね。

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