サブクエリと共通テーブル式（CTE）

PostgreSQLでクエリ性能を改善する

Amy McCarty

Instructor

サブクエリについて

何か？

結合の代替
シンプルなクエリ

理由

1件の結果も返せる
読みやすい
結合に近いSQL記法

方法

SELECT、FROM、WHERE 句で使用

SELECT のサブクエリ

行	script_word	word_length
1	goat	4
2	goat	4
3	dog	3
15,782	...	...

行	english_word	word_length
1	goat	4
2	turkey	6
3	ant	3
171,476	...	...

SELECT のサブクエリ

SELECT AVG(word_length) AS avg_movie
  , (SELECT AVG(word_length) 
      FROM english_language) 
      AS avg_english
FROM MOVIE

avg_movie	avg_english
3	4.5

WHERE のサブクエリ

行	script_word	word_length
1	goat	4
2	goat	4
3	dog	3
15,782	...	...

行	english_word	word_length
1	goat	4
2	turkey	6
3	ant	3
171,476	...	...

WHERE のサブクエリ

SELECT AVG(word_length) AS avg_movie
FROM english_language
WHERE word IN 
  (SELECT DISTINCT word FROM movie)

avg_movie
3

FROM のサブクエリ

SELECT AVG(word_length) AS avg_movie 
FROM (SELECT * FROM movie)

可読性が下がる
クエリ計画の柔軟性を制限

共通テーブル式（CTE）について

何か？

結合の代替
一時結果を持つ単独のクエリ

理由

1件の結果も返せる
読みやすい
一時テーブルを作成

方法

WITH ステートメント

CTE の構造

WITH english_cte AS
(    
  SELECT word_length
      , COUNT(word) AS english_word_count
    FROM english_language 
    GROUP BY word_length
)

SELECT movie.word_length
  , COUNT(movie.word) AS movie_word_count
  , cte.english_word_count
FROM movie
INNER JOIN english_cte cte 
ON movie.word_length = cte.word_length
GROUP BY movie.word_length, cte.english_word_count

Passons à la pratique !

PostgreSQLでクエリ性能を改善する