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 12»»

Windowed Aggregate functions Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 9:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 7,115, Visits: 14,983
Comments posted to this topic are about the item Windowed Aggregate functions

----------------------------------------------------------------------------------
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 #524735
Posted Friday, June 27, 2008 6:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 2,508, Visits: 3,695
Good question. I love learning new things and this is a new one on me. I can keep this for my bag of tricks.

Is there a downside to doing this? The code is clean but my gut feeling is there may be some inefficiencies. Are there any gotchas?



Post #524937
Posted Friday, June 27, 2008 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 7,115, Visits: 14,983
The gotcha is in the Windowed aggregate itself. It's a bit RBAR-like in that it seems to create a worktable with LOOP joins to the outer query, so its perf tends to suffer a lot with the "window" you're running this against is large. Meaning - if you were to need something like a "percentage of total", and your unsummarized data was large, it would likely be more efficient to use a derived table setup to create the "total", since you can control what method is used to link it into the main query.

On the other hand, if you need quick access to different aggregates (especially ones aggregated against different groupings), and the groups aren't huge, it tends to do VERY well.


----------------------------------------------------------------------------------
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 #524961
Posted Friday, June 27, 2008 7:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 6, 2009 1:29 PM
Points: 2,057, Visits: 215
I think the point of the question is that unless you know how windowed aggregation works, it is a gotcha.
Post #524964
Posted Friday, June 27, 2008 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 7,115, Visits: 14,983
Steven Cameron (6/27/2008)
I think the point of the question is that unless you know how windowed aggregation works, it is a gotcha.


That's certainly a good way to describe it! Although this particular behavior is laid out in ANSI's definition, it's certainly not incredibly well documented elsewhere (and not mentioned per se on Ms' side), so I've actually seen it being disclaimed as "a bug".


----------------------------------------------------------------------------------
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 #524969
Posted Friday, June 27, 2008 9:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:45 AM
Points: 554, Visits: 1,190
Awesome question, I've actually used the Window functions in the past and the Group By almost got me!
Post #525118
Posted Friday, June 27, 2008 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 5,563, Visits: 24,697
That's certainly a good way to describe it! Although this particular behavior is laid out in ANSI's definition, it's certainly not incredibly well documented elsewhere (and not mentioned per se on Ms' side), so I've actually seen it being disclaimed as "a bug".


Now this has me wondering how many other items MS tech writers of BOL have missed or failed to properly communicate with MS development teams about.

Thanks, now I have to determine where to keep that link you provided for my own future reference


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #525137
Posted Friday, June 27, 2008 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:14 PM
Points: 41, Visits: 107
I received this error while running the query?
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'OVER'.

Any idea?
Post #525151
Posted Friday, June 27, 2008 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 7,115, Visits: 14,983
thuybui (6/27/2008)
I received this error while running the query?
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'OVER'.

Any idea?


Are you running this in 2005 "native"? meaning - in compatibility 90?


----------------------------------------------------------------------------------
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 #525186
Posted Friday, June 27, 2008 10:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:14 PM
Points: 41, Visits: 107
Yes,
Microsoft SQL Server Management Studio 9.00.1399.00
Post #525193
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse