まとめ
Redshift クエリでは「WITH 句で『値の集合』を定義しておき、後段の IN 条件でそのまま再利用する」ということができません。どうしても先に条件判定用の値の集合を定義しておきたい場合は、 「ARRAY() で定義しておき、複数行に展開して利用する」 方法があります。
モチベーション
RedShift クエリで冒頭の WITH 句に集計の設定値を寄せたいことがあると思います。このとき、「｛りんご, みかん｝の売り上げを集計する」のように「対象値の集合」としての設定値も冒頭に記述しておきたいこともあると思います。
しかし、RedShift には「そのまま IN 条件に渡せる値の集合」を予め定義しておく仕組みはありません。そのため、このようなケースでは 「ARRAY() で (SUPER 型として) 定義しておき、(SUPER 型のネスト解除構文で) 複数行に展開して利用する」 のが一つの方法です。
-
ARRAY()(SUPER 型) で値の集まりを定義しておくことはできますが、これはそのままでは「A 列の値がこの中に含まれるか」という判定はできません。 - もし｛りんご, みかん｝を今後の集計でもずっと使うなら「果物マスタテーブル」を作成しておいた方がよいと思います。
冒頭に全部寄っていない例
対象期間の ('りんご', 'みかん') の売り上げだけを抽出したいとします。ただ、以下のクエリでは ('りんご', 'みかん') が冒頭に寄せられていません。
WITH conf AS ( -- 集計対象の定義
SELECT
20251201 AS lb, -- 開始日
20251210 AS ub -- 終了日
),
data AS ( -- テストデータ
SELECT
v[0] AS date,
v[1]::TEXT AS user_name,
v[2]::TEXT AS item_name,
v[3] AS quantity
FROM (
SELECT ARRAY(
ARRAY(20251130, '太郎', 'りんご', 2000),
ARRAY(20251201, '太郎', 'みかん', 1000),
ARRAY(20251203, '次郎', 'りんご', 4000),
ARRAY(20251210, '三郎', 'りんご', 2000),
ARRAY(20251211, '三郎', 'みかん', 2000),
ARRAY(20251212, '三郎', 'チョコ', 3000)) AS arr
) AS t, t.arr AS v
)
SELECT
d.date,
d.user_name,
d.item_name,
d.quantity
FROM data d
CROSS JOIN conf c
WHERE
d.date BETWEEN c.lb AND c.ub
AND d.item_name IN ('りんご', 'みかん') -- 対象品目集合
実行結果は以下になり、対象期間の対象品目の売り上げは抽出できます。
date,user_name,item_name,quantity
20251201,太郎,みかん,1000
20251203,次郎,りんご,4000
20251210,三郎,りんご,2000
冒頭に全部寄せた例
以下のクエリでは ('りんご', 'みかん') も冒頭に寄せています。ただし、 ARRAY('りんご', 'みかん') のままでは判定に利用できないので、PartiQL 構文 で複数行に展開しています。
WITH conf AS ( -- 集計対象の定義
SELECT
20251201 AS lb, -- 開始日
20251210 AS ub, -- 終了日
ARRAY('りんご', 'みかん') AS arr_fruits -- 対象品目集合
),
tbl_fruits AS ( -- 対象品目集合を展開
SELECT v::TEXT AS item_name
FROM conf c, c.arr_fruits v
),
data AS ( -- テストデータ
SELECT
v[0] AS date,
v[1]::TEXT AS user_name,
v[2]::TEXT AS item_name,
v[3] AS quantity
FROM (
SELECT ARRAY(
ARRAY(20251130, '太郎', 'りんご', 2000),
ARRAY(20251201, '太郎', 'みかん', 1000),
ARRAY(20251203, '次郎', 'りんご', 4000),
ARRAY(20251210, '三郎', 'りんご', 2000),
ARRAY(20251211, '三郎', 'みかん', 2000),
ARRAY(20251212, '三郎', 'チョコ', 3000)) AS arr
) AS t, t.arr AS v
)
SELECT
d.date,
d.user_name,
d.item_name,
d.quantity
FROM data d
CROSS JOIN conf c
JOIN tbl_fruits f ON d.item_name = f.item_name
WHERE d.date BETWEEN c.lb AND c.ub
このクエリも実行結果は先と同じになります。
date,user_name,item_name,quantity
20251201,太郎,みかん,1000
20251203,次郎,りんご,4000
20251210,三郎,りんご,2000
参考文献
上記の Stack Overflow 記事は「RedShift クエリで VALUES() によって複数行を定義できないのか」というものです。RedShift では INSERT 時に VALUES() によって複数行を挿入することはできますが(参考)、クエリ内で VALUES() によって複数行を定義することはできません。
そこで代替手段として 「1 行ずつ定義して UNION ALL で複数行にする」か「ARRAY() を定義して展開して複数行にする」方法が示されています。
この記事は ARRAY() 方式を採用していることになりますが、もしコンフィグ用 WITH 句が複数にまたがってもよいとか、設定値が｛りんご, みかん｝しかないとかの場合は、以下の UNION ALL 式の記述も採用できると思います。
WITH conf AS ( -- 集計対象の定義
SELECT
20251201 AS lb, -- 開始日
20251210 AS ub -- 終了日
),
tbl_fruits AS ( -- くだものテーブル
SELECT 'りんご' AS item_name UNION ALL
SELECT 'みかん' AS item_name
),
ただ、先の Stack Overflow 記事には「UNION ALL 式ではクエリにオーバーヘッドがある」と書いている回答者がいます。実際 UNION ALL 式の方がクエリプランが肥大化しますが、それでクエリがどれほどオーバーヘッドするのかは私が詳しくないためよくわかっていません。ご存じの方がいたら教えてください。