SQL递归查询异常排查：按季度回溯3年汇总销售额问题

创建RECURSIVE SQL查询，回溯3年时间，每3个月为一个区间汇总销售额：依次汇总当前至前3个月、前3-6个月、前6-9个月等区间的sales price总和。现有一张包含sales price（销售额）、sales date（销售日期）字段的大表，需实现上述汇总逻辑。

WITH RECURSIVE PARAMS AS (SELECT '07/01/2023' SDATE, '05/01/23' EDATE), --(this is going to be put in to SSRS so I am hard coding SDATE to with) 

 CteRecursive(SDATE1, totpaid, datereceived) AS (
    Select *
    From (
    select (cast((SELECT SDATE FROM PARAMS) as TIMESTAMP)) SDATE2, sum(totpaid),Datereceived
    From adjudicationresult ar
      where Locations = 'GRE'
      GROUP BY Datereceived
     ) cte
      Where Datereceived <= (cast((SDATE2 ) as date)- (INTERVAL '1 Month' ))
        and Datereceived >= (cast((SDATE2 ) as date)- (INTERVAL '3 Month' ))
--**This is where I think I went wrong trying to get SDATE2 to update

  UNION ALL
    SELECT (SDATE1 - INTERVAL '3 MONTH') SDate3 ,totpaid, Datereceived
    FROM t
    WHERE SDATE1 > (cast((SELECT SDATE FROM PARAMS) as date)- (INTERVAL '12 Month' ))
    GROUP BY t.SDATE1, t.totpaid , t.Datereceived
)
SELECT Sdate1, (totpaid), datereceived
 FROM t
ORDER BY TotPaid;

注：该代码计划用于SSRS，因此硬编码了SDATE参数。

执行后SDATE1的迭代逻辑看似正确，但totpaid汇总结果异常：

• 同一datereceived（收款日期）对应多个不同的SDATE1重复出现（如2023-05-21对应2023-01-01、2023-04-01等多个SDATE1）；
• 结果按单日展示totpaid，未实现按3个月区间的汇总。

1. 同一日期对应多个SDATE1的原因 #

你的递归逻辑完全偏离了目标：初始查询按Datereceived分组，把每个单日的销售额汇总结果和固定的初始SDATE2绑定；递归阶段又把上一层的每条单日记录（含原totpaid和Datereceived）复制一遍，仅将SDATE1减去3个月。这就导致每一轮递归都会生成新的SDATE1与旧的单日记录关联，自然同一个收款日期会对应多个SDATE1。

2. 未按季度汇总的原因 #

从代码逻辑看，你自始至终都是按Datereceived（单日）分组计算销售额，根本没做区间汇总。递归部分没有重新计算对应区间的总和，只是把之前的单日数据重新绑定到新的SDATE1上，本质还是在处理单日数据，而非3个月区间的整体汇总。

正确思路是先通过递归生成所有需要的3个月区间的起止日期，再关联原表汇总对应区间的销售额：

WITH RECURSIVE PARAMS AS (
    SELECT 
        CAST('07/01/2023' AS DATE) AS end_date, -- 当前区间的终点
        CAST('07/01/2023' AS DATE) - INTERVAL '3 years' AS start_limit -- 回溯3年的截止点
),
date_intervals AS (
    -- 初始区间：最近3个月
    SELECT 
        end_date - INTERVAL '3 months' AS interval_start,
        end_date AS interval_end,
        end_date AS current_end
    FROM PARAMS
    UNION ALL
    -- 递归生成更早的3个月区间
    SELECT 
        current_end - INTERVAL '6 months' AS interval_start,
        current_end - INTERVAL '3 months' AS interval_end,
        current_end - INTERVAL '3 months' AS current_end
    FROM date_intervals
    WHERE interval_start >= (SELECT start_limit FROM PARAMS)
)
SELECT 
    interval_start,
    interval_end,
    COALESCE(SUM(ar.totpaid), 0) AS total_sales
FROM date_intervals di
LEFT JOIN adjudicationresult ar
    ON ar.Datereceived >= di.interval_start
    AND ar.Datereceived < di.interval_end
    AND ar.Locations = 'GRE'
GROUP BY di.interval_start, di.interval_end
ORDER BY di.interval_end DESC;

修正说明 #

1. 递归仅生成区间起止日期，避免了单日数据的重复关联；
2. 通过关联原表并按区间过滤日期，实现了每个3个月区间的销售额汇总；
3. 设置了回溯3年的终止条件，符合需求中的时间范围。

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