WITH句で複雑な分析用SQLの可読性を上げる(RedShift)

こんにちは。データサイエンティスト兼、データ基盤エンジニアの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;

クエリを上から読み進めるだけでクエリ全体の構造が把握しやすくなり、可読性が上がったことがお分かりいただけるかと思います。

以上のように、WITH句を活用することで、複雑なクエリを構造化し、可読性を向上することができます。

TVISION INSIGHTSでの活用事例

TVISION INSIGHTSではテレビの視聴傾向を特定するため、複雑な分析用クエリを書く必要が生じる場合があります。

そのため、社内コーディング規約として、サブクエリを使う際にはWITH句を必須とし、 チーム全体で可読性の高いSQLを書けるようにしています。

TVISION INSIGHTSのコーディング規約の概要については、以下の記事でも紹介しましたので、ご参照ください。

tech.tvisioninsights.co.jp

まとめ

分析用SQLはアプリケーション用のSQLと比べ、複雑なクエリを書く必要が生じる場合があります。

その際に、WITH句を用いてサブクエリを記述することで、クエリを構造化し、可読性を高めることができます。

TVISION INSIGHTSでは、社内コーディング規約として、サブクエリを使う際にはWITH句を必須とし、 可読性の高いSQLを書けるようにしています。

*1:MySQLに使っている方は馴染みがないかもしれませんが、MySQL でも8.0から実装されています。

*2:プログラミング言語で例えるのであれば、TEMP TABLEはグローバル変数、WITHはローカル変数のような立ち位置です。