Windowed Aggregate functions

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

  • 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:

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

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

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

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

  • 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]

  • I received this error while running the query?

    Msg 156, Level 15, State 1, Line 17

    Incorrect syntax near the keyword 'OVER'.

    Any idea?

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

  • Yes,

    Microsoft SQL Server Management Studio9.00.1399.00

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

  • 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]

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

  • 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.

  • 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.

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

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