Last active
May 8, 2021 03:05
-
-
Save nimzo6689/b87e0278908ff1115a75622a0c0d5d41 to your computer and use it in GitHub Desktop.
FizzBuzz without CASE clause
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE | |
-- どの倍数でどのワードに置き換えるかを定義 | |
fizzbuzz(multiple, word) AS ( | |
SELECT 3, 'fizz' UNION ALL SELECT 5, 'buzz' | |
), | |
-- 3 or 5 の倍数であれば fizz と buzz にそれぞれ置き換える。 | |
results(seq_no, might_be_replaced, multiple_opt) AS ( | |
SELECT 1, CAST(1 AS CHAR(256)), 0 | |
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(might_be_replaced ORDER BY multiple_opt ASC SEPARATOR '') AS answer | |
FROM results | |
GROUP BY seq_no | |
ORDER BY seq_no | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
-- どの倍数でどのワードに置き換えるかを定義 | |
fizzbuzz(multiple, word) AS ( | |
SELECT 3, 'fizz' FROM dual UNION ALL | |
SELECT 5, 'buzz' FROM dual | |
), | |
-- 3 or 5 の倍数であれば fizz と buzz にそれぞれ置き換える。(重複行が発生する) | |
duplicated_results(seq_no, might_be_replaced, multiple_opt) AS ( | |
SELECT 1, CAST(1 AS VARCHAR(256)), NULL FROM dual | |
UNION ALL | |
SELECT seq_no + 1, COALESCE(word, CAST((seq_no + 1) AS VARCHAR(256))), multiple | |
FROM duplicated_results | |
LEFT JOIN fizzbuzz | |
ON MOD((seq_no + 1), multiple) = 0 | |
WHERE seq_no < 100 | |
), | |
-- 重複行を除去 | |
results(seq_no, might_be_replaced, multiple_opt) AS ( | |
SELECT DISTINCT seq_no, might_be_replaced, multiple_opt | |
FROM duplicated_results | |
) | |
-- seq_no が 15 の倍数であれば、fizz, buzz の 2 行になっているため、 | |
-- fizz → buzz の順に結合する。 | |
SELECT LISTAGG(might_be_replaced, '') WITHIN GROUP (ORDER BY multiple_opt ASC) AS answer | |
FROM results | |
GROUP BY seq_no | |
ORDER BY seq_no | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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::varchar, 0) | |
UNION | |
SELECT seq_no + 1, COALESCE(word, (seq_no + 1)::varchar), 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 array_to_string(array_agg(might_be_replaced ORDER BY multiple_opt ASC), '') AS answer | |
FROM results | |
GROUP BY seq_no | |
ORDER BY seq_no | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment