こんにちは。データサイエンティスト兼、データ基盤エンジニアのshobyです。

皆さんはSQLを書く際に、どの程度サブクエリを使っていますか？ TVISION INSIGHTSのデータチームでは、RedShiftを使用した分析のために複雑なSQLを書く必要があり、サブクエリを多用しています。

今回は、RedShiftにおいて、サブクエリの可読性を向上させるためのWITH句をご紹介します。

WITH句とは

WITH句とは、サブクエリによる一時的なデータセット作成部分を、切り出してクエリ内で共有利用することができる句です。共通テーブル式または、CTE（Common Table Expressions）とも呼ばれます。*1

docs.aws.amazon.com

実際のクエリは以下のような形です。

WITH unique_ids AS (
  SELECT DISTINCT id
  FROM logs
)
SELECT *
FROM unique_ids;

このクエリの unique_ids はこのクエリ内からしか使えず、他のクエリに影響を与えることがありません。

TEMP TABLEを作る場合と可読性はほぼ変わりませんが、セッションが有効な間は生き続けるTEMP TABLEと比べ、生存期間が短く、影響範囲が狭いという違いがあります。*2

他のクエリへの影響を気にせず、可読性の高い形でクエリを書けることがメリットです。

分析SQLにおけるWITH句の活用

アプリケーション用のSQLと比べ、分析用SQLではサブクエリを組み合わせた複雑なSQLが必要になるケースが多く発生します。

そのようなサブクエリを多用するSQLにおいて、WITH句は効果を発揮します。

例えば、ログから条件Aに合致するIDと、条件Bに合致するIDを取り出し、2つのグループに共通するIDを抽出したい、という場合を考えます。

まず、サブクエリの場合は、以下のようになります。

SELECT *
FROM (
  SELECT *
  FROM (
    SELECT DISTINCT id
      FROM logs
    WHERE condition = 'A'
  ) AS a_group_ids
  INNER JOIN (
    SELECT DISTINCT id
      FROM logs
    WHERE condition = 'B'
  ) AS b_group_ids
  ON a_group_ids.id = b_group_ids.id
) AS common_ids;

だいぶつらいですね...

一方、WITH句を使った場合は、以下のように書くことができます。

WITH a_group_ids AS (
  SELECT DISTINCT
    id
  FROM logs
  WHERE condition = 'A'
), b_group_ids AS (
  SELECT DISTINCT
    id
  FROM logs
  WHERE condition = 'B'
), common_ids AS (
  SELECT id
  FROM a_group_ids
  INNER JOIN b_group_ids ON a_group_ids.id = b_group_ids.id
)
SELECT *
FROM common_ids;