Oracle Databaseに i がついた頃にLive SQLと同じようなサービスがあった気がするが....名称を思い出せない。。。w
Live SQL現時点では19c 19.8.0.0.0のようですが、現時点で利用可能な旧バージョンも含めて選択できたりすると嬉しかったり。
次は21cらしいので、例えば、19cでサポートされていない構文と21cからサポートされる構文の違いの確認が簡単に確認できるとか色々敷居が下がって良いのかもしれないですね。
今回は標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)のOracle向けSQLを投げてみた。
来年のネタを検討中.....
家事諸々終わってfreeeeeeee time
良いお年をお迎えください
恒例の標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020のおまけですw
忙しすぎて、この手の遊びが疎かになっておりました。完全に乗り遅れておりましたが、湯婆婆やりましたw
スタジオジブリさん、「画像は常識の範囲でご自由にお使いください。」とのことでありがたく使わせていただきます！
https://www.ghibli.jp/works/chihiro/#frame
では、Oracle (19c)を使って、Oracle 湯婆婆 から
Advent Calendar 標準はあるにはあるが癖の多いSQL 全部俺でも登場したSUBSTR/DBMS_RANDOM.VALUEや文字列連結の違い。
それに、SQLスクリプトでインタラクティブにパラメータを渡せるかという点にも違いがあります。
SQL*Plus/psqlではインタラクティブにパラメータを渡せますが、mysqlにはなさそう（あったらコメントください）
インタラクティブにパラメータを渡せるSQL*Plus/psqlでは、それぞれ、ACCEPTや\promptなどで名前を入力しています。
mysqlでは仕方ないのでSETコマンドで設定する方法をとりました。
ORACLE> @ora_yubaba
契約書だよ。そこに名前を書きな。山田千尋
湯婆婆
--------------------------------------------------------------------------------
フン 山田千尋 というのかい。贅沢な名だねぇ
今からお前の名前は 田 だ。いいかい、田 だよ。
わかったら返事をするんだ、
!!
$ cat ora_yubaba.sql
SET LINESIZE 80
SET TAB OFF
SET VERIFY OFF
ACCEPT fullname CHAR PROMPT '契約書だよ。そこに名前を書きな。'
WITH yourname
AS
(
SELECT
SUBSTR(
'&&fullname'
, DBMS_RANDOM.VALUE(1,LENGTH('&&fullname')), 1
) AS newname
FROM
dual
)
SELECT
'フン '
||'&&fullname'
||' というのかい。贅沢な名だねぇ'
||CHR(13)||CHR(10)
||'今からお前の名前は '
||newname
||' だ。いいかい、'
||newname
||' だよ。'
||CHR(13)||CHR(10)||'わかったら返事をするんだ、'
||CHR(13)||CHR(10)||'!!' AS "湯婆婆"
FROM
yourname;
undefine fullname
SET VERIFY ON
PostgreSQL (12)
次は、PostgreSQL 湯婆婆
postgres=> \i postgresql_yubaba.sql
契約書だよ。そこに名前を書きな : 山田千尋
湯婆婆
----------------------------------------------
フン 山田千尋 というのかい。贅沢な名だねぇ +
今からお前の名は 田 だ。いいかい、田 だよ。+
わかったら返事をするんだ、 +
!!
(1 row)
$ cat postgresql_yubaba.sql
\prompt '契約書だよ。そこに名前を書きな : ' fullname
WITH yourname
AS
(
SELECT
SUBSTR(
:'fullname'::TEXT
, CEIL(RANDOM() * LENGTH(:'fullname'::TEXT))::INTEGER, 1::INTEGER
) AS newname
)
SELECT
'フン '
||:'fullname'
||' というのかい。贅沢な名だねぇ'
||E'\n'
||'今からお前の名は '
||newname
||' だ。いいかい、'
||newname
||' だよ。'
||E'\n'||'わかったら返事をするんだ、'
||E'\n'||'!!' AS "湯婆婆"
FROM
yourname;
MySQL 8.0
最後に、MySQL 湯婆婆
mysql> SET @契約書だよ。そこに名前を書きな = '山田千尋';
Query OK, 0 rows affected (0.13 sec)
mysql> \! vi mysql_yubaba.sql
mysql> \. mysql_yubaba.sql
Query OK, 0 rows affected (0.02 sec)
*************************** 1. row ***************************
湯婆婆: フン 山田千尋 というのかい。贅沢な名だねぇ
今からお前の名は 尋 だ。いいかい、尋 だよ。
わかったら返事をするんだ、
!!
1 row in set (0.02 sec)
$ cat mysql_yubaba.sql
SET sql_mode = 'ANSI';
WITH yourname
AS
(
SELECT
SUBSTRING(
@契約書だよ。そこに名前を書きな
, CEIL(RAND() * CHAR_LENGTH(@契約書だよ。そこに名前を書きな)), 1
) AS newname
)
SELECT
'フン '
||@契約書だよ。そこに名前を書きな
||' というのかい。贅沢な名だねぇ'
||'\r\n'
||'今からお前の名は '
||newname
||' だ。いいかい、'
||newname
||' だよ。'
||'\r\n'||'わかったら返事をするんだ、'
||'\r\n'||'!!' AS "湯婆婆"
FROM
yourname\G
こういう遊びはみなさん好きですよね？
では、また。
標準はあるにはあるが癖の多い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;
Amazon Redshift - サポートされていないPostgreSQL機能
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-features.html
今年は、コロナ禍の中、大変厳しい一年になりましたが、みなさま、お身体を大事に、そして、ご家族と過ごす時間を大切に。
メリークリスマス。（何年か前に作った 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 乱数作るにも癖がある
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の24日目です。
たまに、乱数が必要になることがあるのですが、その乱数生成にも癖があるんですよね。
簡単ですが今日はこれぐらいでw （ラストスパートで息切れ中w
いつものようにOracleから
131.4 DBMS_RANDOMサブプログラムの要約
131.4.7 VALUEファンクション
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_RANDOM.html#GUID-AAD9E936-D74F-440D-9E16-24F3F0DE8D31
Oracleの場合は組み込み関数ではなく、パッケージ関数として提供されています。この点が大きな違いですよね。
ORACLE> SELECT (DBMS_RANDOM.VALUE(1,10)) FROM dual;
(DBMS_RANDOM.VALUE(1,10))
-------------------------
3.37937063
ORACLE> SELECT DBMS_RANDOM.VALUE FROM dual;
VALUE
----------
.853380477
PostgreSQL
表9.6 乱数関数
https://www.postgresql.jp/document/12/html/functions-math.html
postgres=> select random();
random
-------------------
0.774311667308211
(1 row)
MySQL
三者三様で皆関数名も違ったり、提供されている機能も差がありますね。
RAND([N])
https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.6516789492700984 |
+--------------------+
1 row in set (0.06 sec)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の23日目です。
クリスマスイブイブですね。なんとかここまできたw
ネタ尽きた感がなくもないですが、ほぼ使っている方はいないのではないかという、複数行INSERTにも癖があるというお話
では、Oracleからみてください
Oracle
INESRT
https://docs.oracle.com/cd/F19136_01/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423
この構文実は、マルチテーブルインサートの変形パターンで同一表へ複数インサートするようにしたもの。。。
そもそもこの構文が方言ではあるのですが、。。。。
ORACLE> desc a
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
STR VARCHAR2(100)
ORACLE> INSERT ALL
2 INTO a VALUES(2,'yama')
3 INTO a VALUES(3,'kawa')
4 INTO a VALUES(4,'umi')
5 SELECT * FROM dual;
PostgreSQL
PostgreSQLにはやはりない。そりゃ、Oracleの方言だからねw ですが、一応同一表なら可能です。
INSERT
https://www.postgresql.jp/document/12/html/sql-insert.html
同じく、MySQLも同一構文。。。。
postgres=> \d a
Table "bill.a"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | |
str | character varying(100) | | |
postgres=>
postgres=> INSERT
postgres-> INTO a VALUES
postgres-> (2,'yama')
postgres-> ,(3,'kawa')
postgres-> ,(4,'umi');
INSERT 0 3
MySQL(8.0)
INSERT
https://dev.mysql.com/doc/refman/8.0/en/insert.html
mysql> desc a;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| str | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> INSERT
-> INTO a VALUES
-> (2,'yama')
-> ,(3,'kawa')
-> ,(4,'umi');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の22日目です。
さて、残すところ、今日をいれてあと4回
なんとなくネタが１つ足りてない気がするがw なんとかするw
ということで、集合演算の癖を見てみましょう。Oracle 20cがリリースされていたら試せた機能もありますが、使える環境がないので、マニュアルだけ貼っておきます。
Oracle 20c - SQL set演算子の拡張 - union [all] / minus [all] / intersect [all] / except[all]
https://docs.oracle.com/cd/F32587_01/ftnew/enhanced-sql-set-operators1.html
19cまではこんな感じ
Oracle 19c - UNION [ALL]、INTERSECTおよびMINUS演算子
https://docs.oracle.com/cd/F19136_01/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE
MINUSはOracleの方言です。そのうちシノニム扱いされて、EXCEPTが一般的に利用されることになっていくのでしょうね。
Oracle
ORACLE> r
1 SELECT *
2 FROM
3 (
4 WITH gen_nums(v)
5 AS
6 (
7 SELECT 1
8 FROM
9 dual
10 UNION ALL
11 SELECT v + 1
12 FROM
13 gen_nums
14 WHERE v + 1 <= 10
15 )
16 SELECT v FROM gen_nums
17 )
18 UNION
19 SELECT *
20 FROM
21 (
22 WITH gen_nums(v)
23 AS
24 (
25 SELECT 5
26 FROM
27 dual
28 UNION ALL
29 SELECT v + 1
30 FROM
31 gen_nums
32 WHERE v + 1 <= 15
33 )
34 SELECT v FROM gen_nums
35 )
36* ORDER BY 1
V
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15 rows selected.
ORACLE> r
1 SELECT *
2 FROM
3 (
4 WITH gen_nums(v)
5 AS
6 (
7 SELECT 1
8 FROM
9 dual
10 UNION ALL
11 SELECT v + 1
12 FROM
13 gen_nums
14 WHERE v + 1 <= 10
15 )
16 SELECT v FROM gen_nums
17 )
18 UNION ALL
19 SELECT *
20 FROM
21 (
22 WITH gen_nums(v)
23 AS
24 (
25 SELECT 5
26 FROM
27 dual
28 UNION ALL
29 ELECT v + 1
30 FROM
31 gen_nums
32 WHERE v + 1 <= 15
33 )
34 SELECT v FROM gen_nums
35 )
36* ORDER BY 1
V
----------
1
2
3
4
5
5
6
6
7
7
8
8
9
9
10
10
11
12
13
14
15
21 rows selected.
ORACLE> r
1 SELECT *
2 FROM
3 (
4 WITH gen_nums(v)
5 AS
6 (
7 SELECT 1
8 FROM
9 dual
10 UNION ALL
11 SELECT v + 1
12 FROM
13 gen_nums
14 WHERE v + 1 <= 10
15 )
16 SELECT v FROM gen_nums
17 )
18 INTERSECT
19 SELECT *
20 FROM
21 (
22 WITH gen_nums(v)
23 AS
24 (
25 SELECT 5
26 FROM
27 dual
28 UNION ALL
29 SELECT v + 1
30 FROM
31 gen_nums
32 WHERE v + 1 <= 15
33 )
34 SELECT v FROM gen_nums
35 )
36* ORDER BY 1
V
----------
5
6
7
8
9
10
6 rows selected.
ORACLE> r
1 SELECT *
2 FROM
3 (
4 WITH gen_nums(v)
5 AS
6 (
7 SELECT 1
8 FROM
9 dual
10 UNION ALL
11 SELECT v + 1
12 FROM
13 gen_nums
14 WHERE v + 1 <= 10
15 )
16 SELECT v FROM gen_nums
17 )
18 MINUS
19 SELECT *
20 FROM
21 (
22 WITH gen_nums(v)
23 AS
24 (
25 SELECT 5
26 FROM
27 dual
28 UNION ALL
29 SELECT v + 1
30 FROM
31 gen_nums
32 WHERE v + 1 <= 15
33 )
34 SELECT v FROM gen_nums
35 )
36* ORDER BY 1
V
----------
1
2
3
4
PostgreSQL
union [all] / intersect[all] / except[all]
https://www.postgresql.jp/document/12/html/queries-union.html
これらの構文に関しては、PostgreSQLの方が一歩先でしたね：）
Oracle 20c以降ではこんなことができるようになるよ、というイメージをPostgreSQLで掴んでおくと良いかもしれないですねー
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> UNION
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(15 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> UNION ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
----
1
2
3
4
5
5
6
6
7
7
8
8
9
9
10
10
11
12
13
14
15
(21 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> INTERSECT
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
----
5
6
7
8
9
10
(6 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> INTERSECT ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
----
5
6
7
8
9
10
(6 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> EXCEPT
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
---
1
2
3
4
(4 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 1
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 10
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) a
postgres-> EXCEPT ALL
postgres-> SELECT *
postgres-> FROM
postgres-> (
postgres(> WITH RECURSIVE gen_nums(v)
postgres(> AS
postgres(> (
postgres(> SELECT 5
postgres(> UNION ALL
postgres(> SELECT v + 1
postgres(> FROM
postgres(> gen_nums
postgres(> WHERE v + 1 <= 15
postgres(> )
postgres(> SELECT v FROM gen_nums
postgres(> ) b
postgres-> ORDER BY 1;
v
---
1
2
3
4
(4 rows)
MySQL (8.0)
union [all]
13.2.10.3 UNION Clause
https://dev.mysql.com/doc/refman/8.0/en/union.html
MySQLはこれらの部分では少々遅れてますね。実際同じ結果を得ようとすると工夫しないといけないです。
これまで実装されてこなかったということは、恐そのような用途で利用されることが少なかったということなのでしょうか。（想像でしかないけど。。なんとなく今後は実装されそうな方向に向かっているような雰囲気も感じつつ。）
mysql> SELECT *
-> FROM
-> (
-> WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 10
-> )
-> SELECT v FROM gen_nums
-> ) a
-> UNION
-> SELECT *
-> FROM
-> (
-> WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 5
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 15
-> )
-> SELECT v FROM gen_nums
-> ) b
-> ORDER BY 1;
+------+
| v |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+------+
15 rows in set (0.02 sec)
mysql> SELECT *
-> FROM
-> (
-> WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 10
-> )
-> SELECT v FROM gen_nums
-> ) a
-> UNION ALL
-> SELECT *
-> FROM
-> (
-> WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 5
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 15
-> )
-> SELECT v FROM gen_nums
-> ) b
-> ORDER BY 1;
+------+
| v |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 5 |
| 6 |
| 6 |
| 7 |
| 7 |
| 8 |
| 8 |
| 9 |
| 9 |
| 10 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+------+
21 rows in set (0.02 sec)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の21日目です。
以前取り上げた内容にも近いですが、演算だけに着目した方がわかりやすいだろうということでネタにしてみました。
SQLを移植する場合は、このようなところでも注意しないと。。。ね。デフォルトの挙動に任せたままでは危険ですね
Oracle
ORACLE> SELECT 1 / 3 FROM dual;
1/3
----------
.333333333
ORACLE> SELECT 1 / 3 * 3 FROM dual;
1/3*3
----------
1
ORACLE> SELECT (1 / 3) * 3 FROM dual;
(1/3)*3
----------
1
ORACLE> SELECT CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) * CAST(3 AS DOUBLE PRECISION) FROM dual;
CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION)*CAST(3ASDOUBLEPRECISION)
--------------------------------------------------------------------------
1
ORACLE> SELECT DUMP(CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION)) FROM dual;
DUMP(CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION))
--------------------------------------------------------------------------------
Typ=2 Len=21: 192,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34
ORACLE> SELECT DUMP(CAST(1 AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) * CAST(3 AS DOUBLE PRECISION)) FROM dual;
DUMP(CAST(1ASDOUBLEPRECISION)/CAST(3ASDOUBLEPRECISION)*CAST(3ASDOUBLEPRECISION))
--------------------------------------------------------------------------------
Typ=2 Len=21: 192,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,10
0,100,100,100,100
MySQL
Oracleに近いですが、若干差異がありますね。
mysql> SELECT 1 / 3;
+--------+
| 1 / 3 |
+--------+
| 0.3333 |
+--------+
1 row in set (0.02 sec)
mysql> SELECT 1 / 3 * 3;
+-----------+
| 1 / 3 * 3 |
+-----------+
| 1.0000 |
+-----------+
1 row in set (0.02 sec)
mysql> SELECT CAST(1 AS DOUBLE PRECISION) / 3 * 3;
+---------------------------+
| cast(1 as double) / 3 * 3 |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.26 sec)
mysql> SELECT CAST(1 AS DOUBLE) / CAST(3 AS DOUBLE) * 3;
+-------------------------------------------+
| cast(1 as double) / cast(3 as double) * 3 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT CAST(1 AS DOUBLE) / CAST(3 AS DOUBLE) * CAST(3 AS DOUBLE);
+-----------------------------------------------------------+
| cast(1 as double) / cast(3 as double) * cast(3 as double) |
+-----------------------------------------------------------+
| 1 |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
PostgreSQL
PostgreSQLはデフォルトの挙動が明らかに異なります。
postgres=> SELECT 1 / 3;
?column?
----------
0
(1 row)
postgres=> SELECT 1 / 3 * 3;
?column?
----------
0
(1 row)
postgres=> SELECT 1::DOUBLE PRECISION / 3::DOUBLE PRECISION * 3::DOUBLE PRECISION;
?column?
----------
1
(1 row)
postgres=> SELECT PG_TYPEOF(1::DOUBLE PRECISION / 3::DOUBLE PRECISION * 3::DOUBLE PRECISION);
pg_typeof
------------------
double precision
(1 row)
postgres=> SELECT PG_TYPEOF(1 / 3 * 3);
pg_typeof
-----------
integer
(1 row)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の20日目です。
また、本記事はJPOUG Advent Calendar 2020の20日目の窓へクロスポストしています。
JPOUG Advent Calendar 2020の19日目はNaotaka ShinogiさんのNutanix Eraで描くDatabase管理とOracleSEのデータ同期でした。
さて、今日は、最終日のネタ作りの途中でどうしても、非互換なところと格闘しないといけないので、その部分の対応をかねてw
今回は、結果セット（複数行）を単一列に連結する集約関数 Oracleでは LISTAGG()という関数の非互換です。
同様の関数はあるものの関数名は違うし多少引数も違うのでぱっと見、どう書き換えるかってなると迷うわけです。知ってれば別ですけども。
では、早速みてみましょう。
Oracle
LISTAGG
LISTAGG( [ ALL ] [ DISTINCT ] measure_expr [, 'delimiter'] [listagg_overflow_clause] ) [ WITHIN GROUP ] (order_by_clause) [OVER query_partition_clause]
https://docs.oracle.com/cd/F19136_01/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466
使ったことがある方なら、ああ、アレかと思い出せると思います。あまり使う機会はないので私もマニュアル見て思出すことが多いですね。この関数w
ORACLE>
*1 SELECT * FROM list;
ID STR
---------- -------------
1 foo
1 bar
1 tiger
1 scott
2 bill
2 steve
ORACLE> r
1 SELECT
2 id
3 , LISTAGG(str, ',')
4 WITHIN GROUP
5 (
6 ORDER BY str
7 )
8 AS lists
9 FROM
10 list
11 GROUP BY id
12* ORDER BY id
ID LISTS
---------- ----------------------------------------
1 bar,foo,scott,tiger
2 bill,steve
次は、MySQL(8.0)
MySQLでは GROUP_CONCAT()関数が該当します。使い方は似ていますが、セパレータの指定方法に特徴がありますね。
GROUP_CONCAT(expr)
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
mysql> SELECT
-> id
-> , GROUP_CONCAT(
-> str
-> ORDER BY str
-> SEPARATOR ','
-> )
-> AS lists
-> FROM
-> list
-> GROUP BY id
-> ORDER BY id;
+------+---------------------+
| id | lists |
+------+---------------------+
| 1 | bar,foo,scott,tiger |
| 2 | bill,steve |
+------+---------------------+
2 rows in set (0.03 sec)
PostgreSQL
PostgreSQLには類似する機能を持つ関数が複数ありますが、LISTAGG()と同じ結果を得る場合には、array_agg＋array_to_string関数の組み合わせか、LISTAGGに近いSTRING_AGG()関数を単独で利用します。
array_agg(expression)
string_agg(expression, delimiter)
https://www.postgresql.jp/document/12/html/functions-aggregate.html
array_to_string(anyarray, text [, text])
https://www.postgresql.jp/document/12/html/functions-array.html
postgres=> SELECT
postgres-> id
postgres-> , STRING_AGG(
postgres(> str, ','
postgres(> ORDER BY str
postgres(> )
postgres-> AS lists
postgres-> FROM
postgres-> list
postgres-> GROUP BY id
postgres-> ORDER BY id;
id | lists
----+---------------------
1 | bar,foo,scott,tiger
2 | bill,steve
postgres=> SELECT
postgres-> id
postgres-> , ARRAY_TO_STRING(
postgres(> ARRAY_AGG(str ORDER BY str)
postgres(> , ','
postgres(> )
postgres-> AS lists
postgres-> FROM
postgres-> list
postgres-> GROUP BY id
postgres-> ORDER BY id;
id | lists
----+---------------------
1 | bar,foo,scott,tiger
2 | bill,steve
明日の12月21日のJPOUG Advent Calendar 2020は Yohei Azekatsu さんです。何か、やらかして。。。いや、何か、やってくれることでしょうw よろしくおねがいします！
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の19日目です。
標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
で、すっかり忘れてた。非互換ではその手のが多いw
部分文字列の扱いの癖、盲点というかなんというか、小数の扱いの違いを忘れてましたw
Oracle
Positionが大きい場合の挙動では Oracle以外は空文字を返します。これも非互換は非互換ですが。
注目してもらいたいのは、整数じゃないとき。Oracleは、小数点以下切り捨てで動きます。
ORACLE> set tab off
ORACLE> set null [NULL]
ORACLE> col str for a30
ORACLE> SELECT SUBSTR('1234567890', 10, 1) AS str FROM dual;
STR
------------------------------
0
ORACLE> SELECT SUBSTR('1234567890', 11, 1) AS str FROM dual;
STR
------------------------------
[NULL]
ORACLE> SELECT SUBSTR('1234567890',10.4, 1) AS str FROM dual;
STR
------------------------------
0
ORACLE> SELECT SUBSTR('1234567890',10.5, 1) AS str FROM dual;
STR
------------------------------
0
ORACLE> SELECT SUBSTR('1234567890',10.9, 1) AS str FROM dual;
STR
------------------------------
0
MySQL (8.0)
Positionが範囲外であれば空文字を返すのは冒頭で説明した通りですが、Oracleと明らかに違うのは、小数点以下は四捨五入
整数以外も受け付けてくれますが、デフォルトの挙動で、切り捨てか、四捨五入という違いにより取り出される結果に差異が出ます。ハマりますよね。これw
mysql> SELECT SUBSTRING('1234567890', 10, 1) AS str;
+-----+
| str |
+-----+
| 0 |
+-----+
1 row in set (0.01 sec)
mysql> SELECT SUBSTRING('1234567890', 11, 1) AS str;
+-----+
| str |
+-----+
| |
+-----+
1 row in set (0.02 sec)
mysql> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
+-----+
| str |
+-----+
| 0 |
+-----+
1 row in set (0.02 sec)
mysql> SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
+-----+
| str |
+-----+
| |
+-----+
1 row in set (0.06 sec)
mysql> SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
+-----+
| str |
+-----+
| |
+-----+
1 row in set (0.02 sec)
mysql> SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
+-----+
| str |
+-----+
| 1 |
+-----+
1 row in set (0.01 sec)
mysql> SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
+-----+
| str |
+-----+
| 2 |
+-----+
1 row in set (0.02 sec)
mysql> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
+-----+
| str |
+-----+
| '' |
+-----+
1 row in set (0.02 sec)
PostgreSQL
こちらPostgreSQLは単純、Positionに指定できるのは整数のみです。ある意味わかりやすいですw 文字列の位置が 1.9とかなかなかですからね。
postgres=> SELECT SUBSTRING('1234567890', 10, 1) AS str;
str
-----
0
(1 row)
postgres=> SELECT SUBSTRING('1234567890', 11, 1) AS str;
str
-----
(1 row)
postgres=> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 11, 1)),'''') AS str;
str
-----
''
(1 row)
postgres=> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
ERROR: function pg_catalog.substring(unknown, numeric, integer) does not exist
LINE 1: SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
おまけ
Redshift
PostgreSQLの血筋のはずですが、少数はエラーにもならず、そんなのねーよ。的な空文字が帰ってきます。なかなか男前です。素直に考えれば、1.9のところって文字の途中な訳で。。。
これもなかなか気づかいないです。エラーにはならないタイプなので、結果をみて？？？？ としばらく悩むタイプですね。非互換としては事前に判断が難しいタイプ。リテラルで少数指定されていれば気づきやすいですが、バインド変数だったりすると気づくのは、かなり辛いです。
redshift=# SELECT SUBSTRING('1234567890', 10, 1) AS str;
str
-----
0
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 11, 1) AS str;
str
-----
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
str
-----
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
str
-----
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
str
-----
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
str
-----
(1 row)
redshift=# SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
str
-----
(1 row)
redshift=# SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
str
-----
''
(1 row)
hr>
さあ、カウントダウンだw (^^;;;;;
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の18日目です。
今日はこまけーことに気づいてしまい、ブログ忘れそうだったw
ということで、２時間を切ったところで書いてますw
今日は、(+)を使った外部結合はOracle以外で通るのか（まあ、通らないですけどねー、Oracleの方言なのでw
と言った結合関連ネタを軽めで m(_ _)m
Oracle
(+)を使ったOracleの外部結合は、方言として有名ですよね。私は随分前から使わなくなってしまったので、今日は久々なにタイプした気がしますw
ANSI構文より(+)をオススメされる時があることはあるのですが、大抵の場合、実行計画がイケてるない時の対策としてだったりします。最近のは調べてないですが、。。。時間があったらネタにしてみたいと思います。。。w
ORACLE> SELECT *
2 FROM
3 m, d
4 WHERE
5 m.id = d.id(+);
ID ID SUBID
---------- ---------- ----------
1 1 1
2 2 1
3
ORACLE> SELECT *
2 FROM
3 m
4 LEFT OUTER JOIN d
5 ON m.id = d.id;
ID ID SUBID
---------- ---------- ----------
1 1 1
2 2 1
3
ORACLE> SELECT *
2 FROM
3 m, d;
ID ID SUBID
---------- ---------- ----------
1 1 1
2 1 1
3 1 1
1 2 1
2 2 1
3 2 1
ORACLE> SELECT *
2 FROM
3 m
4 CROSS JOIN d;
ID ID SUBID
---------- ---------- ----------
1 1 1
2 1 1
3 1 1
1 2 1
2 2 1
3 2 1
ORACLE> SELECT *
2 FROM
3 m, d
4 WHERE
5 m.id = d.id;
ID ID SUBID
---------- ---------- ----------
1 1 1
2 2 1
ORACLE> SELECT *
2 FROM
3 m
4 INNER JOIN d
5 ON m.id = d.id;
ID ID SUBID
---------- ---------- ----------
1 1 1
2 2 1
PostgreSQL
お次はPostgreSQL、通りませんよね！
postgres=> SELECT *
postgres-> FROM
postgres-> m
postgres-> LEFT OUTER JOIN d
postgres-> ON m.id = d.id;
id | id | subid
----+----+-------
1 | 1 | 1
2 | 2 | 1
3 | |
(3 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> m, d
postgres-> WHERE
postgres-> m.id = d.id(+);
ERROR: syntax error at or near ")"
LINE 5: m.id = d.id(+);
^
postgres=> SELECT *
postgres-> FROM
postgres-> m, d;
id | id | subid
----+----+-------
1 | 1 | 1
1 | 2 | 1
2 | 1 | 1
2 | 2 | 1
3 | 1 | 1
3 | 2 | 1
(6 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> m
postgres-> CROSS JOIN d;
id | id | subid
----+----+-------
1 | 1 | 1
1 | 2 | 1
2 | 1 | 1
2 | 2 | 1
3 | 1 | 1
3 | 2 | 1
(6 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> m, d
postgres-> WHERE
postgres-> m.id = d.id;
id | id | subid
----+----+-------
1 | 1 | 1
2 | 2 | 1
(2 rows)
postgres=> SELECT *
postgres-> FROM
postgres-> m
postgres-> INNER JOIN d
postgres-> ON m.id = d.id;
id | id | subid
----+----+-------
1 | 1 | 1
2 | 2 | 1
(2 rows)
MySQL (8.0)
それは、MySQLでも同じ。。。で(+)はエラーですよねー
mysql> SELECT *
-> FROM
-> m
-> LEFT OUTER JOIN d
-> ON m.id = d.id;
+------+------+-------+
| id | id | subid |
+------+------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | NULL | NULL |
+------+------+-------+
3 rows in set (0.07 sec)
mysql> SELECT *
-> FROM
-> m, d
-> WHERE
-> m.id = d.id(+);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
mysql> SELECT *
-> FROM
-> m, d;
+------+------+-------+
| id | id | subid |
+------+------+-------+
| 1 | 2 | 1 |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 3 | 1 | 1 |
+------+------+-------+
6 rows in set (0.06 sec)
mysql> SELECT *
-> FROM
-> m
-> CROSS JOIN d;
+------+------+-------+
| id | id | subid |
+------+------+-------+
| 1 | 2 | 1 |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 3 | 1 | 1 |
+------+------+-------+
6 rows in set (0.04 sec)
mysql> SELECT *
-> FROM
-> m, d
-> WHERE
-> m.id = d.id;
+------+------+-------+
| id | id | subid |
+------+------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+------+------+-------+
2 rows in set (0.05 sec)
mysql>
mysql> SELECT *
-> FROM
-> m
-> INNER JOIN d
-> ON m.id = d.id;
+------+------+-------+
| id | id | subid |
+------+------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+------+------+-------+
2 rows in set (0.03 sec)
Redshift
Redshiftでは〜、通じるんですよね。 (+) の外部結合
WHERE 句の Oracle スタイルの外部結合
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WHERE_oracle_outer.html
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の17日目です。
もう少しだ頑張れ、自分w
ということで、今日は、そうなの？ みたいな違いをみてみます。
関数と()の間に空白が入るとどうなるか。。。
では、いつもの通り Oracle から初めて、PostgreSQL , MySQLの順に見てみます。
Oracle
まあ、普通ですよね
ORACLE> SELECT COUNT(1) FROM dual;
COUNT(1)
----------
1
ORACLE> SELECT COUNT( 1 ) FROM dual;
COUNT(1)
----------
1
ORACLE> SELECT COUNT (1) FROM dual;
COUNT(1)
----------
1
¥
ORACLE> SELECT COUNT ( 1 ) FROM dual;
COUNT(1)
----------
1
PostgreSQL (Redshiftも同じ)
これもなんとことはない。。。
postgres=> SELECT COUNT(1);
count
-------
1
(1 row)
postgres=> SELECT COUNT( 1 );
count
-------
1
(1 row)
postgres=> SELECT COUNT (1);
count
-------
1
(1 row)
postgres=> SELECT COUNT ( 1 );
count
-------
1
(1 row)
MySQL (8.0)
おおおおおー。これは！
mysql> SELECT COUNT(1);
+----------+
| count(1) |
+----------+
| 1 |
+----------+
1 row in set (0.02 sec)
¥
mysql> SELECT COUNT( 1 );
+------------+
| count( 1 ) |
+------------+
| 1 |
+------------+
1 row in set (0.03 sec)
mysql> SELECT COUNT (1);
ERROR 1046 (3D000): No database selected
mysql> SELECT COUNT ( 1 );
ERROR 1046 (3D000): No database selected
ところが、sql_modeをANSIにすると。。。。。
ここ知らないと？？ってなりますよね。関数と()の間にスペース入れるかどうかって、私はスペースなし派ですが、流派によってはありそうな。。。知らんけど。
5.1.11 Server SQL Modes
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
MySQLに絵文字を保存しようとしたら文字列が消える問題
http://soudai1025.blogspot.com/2016/03/"
mysql> set sql_mode = 'ANSI';
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT COUNT(1);
+----------+
| count(1) |
+----------+
| 1 |
+----------+
1 row in set (0.05 sec)
mysql> SELECT COUNT( 1 );
+------------+
| count( 1 ) |
+------------+
| 1 |
+------------+
1 row in set (0.01 sec)
mysql> SELECT COUNT (1);
+-----------+
| count (1) |
+-----------+
| 1 |
+-----------+
1 row in set (0.06 sec)
mysql> SELECT COUNT ( 1 );
+-------------+
| count ( 1 ) |
+-------------+
| 1 |
+-------------+
1 row in set (0.04 sec)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の16日目です。
さて、別の関数ネタをやろうと思うと予定外の打ち合わせが多く、今日も癖の多いSQLネタのはずが、その癖の多いSQLのレントゲン（実行計画）の撮り方の違いをネタにしてみました。m(_ _)m
大きな進化を遂げたのはいうまでも無く、MySQLですね。8.0になって大幅に機能格納した感があります。チューニングもしやすくなることでしょうね:)
こうやって、CTEの再帰問合せの実行計画見ながらハードリカー飲むのもいいものですw
まず、Oracleの実行計画確認方法はEEオプション含め3つ。SQL*Plusのauto trace以外はActual Planが確認できます。一手間かかりますが。
8 SQL*Plusのチューニング
https://docs.oracle.com/cd/F19136_01/sqpug/tuning-SQL-Plus.html#GUID-233D9103-017C-4832-B5E1-E38D32F9B00D
Oracle その1 / auto trace : SQL*Plusの機能で、オプションなしで利用できますが Actual Plan は見ることができません＞＜ 実行統計は見れるのですけども
ORACLE> set tab off
ORACLE> set linesize 300
ORACLE> set autot trace exp
ORACLE> r
1 WITH gen_nums(v)
2 AS
3 (
4 SELECT 1
5 FROM
6 dual
7 UNION ALL
8 SELECT v + 1
9 FROM
10 gen_nums
11 WHERE v + 1 <= 10
12 )
13 SELECT v from gen_nums
14*
Execution Plan
----------------------------------------------------------
Plan hash value: 1492144221
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | RECURSIVE WITH PUMP | | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("V"+1<=10)
Oracleその２ / DBMS_XPLAN.DISPLAY_CURSOR()を利用したActual Plan取得（これはSEでも使えるオプション不要の機能
DBMS_XPLAN.DISPLAY_CURSOR
https://docs.oracle.com/cd/F19136_01/tgsql/generating-and-displaying-execution-plans.html#GUID-83F88700-3902-4D19-8182-AF2B92AEA7EB
ORACLE> r
1 WITH gen_nums(v)
2 AS
3 (
4 SELECT /*+ GATHER_PLAN_STATISTICS */ 1
5 FROM
6 dual
7 UNION ALL
8 SELECT v + 1
9 FROM
10 gen_nums
11 WHERE v + 1 <= 10
12 )
13* SELECT v from gen_nums
1
2
3
4
5
6
7
8
9
10
10 rows selected.
ORACLE> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));
SQL_ID a8yzv4a008jvr, child number 0
-------------------------------------
WITH gen_nums(v) AS ( SELECT /*+ GATHER_PLAN_STATISTICS */ 1 FROM
dual UNION ALL SELECT v + 1 FROM gen_nums WHERE v + 1 <= 10 )
SELECT v from gen_nums
Plan hash value: 1492144221
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 |
| 1 | VIEW | | 1 | 2 | 10 |00:00:00.01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | | 10 |00:00:00.01 |
| 3 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 |
| 4 | RECURSIVE WITH PUMP | | 10 | | 9 |00:00:00.01 |
--------------------------------------------------------------------------------------------------
Oracle その3 / Real Time SQL Monitor (EEオプション)
21 データベース操作の監視
https://docs.oracle.com/cd/F19136_01/tgsql/monitoring-database-operations.html#GUID-C941CE9D-97E1-42F8-91ED-4949B2B710BF
ORACLE> set pages 0
ORACLE> set linesize 1000
ORACLE> set long 1000000
ORACLE> set longchunksize 1000000
r
1 WITH gen_nums(v)
2 AS
3 (
4 SELECT /*+ MONITOR */ 1
5 FROM
6 dual
7 UNION ALL
8 SELECT v + 1
9 FROM
10 gen_nums
11 WHERE v + 1 <= 10
12 )
13* SELECT v from gen_nums
1
2
3
4
5
6
7
8
9
10
10 rows selected.
ORACLE> select dbms_sqltune.report_sql_monitor(type=>'text') from dual;
SQL Monitoring Report
SQL Text
------------------------------
WITH gen_nums(v) AS ( SELECT /*+ MONITOR */ 1 FROM dual
UNION ALL SELECT v + 1 FROM gen_nums WHERE v + 1 <= 10 ) SELECT v from gen_nums
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (25:48803)
SQL ID : 9g75y7v030mbt
SQL Execution ID : 16777216
Execution Started : 12/15/2020 15:58:45
First Refresh Time : 12/15/2020 15:58:45
Last Refresh Time : 12/15/2020 15:58:45
Duration : .000232s
Module/Action : SQL*Plus/-
Service : orcl
Program : sqlplus@localhost.localdomain (TNS V1-V3)
Fetch Calls : 2
Global Stats
=============================
| Elapsed | Cpu | Fetch |
| Time(s) | Time(s) | Calls |
=============================
| 0.00 | 0.00 | 2 |
=============================
SQL Plan Monitoring Details (Plan Hash Value=1492144221)
=================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 10 | | |
| 1 | VIEW | | 2 | 4 | 1 | +0 | 1 | 10 | | |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | 1 | +0 | 1 | 10 | | |
| 3 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | |
| 4 | RECURSIVE WITH PUMP | | | | 1 | +0 | 10 | 9 | | |
=================================================================================================================================================
MySQL
なんと、MySQL8.0から実行計画みやすいし、Actualまで出るじゃないですかーーーーーーー〜。
8.8.2 EXPLAIN Output Format
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
mysql> explain analyze WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 10
-> )
-> SELECT v from gen_nums;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------+
| -> Table scan on gen_nums (actual time=0.000..0.001 rows=10 loops=1)
-> Materialize recursive CTE gen_nums (actual time=0.019..0.020 rows=10 loops=1)
-> Rows fetched before execution (actual time=0.000..0.000 rows=1 loops=1)
-> Repeat until convergence
-> Filter: ((gen_nums.v + 1) <= 10) (cost=2.73 rows=2) (actual time=0.002..0.003 rows=4 loops=2)
-> Scan new records on gen_nums (cost=2.73 rows=2) (actual time=0.001..0.001 rows=5 loops=2)
|
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
PostgreSQL
そういえば、なんと無くMySQLの実行計画の見え方とPostgreSQLのは似てる気がします:)
14.1. EXPLAINの利用
https://www.postgresql.jp/document/12/html/using-explain.html
postgresql=> explain (analyze, buffers, verbose) WITH RECURSIVE gen_nums(v)
AS
(
SELECT 1
UNION ALL
SELECT v + 1
FROM
gen_nums
WHERE v + 1 <= 10
)
SELECT v from gen_nums;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
CTE Scan on gen_nums (cost=3.21..3.83 rows=31 width=4) (actual time=0.004..0.016 rows=10 loops=1)
Output: gen_nums.v
CTE gen_nums
-> Recursive Union (cost=0.00..3.21 rows=31 width=4) (actual time=0.003..0.013 rows=10 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
Output: 1
-> WorkTable Scan on gen_nums gen_nums_1 (cost=0.00..0.26 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=10)
Output: (gen_nums_1.v + 1)
Filter: ((gen_nums_1.v + 1) <= 10)
Rows Removed by Filter: 0
Planning Time: 0.055 ms
Execution Time: 0.039 ms
(12 rows)
さて、rebootしますよ。何かを。という話はもう少しあとでw
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の15日目です。
最初に、これSQLじゃないじゃん！ はい。その通りです m(_ _)m
如何にもこうにも、時間取れなくて、安易な差異の紹介に走りました。
とはいえ、SQL command line clentの使い勝手の違いって意外に無視できなかったりします
全ては紹介できないですが、個人的にどのエンジンのSQL command line clientでも使う機能だけですが:)
SQL command line clientでSQL叩いて、一旦、exitしてなんて面倒なことしたくないのでホストコマンドを実行したくなった時は ! とか \! です。
Oracle
OracleのSQL*Plusでは ! でホストコマンドを実行できます(Windowsは host or $)
Use the following command to execute operating system commands. - ! [ command ]
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpqr/index.html#SQPQR108
ORACLE> ! date
2020年 12月15日 火曜日 0時34分38秒 JST
PostgreSQL
psql - \! [ command ]
https://www.postgresql.jp/document/12/html/app-psql.html
postgres=> \! date
2020年 12月15日 火曜日 0時35分34秒 JST
Mysql
mysql - \! [ command ]
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html
mysql> \! date
2020年 12月15日 火曜日 0時39分00秒 JST
そして、無くてはならない編集コマンド, edit や \e と言ったショートカットなどがありますね。一通り覚えておくと便利です。
ORACLE
ED[IT] [file_name[.ext]]
https://docs.oracle.com/cd/F19136_01/sqpug/EDIT.html#GUID-25BC5CA1-4B03-4186-8ED3-715B5C6A6C42
ORACLE> select 1 from dual;
1
----------
1
ORACLE> edit
PostgreSQL
\e, \edit [ filename ] [ line_number ]
https://www.postgresql.jp/document/12/html/app-psql.html
postgres=> select 1;
?column?
----------
1
(1 row)
postgres=> \e
MySQL
edit, \e
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.02 sec)
mysql> edit
これ、書いてて思い出した、explainの違いも書いておいた方がいいか。。。。これもSQLそのものではないけど、重要なはず。
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の14日目です。
今回は大作（軽めにしたかったけど、少々難しのでそのまま載せることにしました）
業務上あまり多くないですが、一連番号の集合を作りたくなることがあります。シーケンスを使わずに。。
Oracleには昔から比較的簡単なクエリーで一連番号の集合を作り出せる（本来その目的のた目のクエリーではないですが）クエリーがいくつかあります。
PostgreSQLには、8.0(7.xぐらいから存在していたのか調べきれず。間違っていたらコメントいただけると助かります)ぐらいから 集合を返す関数として、generate_series()が組み込まれています。
MySQLは調べた限りですが、その手の便利関数やクエリーはあまりなさそうでした。
でそれを救う救世主w と言うのは大げさですが、WITH句を使った再帰問い合わせを使うと比較的互換の高い利用ができるようになってきました。
完全に同一構文ということではないのですけども。。。書き換える部分は少ない方だと思います：）
では、
Oracleから
まず、方言から古い順に紹介していきます。
Oracle/その1：階層問合せとlevel擬似列を利用する方法
他の方法に比べると処理時間なども有利ではあるのですが、Oracleだけでしか利用できない方法です。昔から利用している方は手グセでこちらをタイプすることも多いはずw
実行計画もシンプルで、Oracleで利用可能な方法の中では負荷は軽め（私が検証した範囲では）
階層問合せか、再帰問合せか、それが問題だ #2
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2011/01/2-8488.html
ORACLE> set tab off
ORACLE> set linesize 300
ORACLE> r
1 SELECT
2 LEVEL AS r
3 FROM
4 dual
5 CONNECT BY
6 LEVEL <= 10
7*
R
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.10
ORACLE>ORACLE> set autot trace exp
ORACLE> r
...略...
Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LEVEL<=10)
Oracle/その2：CUBEとrownum擬似列を利用する方法
これOracle 8iぐらいから拡張されたという記憶（間違ってたらごめんなさい）が、CUBEというクロス集計が簡単に書ける構文で長いクエリー書かなくて済むようになったーーーと
リリースされた当時は嬉しかった拡張の一つです。こんな使い方もできるのなーというところですが、実行計画を見るとかなり重めなんですよね。実際階層問合せより重いので、一連番号生成目的で利用することはほぼないですが、できるということで紹介しておきます。
20.3 CUBE(GROUP BYの拡張)
https://docs.oracle.com/cd/F19136_01/dwhsg/sql-aggregation-data-warehouses.html#GUID-C5FDD050-DCE0-4FE1-9741-420E2F970A36
ORACLE> r
1 SELECT rownum
2 FROM
3 (
4 SELECT 1
5 FROM
6 dual
7 GROUP BY
8 CUBE(1,1,1,1,1)
9 )
10 WHERE
11* rownum <= 10
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 2264780677
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 66 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | | 66 (0)| 00:00:01 |
| 3 | TEMP TABLE TRANSFORMATION | | | | | |
| 4 | MULTI-TABLE INSERT | | | | | |
| 5 | SORT GROUP BY NOSORT ROLLUP | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D698E_276CE9B | | | | |
| 8 | DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D698F_276CE9B | | | | |
| 9 | VIEW | | 32 | 416 | 64 (0)| 00:00:01 |
| 10 | VIEW | | 32 | 416 | 64 (0)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 37 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 41 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698E_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D698F_276CE9B | 1 | 13 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
Oracle/その3：再帰問合せを利用する方法
冒頭で紹介した階層問合せはOracleの方言ばりばりですが、階層問合せをサポートしているエンジンも多くなってきたこともあり、同一構文とまでは行きませんがかなり互換性は高い方法です。
汎用性のある方法にしたい場合は階層問合せを利用しておくと良いかもしれませんね。
Oracleの再帰問合せ構文では、recursive がないのが大きな違いです。また、dual表の利用も必要なのでこの点が他のエンジンと違うところと思っておけば大丈夫だと思います。
意外と構文はシンプルです。階層問合せに比べるとタイプする文字列は多いですけどw
再帰的副問合せのファクタリング
https://docs.oracle.com/cd/F19136_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
ORACLE> r
1 WITH gen_nums(v)
2 AS
3 (
4 SELECT 1
5 FROM
6 dual
7 UNION ALL
8 SELECT v + 1
9 FROM
10 gen_nums
11 WHERE v + 1 <= 10
12 )
13 SELECT v from gen_nums
14*
V
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 1492144221
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | RECURSIVE WITH PUMP | | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("V"+1<=10)
MySQL
調べた限りですが、MySQLにはPostgreSQLのような集合を返す関数として、generate_series()関数やOracleのような多くの方言はなさそうで
MySQL8.0からサポートされた再帰問合せが利用できます。Oracleで紹介した3つめの方法です。
多少構文は異なりますが、違うのは dual表がないのとrecursiveが必要なところ（MySQLの場合dual表を利用することも可能なので差異はrecursiveだけにすることもできます）
Recursive Common Table Expressions
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
mysql> WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 10
-> )
-> SELECT v from gen_nums;
+------+
| v |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.06 sec)
mysql> explain analyze WITH RECURSIVE gen_nums(v)
-> AS
-> (
-> SELECT 1
-> UNION ALL
-> SELECT v + 1
-> FROM
-> gen_nums
-> WHERE v + 1 <= 10
-> )
-> SELECT v from gen_nums;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------+
| -> Table scan on gen_nums (actual time=0.000..0.001 rows=10 loops=1)
-> Materialize recursive CTE gen_nums (actual time=0.019..0.020 rows=10 loops=1)
-> Rows fetched before execution (actual time=0.000..0.000 rows=1 loops=1)
-> Repeat until convergence
-> Filter: ((gen_nums.v + 1) <= 10) (cost=2.73 rows=2) (actual time=0.002..0.003 rows=4 loops=2)
-> Scan new records on gen_nums (cost=2.73 rows=2) (actual time=0.001..0.001 rows=5 loops=2)
|
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
さて、最後は、
PostgreSQL
PostgreSQL/その1：generate_series()関数を利用する方法
PostgreSQL方言の関数ですが、使いやすいですねタイプする文字数は一番少ない：）
9.24. 集合を返す関数 - 表9.61 連続値生成関数 - generate_series
https://www.postgresql.jp/document/12/html/functions-srf.html
postgresql=> SELECT r FROM generate_series(1, 10) r;
r
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
test=> explain (analyze,buffers,verbose) SELECT r FROM generate_series(1, 10) r;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Function Scan on pg_catalog.generate_series r (cost=0.00..10.00 rows=1000 width=4) (actual time=0.007..0.008 rows=10 loops=1)
Output: r
Function Call: generate_series(1, 10)
Planning Time: 0.025 ms
Execution Time: 0.022 ms
(5 rows)
PostgreSQL/その2：再帰問合せを利用する方法
見ての通り、MySQLで利用した構文がそのまま利用できます。全体で見る再帰問合せを利用する方法がもっとも差異の少ないことがわかります。微妙なさなんですけどね。 無くせないものか。。そこw
7.8. WITH問い合わせ（共通テーブル式）
https://www.postgresql.jp/document/12/html/queries-with.html
postgresql=> WITH RECURSIVE gen_nums(v)
postgresql-> AS
postgresql-> (
postgresql(> SELECT 1
postgresql(> UNION ALL
postgresql(> SELECT v + 1
postgresql(> FROM
postgresql(> gen_nums
postgresql(> WHERE v + 1 <= 10
postgresql(> )
postgresql-> SELECT v from gen_nums;
v
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
postgresql=> explain (analyze, buffers, verbose) WITH RECURSIVE gen_nums(v)
AS
(
SELECT 1
UNION ALL
SELECT v + 1
FROM
gen_nums
WHERE v + 1 <= 10
)
SELECT v from gen_nums;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
CTE Scan on gen_nums (cost=3.21..3.83 rows=31 width=4) (actual time=0.004..0.016 rows=10 loops=1)
Output: gen_nums.v
CTE gen_nums
-> Recursive Union (cost=0.00..3.21 rows=31 width=4) (actual time=0.003..0.013 rows=10 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
Output: 1
-> WorkTable Scan on gen_nums gen_nums_1 (cost=0.00..0.26 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=10)
Output: (gen_nums_1.v + 1)
Filter: ((gen_nums_1.v + 1) <= 10)
Rows Removed by Filter: 0
Planning Time: 0.055 ms
Execution Time: 0.039 ms
(12 rows)
ということで、今日は、役に立つような立たないような、でも何かの役に立ちそうなネタにしてみました。:)
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の13日目です。
ゼェゼェ。半ばに差し掛かりネタはあるものの書くことに疲れつつあるw （頑張れ自分w
と言うことで、今日は、あれば便利なので使ったりしますが、意外とありそうでなかったりする INITCAP()
この関数単語の先頭を大文字にしてくれる関数ですが、 MySQLの組み込み関数にはなかったりします。PostgreSQLにはあったり。
最初は
Oracleから。
INITCAP(char)
https://docs.oracle.com/cd/F19136_01/sqlrf/INITCAP.html#GUID-9FE9E0EE-D6B6-4C2C-BDEF-4FF4E1314560
ORACLE> SELECT INITCAP('oracle') FROM dual;
INITCA
------
Oracle
ORACLE> SELECT INITCAP('oracle-elison') FROM dual;
INITCAP('ORAC
-------------
Oracle-Elison
ORACLE> SELECT INITCAP('oracle,elison') FROM dual;
INITCAP('ORAC
-------------
Oracle,Elison
ORACLE> SELECT INITCAP('oracle|elison') FROM dual;
INITCAP('ORAC
-------------
Oracle|Elison
PostgreSQL
PostgreSQLにはOracleと同じ関数がサポートされています。
initcap(string)
https://www.postgresql.jp/document/12/html/functions-string.html
postgres=> SELECT INITCAP('oracle');
initcap
---------
Oracle
(1 row)
postgres=> SELECT INITCAP('oracle-elison');
initcap
---------------
Oracle-Elison
(1 row)
postgres=> SELECT INITCAP('oracle,elison');
initcap
---------------
Oracle,Elison
(1 row)
postgres=> SELECT INITCAP('oracle|elison');
initcap
---------------
Oracle|Elison
(1 row)
MySQL
個人的に意外だったのはMySQL. INITCAP()サポートされてません。UDFで作り込むしかないですね。
N/A
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
おまけ、
PostgreSQLを祖先にもつRedshiftにはPostgreSQL同様の関数がありました。
Redshift
INITCAP(string)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_INITCAP.html
・標準はあるにはあるが癖の多い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 全部俺w Advent Calendar 2020の12日目です。
私も癖多めですw
とうことで、アドベントカレンダーも約半分の折り返し地点です。ふー。
今日は、INSTR()
Oracleでは、SUBSTR()同様のバリエーションと挙動が見られます。ある意味分かり易ですね。
と言うことは、方言になりやすいはず。とも言えるわけです。はい。
では、見ていきましょう。
Oracle
positionは、SUBSTR()同様に負の値が使えます。0は、0しか返しません。
occurrenceは、正の値のみを取ります。
{ INSTR| INSTRB| INSTRC| INSTR2| INSTR4}(string , substring [, position [, occurrence ] ])
https://docs.oracle.com/cd/F19136_01/sqlrf/INSTR.html#GUID-47E3A7C4-ED72-458D-A1FA-25A9AD3BE113
ORACLE> SELECT INSTR('1234a56789a', 'a') FROM dual;
INSTR('1234A56789A','A')
------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', 1) FROM dual;
INSTR('1234A56789A','A',1)
--------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', -1) FROM dual;
INSTR('1234A56789A','A',-1)
---------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 1) FROM dual;
INSTR('1234A56789A','A',1,1)
----------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 2) FROM dual;
INSTR('1234A56789A','A',1,2)
----------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 2) FROM dual;
INSTR('1234A56789A','A',-1,2)
-----------------------------
5
ORACLE> SELECT INSTR('1234a56789a', 'a', -1, 1) FROM dual;
INSTR('1234A56789A','A',-1,1)
-----------------------------
11
ORACLE> SELECT INSTR('1234a56789a', 'a', 0) FROM dual;
INSTR('1234A56789A','A',0)
--------------------------
0
ORACLE> set null [NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', null) FROM dual;
INSTR('1234A56789A','A',NULL)
-----------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', '') FROM dual;
INSTR('1234A56789A','A','')
---------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', '') FROM dual;
INSTR('1234A56789A','')
-----------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', NULL) FROM dual;
INSTR('1234A56789A',NULL)
-------------------------
[NULL]
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, 0) FROM dual
*
ERROR at line 1:
ORA-01428: argument '0' is out of range
ORACLE> SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual;
SELECT INSTR('1234a56789a', 'a', 1, -1) FROM dual
*
ERROR at line 1:
ORA-01428: argument '-1' is out of range
MySQL
SUBSTR()はOracleに類似した挙動を持つ部分が多かったMySQLもINSTR()についてはそうでもないですね。positionやoccurrenceなどの引数がありません。
ただ、LOCATE()と言う類似した関数があります。LOCATE()と言う関数ではposition引数がありますが、０以上の整数でのみOracleと同じ挙動で負の値は、常に０ゼロを返すようです。
positionやoccurrence を利用している場合の移行は要注意と言うところですね。
INSTR(str,substr)
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_instr
mysql> SELECT INSTR('1234a56789a', 'a');
+---------------------------+
| instr('1234a56789a', 'a') |
+---------------------------+
| 5 |
+---------------------------+
1 row in set (0.04 sec)
mysql> SELECT INSTR('1234a56789a', '');
+--------------------------+
| instr('1234a56789a', '') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.04 sec)
mysql> SELECT INSTR('1234a56789a', null);
+----------------------------+
| instr('1234a56789a', null) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.03 sec)
mysql> SELECT INSTR('1234a56789a', 'a', 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'
mysql> SELECT INSTR('1234a56789a', 'a', 1, 1);
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'instr'
LOCATE(substr,str), LOCATE(substr,str,pos)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate
mysql> SELECT LOCATE('a', '1234a56789a');
+----------------------------+
| locate('a', '1234a56789a') |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.08 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 2);
+-------------------------------+
| locate('a', '1234a56789a', 2) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.05 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 5);
+-------------------------------+
| locate('a', '1234a56789a', 5) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.08 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 6);
+-------------------------------+
| locate('a', '1234a56789a', 6) |
+-------------------------------+
| 11 |
+-------------------------------+
1 row in set (0.09 sec)
mysql> SELECT LOCATE('a', '1234a56789a', -1);
+--------------------------------+
| locate('a', '1234a56789a', -1) |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.35 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 1);
+-------------------------------+
| locate('a', '1234a56789a', 1) |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.13 sec)
mysql> SELECT LOCATE('a', '1234a56789a', 0);
+-------------------------------+
| locate('a', '1234a56789a', 0) |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.35 sec)
PostgreSQL
42.13.3. 付録 本節には、移植作業を簡略化するために使用できる、Oracle互換のinstr関数のコードがあります。
https://www.postgresql.jp/document/12/html/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX
INSTR()はないのですが、類似関数として以下があるようです。また、position()と言う関数もあります。ですが、どちらもpositionやoccurrenceといった引数はない。マニュアルにOracleからの移植作業向けUDFの解説がある点は興味深いところ。
strpos(string, substring)
https://www.postgresql.jp/document/7.4/html/functions-string.html
postgres=> SELECT STRPOS('1234a56789a', 'a');
strpos
--------
5
(1 row)
postgres=> SELECT STRPOS('1234a56789a', '');
strpos
--------
1
(1 row)
postgres=> \pset null [NULL]
Null display is "[NULL]".
postgres=> SELECT STRPOS('1234a56789a', NULL);
strpos
--------
[NULL]
(1 row)
position(substring in string)
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html
postgres=> SELECT POSITION('a' in '1234a56789a');
position
----------
5
(1 row)
postgres=> SELECT POSITION('' in '1234a56789a');
position
----------
1
(1 row)
postgres=> SELECT POSITION(NULL in '1234a56789a');
position
----------
[NULL]
(1 row)
postgres=> SELECT POSITION(0 in '1234a56789a');
ERROR: function pg_catalog.position(unknown, integer) does not exist
LINE 1: SELECT POSITION(0 in '1234a56789a');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
postgres=>
Redshift
PostgreSQL系の流れをくむRedshiftに、CHARINDEX()なる関数がある。同名の関数名が見つかるのはSQL Serverですね。それはそれで興味深い。
STRPOS(string, substring )
CHARINDEX( substring, string )
POSITION(substring IN string )
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_POSITION.html
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の10日目です。
ネタがなくてw そこ狙って来たか！
と思われるかもしれませんが、これを語らずして、非互換語るなかれw
とうことで、Oracleでは、当たり前に利用している dual について
定数式をSELECT文で計算する場合などに指定する表です。dual表に、おイタしたりしたネタも過去あったような気がしますw
Oracleの方言なので、単純ですが、非互換では有名ですね。
では、本家から
Oracle
ORACLE> select 1 from dual;
1
----------
1
ORACLE> select 1;
select 1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
MySQL
昨日のエントリで使ってしまったw ので気づいた方もいると思いますが、MySQLは dual 付けられるんですよね。
13.2.10 SELECT Statement
https://dev.mysql.com/doc/refman/8.0/en/select.html
mysql> select 1 from dual;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.06 sec)
mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.04 sec)
PostgreSQL
PostgreSQLには dual を使うような習慣もないですし、文法的に用意されていません。無理やり dual 表を定義すれば別でしょうけど、無駄なだけなので移行するなら、素直に dualを削除が潔いと思いますw
SELECT
https://www.postgresql.jp/document/12/html/sql-select.html
postgres=> select 1 from dual;
ERROR: relation "dual" does not exist
LINE 1: select 1 from dual;
^
postgres=> select 1;
?column?
----------
1
(1 row)
おまけ
Redshift
Redshiftにも dual はありません。
SELECT
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_SELECT_synopsis.html
redshift=# select 1 from dual;
ERROR: relation "dual" does not exist
redshift=# select 1;
?column?
----------
1
(1 row)
また、Athenaも同様です
SELECT
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html
新しい年には、何か変化が欲しいと感じる今日この頃w
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の10日目です。
なんとか10日目の窓をあけましたw
今回は、有名な非互換なので、まさか、これに引っかかる方はいないと思いますが、定番のお約束みたいな非互換ネタなので書かないといけないですよね！！
では、いつも通り Oracle から。
NULLと空文字(マニュアルでは長さゼロの文字列値と記載されています。有名な非互換)
https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071
CONCAT(char1, char2)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CONCAT.html#GUID-D8723EA5-C93A-45C3-83FB-1F3D2A4CEAF2
空文字をNULLとして扱う点と、||による文字列連結(CONCATと同意）の挙動がOracle以外の世界と違うんですよね。これも有名です。
ORACLE> SELECT 'foo' || 'bar' FROM dual;
'FOO'|
------
foobar
ORACLE> SELECT 'foo' || '' FROM dual;
'FO
---
foo
ORACLE> SELECT 'foo' || NULL FROM dual;
'FO
---
foo
ORACLE> SELECT CONCAT('foo','bar') FROM dual;
CONCAT
------
foobar
ORACLE> SELECT CONCAT('foo','') FROM dual;
CON
---
foo
ORACLE> SELECT CONCAT('foo',null) FROM dual;
CON
---
foo
PostgreSQL
PostgreSQLでは、Oracleと異なり 文字列連結子でNULLを結合すると結果は、NULLになります。ここがOracleと異なる挙動ですね。
これを回避するにはconcat() or concat_ws()のいずれかを利用できます。
string || string
concat(str "any" [, str "any" [, ...] ])
concat_ws(sep text, str "any" [, str "any" [, ...] ])
https://www.postgresql.jp/document/12/html/functions-string.html
そして、これまた、悩ましいのは、 ||でNULLを結合した場合と、CONCAT_WS()でNULLを結合した挙動が異なるんですね。
||でNULLの場合はNULLですが、CONCATでNULLを結合するとOracleと同じ挙動になるんですよ。
postgres=> SELECT 'foo' || 'bar';
?column?
----------
foobar
(1 row)
postgres=> SELECT 'foo' || '';
?column?
----------
foo
(1 row)
postgres=> SELECT 'foo' || null;
?column?
----------
[NULL]
(1 row)
postgres=> SELECT CONCAT('foo', '');
concat
--------
foo
(1 row)
postgres=> SELECT CONCAT('foo', NULL);
concat
--------
foo
(1 row)
postgres=>
postgres=> SELECT CONCAT_WS('','foo','bar' );
concat_ws
-----------
foobar
(1 row)
postgres=> SELECT CONCAT_WS('','foo','' );
concat_ws
-----------
foo
(1 row)
postgres=> SELECT CONCAT_WS('','foo',NULL );
concat_ws
-----------
foo
(1 row)
postgres=>
MySQL
MySQLでは、|| は文字列連結子ではなく、なんと、論理演算子！！！！！！
え〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜~っ。
12.4.3 Logical Operators - OR, ||
https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html
mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
| 0 |
+----------------+
1 row in set, 3 warnings (0.03 sec)
mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
| 0 |
+-------------+
1 row in set, 2 warnings (0.04 sec)
mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
| NULL |
+---------------+
1 row in set, 2 warnings (0.06 sec)
実は逃げ道があるようで、sql_mode='PIPES_AS_CONCAT' にすると文字列連結子に早変わり！w
ですが、挙動はPostgreSQL同様に、NULLと連結したり演算子すると結果はNULLになると言う一般的な動きをします。
Oracleは演算に関してはNULLが絡むとNULLになりますが、文字列連結だけはNULLが空文字のような扱いを受けると言う挙動を示します。理解しちゃえばあれですが、エラーにならないだけに混乱するタイプの非互換ですね。
PostgreSQLとは異なり、CONCAT_WS()だけがOracleと同じ挙動を示します。
CONCAT(str1,str2,...)
CONCAT_WS(separator,str1,str2,...)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
mysql> set sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
| foobar |
+----------------+
1 row in set (0.02 sec)
mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
| foo |
+-------------+
1 row in set (0.04 sec)
mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
| NULL |
+---------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT('foo','bar');
+---------------------+
| concat('foo','bar') |
+---------------------+
| foobar |
+---------------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT('foo','');
+------------------+
| concat('foo','') |
+------------------+
| foo |
+------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT('foo',NULL);
+--------------------+
| concat('foo',null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.03 sec)
mysql> SELECT CONCAT_WS('foo',NULL);
+-----------------------+
| concat_ws('foo',null) |
+-----------------------+
| |
+-----------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo |
+--------------------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT_WS('','foo','bar');
+---------------------------+
| concat_ws('','foo','bar') |
+---------------------------+
| foobar |
+---------------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo','');
+------------------------+
| concat_ws('','foo','') |
+------------------------+
| foo |
+------------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo |
+--------------------------+
1 row in set (0.01 sec)
mysql>
ややこしやー、ややこしやー。
みなさん、ついてこれてますか？ この手の内容が25日まで続きますからね。（私が続けられるか次第だが。。。。頑張りマッス！
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の9日目です。
それでいいのだ！ バカボンのパパより。
いや、めんどくさいw
と言うことで、今日は単純そうにみえる部分文字列取得の違い。関数名の違いもあれば挙動の違いもあります。
そして、ものすごくバリエーションが多い。多すぎるので、SUBSTR()だけに絞りますw
Oracle
Unicodeキャラクタ向けSUBSTRCやUCS2コードポイント対応、UCS4コードポイント対応などのバリエーションが豊富ですが、それが方言になってますよね。
{ SUBSTR| SUBSTRB| SUBSTRC| SUBSTR2| SUBSTR4}(char, position [, substring_length ])
https://docs.oracle.com/cd/F19136_01/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E
SUBSTR()はこんな感じ。 positionをマイナスにすると一回りして切り出してきます。
ORACLE> select substr('hoge1234',1,4) from dual;
SUBS
----
hoge
ORACLE> select substr('hoge1234',-4,4) from dual;
SUBS
----
1234
MySQL
Oracleと同じ関数名、同じ引数をサポートしています。引数に特徴がありますね。from forを使った方が可読性は良いかもしれませんが、逆にウザがれる可能性も否定できません。その点個人の志向次第か。
positionにマイナスを指定した場合、Oracleと同じ挙動になりますね。興味深い。
そして、SUBSTR()はSUBSTRING()のシノニムと言うことなんですね。と言うことはオリジナルはSUBSTRING()なのか。。
SUBSTR() is a synonym for SUBSTRING().
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substr
mysql> select substr('hoge1234',1,4);
+------------------------+
| substr('hoge1234',1,4) |
+------------------------+
| hoge |
+------------------------+
1 row in set (0.08 sec)
mysql> select substr('hoge1234',-4,4);
+-------------------------+
| substr('hoge1234',-4,4) |
+-------------------------+
| 1234 |
+-------------------------+
1 row in set (0.07 sec)
PostgreSQL
最後にPostgreSQL、こちらはMySQLのオリジナルと同じ関数名SUBSTRING()となっています。 ぱっと見、Oracleから関数名さえ変更すれば移行できそうですが、実はpositionにマイナスを指定した場合の挙動に違いがあります！
一回りせずに、空文字を返してきます！！！！ ここ要注意です。
substring(string [from int] [for int])
https://www.postgresql.jp/document/12/html/functions-string.html
postgres=> \pset null [NULL]
Null display is "[NULL]".
postgres=> select substring('hoge1234',1,4);
substring
-----------
hoge
(1 row)
postgres=> select substring('hoge1234',-4,4);
substring
-----------
(1 row)
おまけですが、PostgreSQLのSUBSTRING()関数では、パターンマッチングが行えるようです。Oraclerの私には、REGEXP_SUBSTR()をイメージしちゃうのですが、
調べてみると、PostgreSQLにはregexp_matches()関数もあります。@@ 違いがわからなくなってきたので、この辺りはあとでゆっくり勉強しておきます。w
(MySQL8.0になると、regexp_substr()関数がサポートされているようですね...終わりのない世界w)
substring(string from pattern)
substring(string from pattern for escape)
実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺） Advent Calendar 2019
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の8日目です。
日付関連の非互換はネタが沢山w
翌月末日って何日？ を求める場合にも違いがあります。ほんとにもうww
Oracle
Oracleには、last_day()関数ががあります。date型の引数をとるので期間リテラルと演算可能。
では、Oracleで翌月末日を求めてみる。シンプルですね。
LAST_DAY(date)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LAST_DAY.html#GUID-296C7C02-7FB9-4AAC-8927-6A79320CE0C6
ORACLE> alter session set nls_date_format = 'yyyy-mm-dd';
Session altered.
ORACLE> select last_day(sysdate + interval '1' month) from dual;
LAST_DAY(S
----------
2021-01-31
Mysql
MySQLにもOracle同様にlast_day()関数を使って、簡単に「翌月末日って何日？」を求めることができますよね。
LAST_DAY(date)
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
mysql> select last_day(curdate() + interval 1 month);
+----------------------------------------+
| last_day(curdate() + interval 1 month) |
+----------------------------------------+
| 2021-01-31 |
+----------------------------------------+
1 row in set (0.03 sec)
Postgresql
なぜか、昔から頑なに last_day()関数がありません。UDFで頑張るか、ちょいと頑張って「翌月末日って何日？」を求めなければいけません。
翌々月１日の前日が「翌月末日って何日？」なので、その方法で求めてみます。やれやれ、last_day()ってUDF作った方が楽そうですねw
N/A
https://www.postgresql.jp/document/12/html/functions-datetime.html
postgres=> select cast(date_trunc('month', current_date + interval '2 month') - interval '1 day' as date);
date
------------
2021-01-31
(1 row)
Redshift
PostgreSQLの流れを汲むRedshiftですが、last_day()関数ありました！ PostgreSQLに、もしあればこんな構文だろうと想像します。
LAST_DAY ( { date | timestamp } )
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_LAST_DAY.html
redshift=# select last_day(current_date + interval '1 month');
last_day
------------
2021-01-31
(1 row)
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の7日目です。
さて、日付関連は非互換の宝庫ではありますが、ほんとなんで違うの。。。と言う微妙な違いだったり、キーーーーってなりますよね。なぜ合わせられないw
今日は期間リテラル。
Oracle
期間リテラル
https://docs.oracle.com/cd/F19136_01/sqlrf/Literals.html#GUID-49FADC66-794D-4763-88C7-B81BB4F26D9E
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
セッションが変更されました。
SQL> SELECT SYSDATE + INTERVAL '10' DAY FROM DUAL;
SYSDATE+IN
----------
2020-12-16
MySQL
Temporal Intervals
https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals
mysql> SELECT CURDATE() + INTERVAL 10 DAY;
+-----------------------------+
| curdate() + interval 10 day |
+-----------------------------+
| 2020-12-16 |
+-----------------------------+
1 row in set (0.00 sec)
PostgreSQL
8.5. 日付/時刻データ型
https://www.postgresql.jp/document/12/html/datatype-datetime.html
sql=> SELECT CAST(CURRENT_DATE + INTERVAL '10 DAY' AS DATE);
date
------------
2020-12-16
(1 row)
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!/a>
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の6日目です。
日付、日時関連も非互換の多い部分ですね。
該当関数の挙動を見てみると違いと、以外と違いがあるので面白いですよ。日付、日時関数って多くて全てを取り上げてると大変なので、今回は、OracleのSYSDATEを取り上げて、PostgreSQLではどれ使うのがいいのかなぁ
と言うところを見ていきたいと思います。
まず、このエントリー内で利用している、関数の特性を示す単語の意味を定義しておきたいと思います。
・同一SQL文の上で何度callされても同じ日時を返す特性：文内同一
・同一SQL文の上でcallされる毎に異なる日時を返す特性：文内非同一
・同一トランザクション内で何度callしても同一日時を返す特性：トランザクション内同一
・同一トランザクション内でcallされる毎に異なる日時を返す特性：トランザクション内非同一
関数としては沢山ありすぎて個別に調査した内容を個別に書こうと思うと、アドベントカレンダーの一エントリーとして終わる気がししないのでw
軽めに書いてもこんな感じw
SYSDATEはタイムゾーンを持たないので、PostgreSQLのCURRENT_DATEかなー、と思ってしまうと大きな間違いで、もっとも挙動として近いのは、Oracleで利用していたタイムゾーンに合わせる形で利用するPostgreSQLのstatement_timestamp()と言うことになる。
有名なOrafceで提供されている oracle.sysdate() も中を覗いてみると、statement_timestamp() が利用されている。
このエントリーの後半で挙動の確認方法と oracle.sysdate() が statement_timestamp() を利用しているソースを記載しています。
Oracle Database
SYSDATE
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSDATE.html#GUID-807F8FC5-D72D-4F4D-B66D-B0FE1A8FA7D2
年月日時分秒 タイムゾーンなし
特性
文内同一、トランザクション内非同一
SYSTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSTIMESTAMP.html#GUID-FCED18CE-A875-4D5D-9178-3DE4FA956516
年月日時分秒.秒未満精度(9) タイムゾーンあり
特性
文内同一、ランザクション内非同一
CURRENT_DATE
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_DATE.html#GUID-96795097-D6F0-4288-90E7-9D7C49B4F6E5
年月日時分秒 タイムゾーンなし
特性
文内同一、トランザクション内非同一
CURRENT_TIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_TIMESTAMP.html#GUID-CBD42B84-869D-45C7-9FFC-001DD7712097
年月日時分秒.秒未満精度(9) タイムゾーンあり
特性
文内同一、トランザクション内非同一
LOCALTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/LOCALTIMESTAMP.html#GUID-3C3D1F29-5F53-41F2-B2D6-A3767DFB22CA
年月日時分秒.秒未満精度(9) タイムゾーンなし
特性
文内同一、トランザクション内非同一
CURRENT_DATE
年月日（PostgreSQLのDATE型で返す）タイムゾーンなし
特性
文内同一、トランザクション内同一
CURRENT_TIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンあり
特性
文内同一、トランザクション内同一
LOCALTIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンなし
特性
文内同一、トランザクション内同一
clock_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり
特性
文内非同一、トランザクション内非同一
transaction_timestamp()
CURRENT_TIMESTAMPに同じ
関数名が具体的に何を返すか明確になっている点の違いのみ
特性
文内同一、トランザクション内同一
statement_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり
特性
文内同一、トランザクション内非同一
statement_timestamp()が、もっともOracleのSYSDATE/SYSTIMESTAMPに近い挙動を示す
now()
transaction_timestamp()の別名
利用することは推奨されていない（何を返すかわかりにくい関数名である影響と思われる)
特性
文内同一、トランザクション内同一
例
Oracle Database
トランザクション内同一性の確認（1秒間隔で3回実行）
Oracle Databaseの日付時刻関数は、トランザクション内非同一。
SYSDATE SYSTIMESTAMP
-------------------- ----------------------------------------
2020/11/08 16:12:03 2020/11/08 16:12:03.345275 +00:00
2020/11/08 16:12:04 2020/11/08 16:12:04.397601 +00:00
2020/11/08 16:12:05 2020/11/08 16:12:05.475719 +00:00
LOCALTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP
------------------------------ -------------------- ----------------------------------------
2020/11/09 01:12:03.345277 2020/11/09 01:12:03 2020/11/09 01:12:03.345277 +09:00
2020/11/09 01:12:04.397603 2020/11/09 01:12:04 2020/11/09 01:12:04.397603 +09:00
2020/11/09 01:12:05.475721 2020/11/09 01:12:05 2020/11/09 01:12:05.475721 +09:00
文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
(ore_sleep()と言うUDFを作成し、内部で（Oracle 18cまで）dbms_lock.sleep(1) or （Oracle 19c以降）dbms_session.sleep(1) を実行)
以下の結果から、Oracle Databaseの日付時刻関数は、すべて文内同一。
SQL> SELECT
sysdate, sysdate, ORE_SLEEP(1), sysdate
FROM
dual;
SYSDATE SYSDATE ORE_SLEEP(1) SYSDATE
------------------- ------------------- -------------- -------------------
2020/11/21 05:49:46 2020/11/21 05:49:46 0 2020/11/21 05:49:46
SQL> r
1 SELECT
2 systimestamp, systimestamp, ORE_SLEEP(1), systimestamp
3 FROM
4* dual
SYSTIMESTAMP SYSTIMESTAMP OREO_SLEEP(1) SYSTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/05:48:19.459180 +00:00 2020/11/21/05:48:19.459180 +00:00 0 2020/11/21/05:48:19.459180 +00:00
SQL> r
1 SELECT
2 localtimestamp,localtimestamp,ORE_SLEEP(1),localtimestamp
3 FROM
4* dual
LOCALTIMESTAMP LOCALTIMESTAMP ORE_SLEEP(1) LOCALTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21 14:58:13.350655 2020/11/21 14:58:13.350655 0 2020/11/21 14:58:13.350655
SQL> r
1 SELECT
2 current_date,current_date,ORE_SLEEP(1),current_date
3 FROM
4* dual
CURRENT_DATE CURRENT_DATE ORE_SLEEP(1) CURRENT_DATE
------------------- ------------------- -------------- -------------------
2020/11/21 14:59:47 2020/11/21 14:59:47 0 2020/11/21 14:59:47
SQL> r
1 SELECT
2 current_timestamp,current_timestamp,ORE_SLEEP(1),current_timestamp
3 FROM
4* dual
CURRENT_TIMESTAMP CURRENT_TIMESTAMP ORE_SLEEP(1) CURRENT_TIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/15:00:41.661495 +09:00 2020/11/21/15:00:41.661495 +09:00 0 2020/11/21/15:00:41.661495 +09:00
トランザクション内同一性の確認（1秒間隔で3回実行）
PostgreSQLの日付時刻関数は、トランザクション内同一と非同一が混在
CURRENT_DATEは時刻を持たないためこの方法では検証不能だが、
マニュアルではトランザクション内で同一と記載されている。
clock_timestamp(),statement_timestamp()はトランザクション内非同一、それ以外は、トランザクション内同一。
current_date | current_timestamp |
--------------+-------------------------------+
2020-11-08 | 2020-11-08 16:12:05.897343+00 |
2020-11-08 | 2020-11-08 16:12:05.897343+00 |
2020-11-08 | 2020-11-08 16:12:05.897343+00 |
clock_timestamp | localtimestamp |
------------------------------+----------------------------+
2020-11-08 16:12:05.936468+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:07.304506+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:08.532788+00 | 2020-11-08 16:12:05.897343 |
now | statement_timestamp | transaction_timestamp
------------------------------+-------------------------------+-------------------------------
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:05.936423+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:07.304408+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:08.532704+00 | 2020-11-08 16:12:05.897343+00
文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
以下の結果から、PostgreSQL日付時刻関数は、文内同一/非同一混在。
current_dateについてはこの方法では検証できないが、ドキュメントより文内同一であると判断。
sql=> SELECT
sql-> current_date, current_date, pg_sleep(1), current_date;
current_date | current_date | pg_sleep | current_date
--------------+--------------+----------+--------------
2020-11-21 | 2020-11-21 | | 2020-11-21
文内同一
sql=> SELECT
sql-> current_timestamp,current_timestamp,pg_sleep(1),current_timestamp;
current_timestamp | current_timestamp | pg_sleep | current_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
2020-11-21 06:05:48.930432+00 | 2020-11-21 06:05:48.930432+00 | | 2020-11-21 06:05:48.930432+00
文内非同一
sql=> SELECT
sql-> clock_timestamp(),clock_timestamp(),pg_sleep(1),clock_timestamp();
clock_timestamp | clock_timestamp | pg_sleep | clock_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
2020-11-21 06:08:48.920466+00 | 2020-11-21 06:08:48.920466+00 | | 2020-11-21 06:08:49.925492+00
文内同一
sql=> SELECT
sql-> localtimestamp,localtimestamp,pg_sleep(1),localtimestamp;
localtimestamp | localtimestamp | pg_sleep | localtimestamp
----------------------------+----------------------------+----------+----------------------------
2020-11-21 06:19:19.547055 | 2020-11-21 06:19:19.547055 | | 2020-11-21 06:19:19.547055
文内同一
sql=> SELECT
sql-> now(),now(),pg_sleep(1),now();
now | now | pg_sleep | now
-------------------------------+-------------------------------+----------+-------------------------------
2020-11-21 06:20:07.457373+00 | 2020-11-21 06:20:07.457373+00 | | 2020-11-21 06:20:07.457373+00
文内同一
sql=> SELECT
sql-> statement_timestamp(),statement_timestamp(),pg_sleep(1),statement_timestamp();
statement_timestamp | statement_timestamp | pg_sleep | statement_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
2020-11-21 06:20:52.502137+00 | 2020-11-21 06:20:52.502137+00 | | 2020-11-21 06:20:52.502137+00
文内同一
sql=> SELECT
sql-> transaction_timestamp(),transaction_timestamp(),pg_sleep(1),transaction_timestamp();
transaction_timestamp | transaction_timestamp | pg_sleep | transaction_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
2020-11-21 06:21:24.562833+00 | 2020-11-21 06:21:24.562833+00 | | 2020-11-21 06:21:24.562833+00
orafce によるエミュレーション関数（参考)
https://github.com/orafce/orafce/blob/master/README.asciidoc
oracle.sysdate()
PostgreSQLのstatement_timestamp()をラップしているため属性はstatement_timestamp()と同じ
sysdate関数のエミュレーションとしては最も近い属性を持っていると見られる。
OracleのSYSDATE代替関数とされている。
特性としては、問題ないと考えられ、文内同一 (OracleのSYSDATEと同じ挙動）,トランザクション内非同一（OracleのSYSDATEと同じ挙動）
実装を見てみると、
https://github.com/orafce/orafce/blob/master/orafce--3.14.sql
の関数定義を見るとC言語の関数であり、statement_timestampをと言うコメントがある.
statement_timestamp()がOracleのSYSDATEの挙動に近いと言う理由からなのだろうと想像する。納得感あり！！！！
CREATE FUNCTION oracle.sysdate()
RETURNS oracle.date
AS 'MODULE_PATHNAME','orafce_sysdate'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION oracle.sysdate() IS 'Ruturns statement timestamp at server time zone';
さらに、orafceのoracle.sysdate()のC言語のソースを追ってみる。。。
datefce.cのorafce_sysdateが本体であることがわかる
https://github.com/orafce/orafce/blob/master/builtins.h
extern PGDLLEXPORT Datum orafce_sysdate(PG_FUNCTION_ARGS);
https://github.com/orafce/orafce/blob/master/datefce.c
/********************************************************************
*
* ora_sysdate - sysdate
*
* Syntax:
*
* timestamp sysdate()
*
* Purpose:
*
* Returns statement_timestamp in server time zone
* Note - server time zone doesn't exists on PostgreSQL - emulated
* by orafce_timezone
*
********************************************************************/
Datum
orafce_sysdate(PG_FUNCTION_ARGS)
{
Datum sysdate;
Datum sysdate_scaled;
sysdate = DirectFunctionCall2(timestamptz_zone,
CStringGetTextDatum(orafce_timezone),
TimestampTzGetDatum(GetCurrentStatementStartTimestamp()));
/* necessary to cast to timestamp(0) to emulate Oracle's date */
sysdate_scaled = DirectFunctionCall2(timestamp_scale,
sysdate,
Int32GetDatum(0));
PG_RETURN_DATUM(sysdate_scaled);
}
および、GetCurrentStatementStartTimestamp()よりstatement_timestamp()であることがわかる
https://docs.huihoo.com/doxygen/postgresql/backend_2utils_2adt_2timestamp_8c_source.html#l01239
01239 statement_timestamp(PG_FUNCTION_ARGS)
01240 {
01241 PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
01242 }
ore_seep(seonds) - UDF sample
CREATE OR REPLACE FUNCTION ore_sleep
(
seconds NUMBER
)
RETURN NUMBER
AS
BEGIN
$IF DBMS_DB_VERSION.VER_LE_12 $THEN
DBMS_LOCK.SLEEP(seconds);
$ELSE
DBMS_SESSION.SLEEP(seconds);
$END
RETURN 0;
END;
/
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!/a>
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の5日目です。
これもあるのが当たり前だよねーと思っていると、ないの！！ と言うありがちなネタで、日付の和暦変換など、
Oracleだとグローバリゼーション対応で組み込まれてます。昨年令和に切り替わったばかりなので、Oracleが中心の方々は、MySQLやPostgreSQL界隈は大丈夫なの？間に合うの？なんて話題も多かったことだろうと思います。
私もそうでした。でも心配する必要のあるのはOracleだけだったw と言う取り越し苦労でした。はい。
まず、Oracle
SCOTT@orcl> r
1 SELECT
2 TO_CHAR(
3 SYSDATE
4 , 'EEYY/MM/DD'
5 , 'NLS_CALENDAR = ''JAPANESE IMPERIAL'''
6 )
7 FROM
8* DUAL
TO_CHAR(SYSDATE,'EEYY/MM/DD','NLS_CALE
--------------------------------------
令和02/12/05
経過: 00:00:00.01
SCOTT@orcl>
Oracle 19c - Databaseグローバリゼーション・サポート・ガイド 3.7.1.4 紀元の年
https://docs.oracle.com/cd/F19136_01/nlspg/setting-up-globalization-support-environment.html#GUID-9674F2F3-D3A2-436A-83D1-7A8AC0D2B1ED
実は、年号が平成から令和切り替わると言う時になって始めて知ったのですが、みんな対応しているわけではないのですね。。和暦。
そう言う意味では、顧客要求をしっかり拾っていたと言うことなのでしょうね。
まだまだ、知らないことが多いSQLな世界w
PostgreSQL
ないのでUDF等で対応ですよね
PostgreSQLの新元号への対応について
https://www.ashisuto.co.jp/support/gengo/product/postgresql.html
MySQL
ないのでUDF等で対応ですよね
と言うことで、RDS/Auroraにつても同様 この記事、現在は Snowflake - Principal Cloud Support Engineer の松崎さんの記事ですね！
https://aws.amazon.com/jp/blogs/news/how-to-determine-whether-kaigen-japan-era-name-transition-affects-your-mysql-compatible-engines-running-on-rds/"
Redshift
ないので、必要ならUDF等で対応ですよね
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!/a>
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の4日目です。
モルダー、あなた疲れてるのよ！ 的なタイトルになってしまったw
エラーにはならないけど、この手の差異もなかなか嫌らしい差異ですよね。
結論から言うと、この差異の影響を避けるためにはデフォルトの挙動任せは危険かな。と言うことになるのですが、まあ、色々ありますよね。長いこと同じエンジン使って他ところへ乗り換えるとなると。
Oracle Database
なお、SQL*plusのnumformatパラメータは未設定の状態（デフォルト）です。
SQL> SELECT CAST((1.0/6.0) AS NUMBER(38,37)) FROM DUAL;
リテラル値の除算結果は小数点以下1桁の状態でデフォルトのキャストを利用した場合、内部的に小数点以下37桁精度。
実は、表示されている桁数は、SQL*Plusのnumformatの影響を受け、numformat(デフォルト小数点以下7桁)で丸る
一見 小数点以下8桁に見えるが、
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
--------------------------
1.28571429
numformatの桁数を大きくすると内部的は小数点以下37桁精度！
SQL> set numformat "0.0999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143
ね！
SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;
CAST((9.0/7.0)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143
Oracleの場合、返却時の精度が全ての計算精度に影響
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,37))
--------------------------------------------------------------------
1.2857142857142857142857142857142857143
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,8)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,8))
-------------------------------------------------------------------
1.2857142900000000000000000000000000000
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,8)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,8))
---------------------------------------------------------------------
1.2857142900000000000000000000000000000
SQL> set numformat "0.09999999999999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;
CAST((9/7)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000
SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;
CAST((9.0/7.0)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000
SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;
CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.28571428571428571428571428571428571430000000000
PostgreSQLだとどうなるかと言うと、
整数として扱われ、除算結果もinteger型！
sql=> SELECT 9/7;
?column?
----------
1
pg_typeof
-----------
integer
リテラル値は小数点以下1桁の精度だが、除算結果は小数点以下16桁精度のnumeric型で返されるようだ。
PostgreSQLのマニュアルには記載されていないようだが、どこかに記載されてるのだろうか。。。知ってる方がいたらコメントお待ちしております。:)
sql=> SELECT 9.0/7.0;
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下の精度0でnumeric型にキャスト**した場合も、除算結果は小数点以下16桁精度のnumeric型となるようだが、これもPostgreSQLのマニュアルには記載されていないようだ。
なお、PostgreSQLの場合numeric型へのキャストで指定できる最大精度は1000と記載され内部的にはそれ以上の精度となっている
詳細は
8.1.2. 任意の精度を持つ数
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
表8.2 数値データ型
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
を参照のこと
sql=> SELECT 9::NUMERIC(100,0)/7::NUMERIC(100,0);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下精度1桁でnumeric型へキャスト**した場合も同様に、除算結果は小数点以下16桁のnumeric型になるのな。これ
sql=> SELECT 9::NUMERIC(100,1)/7::NUMERIC(100,1);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
同様に小数点以下2桁でnumeric型へキャストし、除算した結果も小数点以下16桁精度のnumeric型として返される模様。
sql=> SELECT 9::NUMERIC(100,2)/7::NUMERIC(100,2);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下15桁のnumeric型にキャストした場合も結果は小数点以下16桁精度のnumeric型！
sql=> SELECT 9::NUMERIC(100,15)/7::NUMERIC(100,15);
?column?
--------------------
1.2857142857142857
pg_typeof
-----------
numeric
小数点以下16桁のnumber型にキャストした場合は、指定したscaleで返された。
sql=> SELECT 9::NUMERIC(100,16)/7::NUMERIC(100,16);
?column?
--------------------
1.2857142857142857
pg_typeof
--------------------
numeric
小数点以下17桁精度でnumeric型へキャストすると、指定した小数点以下17桁で除算結果が返された。
(どうやら、小数点以下16桁未満は小数点以下の精度を16桁として扱っているように見える
sql=> SELECT 9::NUMERIC(100,17)/7::NUMERIC(100,17);
?column?
---------------------
1.28571428571428571
pg_typeof
-----------
numeric
表示される桁数が16桁なので内部も16桁かと思ったら、少々多めに持っているらしい。このあたりマニュアルに記載がない....
内部でも小数点以下16精度なのか？ という疑問を確認するための確認の結果、小数点以下20桁なので多少大きめの精度で持っているだが、この辺りもPostgreSQLのマニュアルを見る限り記載されていないようだ。
sql=> SELECT CAST((1.0/6.0) AS NUMERIC(38,37));
numeric
-----------------------------------------
0.1666666666666666666700000000000000000
しかし、小数点以下1桁のリテラル値については、小数点以下16桁の精度で計算されてしまう。（ルールはあるようだがドキュメントに明確な記載が見当たらないのは辛いとこと。
sql=> SELECT CAST((9.0/7.0) AS NUMERIC (38,37));
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
結局のところ、デフォルトの挙動に任せず、必要な精度にキャストすることで必要な精度を持たせるようにすることでOracleのデフォルトの挙動と同様の精度になりそう。これが無難な対応なんだろうね。
sql=> SELECT CAST((9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37)) AS NUMERIC(38,37));
numeric
-----------------------------------------
1.2857142857142857142857142857142857143
PostgreSQLでは可能な精度の検証。Oracleからの移行ではここまでの精度は必要としない、よね。多分。
sql=> SELECT 9::NUMERIC(100,99)/7::NUMERIC(100,99);
?column?
-------------------------------------------------------------------------------------------------------
1.285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714286
pg_typeof
-----------
numeric
PostgreSQLで指定可能な最大精度（内部的な精度は1000を超えるがして可能な精度は1000)
試して！ Oracler皆びっくり！ な感じ
sql=> SELECT 9::NUMERIC(1000,999)/7::NUMERIC(1000,999);
そして、
計算結果とそれぞれのリテラル値の精度ではどちらが優先されるのか確認したところ、Oracleとは異なり、最大精度ではなく、リテラル値の制度が優先される結果となった。この点、Oracleからの移行では注意が必要かな。
以下は、小数点以下の精度を37桁に統一した結果だが、この場合Oracleと同様の精度の結果が得られる
sql=> SELECT (9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857142857142857142857143
しかし、リテラル側の精度を落とし8桁にすると、16桁以下は全て16桁の精度になるという挙動が見られ流・
sql=> SELECT (9.0::NUMERIC(38,8)/7.0::NUMERIC(38,8))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
sql=> SELECT (9.0::NUMERIC(38,16)/7.0::NUMERIC(38,16))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857000000000000000000000
リテラル値の精度を17桁にすると、指定精度で結果を返すが、やはり計算結果の精度にはならない
sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,37);
numeric
-----------------------------------------
1.2857142857142857100000000000000000000
さらに、17桁の精度で計算し結果を16桁精度で返す例。
sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,16);
numeric
--------------------
1.2857142857142857
おまけ
Redshiftのnumeric型はPostgreSQL由来ではあるが、デフォルトの精度に関する挙動はPostgreSQLに近い。ただし、多少異なる部分もある。以下の例はPostgreSQL 11.xでは、小数点以下 20桁精度だったが、Redshiftでは16桁精度となっている。
微妙な違いだが違いがあるのは確かなので注意した方が良さげ
sql=# select cast((1.0/6.0) as numeric(38,37));
numeric
-----------------------------------------
0.1666666666666667000000000000000000000
噂のプログラム発動中だったのか。そんなの気にしてなかった。全くwwww
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の3日目です。
忙しい、今年も勢いで始めた割には、事前にネタの構想があったわけでもないというギリギリの状態で書いてますがw
なんとか3つ目の窓をあけました。:)
今回は、データ型確認したいことありませんか？ という話。
Oracleを長年使い、PostgreSQL（互換含む）への移行という時に、調査していた時のこと、。。。
データ型見たいよね、これ。Oracleだとdump()で間接的にデータ型見れたなと。。。で他のエンジンではどうなのよ。。。と調べ始めたら結構大変でした。。
という２年半ぐらい前の苦労を思い出しつつ書いてみますw
調べていくと、わかりやすい関数名だったり、ありそうでないものもあるんですよね。。。。まさに、非互換の多い部分だった。。。
まず、Oracle
おなじみのdump()関数ですね。typ=nn の数字でデータ型を判断します. typ=2は、NUMBER型、typ=96は、CHAR型ですね。
SQL> select dump(123) from dual;
DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24
SQL> select dump(123,8) from dual;
DUMP(123,8)
---------------------
Typ=2 Len=3: 302,2,30
SQL> select dump(123,10) from dual;
DUMP(123,10)
---------------------
Typ=2 Len=3: 194,2,24
SQL> select dump(123,16) from dual;
DUMP(123,16)
--------------------
Typ=2 Len=3: c2,2,18
SQL> select dump(123,17) from dual;
DUMP(123,17)
---------------------
Typ=2 Len=3: c2,^B,^X
SQL>
SQL> select dump('123',1016) from dual;
DUMP('123',1016)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: 31,32,33
次は、Postgresql
pg_typeof()って関数が使えます。結果がデータ型名で返されるのでわかりやすい！
sql=> select pg_typeof(123);
pg_typeof
-----------
integer
(1 row)
sql=> select pg_typeof('123'::text);
pg_typeof
-----------
text
(1 row)
sql=> select pg_typeof(now());
pg_typeof
--------------------------
timestamp with time zone
(1 row)
Athena
Prestoで使える関数なので、そのまま typeof()って関数でこれもデータ型名で返されます。
なぜ、Athenaかって？ 勢いですかねぇ。
% aws athena start-query-execution --query-string "select typeof(123);" --result-configuration OutputLocation=s3://xxxx-athena-results
--------------------------------------------------------------
| StartQueryExecution |
+-------------------+----------------------------------------+
| QueryExecutionId | f658de1e-b711-433d-b603-15835b6e5de5 |
+-------------------+----------------------------------------+
%
% aws athena get-query-results --query-execution-id 92f46e33-0b2e-4e94-90b7-8acb3d6fce3b --output text | grep DATA
DATA _col0
DATA integer
Redshift
N/A
MySQL
もし、あったらツッコミよろしくお願いします。 m(_ _)m
N/A
参考
Oracle - DUMP
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DUMP.html#GUID-A05793C9-B35D-4BA7-B68C-E3693BCF47A5
Oracle Built-in Data Types
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6
PostgreSQL - 表9.63 システムカタログ情報関数 - pg_typeof
https://www.postgresql.jp/document/11/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
Athena - 6.4. Conversion Functions - typeof
https://prestodb.io/docs/0.172/functions/conversion.html
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の2日目です。
今日は、心と時間の余裕がないので、軽めですw
いきなりですがタイトルの通り、関数名が同じなら引数の並びや数も一緒だ！
と決めつけちゃいけないw 案件です。 数時間ハマった挙句、マニュアルを読み直すという王道で解決した事案ですはい。マニュアル読みましょうね。読みづらいのもあるけど。。
現在の私、何をやってるかピンボケ感満載なロール名ではあるのですが、その名の通り、OracleのSQLで質問もうけるわ、たまには、SparkSQLでも質問受けたりしますw
そのSparkSQLでハマったのがrtrim(),ltrim()。
長年Oracleを使ってきたので、手癖でタイプしちゃうわけですよ！ 思い込み、ダメ絶対！w
では、その大切なマニュアルの解説でRDBMSの有名どころのltrim/rtrimとSparkSQLのltrim/rtrimのシンタックスを確認してみましょう。
まず、Oracleは以下の通り。見たなれ安心感w
LTRIM( str [, trimStr] )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
次、PostgreSQL
RTRIM( str [, trimStr] )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
https://www.postgresql.jp/document/11/html/functions-string.html
ついでなので、Redshift
PostgreSQLと同じですね。
RTRIM( string, trim_chars ) string : 切り捨てる文字列の列または式。 trim_chars : 末尾から切り捨てる文字を表す、文字列の列または式。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_RTRIM.html
MySQL
MySQLのRTRIMには第二引数は無い! 単体では同じことができないので、他の関数と組み合わせるんでしょうね。（試してないですが）
RTRIM( str )str : トリムされるソースの文字列型または、リテラル文字列
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_rtrim
そして、最後に、今回ハマった
SparkSQL
RTRIM( [trimStr ,] str )str : トリムされるソースの文字列型または、リテラル文字列trimStr : トリムしたい文字列。デフォルトは、半角空白1文字
トリムしたい文字列の引数位置が、Oracle/PostgreSQL/Redshiftとは逆なので引数が少ないMySQLと異なりシンタックスエラーにならない＞＜
https://spark.apache.org/docs/2.3.1/api/sql/index.html#rtrim
rtrim()も同じです。
Oracle
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
PostgreSQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
Redshift
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
MySQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
SparkSQL
https://docs.oracle.com/cd/F19136_01/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA
簡単な例を
SparkSQL
>>> SQL="select rtrim(' ', 'SparkSQL ') as d"
>>> spark.sql(SQL).show()
+--------+
| d |
+--------+
|SparkSQL|
+--------+
Oracle
SQL> select '|' || rtrim('SparkSQL ', ' ') || '|' as d from dual;
D
----------
|SparkSQL|
PostgreSQL / Redshift
test=> select '|' || rtrim('SparkSQL ', ' ') || '|' as d;
d
------------
|SparkSQL|
いよいよ始まりました。Advent Calendar 2020
今年も勢いで全部俺ですw
JPOUG Advent Calendar 2020もよろしくお願いします！
（最後まで書けるのか、書き抜ける喜びw どこかのCMっぽくなってしまいましたが、第一日めの窓を開けましょうw）
今年の全部俺はレントゲンではなく、標準はあれど、非常に癖の多いSQLを25回に渡り、眠い目を擦りながら書きづつけて行きたい（すでに希望になってるw）と思っております。はい。
先日、AthenaでLIMIT句使おうとして、エラーに遭遇したところからw
Oracle/MySQL/PostgreSQL/Redshiftとか LIMIT OFFSET的ところから違うわけですが（同じ部分もあります）、何も考えずに、Athenaに投げて、撃沈w
Prestoの311以降だとLIMIT OFFSETでPagination可能なわけですが、Amazon Athenaは今の所(2020/12/1現在）Presto 0.172なのをすっかり忘れてたわけです。はい。さーせん。
あ〜、SQLってバージョンでもそうですが、エンジン違えば、気が狂うw程度に違う時があって、き〜〜〜って。なることしばしば。
で、イラっとしたSQLの違いを Oracleの構文と比較しながら自分の備忘録として書いて残しておこうと思った次第です。
（なーんだ、自分の為か、と思わないでくださいね。明日はわが身かもしれませんよw）
以下、どの構文がどのエンジンで通るのかをざっとまとめたもの。
1.ROWNUMとWHERE句によるPagination
Oracle
SELECT
id
FROM (
SELECT
ROWNUM as ln
,id
FROM
hoge
ORDER BY id
)
WHERE
ln BETWEEN 1 AND 100;
2.ROW_NUMBER()ウィンドウ関数とWHERE句によるPagination
意外に使えるw 可読性悪いけどねー
Oracle / PostgreSQL / Redshift / Athena
SELECT
id
FROM (
SELECT
ROW_NUMBER() OVER(
ORDER BY id
) AS ln
,id
FROM
hoge
)
WHERE
ln BETWEEN 1 AND 100;
3.OFFSET句とFETCH FIRST n ROWS ONLYのPagination
Oracle / PostgreSQL
SELECT
id
FROM
hoge
ORDER BY
id
OFFSET 0 ROW
FETCH FIRST 100 ROWS ONLY;
4.LIMIT句とOFFSET句のPagination
PostgreSQL / MySQL / Redshift
SELECT
id
FROM
hoge
ORDER BY
id
LIMIT 100 OFFSET 0;
ちなみに、Oracleでは、2.のROW_NUMBER()と3.のOFFSET + FETCHの構文の実行計画は同じなので、可読性が高い2.と3.いずれかと言われれば3.がおすすめ。
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 260 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 50 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | HOGE_PK | 1000K| 4882K| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LN">=1 AND "LN"<=100)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 390 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 390 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 10 | 50 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | HOGE_PK | 1000K| 4882K| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">0)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)
MySQL
https://dev.mysql.com/doc/refman/8.0/en/select.html
PostgreSQL
https://www.postgresql.jp/document/11/html/queries-limit.html
Reshift
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ORDER_BY_clause.html
Amazon Athena
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html
寝ぼけながら書いているので、誤り等ございましたらご指摘願います。
仕事忙しいのに全部俺始めてしまい、かなり不安なスタートw（この時間だし）
最近のコメント