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 «««1011121314»»»

Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5 Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 11:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 74, Visits: 450
Jeff Moden (2/13/2008)
Well stated in both posts, Christopher... But, you've been spoiled by SS 2k5... none of that works in 2k and, I don't know about other folks, but the companies I've had the fortune/misfortune to work with since the year 2000, have all used the 2k version... finally found 1 that actually did use 2k5 and had to give them up because they were slaughtering the database, the data, and the server.


I can't comment on that. ;) I'm sure someone will see this post and who I work for and fire me if I did.



Christopher Ford
Post #455529
Posted Thursday, February 14, 2008 1:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:38 AM
Points: 1,070, Visits: 908
Christopher Ford (2/13/2008)
Earlier Jeff posted a question on using SUM(SAL) OVER()

Since OVER() doesn't support ORDER BY when used with an aggregation.

How about this one?

SELECT J1.AccountID,
J1.Date,
J1.Amount AS AmountThisDate,
SUM(J2.Amount) AS RunningBalance,
CAST(AVG(1.*J2.Amount) AS DECIMAL(12, 2)) AS AvgRunningBalance
FROM dbo.JBMTest AS J1
JOIN dbo.JBMTest AS J2
ON J2.AccountID = J1.AccountID
AND (J2.Date >= '01/01/2000' --Anchor Date from example
AND J2.Date <= J1.Date)
GROUP BY J1.AccountID, J1.Date, J1.Amount
ORDER BY J1.AccountID, J1.Date;

Not quite as elegant as SUM(SAL) OVER()

But I'm willing to bet that the query plans generated for both would be similiar to this query.

Credit doesn't go to me for this method. Learned this, like many other things from other people. This method gets credited to Itzik, since I learned it from one of his many articles, but it's useful in so many circumstances.

And this one works on SQL 2000. =)


Sadly that set based solution in my system over several million rows has a duration of about a half hour. The update variable nips through it in 10 seconds.



Post #455552
Posted Thursday, February 14, 2008 7:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 74, Visits: 450
Sadly that set based solution in my system over several million rows has a duration of about a half hour. The update variable nips through it in 10 seconds.


Yeah, I can understand that, after I ran it, I ran it with Statistics IO on.

because of the way the JOIN works, it basically rescans the table quite a few times on the right side of the JOIN, J2 gets scanned like 10000 times.

BUT, it works on SQL 2000. :)


Christopher Ford
Post #455698
Posted Thursday, February 14, 2008 8:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
Christopher Ford (2/13/2008)
Earlier Jeff posted a question on using SUM(SAL) OVER()

Since OVER() doesn't support ORDER BY when used with an aggregation.

How about this one?

SELECT J1.AccountID,
J1.Date,
J1.Amount AS AmountThisDate,
SUM(J2.Amount) AS RunningBalance,
CAST(AVG(1.*J2.Amount) AS DECIMAL(12, 2)) AS AvgRunningBalance
FROM dbo.JBMTest AS J1
JOIN dbo.JBMTest AS J2
ON J2.AccountID = J1.AccountID
AND (J2.Date >= '01/01/2000' --Anchor Date from example
AND J2.Date <= J1.Date)
GROUP BY J1.AccountID, J1.Date, J1.Amount
ORDER BY J1.AccountID, J1.Date;

Not quite as elegant as SUM(SAL) OVER()



This is precisely the Triangular Join mentioned in the first paragraph of the article. This is what Jeff is trying to get rid of.
--
JimFive
Post #455717
Posted Thursday, February 14, 2008 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 7,112, Visits: 15,494
Christopher -

First off - nice CTE solution. However - the order by inside of the ROW_NUMBER() only guarantees the order in which the numbers are assigned, and not the physical order in which things are returned. So - unless you use the same parlor room tricks Jeff is advocating, you're likely to run into the same issues everyone else is talking about.

Without guaranteeing the order, you have no guarantees that the "previous row" is the row you should be pulling the running total from.

I'll refer you to this: http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx

So again - without the clustered index in there to help you, this solution STILL can fall apart....

If you want to see - drop the clustered index, or change it to a non-clustered index.

Of course - if you DO take into account those caveats previously mentioned (clustered index, WITH(INDex(), TabLock) OPTION (MAXDOP 1), no partitioned tables), it's very elegant.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455733
Posted Thursday, February 14, 2008 8:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 4,411, Visits: 6,284
VERY nice find on that blog post! It sums up the ordered-output issue(s) quite explicitly.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #455753
Posted Thursday, February 14, 2008 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 7,112, Visits: 15,494
Conor had a lot of interesting stuff in the few blog posts he has made - like rules of thumb when queries will recompile, why Top 100 PERCENT is evil, etc... I'm disappointed that he seems to have given up on it (nothing new in a few years).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455769
Posted Thursday, February 14, 2008 11:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
You're talking about this Conor? :)

http://www.sqlskills.com/blogs/conor/


Best Regards,
Chris Büttner
Post #455879
Posted Thursday, February 14, 2008 11:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 7,112, Visits: 15,494
Cool - something to add to my reading list....I'll have to send him a stern talking to about orphaning blog readers like that! (I'm so sure he'll care about that.....:P)

Thanks!


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #455895
Posted Thursday, February 14, 2008 2:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 74, Visits: 450
Fine...

So...solutions which guaratee order in which rows are processed is it? Man, you guys are hard to please.

Here's 2 solutions I can think of. One for SQL 2005 and one for SQL 2000. I'm quite sure there's a much simpler solution that also guarantee's the row output. These weren't quite as speedy...in fact, the sql 2000 one really chewed the I/O a bit. Can't imagine why. =)

This is what happens when you think too hard as well. =) Or get nit picky on something like order... bah...who cares about order?? :D It's too hard to write queries that require order and updates and in order and accumulate. ;)

I will say that I haven't had quite as much fun coding as I have trying to come up with alternative ways to do Running Totals since finding this article.

SET STATISTICS IO ON;
---- Guarantee Order Returned ----
WITH RunningTotal AS
(
SELECT AccountID, Date, Amount, GrpBal,
ROW_NUMBER() OVER(PARTITION BY AccountID
ORDER BY AccountID, Date) AS rn
FROM dbo.JBMTest
)
SELECT Cur.AccountID,
Cur.Date AS Date,
Cur.Amount AS Amount,
ISNULL(Cur.Amount + Prv.Amount,cur.Amount) AS grpBal
FROM RunningTotal AS Cur
LEFT OUTER JOIN RunningTotal AS Prv
ON Cur.AccountID = Prv.AccountID
AND Cur.rn - 1 = Prv.rn
ORDER BY Cur.AccountID, Cur.Date;

---- Really thinking too hard and order guarantee -----
SELECT a.AccountID,
a.RowNum,
a.Date AS Date,
a.Amount AS Amount,
ISNULL(a.Amount + b.Amount,a.Amount) AS grpBal
FROM (SELECT AccountID, RowNum, Date, Amount,
(SELECT TOP(1) RowNum
FROM dbo.JBMTest AS A2
WHERE A2.AccountID = A1.AccountID
AND (A2.Date < A1.Date
OR A2.Date = A1.Date
AND A2.RowNum < A1.RowNum)
ORDER BY Date DESC, RowNum DESC) AS PrvRowNum
FROM dbo.JBMTest AS A1) AS a
LEFT OUTER JOIN dbo.JBMTest AS b
ON a.PrvRowNum = b.RowNum
ORDER BY a.AccountID, a.date, a.rowNum;



Christopher Ford
Post #456001
« Prev Topic | Next Topic »

Add to briefcase «««1011121314»»»

Permissions Expand / Collapse