📖
達人に学ぶSQL徹底指南書をTiDB Serverlessで写経する(2)
写経その2。 第一章の7から
第一章 魔法のSQL
7. ウィンドウ関数で行間比較を行う
- 問題なし。
ウィンドウ関数は演習にあるような移動平均を出すようなケースでしか使ったことが無かったけど、行同士を比較する処理一般に利用できることが分かった。勉強になる。
8. 外部結合の使い方
外部結合で行列変換
-
P.158 コード例は正しいが、書籍サイトからダウンロードできるsqlはコメント文の
--
の右側にスペースがないため、コメントとして解釈されず実行に失敗する。スペースを入れて正しくスペースとして認識させる必要がある。具体的には下記の箇所。FROM (SELECT DISTINCT name FROM Courses) C0 --このC0が表側になる
参照: https://dev.mysql.com/doc/refman/8.0/ja/comments.html
他のSQLも同様に修正する。
完全外部結合
-
P.171 MySQLではFULL OUTER JOINは利用できない。TiDBも同様に利用できないので、LEFT/RIGHT OUTER JOINとUNION ALLを利用する必要がある。
-
P.178 MySQLではMERGE文は使えないので、
INSERT ... SELECT ... ON DUPLICATE KEY
またはINSERT ... TABLE ... ON DUPLICATE KEY
を利用する。TABLEはSELECT * FROM ...
と一緒。演習のようにClass_AにClass_Bをマージする場合は
-- 8.0.19 以下 INSERT INTO Class_A TABLE Class_B ON DUPLICATE KEY UPDATE name = VALUES(name); -- 8.0.20 以上 INSERT INTO Class_A TABLE Class_B ON DUPLICATE KEY UPDATE name = Class_B.name;
のように書く。
このTABLE句をTiDBがサポートしているか不安だったが(リファレンスでも記載ないし)、しっかりサポートしていた。TABLE文も利用できるので
SELECT * FROM table
みたいに書く必要はない。
9. SQLで集合演算
-
P.180 MySQLは2018年現在まだ両方サポートしていません と記載あるが、2023年現在のMySQL 8.2.0 および TiDBではINTERSECTとEXCEPTの両方をサポートしている。 P.185のクエリもそのまま動作する。
EXCEPTは差分検出に効果を発揮する。例えば前日との差分やバルク更新前後の確認などで有効だろうと思われる。
等しい部分集合を見つける
-
P.191 TiDBではHAVINGにGROUP BYを行った際の列名のエイリアスを利用することができる。この挙動は若干MySQLや標準SQLと異なる。
MySQLではGROUP BYの列名もエイリアスも両方HAVINGで利用できるが、TiDBではエイリアスをつけるとエイリアスを利用する必要があるようだ。
具体的には下記のようになる。SELECT SP1.sup as s1, SP2.sup as s2 FROM SupParts SP1, SupParts SP2 WHERE SP1.part = SP2.part AND SP1.sup < SP2.sup GROUP BY SP1.sup, SP2.sup HAVING COUNT(*) = (SELECT COUNT(*) FROM SupParts SP3 WHERE SP3.sup = s1) -- 書籍では SP1.sup AND COUNT(*) = (SELECT COUNT(*) FROM SupParts SP4 WHERE SP4.sup = s2); -- 書籍では SP2.sup
重複行を削除する高速なクエリ
-
P.193 自己相関の時同様、TiDBでは行番号として
_tidb_rowid
を利用できる。ただ、基本的には主キーが設定してあると思うので、そちらを利用するのが主になるだろう。SELECT * FROM Products WHERE _tidb_rowid IN ( SELECT _tidb_rowid FROM Products EXCEPT SELECT MAX(_tidb_rowid) FROM Products GROUP BY name, price);
10. SQLで数列を扱う
連番を作ろう
-
P.198 再帰WITH句を使った連番の作成方法についてはMySQLのドキュメントにも記載があり、次のように書ける。もちろん書籍記載のCROSS JOINを使った方法も動作する。
WITH RECURSIVE digit(n) AS ( SELECT 0 UNION ALL SELECT n + 1 FROM digit WHERE n < 99 ) SELECT * FROM digit;
欠番を全部埋める
-
P.202 先の例と同様に、再帰WITH句を利用して次のように書ける。MySQLではTABLE文も利用できる。ただ残念ながら再帰WITH句の再帰SELECT部分では集計関数は利用できないため、範囲を動的に設定するクエリは実行することができない。
WITH RECURSIVE Sequence(seq) AS ( SELECT 1 UNION ALL SELECT seq + 1 FROM Sequence WHERE seq < 12 ) TABLE sequence EXCEPT TABLE SeqTbl;
-- これは実行できない WITH RECURSIVE Sequence(seq) AS ( SELECT 1 UNION ALL SELECT seq + 1 FROM Sequence WHERE seq < (SELECT MAX(seq) FROM SeqTbl) ) TABLE sequence EXCEPT TABLE SeqTbl;
Discussion