SQL Server中按日统计Well_count累计值的技术实现问询

针对你需要基于日期序列按日统计全年Well_count累计值的需求，推荐使用日期序列CTE + 窗口函数的组合方案，相比递归CTE中嵌套聚合查询的方式，性能提升显著。

实现代码 #

DECLARE @SD DATE = '2023-01-01'; -- 统计起始日期
DECLARE @ED DATE = '2023-12-31'; -- 统计结束日期

WITH cte_dates AS (
    -- 生成完整的日期序列（复用你已有的递归CTE逻辑）
    SELECT @SD AS Date_
    UNION ALL
    SELECT DATEADD(day, 1, Date_)
    FROM cte_dates
    WHERE Date_ < @ED
),
cte_release_totals AS (
    -- 先按释放日期汇总Well_count，减少后续关联的数据量
    SELECT 
        Rig_release_date,
        SUM(Well_count) AS Daily_Well_Count
    FROM Table_1
    GROUP BY Rig_release_date
)
SELECT 
    d.Date_ AS [Date],
    -- 用滑动窗口函数计算累计值：所有早于当前日期的Well_count总和
    COALESCE(SUM(rt.Daily_Well_Count) OVER (ORDER BY d.Date_), 0) AS [Well Count]
FROM cte_dates d
LEFT JOIN cte_release_totals rt 
    ON rt.Rig_release_date < d.Date_
ORDER BY d.Date_
OPTION (MAXRECURSION 400); -- 递归次数上限，全年365天足够

方案优势 #

• 性能更优：避免了递归CTE中每次迭代都执行全表聚合查询的低效操作（原思路会导致O(n*m)的时间复杂度，n为日期数，m为Table_1行数），本方案仅需一次关联+一次窗口计算，时间复杂度为O(n+m)
• 逻辑清晰：先汇总释放日期的Well_count总量，再与日期序列关联，最后通过窗口函数SUM() OVER(ORDER BY Date_)实现累计统计，逻辑直观易维护
• 兼容性好：支持SQL Server 2012及以上版本（窗口函数从2012开始支持）

简化版代码（若Table_1无重复Rig_release_date） #

如果你的Table_1中每个Rig_release_date仅对应一条记录，可以省略中间的汇总CTE，直接关联计算：

DECLARE @SD DATE = '2023-01-01';
DECLARE @ED DATE = '2023-12-31';

WITH cte_dates AS (
    SELECT @SD AS Date_
    UNION ALL
    SELECT DATEADD(day, 1, Date_)
    FROM cte_dates
    WHERE Date_ < @ED
)
SELECT 
    d.Date_ AS [Date],
    COALESCE(SUM(t.Well_count) OVER (ORDER BY d.Date_), 0) AS [Well Count]
FROM cte_dates d
LEFT JOIN Table_1 t 
    ON t.Rig_release_date < d.Date_
ORDER BY d.Date_
OPTION (MAXRECURSION 400);

结果验证 #

针对你提供的测试数据，上述代码会返回符合预期的结果：

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