📖

達人に学ぶSQL徹底指南書をTiDB Serverlessで写経する(2)

2024/01/16に公開

Database

tech

写経その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のドキュメントにも記載があり、次のように書ける。
```
WITH RECURSIVE digit(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM digit WHERE n < 99
  )
SELECT * FROM digit;
```
もちろん書籍記載のCROSS JOINを使った方法も動作する。

欠番を全部埋める

P.202 先の例と同様に、再帰WITH句を利用して次のように書ける。MySQLではTABLE文も利用できる。

WITH RECURSIVE Sequence(seq) AS (
    SELECT 1
    UNION ALL
    SELECT seq + 1 FROM Sequence WHERE seq < 12
  )
TABLE sequence
EXCEPT
TABLE SeqTbl;

ただ残念ながら再帰WITH句の再帰SELECT部分では集計関数は利用できないため、範囲を動的に設定するクエリは実行することができない。

-- これは実行できない
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;