[Athena] CROSS JOIN を使って疑似的な変数を宣言する

例

Amazon Athena でログデータを分析する際、スキャン量を制限するためにパーティション列で範囲を絞り込むための SQL を発行したいケースを考えます。以下の例では、UTC の日時を 2025/11/12/15, 2025/11/12/16, ..., 2025/11/13/15, ... という形式で表現する dt 列がパーティション列に該当します。

WITH
  request_uuids AS (
    SELECT DISTINCT
      request_uuid AS uuid
    FROM
      logs
    WHERE
      dt >= DATE_FORMAT((timestamp '2025-11-13 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H')
      AND dt < DATE_FORMAT((timestamp '2025-11-14 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H')
      AND message LIKE '%Processing by YoyosController#index%'
  )

SELECT
  logs.time,
  CAST(REGEXP_EXTRACT(logs.message, 'in (\d+(\.\d+)?)ms', 1) AS INTEGER) AS response_time_ms,
  logs.message
FROM
  logs
INNER JOIN
  request_uuids
  ON request_uuids.uuid = logs.request_uuid
WHERE
  dt >= DATE_FORMAT((timestamp '2025-11-13 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H')
  AND dt < DATE_FORMAT((timestamp '2025-11-14 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H')
  AND logs.message LIKE '%Completed%'
;

この例では、WITH 句内の request_uuids の定義と、メインの SELECT 文の WHERE 句内の両方で dt 列に対する同じ日付範囲の絞り込み条件が記述されています。このままだと日付範囲を変更する際に 2 箇所を修正する必要があり、手間がかかる上に修正漏れのリスクもあります 😢

改善方法: CROSS JOIN を使う

以下のように、まず WITH 句で日付範囲だけを持つ dt_range という名前の CTE (共通テーブル式) を定義します。

この dt_range は FROM 句を持たないため、常に 1 行だけの結果を返します。そして、この dt_range を日付範囲を使いたい FROM 句で CROSS JOIN (デカルト積) します。dt_range が 1 行であるため、CROSS JOIN を行っても元のテーブルの行数は増えず、単に dt_range で定義した列 (utc_start, utc_end) が追加された状態になります。

WITH
  dt_range AS (
    SELECT
      DATE_FORMAT((timestamp '2025-11-13 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H') AS utc_start,
      DATE_FORMAT((timestamp '2025-11-14 00:00:00 Asia/Tokyo') AT TIME ZONE 'UTC', '%Y/%m/%d/%H') AS utc_end
  ),
  request_uuids AS (
    SELECT DISTINCT
      request_uuid AS uuid
    FROM
      logs
    CROSS JOIN
      dt_range
    WHERE
      dt >= dt_range.utc_start
      AND dt < dt_range.utc_end
      AND message LIKE '%Processing by YoyosController#index%'
  )

SELECT
  logs.time,
  CAST(REGEXP_EXTRACT(logs.message, 'in (\d+(\.\d+)?)ms', 1) AS INTEGER) AS response_time_ms,
  logs.message
FROM
  logs
INNER JOIN
  request_uuids
  ON request_uuids.uuid = logs.request_uuid
CROSS JOIN
  dt_range
WHERE
  dt >= dt_range.utc_start
  AND dt < dt_range.utc_end
  AND logs.message LIKE '%Completed%'
;

これにより日付範囲の定義が一箇所にまとまり、あたかも変数のように扱えて便利です 😉

参考

• PostgreSQL 17.6 文書 > 第 7 章 問い合わせ > 7.2. テーブル式¹

1. Athena ではなく PostgreSQL のドキュメントですが、CROSS JOIN の基本的な動作についての説明が記載されています。なお、Athena は Presto/Trino ベースですが、CROSS JOIN の挙動は標準 SQL に準拠しています。 ↩