SQL中如何计算分组累计和与当前值的叠加结果？

Got it, let's break this down. Your desired Result isn't a standard running total—it's a recursive cumulative sum where each row's value equals the current Value plus the sum of all prior Result values. Regular window functions can't handle this kind of recursive dependency, so we'll use a recursive CTE (Common Table Expression) to compute it step by step.

Step-by-Step Explanation #

1. Rank rows within each group: First, we assign a row number to each entry grouped by id and ordered by date—this helps us link each row to its previous entry in the recursion.
2. Anchor the recursion: Start with the first row of each id group, where the Result is just the current Value (since there are no prior results to sum).
3. Recursively compute values: For each subsequent row, calculate the cumulative total of all Result values up to that point, then derive the current Result as the difference between this new total and the prior total.

Working SQL Code #

WITH ranked_data AS (
    -- Assign row numbers to each row per id, ordered by date
    SELECT 
        date,
        id,
        Value,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
    FROM t1
),
recursive_cum AS (
    -- Anchor member: first row of each id group
    SELECT 
        date,
        id,
        Value,
        rn,
        CAST(Value AS BIGINT) AS total_sum, -- Tracks sum of all Results up to current row
        CAST(Value AS BIGINT) AS Result
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    -- Recursive member: compute values for subsequent rows
    SELECT 
        rd.date,
        rd.id,
        rd.Value,
        rd.rn,
        -- total_sum = 2 * previous total_sum + current Value (derived from recursive formula)
        2 * rc.total_sum + rd.Value AS total_sum,
        -- Result = new total_sum - previous total_sum
        (2 * rc.total_sum + rd.Value) - rc.total_sum AS Result
    FROM ranked_data rd
    JOIN recursive_cum rc 
        ON rd.id = rc.id 
        AND rd.rn = rc.rn + 1
)
-- Final output: select the columns we need, ordered by id and date
SELECT date, id, Value, Result
FROM recursive_cum
ORDER BY id, date;

Verification Against Your Example #

Let's confirm this works for your sample data:

• For id = A, the Result sequence becomes 1, 2, 3, 8, 15—exactly matching your expected output.
• For id = B, we get 0, 1, 4, 4, 11—which aligns with your requirements.
• For id = C, the sequence is 1, 3, 5, 10, 21—perfectly matches your desired result.

The CAST(Value AS BIGINT) ensures we avoid integer overflow if your Value values or cumulative sums get large—adjust the data type if needed for your specific use case.

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