AWS Redshift下统计客户物料对3年出现次数设置状态标识方案咨询

你原有方案的问题在于使用current_date作为时间基准，统计的是从当前时间往前3年的固定窗口数据，无法满足每行以自身日期为基准的滚动3年窗口计算需求，使用Redshift的RANGE类型窗口函数可以直接实现该需求。

如果你的Redshift版本支持INTERVAL类型的窗口RANGE限定，推荐使用如下写法，时间计算精度更高：

SELECT
    -- 如果date字段是字符串格式，需先转成日期类型：TO_DATE("date", 'MM/DD/YYYY') AS "date"
    "date",
    "customer name",
    material,
    CASE
        WHEN COUNT(*) OVER (
            -- 按客户+物料分组，仅同组内判断历史记录
            PARTITION BY "customer name", material
            -- 分组内按日期升序排序
            ORDER BY "date"
            -- 窗口范围：当前行日期往前3年 ~ 当前行日期前1天，排除当前行本身
            RANGE BETWEEN INTERVAL '3 years' PRECEDING AND INTERVAL '1 day' PRECEDING
        ) >= 1 THEN 'Yes'
        ELSE 'No'
    END AS status
FROM your_table_name
-- 可选排序，和示例输出顺序对齐
ORDER BY "customer name", "date" DESC;

低版本兼容写法 #

如果你的Redshift版本不支持INTERVAL类型的RANGE窗口，可以将日期转换为距离固定日期的天数后做数值判断，适合对闰年精度要求不高的场景：

SELECT
    "date",
    "customer name",
    material,
    CASE
        WHEN COUNT(*) OVER (
            PARTITION BY "customer name", material
            ORDER BY DATEDIFF(day, '1970-01-01'::date, "date")
            -- 1095=3*365，对应3年的天数范围
            RANGE BETWEEN 1095 PRECEDING AND 1 PRECEDING
        ) >= 1 THEN 'Yes'
        ELSE 'No'
    END AS status
FROM your_table_name
ORDER BY "customer name", "date" DESC;

• 分区规则：按customer name+material分组，仅判断同一客户同一物料的历史出现记录
• 窗口范围：以当前行的日期为基准，向前回溯3年，且排除当前行本身，避免统计到当前记录
• 状态判断：窗口范围内统计到的记录数大于等于1，说明3年内有其他出现记录，标识为Yes，否则为No，完全匹配你提供的示例输出。

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