如何利用SUM()窗口函数实现按Term分组的正确累计总和？

It looks like your goal is to compute a running total that sums the full total of all prior terms plus the current row's Value1 (which already represents the cumulative sum within the current term). The EXCLUDE TIES approach wasn't working because it only affects the current row's peers, not the aggregation of prior groups.

Understanding the Problem #

From your sample data, each term's final Value1 is 1 (the total for that term), and you want:

• For rows in Term 1: Just the row's Value1 (no prior terms to sum)
• For rows in Term 2: Term 1's total (1) + current row's Value1
• For rows in Term 3: Term 1's total + Term 2's total (2) + current row's Value1

Solution Approach #

We can break this into two simple steps:

1. Calculate the total value for each term (using MAX(Value1) since you noted the maximum Value1 per term equals the term's total)
2. Compute the cumulative sum of these term totals only for terms before the current one, then add that to the current row's Value1

SQL Implementation #

Here's a clear query using CTEs to achieve your expected results:

WITH term_totals AS (
    -- Step 1: Get the total value for each term
    SELECT 
        Term,
        MAX(Value1) AS term_total
    FROM your_table
    GROUP BY Term
),
prior_term_cumulative AS (
    -- Step 2: Calculate cumulative sum of totals for all terms before the current one
    SELECT 
        Term,
        SUM(term_total) OVER (
            ORDER BY Term 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prior_terms_total
    FROM term_totals
)
-- Combine prior terms' total with current row's Value1
SELECT 
    t.Value1,
    t.Term,
    COALESCE(pt.prior_terms_total, 0) + t.Value1 AS Expected_Results
FROM your_table t
LEFT JOIN prior_term_cumulative pt ON t.Term = pt.Term
ORDER BY t.Term, t.Value1;

Explanation #

• term_totals: This CTE captures the total for each term (since each term's final Value1 is its total, MAX(Value1) reliably pulls this value).
• prior_term_cumulative: The window function with ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ensures we only sum totals from terms that come before the current one. For the first term, this returns NULL, which we replace with 0 using COALESCE.
• The final query joins these results back to your original table, adding the prior terms' cumulative total to each row's Value1 to produce your desired running total.

Concise Alternative (Single Window Function) #

If you prefer a more compact approach without CTEs, you can use nested window functions:

SELECT 
    Value1,
    Term,
    COALESCE(
        SUM(MAX(Value1)) OVER (
            ORDER BY Term 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 
        0
    ) + Value1 AS Expected_Results
FROM your_table
GROUP BY Term, Value1
ORDER BY Term, Value1;

This works by first computing each term's total via MAX(Value1), then summing those totals for all prior terms using the outer window function. Grouping by Term and Value1 preserves all original rows while enabling the term-level aggregation.

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