SQL如何让Running Total包含无交易ppid记录并延续累计值

需求说明 #

需要确保累计求和结果集中每个ppid都有对应记录：当某个plan_id下无特定ppid的交易时，需补全该记录，将Assets设为0，同时累计值（rt）延续之前的数值。例如plan_id 00-PEB17A无ppid 388的交易，导致该记录缺失，需按需求补全。

初始实现代码 #

最初通过CTE分组+OVER子句计算累计求和，但无法生成缺失的ppid记录：

WITH cte AS
(
  SELECT plan_id, ppid,
    SUM(amount) AS Assets
  FROM #assets
  GROUP BY plan_id, ppid
  --ORDER BY plan_id, ppid ASC
)
SELECT *,
  SUM(cte.Assets) OVER(PARTITION BY cte.plan_id ORDER BY cte.ppid) AS rt
FROM cte

存在的问题 #

当前结果集缺少无交易的ppid记录，无法满足全量ppid展示、无交易时Assets为0且累计值延续的需求。

自定义解决方案代码 #

通过关联数字表（numbers table）生成全量组合，补全缺失记录，原实现代码如下：

WITH a AS 
(
SELECT *
FROM #assets
CROSS JOIN #numbers
),
combine AS
(
    SELECT a.plan_id, a.n
    FROM a
    RIGHT OUTER JOIN #numbers AS n
        ON n.n = a.ppid
),
dist AS
(
    SELECT DISTINCT combine.plan_id, combine.n
    FROM combine
),
new_asset_list AS
(
    SELECT dist.plan_id, dist.n, ISNULL(ass.amount, 0) AS assets
    FROM dist
        LEFT OUTER JOIN #assets AS ass
            ON ass.plan_id = dist.plan_id
                AND ass.ppid = dist.n
),
grouped AS
(
    SELECT nal.plan_id, nal.n, SUM(nal.assets) AS Assets
    FROM new_asset_list AS nal
    GROUP BY nal.plan_id, nal.n
)
SELECT *,
    SUM(g.Assets) OVER(PARTITION BY g.plan_id ORDER BY g.n) AS rt
FROM grouped AS g

数字表创建语句 #

用于提供所有需要覆盖的ppid值：

CREATE TABLE #numbers
(
    n INT
)
INSERT INTO #numbers
(
    n
)
VALUES ( 386 ), ( 387 ), ( 388 ), ( 389 )

方案反馈与优化 #

原方案合理性 #

原思路正确：通过生成plan_id与ppid的全量组合，再左关联资产表补全缺失数据，最终计算累计值，能够满足需求。但存在冗余CTE步骤，可以简化。

优化后的简化代码 #

直接生成所有plan_id与ppid的全量组合，减少不必要的中间步骤，逻辑更清晰：

WITH full_plan_ppid AS (
    -- 生成所有plan_id与所有ppid的全量组合
    SELECT DISTINCT a.plan_id, n.n AS ppid
    FROM #assets a
    CROSS JOIN #numbers n
),
asset_summary AS (
    -- 关联资产表，无交易时Assets设为0
    SELECT 
        f.plan_id, 
        f.ppid,
        ISNULL(SUM(a.amount), 0) AS Assets
    FROM full_plan_ppid f
    LEFT JOIN #assets a
        ON f.plan_id = a.plan_id 
        AND f.ppid = a.ppid
    GROUP BY f.plan_id, f.ppid
)
-- 计算累计求和
SELECT 
    plan_id,
    ppid,
    Assets,
    SUM(Assets) OVER(PARTITION BY plan_id ORDER BY ppid) AS rt
FROM asset_summary
ORDER BY plan_id, ppid;

优化点说明 #

1. 合并原方案中a、combine、dist三个CTE为一步：直接通过DISTINCT #assets.plan_id与#numbers做CROSS JOIN，生成所有需要的plan_id+ppid组合，避免冗余的关联与去重操作。
2. 简化资产统计步骤：在asset_summary中直接完成左关联与求和，无需额外的new_asset_list与grouped步骤。
3. 代码结构更紧凑，逻辑分层清晰，便于维护与调试。

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