Aqua Data Studio连Redshift计算3日移动平均报interval语法错误

• 语法不兼容：Amazon Redshift是基于PostgreSQL 8.0.2分支深度改造的云数仓引擎，并未继承高版本PostgreSQL的窗口函数时间间隔范围特性，其窗口RANGE子句仅支持数值类型的偏移计算，不支持INTERVAL关键词定义时间窗口。你尝试的几种带INTERVAL '3 DAYS'的写法是原生PostgreSQL、BigQuery、Spark SQL等引擎支持的语法，在Redshift中运行会直接抛出syntax error at or near 'interval'错误。
• 初始SQL存在逻辑错误：原SQL中PARTITION BY CUSTOMER_ID, AUTH_DATE的写法完全无法实现跨天计算——分区字段加入AUTH_DATE后，每个窗口分区仅包含单个客户单日的交易数据，无论窗口范围如何配置，都无法拉取其他日期的记录。另外你尝试的3 PRECEDING默认是行级偏移逻辑，代表取当前行往前数3行，而非3个自然日的范围，遇到单日多笔交易、交易日期断档的场景，计算结果会完全偏离3日移动平均的业务定义。
• 笔误问题：原SQL中表名拼写错误，CUTOMER_TABLE应为CUSTOMER_TABLE。

根据你使用的Redshift版本选择对应写法即可：

方法1：全版本通用写法（无版本限制，逻辑最稳定） #

先按客户+交易日期聚合出单日交易总额，再通过自关联匹配每个日期往前3天的所有日度记录计算均值，所有Redshift版本均可正常运行：

WITH daily_trans AS (
    -- 先聚合每个客户每日的总交易金额
    SELECT
        CUSTOMER_ID,
        AUTH_DATE,
        SUM(AMOUNT) AS DAY_TOTAL_AMT
    FROM CUSTOMER_TABLE
    GROUP BY CUSTOMER_ID, AUTH_DATE
)
SELECT
    a.CUSTOMER_ID,
    a.AUTH_DATE,
    AVG(b.DAY_TOTAL_AMT) AS MA_3_DAY
FROM daily_trans a
LEFT JOIN daily_trans b
    ON a.CUSTOMER_ID = b.CUSTOMER_ID
    -- 匹配当前日期往前3天至当日的所有日度数据
    AND b.AUTH_DATE BETWEEN DATEADD(day, -3, a.AUTH_DATE) AND a.AUTH_DATE
GROUP BY a.CUSTOMER_ID, a.AUTH_DATE
;

如果需要保留每笔交易明细行的3日均值，不需要做日度聚合，去掉CTE里的聚合逻辑、调整关联条件为明细日期范围匹配即可。

方法2：窗口函数简化写法（适用于Redshift 1.0.25762及以上版本、Redshift Serverless） #

利用Redshift RANGE子句支持数值偏移的特性，先将日期转换为距固定基准日的天数整数值，基于该数值设置3个单位的偏移范围，即可实现时间维度的RANGE窗口计算，性能比自关联写法更好：

WITH daily_trans AS (
    SELECT
        CUSTOMER_ID,
        AUTH_DATE,
        SUM(AMOUNT) AS DAY_TOTAL_AMT,
        -- 将日期转换为距1970-01-01的天数整数值，供窗口范围匹配使用
        DATEDIFF(day, '1970-01-01', AUTH_DATE) AS DATE_SERIAL
    FROM CUSTOMER_TABLE
    GROUP BY CUSTOMER_ID, AUTH_DATE, DATEDIFF(day, '1970-01-01', AUTH_DATE)
)
SELECT
    CUSTOMER_ID,
    AUTH_DATE,
    AVG(DAY_TOTAL_AMT) OVER(
        PARTITION BY CUSTOMER_ID -- 分区仅需按客户维度，不要加入AUTH_DATE
        ORDER BY DATE_SERIAL
        -- 基于天数值的范围匹配，值差≤3即为3天范围内的数据，注意是RANGE不是ROWS
        RANGE BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS MA_3_DAY
FROM daily_trans
;

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