【回答編】SQL FizzBuzz

IT/テクニカル系

「FizzBuzz問題を考えてみた(SQLスキル判定用)」改め「SQL FizzBuzz」

やはり、プログラミングパラダイムが他と異なるので。。


FizzBuzz問題を考えてみた(SQLスキル判定用)
http://d.hatena.ne.jp/qaz76/20120325/1332674095

の準備していた回答をば。

※コードは、最近なぜか身近なSQL ServerのSQLで。


コメントやTwitterでレスポンス下さった方々、ありがとうございました！

SQL FizzBuzz問題

命題はこう。。

    • 1から100まで表示すること
    • その数が3で割り切れるなら"Fizz"を表示すること
    • その数が5で割り切れるなら"Buzz"を表示すること
    • その数が両方で割り切れるなら"FizzBuzz"を表示すること
    • SQLクエリで完結すること
    • テーブルを使用しないことDDLを使用しないこと
    • CASE式を使用しないこと
    • 結合を効果的に使うこと

マイナス50点の例解からｗ

WITH
CTE1
AS
(
SELECT 1 SEQ
UNION ALL
SELECT SEQ + 1 SEQ FROM CTE1 WHERE SEQ < 100
),
CTE2
AS
(
SELECT
	SEQ % 3 Fizz,
	SEQ % 5 Buzz,
	SEQ
FROM
	CTE1
),
CTE3
AS
(
SELECT
	CASE
		WHEN Buzz = 0 AND Fizz = 0 THEN 'FizzBuzz'
		WHEN Fizz = 0 THEN 'Fizz'
		WHEN Buzz = 0 THEN 'Buzz'
		ELSE CAST(SEQ AS VARCHAR)
	END ANS
FROM
	CTE2
)
SELECT * FROM CTE3
;

これは、SQL99の共通表式による再帰クエリがポイントです。(CTE1のとこ)

CASE式のところは、FizzBuzzのよくある回答例まんまですねｗ


ちなみに、

SELECT 1 SEQ

は、MySQLやSQL Serverで書けます。


Oracleだと、

SELECT 1 SEQ FROM DUAL

DB2だと、

SELECT 1 SEQ FROM SYSIBM. SYSDUMMY1

でしたかね？

それ以外は、だいたい同じ感じだったはず。。


もうちょっと短くしておきましょう。

WITH
CTE1
AS
(
SELECT 1 SEQ, 1 Fizz, 1 Buzz
UNION ALL
SELECT
	SEQ + 1,
	(SEQ + 1) % 3,
	(SEQ + 1) % 5
FROM
	CTE1
WHERE
	SEQ < 100
)
SELECT
	CASE
		WHEN Buzz = 0 AND Fizz = 0 THEN 'FizzBuzz'
		WHEN Fizz = 0 THEN 'Fizz'
		WHEN Buzz = 0 THEN 'Buzz'
		ELSE CAST(SEQ AS VARCHAR)
	END ANS
FROM
	CTE1
;

ここまでで、以下の条件を満たしています。

    • SQLクエリで完結すること
    • テーブルを使用しないことDDLを使用しないこと

例解

命題に対しては、さらに以下の条件を満たす必要があります。

    • CASE式を使用しないこと
    • 結合を効果的に使うこと

準備してたのはこんなSQLです。

WITH
CTE1
AS
(
SELECT 1 SEQ
UNION ALL
SELECT SEQ + 1 SEQ FROM CTE1 WHERE SEQ < 100
)
SELECT
	ISNULL(T2.C + T3.C, ISNULL(T2.C, ISNULL(T3.C, SEQ))) ANS
FROM
	CTE1 T1
LEFT OUTER JOIN
	(SELECT 3 N, 'Fizz' C) T2
ON
	T1.SEQ % T2.N = 0
LEFT OUTER JOIN
	(SELECT 5 N, 'Buzz' C) T3
ON
	T1.SEQ % T3.N = 0
;

出題の意図

出題の意図としては、こんなものがありました。

  • 共通表式(Common Table Expression)の再帰クエリを知ってるかーい？
    • テーブルを使用しないことDDLを使用しないことの解の一つです。
    • One Fuct In One Placeの世界では、「あるタプルが存在しなかった事」を表現できません。任意の行数を生成できることを知っていれば、LOOPが不要になるという事です。例えば、GEN_ROW(int ROWS)的なTable Functionを準備しておくと便利だったりします。
  • 単一行表現って知ってるかーい？
    • テーブルを使用しないことDDLを使用しないことの解の一つです。
    • "SELECT 1 SEQ FROM DUAL"(Oracle)
    • "SELECT 1 SEQ FROM SYSIBM.SYSDUMMY1"(DB2)
  • 公倍数を集合で表現してみないかーい？
    • 結合を効果的に使うことの解の一つです。
    • 「3の倍数」と「5の倍数」を結合2つで表現できますね。
  • NULLの扱いを知ってるかーい？
    • CASE式を使用しないことの解の一つです。
    • 「A + B」「A || B」は他方がNULLだとNULLになりますよね。
  • CASE式の代わりのスカラーファンクションを知ってるかーい？
    • CASE式を使用しないことの解の一つです。
    • COALESCE(DB2)

Oracleの場合

id:masa711115さんから回答頂きました。

WITH
ROWNO
AS
(
SELECT
	ROWNUM NO,
	MOD(ROWNUM, 3) MOD3,
	MOD(ROWNUM, 5) MOD5
FROM
	DUAL
CONNECT BY
	ROWNUM < 101
)
SELECT
	A.FIZZBUZZ || B.FIZZBUZZ || C.FIZZBUZZ FIZZBUZZ
FROM
	ROWNO
LEFT  JOIN
	(SELECT NO FIZZBUZZ, NO FROM ROWNO WHERE MOD3 <> 0 AND MOD5 <> 0)  A
ON
	NO = ROWNO.NO
LEFT  JOIN
	(SELECT ’FIZZ’ FIZZBUZZ, NO FROM ROWNO WHERE MOD3 = 0) B
ON
	NO = ROWNO.NO
LEFT  JOIN
	(SELECT ’BUZZ’ FIZZBUZZ, NO FROM ROWNO WHERE MOD5 = 0) C
ON
	NO = ROWNO.NO
ORDER BY
	ROWNO.NO
;

再帰クエリの代わりに、Oracleの"CONNECT BY"を使っている事がポイントですね。

まさか、ここまで出題の意図を理解してくださると思わなかったですｗｗｗ

ざした！！


最初は、「SQLスキル判定用」なんて程大袈裟なもんじゃないよね？って思ってましたが、案外使えるかも？


Work! Enjoy it!