Redshift SQL中如何实现正确的滚动环比日期区间查询？

原查询说明 #

原SQL用于获取当年1月1日至最近完整季度最后一天的记录：

SELECT *
FROM table
WHERE date_value BETWEEN DATE_TRUNC('year',getdate()) AND DATE_TRUNC('quarter',dateadd(day,-1,getdate()));

需求目标 #

生成环比查询，返回前一年对应原区间的镜像时间段：比如原查询返回2022年1-6月记录时，环比需返回2021年7-12月记录。

问题分析 #

你当前的写法仅将原查询的日期整体减1年，导致返回前一年的同时间段（如2021年1-6月），未实现镜像区间的需求。

正确实现方案 #

方案1：使用CTE清晰定义日期参数

WITH date_params AS (
    SELECT 
        DATE_TRUNC('year', getdate()) AS current_year_start,
        DATE_TRUNC('quarter', dateadd(day, -1, getdate())) AS last_complete_quarter_end
)
SELECT *
FROM table, date_params
WHERE date_value 
    -- 环比区间起始：前一年年初 + 原区间结束月份数
    BETWEEN DATEADD(month, DATE_PART('month', last_complete_quarter_end), DATE_TRUNC('year', DATEADD(year, -1, getdate())))
    -- 环比区间结束：当年年初前一天（即前一年年末）
    AND DATEADD(day, -1, current_year_start);

方案2：直接嵌入计算逻辑（无CTE）

SELECT *
FROM table
WHERE date_value 
    BETWEEN DATEADD(month, DATE_PART('month', DATE_TRUNC('quarter', dateadd(day, -1, getdate()))), DATE_TRUNC('year', DATEADD(year, -1, getdate())))
    AND DATEADD(day, -1, DATE_TRUNC('year', getdate()));

逻辑解释 #

1. 先计算last_complete_quarter_end：最近完整季度的最后一天（如2022年6月30日）
2. 环比起始日期：取前一年的年初（2021-01-01），加上原区间结束日期的月份数（6个月），得到2021-07-01
3. 环比结束日期：当年年初（2022-01-01）减1天，即前一年年末（2021-12-31）
   通过这种方式，精准匹配前一年对应原区间的镜像时间段。

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