Redshift中动态生成特定月份（1月）滚动12个月数据的实现

首先明确动态目标日期的规则：

• 若当前运行月份为1月（当月数据未完整），取上一年的1月31日
• 若当前运行月份≥2月或为12月，取当年的1月31日

基于此逻辑，修改后的查询如下：

WITH target_date AS (
    SELECT
        CASE
            WHEN EXTRACT(MONTH FROM CURRENT_DATE) = 1
                THEN DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' + INTERVAL '1 month' - INTERVAL '1 day'
            ELSE DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day'
        END AS jan_last_day
)
SELECT
    ASIN,
    SUM(all_searches) AS jan_ttm_searches
FROM search_table, target_date
WHERE search_date BETWEEN DATEADD(month, -12, jan_last_day) AND jan_last_day
GROUP BY 1;

逻辑拆解 #

1. CTE target_date：动态计算目标1月的最后一天
   • DATE_TRUNC('year', CURRENT_DATE) 获取当前年份的1月1日（如2023-01-01）
   • 当前为1月时，减去1年得到上一年1月1日，再加1个月减1天得到上一年1月31日（2022-01-31）
   • 其他月份直接加1个月减1天，得到当年1月31日（如2023-01-31）
2. WHERE条件：用动态日期往前推12个月，锁定滚动12个月的时间范围
3. 聚合计算：直接SUM符合范围的搜索次数，无需嵌套CASE（WHERE已过滤目标区间）

场景验证 #

• 2022年12月运行：jan_last_day为2022-01-31，TTM范围是2021-01-31至2022-01-31，符合需求
• 2023年1月运行：jan_last_day为2022-01-31，TTM范围同上，符合需求
• 2023年2月运行：jan_last_day为2023-01-31，TTM范围是2022-01-31至2023-01-31，符合需求

之前方法失败原因 #

1. 嵌套聚合报错：MAX(search_date)是聚合函数，GROUP BY执行前无法计算，不能用于CASE WHEN的过滤逻辑
2. RUN_DATE方法：仅基于当前日期生成TTM，未定位到1月的最后一天，无法获取指定1月的滚动数据

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