前の記事 Python3 で FizzBuzz (sqllite3利用) の SQL 、CASE文が美しくないというわけで新版作成。

Python3.13.1 利用。sqlite3.sqlite_version は 3.45.3 なので Window関数 row_number() を利用。

FizzBuzz03.py

import sqlite3

SQL = '''
WITH NB(n) AS (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(0))
SELECT
    GROUP_CONCAT(
        COALESCE(TT.column2 || TF.column2,TT.column2, TF.column2, CAST(n AS TEXT))
        , CHAR(13, 10))
    FROM
        (
	        SELECT
	            ROW_NUMBER() OVER (
	                ORDER BY
	                    NB1.n
	                    ,NB2.n
	            ) n
	        FROM
	            NB AS NB1
	            , NB AS NB2
	    ) N
        LEFT OUTER JOIN (
            VALUES(3,'FIZZ')
        ) TT
        ON N.n % TT.column1 = 0
        LEFT OUTER JOIN (
            VALUES(5,'BUZZ')
        ) TF
        ON N.n % TF.column1 = 0
;
'''

def main():
    with sqlite3.connect(':memory:') as conn:
        print(conn.execute(SQL).fetchall()[0][0])

if __name__ == '__main__':
    main()