標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の25日目です。
５年前にクリスマスのお遊び - SQL de Fractals :)というネタを書いてました。
EXPLAIN EXTENDED - Happy New Year!
元ネタは、ARRAY_TO_STRINGとARRAY_AGGの組み合わせ、とgenerate_seriesを利用した再帰問合せを利用したPostgreSQLバージョンのSQL
今であれば、以下のように書き換え、STRING_AGGでけにした方が良いのではないだろうか。
また、generate_series部分の方言の影響を最小にするのであれば、この部分も再帰問合せを利用した一連番号のセット生成にするなどの変更は可能ですね。
では、オリジナルのPostgreSQLの構文でARRAY_TO_STRINGとARRAY_AGGをSTRING_AGGに変更した例から（generate_seriesを階層再帰問合せにすることも可能）
PostgreSQL
WITH RECURSIVE
q (r, i, rx, ix, g) AS
(
SELECT
r::DOUBLE PRECISION * 0.02
, i::DOUBLE PRECISION * 0.02
, .0::DOUBLE PRECISION
, .0::DOUBLE PRECISION
, 0
FROM
generate_series(-60, 20) r
, generate_series(-50, 50) i
UNION ALL
SELECT
r
, i
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN rx * rx - ix * ix
END + r
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN 2 * rx * ix
END + i
, g + 1
FROM
q
WHERE
rx IS NOT NULL AND g < 99
)
SELECT
STRING_AGG(s, '' ORDER BY r) AS Mandelbrot
FROM
(
SELECT
i, r, SUBSTRING(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
FROM
q
GROUP BY i, r
) q
GROUP BY i
ORDER BY i;
Oracle
では、上記、非互換の多いSQLをOracle向けに書き換えてみましょう。一連番号生成はOracleの方言である階層問合せにしてあります。あえてw
また、PostgreSQLのSTRING_AGG部分は、OracleのLISTAGGで代替しています。
WITH
q (r, i, rx, ix, g) AS
(
SELECT
CAST(r.r AS DOUBLE PRECISION) * 0.02 AS r
, CAST(i.i AS DOUBLE PRECISION) * 0.02 AS i
, CAST(.0 AS DOUBLE PRECISION) AS rx
, CAST(.0 AS DOUBLE PRECISION) AS ix
, 0 AS g
FROM
(
SELECT
LEVEL - 61 AS r
FROM
DUAL
CONNECT BY
LEVEL <= 80
) r,
(
SELECT
LEVEL - 51 AS i
FROM
DUAL
CONNECT BY
LEVEL <= 100
) i
UNION ALL
SELECT
r
, i
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN
rx * rx - ix * ix
END + r AS rx
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN
2 * rx * ix
END + i AS ix
, g + 1 AS g
FROM
q
WHERE
rx IS NOT NULL
AND g < 99
)
SELECT
LISTAGG(s,'') WITHIN GROUP ( ORDER BY r ) AS Mandelbrot
FROM
(
SELECT
i, r, SUBSTR(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
FROM
q
GROUP BY i, r
) q
GROUP BY i
ORDER BY i;
MySQL 8.0
さて、最後は、これまで一連番号生成が辛かったMySQLです。
MySQL 8.0から再帰問合せが利用できるようになったおかげてMySQLのSQLでもこんな遊びができるようになりました！！！！
すげーーーーーーーっ！
再帰問合せを駆使し、PostgreSQLのSTRING_AGG、OracleのLISTAGGの代替としてGROUP_CONCAT関数を利用しています。
部分文字列はSUBSTRINGですね。
そしてもう一つの非互換対応が TRUNCATE(MAX(g) / 10 + 1, 0) 部分です。
OracleとPostgreSQLは MAX(g) / 10 + 1 だけでも問題ないですが、MySQLでは MAX(g) / 10 + 1 の結果は整数にはなりません。
その対策としてTRUNCATEを追加しています。
なかなか痺れますね。これまで紹介してきた非互換対応の総まとめは大げさですが、それらを有効に組み合わせて書き換えてみました。
WITH RECURSIVE
q (r, i, rx, ix, g)
AS
(
SELECT
CAST(r.v AS DOUBLE PRECISION) * 0.02 AS r
, CAST(i.v AS DOUBLE PRECISION) * 0.02 AS i
, CAST(0.0 AS DOUBLE PRECISION) AS rx
, CAST(0.0 AS DOUBLE PRECISION) AS ix
, 0 AS g
FROM
(
WITH RECURSIVE gen_nums(v)
AS
(
SELECT -60
UNION ALL
SELECT v + 1
FROM
gen_nums
WHERE v + 1 < 20
)
SELECT v from gen_nums
) r
,(
WITH RECURSIVE gen_nums(v)
AS
(
SELECT -50
UNION ALL
SELECT v + 1
FROM
gen_nums
WHERE v + 1 < 50
)
SELECT v from gen_nums
) i
UNION ALL
SELECT
CAST(r AS DOUBLE PRECISION) AS r
, CAST(i AS DOUBLE PRECISION) AS i
, CAST(
CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN rx * rx - ix * ix
END + r
AS DOUBLE PRECISION
) AS rx
, CAST(
CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN 2 * rx * ix
END + i
AS DOUBLE PRECISION
) AS ix
, g + 1 AS g
FROM
q
WHERE
rx IS NOT NULL
AND g < 99
)
SELECT
GROUP_CONCAT(s,'' ORDER BY r SEPARATOR '') AS Mandelbrot
FROM
(
SELECT
i, r, SUBSTRING(' .:-=+*#%@', TRUNCATE(MAX(g) / 10 + 1, 0), 1) s
FROM
q
GROUP BY
i, r
) q
GROUP BY q.i
ORDER BY q.i;
ちなみに、Redshiftは再帰的なCTEは現時点では未サポートなので再帰問合せが必要なお遊びは今のところできません。
Amazon Redshift - サポートされていないPostgreSQL機能
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-features.html
さてさて、なんとか25個の窓を開けることができました。
今年は、コロナ禍の中、大変厳しい一年になりましたが、みなさま、お身体を大事に、そして、ご家族と過ごす時間を大切に。
メリークリスマス。（何年か前に作った Pipeline function で christmas treeのムービーで）
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
最近のコメント