by shigemk2

当面は技術的なことしか書かない

PrestoでWITHを複数使う

PrestoでWITHを複数使う

こういう書き方なんだけど

WITH a AS (
  SELECT *
  FROM a
), b AS (
  SELECT *
  FROM b
)
SELECT COUNT(1)
FROM a INNER JOIN b ON a.id = b.id

ドキュメントに書いてあるじゃんか。コンマで区切ろう。

WITH
  t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
  t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1
JOIN t2 ON t1.a = t2.a;

こういう書き方しようとしてエラーになるのは凡ミス。

WITH a AS (
  SELECT *
  FROM a
)
WITH b AS (
  SELECT *
  FROM b
)
SELECT COUNT(1)
FROM a INNER JOIN b ON a.id = b.id

12.16. SELECT — Presto 0.141t Documentation