<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Matt Miller
»
Windowed Aggregate functions
19 posts, Page 1 of 2
1
2
»»
Windowed Aggregate functions
Rate Topic
Display Mode
Topic Options
Author
Message
Matt Miller (#4)
Matt Miller (#4)
Posted Thursday, June 26, 2008 9:52 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 6,998,
Visits: 13,951
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
OCTom
OCTom
Posted Friday, June 27, 2008 6:54 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 2,018,
Visits: 2,852
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
Matt Miller (#4)
Matt Miller (#4)
Posted Friday, June 27, 2008 7:18 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 6,998,
Visits: 13,951
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
Steven Cameron
Steven Cameron
Posted Friday, June 27, 2008 7:19 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, April 06, 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
Matt Miller (#4)
Matt Miller (#4)
Posted Friday, June 27, 2008 7:22 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 6,998,
Visits: 13,951
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
Bradley Deem
Bradley Deem
Posted Friday, June 27, 2008 9:40 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:22 AM
Points: 551,
Visits: 1,150
Awesome question, I've actually used the Window functions in the past and the Group By almost got me!
Post #525118
bitbucket-25253
bitbucket-25253
Posted Friday, June 27, 2008 9:59 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103,
Visits: 20,220
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
thuybui-791943
thuybui-791943
Posted Friday, June 27, 2008 10:10 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 05, 2013 5:17 PM
Points: 41,
Visits: 102
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
Matt Miller (#4)
Matt Miller (#4)
Posted Friday, June 27, 2008 10:43 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 6,998,
Visits: 13,951
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
thuybui-791943
thuybui-791943
Posted Friday, June 27, 2008 10:51 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 05, 2013 5:17 PM
Points: 41,
Visits: 102
Yes,
Microsoft SQL Server Management Studio 9.00.1399.00
Post #525193
« Prev Topic
|
Next Topic »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
