subqueryで複数項目を取得したい場合(PostgreSQLでLATERALを使う)

SQLで複雑なクエリを作っていると、メイン・テーブルの1行に対して、サブ・テーブルの集計した値を使用したい場合に、

SELECT
  main.id,
  main.name,
  AVG(sub.value) as average_value
FROM main_table main
INNER JOIN sub_table sub
ON main.id = sub.id
GROUP BY
  main.id,
  main.name

などと書いたりすると思います。

このときに、メイン・テーブルから取ってくる項目が多い場合

-- SQL1
SELECT
  main.id,
  main.name,
  main.column1,
  main.column2,
  main.column3,
  main.column4,
  main.column5,
  main.column6,
  main.column7,
  main.column8,
  main.column9,
  AVG(sub.value) as average_value
FROM main_table main
INNER JOIN sub_table sub
ON main.id = sub.id
GROUP BY
  main.id,
  main.name,
  main.column1,
  main.column2,
  main.column3,
  main.column4,
  main.column5,
  main.column6,
  main.column7,
  main.column8,
  main.column9

など、集計関数があるせいでGROUP BY項目が多くなってしまい、またSELECT項目を増やすたびにGROUP BY項目を追加しないといけなくなります。

それを嫌がり

SELECT
  main.id,
  main.name,
  (
    SELECT AVG(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as average_value
FROM main_table main

とSELECT句のsubqueryとして書いた場合、集計項目が多くなると、

-- SQL2
SELECT
  main.id,
  main.name,
  (
    SELECT AVG(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as average_value,
  (
    SELECT MAX(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as max_value,
  (
    SELECT MIN(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as min_value,
  (
    SELECT SUM(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as sum_value,
  (
    SELECT COUNT(sub.value) 
    FROM sub_table sub 
    WHERE main.id = sub.id
  ) as count_value
FROM main_table main

のように、SELECT句の見通しがとても悪くなります。 こんなとき、LATERAL句(PostgreSQL9.3以上)を使うと見通しの良いSQLを作ることができます。

LATERAL句はテーブル(サブクエリ)のJOINとして働くのですが、結合対象のサブクエリ内で結合元の値を指定することができ、その値が遅延評価されます。(文章だけ見ても、意味が分からない…)

具体的には、

SELECT
  main.id,
  main.name,
  sub.average_value,
  sub.max_value,
  sub.min_value,
  sub.sum_value,
  sub.count_value
FROM main_table main
LEFT JOIN LATERAL (
  SELECT
    AVG(sub.value) as average_value,
    MAX(sub.value) as max_value,
    MIN(sub.value) as min_value,
    SUM(sub.value) as sum_value,
    COUNT(sub.value) as count_value
  FROM sub_table sub 
  WHERE main.id = sub.id
) as sub
ON TRUE

というSQLで、サブクエリ内のWHERE main.id = sub.idが、メイン・テーブルの1行ごとに評価され、集計関数の値を取得することができます。実行計画的にはSQL2と同等になるようです。

この程度であれば

SELECT
  main.id,
  main.name,
  sub.average_value,
  sub.max_value,
  sub.min_value,
  sub.sum_value,
  sub.count_value
FROM main_table main
LEFT OUTER JOIN (
  SELECT
    sub.id,
    AVG(sub.value) as average_value,
    MAX(sub.value) as max_value,
    MIN(sub.value) as min_value,
    SUM(sub.value) as sum_value,
    COUNT(sub.value) as count_value
  FROM sub_table sub 
  GROUP BY sub.id
) as sub
ON main.id = sub.id

と、サブ・テーブルをまるっとidのGROUP BYで集計してしまうこともできますが、もう少し複雑なSQLでも見通しを良くしようと思ったら、LATERALが有用なことがあると思います。