Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cumulative sum of previous rows Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 10:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:17 PM
Points: 1,862, Visits: 481
Comments posted to this topic are about the item Cumulative sum of previous rows
Post #1568758
Posted Thursday, May 8, 2014 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,789, Visits: 14,000
Nice article, Ben - does exactly what it says on the tin, although many folks lurking around here would be more familiar with the name Running Total.
The old version of your update is what we call a "Triangular Join". If the partitions are small and hence you're only summing up a few rows it can be fast enough for production code. There are other ways of performing a running totals update including cursor, recursive CTE and the so-called "Quirky Update", and these have been exhaustively researched and compared by Jeff Moden et al in this and other articles.

Cheers

ChrisM


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1568790
Posted Thursday, May 8, 2014 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 29, 2014 6:46 AM
Points: 8, Visits: 53
Hey Ben,

Speaking of SQL 2012 and using "PARTITION OVER" have you tried specifying the rows relative to the current one and avoiding the UPDATE statement completely?

This will return the same results as at the end of your article.
eg)
SELECT D.demandID
,D.itemID
,D.orderNbr
,D.orderQty
,I.inventoryQty-SUM(D.orderQty) OVER (PARTITION BY D.itemID ORDER BY D.orderNbr RANGE UNBOUNDED PRECEDING) as netInventoryQty
FROM Demand D
INNER JOIN Inventory I
ON I.itemID=D.itemID
ORDER BY D.demandID


"PARTITION BY ItemID " will group them by item number and "ORDER BY orderNbr" will also put them in the correct order for the aggregation.
"RANGE UNBOUNDED PRECEDING" grabs all the preceding rows, grouped by itemID ordered by orderNbr.
I just found out about this new relative reference clause for OVER myself, very neat.

Simon
Post #1568820
Posted Thursday, May 8, 2014 5:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:17 PM
Points: 1,862, Visits: 481
Thanks Chris for your post.

Ben
Post #1568825
Posted Thursday, May 8, 2014 5:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:17 PM
Points: 1,862, Visits: 481
I will have to take a closer look at your code. Thanks for posting it.

Ben
Post #1568827
Posted Thursday, May 8, 2014 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:25 PM
Points: 2, Visits: 35
I agree with SimonC, the RANGE clause is the better solution to the Running Total problem for read operations.
Post #1568842
Posted Thursday, May 8, 2014 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
Here's a version using a CTE for an easy-to-read implimenation:

;with d as (
select netInventoryQty, demandID, d.itemID, i.inventoryQty,
SUM(orderQty) OVER ( PARTITION BY d.itemID ORDER BY demandid ) AS 'cumQty'
from demand d
join inventory i on d.itemID = i.itemID
)

update d set netInventoryQty = d.inventoryQty - d.cumqty

Post #1568954
Posted Thursday, May 8, 2014 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:39 PM
Points: 2, Visits: 38
Good article.. Thanks!..

Here is how I would have solve the problem.

UPDATE a
SET a.netInventoryQty=c.inventoryQty-b.cQty
--SELECT *
FROM demand a
INNER JOIN (
select a.demandID, sum(b.orderQty) as cQty
FROM demand a
INNER JOIN demand b ON b.demandID<=a.demandID AND b.itemID=a.itemID
GROUP BY a.demandID
) b on b.demandID=a.demandID
INNER JOIN Inventory c ON c.itemID=a.itemID


I'll have to check out if your method is faster.

Thanks again
Post #1568957
Posted Thursday, May 8, 2014 9:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:17 PM
Points: 1,862, Visits: 481
Not sure if you completely read my article, but the solution you posted is what I replaced to speed up the update.

Thanks for your post.

Ben
Post #1568960
Posted Thursday, May 8, 2014 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 9:39 PM
Points: 2, Visits: 38
I did read your article and my solution is similar but there are differences - mainly that I'm joining to sub query that is grouping - where you originally are using nested query for summing - correct??.. Before I read your article I wouldn't have even thought to have used OVER ( PARTITION BY... -- As it is pretty new to me.

Thanks
Post #1568993
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse