【Athena】With句を使って一時テーブルを作成してSQLを簡略化させる

複雑なサブクエリ

AthenaでSQLを書いてデータを抽出しているとどうしても複雑なSQLになることが多いです。何度もつかうサブクエリに関してはviewテーブルを作成することも考えられますが、一時的に使うものまでviewテーブルを作成するのは面倒に感じられます。

With句を使って一時テーブルを作成する

With句のPrestodbのドキュメントはこちらです。

例えば以下のSQLを作成したとします。

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

サブクエリで取得しているテーブルをWith句で書くと処理がSQLスッキリします。

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
INNER JOIN t2 ON t1.a = t2.a;

スッキリするだけでなく、複数回同じサブクエリが登場するときはそのまま使いまわしも可能、サブクエリの条件が変わったときもWith句の変更のみで済むため、変更漏れのリスクが避けられます。
With句は非常に便利なので使えることだけでも覚えておくと良いと思います。