Windowed Aggregate functions

Question

Windowed Aggregate functions

Matt Miller (4)

SSC Guru

Points: 124210
More actions
June 26, 2008 at 9:52 pm

#193743

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?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

OCTom SSChampion Points: 11785 More actions · Answer 1

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?

:discuss:

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Steven Cameron SSCrazy Points: 2903 More actions · Answer 3

I think the point of the question is that unless you know how windowed aggregation works, it is a gotcha.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

Bradley Deem SSCrazy Points: 2565 More actions · Answer 5

Awesome question, I've actually used the Window functions in the past and the Group By almost got me!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

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[/url]
Before posting a performance problem please read[/url]

thuybui-791943 Old Hand Points: 311 More actions · Answer 7

I received this error while running the query?

Msg 156, Level 15, State 1, Line 17

Incorrect syntax near the keyword 'OVER'.

Any idea?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

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?

thuybui-791943 Old Hand Points: 311 More actions · Answer 9

Yes,

Microsoft SQL Server Management Studio9.00.1399.00

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 10

I really hate marking the wrong anwser selection letter after determing the right output. My hand eye coordination wasn't that good back in the days of PE either.

-- Mark D Powell --

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

thuybi

More than likely it is a typographical error, for example the following is missing the ) directly after the asterik

select grp,subgrp,

count(* OVER

Should be

select grp,subgrp,

count(*)OVER

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

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

thuybui (6/27/2008)
Yes,
Microsoft SQL Server Management Studio9.00.1399.00

And what is the compatibility level of the DB in question?

----------------------------------------------------------------------------------
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?

john.arnott SSChampion Points: 11882 More actions · Answer 13

Excellent QOD. I spent enough time studying the code and the results in QA that I don't feel guilty about running it before choosing the correct answer :Whistling:.

The light bulb finally went on for me that including "subgrp" in the result set is a red herring of sorts -- none of the three counts change depending on its value. The results are easier to understand by removing subgrp and making them distinct. If we make the Select statement....

select distinct grp --,subgrp

,count(*) OVER (partition by grp,subgrp) grpcount

,count(*) OVER (partition by grp) subcount

,count(*) OVER (partition by NULL) grandcount

from #windows

group by grp , subgrp

Then our results are much more straight-forward and, to me anyway, easier to tie back to the query:

grp grpcount subcount grandcount

----- ----------- ----------- -----------

aaaaa 1 3 4

bbbbb 1 1 4

Putting the results in plain English,

there's 1 grp "aaaaa" with 3 subgrps out of 4 total subgrps,

there's 1 grp "bbbbb" with 1 subgrp out of the same total 4 subgrps

The grpcount will always be 1 since we did "group by" and windowed on the same combination of grp and subgrp.

thuybui-791943 Old Hand Points: 311 More actions · Answer 14

Thanks. I was able to run a quey in a DB with compatibility set to 90. The previous DB only has compatibility set up to 80.