如何在AWS Redshift查询中声明并使用变量替代硬编码日期

Redshift里普通SQL查询不能直接用DECLARE（那是存储过程专用语法），你之前的CTE写法只是定义了参数却没关联到主查询，所以无效。下面给几种实用的解决办法：

方法1：关联CTE复用参数 #

把定义参数的CTE和主查询做交叉连接，就能在整个查询里引用参数值：

WITH parameter_date AS (
    SELECT '2024-01-01'::DATE AS param_date
)
INSERT into segmentation (import_date, id, orders, qty, value)
SELECT
    pd.param_date AS import_date,
    td.id,
    COUNT(DISTINCT td.date) AS total_orders,
    SUM(td.qty) AS total_qty,
    SUM(td.amount) AS total_value
FROM
    customer_actions td
CROSS JOIN parameter_date pd
WHERE
    td.date_trading_nk <= pd.param_date
    -- 这里可以继续添加其他使用pd.param_date的条件
GROUP BY
    pd.param_date, td.id;

方法2：临时表存储参数 #

适合需要在多个查询里复用同一个参数的场景，创建临时表存日期后反复引用：

-- 创建临时表存储参数
CREATE TEMP TABLE param_table AS
SELECT '2024-01-01'::DATE AS param_date;

-- 插入查询中引用参数
INSERT into segmentation (import_date, id, orders, qty, value)
SELECT
    pt.param_date AS import_date,
    td.id,
    COUNT(DISTINCT td.date) AS total_orders,
    SUM(td.qty) AS total_qty,
    SUM(td.amount) AS total_value
FROM
    customer_actions td
CROSS JOIN param_table pt
WHERE
    td.date_trading_nk <= pt.param_date
GROUP BY
    pt.param_date, td.id;

方法3：会话级变量（SET + CURRENT_SETTING） #

设置会话级变量，用current_setting获取值时注意做类型转换：

-- 设置会话变量
SET param_date = '2024-01-01';

-- 插入查询中引用参数
INSERT into segmentation (import_date, id, orders, qty, value)
SELECT
    current_setting('param_date')::DATE AS import_date,
    td.id,
    COUNT(DISTINCT td.date) AS total_orders,
    SUM(td.qty) AS total_qty,
    SUM(td.amount) AS total_value
FROM
    customer_actions td
WHERE
    td.date_trading_nk <= current_setting('param_date')::DATE
GROUP BY
    td.id;

内容的提问来源于stack exchange，提问作者Highland