SQL Server Query, running total in view, reset whe

问题:

A colleague of mine has a problem which I am trying to help him with.

He has a SQL view which contains the following data (Sample data):-

Category          Value
Cat A             10
Cat A             20
Cat A             30
Cat B             15
Cat B             15
Cat C             10
Cat C             10

He wants to add a column to the view which keeps a running total of the Value column.

This column must reset the running total when the Category changes.

So the output data must be like this:-

Category          Value       Running
Cat A             10          10
Cat A             20          30
Cat A             30          60
Cat B             15          15
Cat B             15          30
Cat C             10          10
Cat C             10          20

We could get the running total by joining the table onto itself like this:-

select t1.id, t1.[count], SUM(t2.[count]) as sum
from TableA t1
inner join TableA t2 on t1.id >= t2.id
group by t1.id, t1.[count]
order by t1.id

Problem is we don't have an ID column and how would we instruct the running total to reset when the category changes ??

回答1:

This will not perform particularly well on larger tables, but will do the job!

   select 'Cat A' as class,10 as value into #x
UNION ALL SELECT 'Cat A',20 
UNION ALL SELECT 'Cat A',30 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat B',15 
UNION ALL SELECT 'Cat C',10 
UNION ALL SELECT 'Cat C',10 

;WITH running_total AS
(
select *
,ROW_number() OVER (PARTITION BY class order by value ASC) as row 
from #x 
)
SELECT 
r1.class
,MAX(r1.value) as value
,SUM(r2.value) as running_total
FROM running_total r1
LEFT OUTER JOIN running_total r2 on r2.class = r1.class
                AND r2.row <= r1.row
GROUP BY 
r1.class
,r1.row

This works by using a Common table Expression (CTE) and windowed functions. The CTE works in a similar fashion to a subquery, where the first part (called running_total), adds a row-based ID to each class based on value.

Row_number() works as documented here but basically it auto-increments depending on how you define partitioning (essentially the same as group by, but without having to group in the main query) and an in-built order by class.

In this example partitionign by class ensures that each new category name will have the lowest value set to 1 - if you wanted a total running total not based on specific categories you would remove this part of the clause.

The second part of the CTE selects the results from the first part of the CTE and joins onto itself where classes match. By joining on r2.row <= r1.row this ensures that the second join includes all values <= the current row - i.e. Row 3 in R1 will include Row 1,2,3 in R2 join.

回答2:

You could use the ROW_NUMBER() function combined with an OUTER APPLY

DECLARE @T TABLE (Category VARCHAR(5), Value INT)
INSERT INTO @T VALUES 
    ('Cat A', 10),
    ('Cat A', 20),
    ('Cat A', 30),
    ('Cat B', 15),
    ('Cat B', 15),
    ('Cat C', 10),
    ('Cat C', 10)

;WITH T AS
(   SELECT  Category, Value, ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Value) [RowNumber]
    FROM    @T
)
SELECT  T1.Category,
        T1.Value,
        RunningTotal
FROM    T T1
        OUTER APPLY
        (   SELECT  SUM(Value) [RunningTotal]
            FROM    T T2
            WHERE   T2.Category = T1.Category
            AND     T2.RowNumber <= T1.RowNumber
        ) RunningTotal