跨年度7个月前瞻数据与2019年同月对比SQL实现方案

现有SQL跨年对比失效是两个逻辑问题导致的：

• 历史对比区间用了固定-36个月/-1095天的偏移规则，当7个月前瞻窗口跨年覆盖到次年1月时，2019年1月的出发日期不在这个偏移区间内，会被WHERE条件过滤掉
• 去年同期收入（PY_REVENUE_THIS_WEEK）的汇总仅判断年份为2019，没有做月份匹配，无法实现「窗口内1月数据仅对比2019年1月、12月数据仅对比2019年12月」的分月对应要求

1. 改写WHERE子句中2019年历史数据的过滤规则，不再用固定3年时间偏移，而是直接匹配和当前前瞻窗口月份完全对应的2019年日期段，保证跨年时2019年1月的数据能被正常纳入统计范围
2. 给PY_REVENUE_THIS_WEEK的汇总逻辑增加月份强匹配规则，确保每个月的当期值仅和2019年同月份的历史值计算，不会出现跨月混算

SELECT T.postdate_debug AS POSTDATE,
(CAST(T.DEPART_DATE AS DATE FORMAT 'MM') (char(2))) AS "Month",
SUM(CASE 
    WHEN T.dpt_date_yr_debug = '2019' 
    -- 按depart月份分组聚合时自动匹配同月份值，保证1月对1月、12月对12月
    THEN T.tot_REV 
    ELSE 0 
END) AS PY_REVENUE_THIS_WEEK,
SUM(CASE WHEN T.dpt_date_yr_debug IN (EXTRACT(YEAR FROM CURRENT_DATE)-1, EXTRACT(YEAR FROM CURRENT_DATE)) AND T.postdate_debug = CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)+1 THEN T.tot_REV ELSE 0 END) AS CY_REVENUE_THIS_WEEK,
SUM(CASE WHEN T.dpt_date_yr_debug IN (EXTRACT(YEAR FROM CURRENT_DATE)-1, EXTRACT(YEAR FROM CURRENT_DATE)) AND T.postdate_debug = CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)-6 THEN T.tot_REV ELSE 0 END) AS CY_REVENUE_LAST_WEEK,
SUM(CASE WHEN T.dpt_date_yr_debug IN (EXTRACT(YEAR FROM CURRENT_DATE)-1, EXTRACT(YEAR FROM CURRENT_DATE)) AND T.postdate_debug = CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)-13 THEN T.tot_REV ELSE 0 END) AS CY_REVENUE_TWO_WEEKS

FROM T
WHERE
(
-- 当期7个月前瞻窗口逻辑保持原有规则不变
(
(T.DEPART_DATE BETWEEN ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0) AND ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, +7) -1)
AND (T.postdate_debug=CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)+1 OR T.postdate_debug=CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)-6  OR T.postdate_debug=CURRENT_DATE - TD_DAY_OF_WEEK(CURRENT_DATE)-13)
)
OR
-- 重写2019年历史数据取数逻辑
(
T.dpt_date_yr_debug = '2019'
AND (
    -- 非跨年窗口：月份在起始月到结束月之间
    (EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 6)) >= EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0))
     AND EXTRACT(MONTH FROM T.DEPART_DATE) BETWEEN EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0)) AND EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 6))
    )
    OR
    -- 跨年窗口：月份大于等于起始月，或小于等于结束月（比如7月到次年1月，即取>=7或<=1的月份）
    (EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 6)) < EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0))
     AND (EXTRACT(MONTH FROM T.DEPART_DATE) >= EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0))
          OR EXTRACT(MONTH FROM T.DEPART_DATE) <= EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 6))
         )
    )
)
-- 保留原有3年前同周postdate的匹配规则
AND T.postdate_debug=CURRENT_DATE - 1095
)
)

• 无论前瞻窗口是否跨年，只要窗口包含1月，就会自动拉取2019年1月的对应数据做对比，不会出现历史数据被过滤的问题
• 按月份分组聚合时自然实现同月份匹配，不会把2019年其他月份的收入误算到当月对比值中
• 原有当期收入、周维度偏移的统计逻辑完全保留，不会影响现有指标的计算规则

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