テーブルを作らずにクエリをちょっと試す

あるクエリがどんな動きをするのかを手を動かしながら探索的に調べたいときに、テーブルを作らずにCTE(Common Table Expression)を使ってデータを作ればはかどるんじゃないだろうかと思った。

たとえばこれはSQLアンチパターンにでてくるクエリだけど。

SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';

これがどんな動きをするのかを、テーブルを作らずにCTEを使って実行してみる。まずそのまま実行すると、

=> SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
ERROR:  relation "bugs" does not exist
LINE 1: SELECT * FROM Bugs

当然エラーとなる。Bugsが無いのでWITH句を使って適当に作ろう。

=> WITH Bugs AS (
    VALUES (1)
)
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
ERROR:  relation "tags" does not exist
LINE 5:   JOIN Tags AS t1 USING (bug_id)

今度はTagsが無い。

=> WITH Bugs AS (
    VALUES (1)
),
Tags AS (
    VALUES (1)
)
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
ERROR:  column "bug_id" specified in USING clause does not exist in left table

両方のテーブルにbug_idが必要だ。

=> WITH Bugs(bug_id) AS (
    VALUES (1)
),
Tags(bug_id) AS (
    VALUES (1)
)
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
ERROR:  column t1.tag does not exist
LINE 10: WHERE t1.tag = 'printing' AND t2.tag = 'performance';

Tags.tagが必要だ。

=> WITH Bugs(bug_id) AS (
    VALUES (1)
),
Tags(bug_id, tag) AS (
    VALUES (1, 'printing')
)
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
 bug_id | tag | tag
--------+-----+-----
(0 rows)

OK。エラーはなくなった。後は値を工夫すればいい。

=> WITH Bugs(bug_id, summary) AS (
    VALUES
    (1, 'bug 1'),
    (2, 'bug 2'),
    (3, 'bug 3'),
    (4, 'bug 4')
),
Tags(bug_id, tag) AS (
    VALUES
    (1, 'printing'),
    (1, 'performance'),
    (2, 'printing'),
    (3, 'performance')
)
SELECT * FROM Bugs
  JOIN Tags AS t1 USING (bug_id)
  JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';
 bug_id | summary |   tag    |     tag
--------+---------+----------+-------------
      1 | bug 1   | printing | performance
(1 row)

データとクエリが1つになるので試行錯誤の過程を記録しやすそうだ。

あと、この方法はテーブルが存在する場合もWITH句を優先してくれるので、たとえば実際にはBugsが100万行くらいあるんだけど5行くらいで試したい、みたいなこともできる。