Introduction to the Partition By Window Function

  • Additional Articles

    Newbie

    Points: 5

    Comments posted to this topic are about the item Introduction to the Partition By Window Function

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21166

    a great start - I use rownumber over (partition by order by) a lot

    one of the more overlooked windowing fuctions though is NTILE - especially when you are dealing with financial quarters

    NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile

    these tools have made life a lot easier (once you know they exist)

     

    MVDBA

  • Alan Burstein

    SSC Guru

    Points: 61067

    MVDBA (Mike Vessey) wrote:

    a great start - I use rownumber over (partition by order by) a lot

    one of the more overlooked windowing fuctions though is NTILE - especially when you are dealing with financial quarters

    NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile

    these tools have made life a lot easier (once you know they exist)

    This is arguably the number one mistake I see with window functions... NTILE cannot accurately calculate Quartiles or Percentiles. This is covered here: . It you're using NTILE for this and getting the answer you want it's purely coincidental.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • drew.allen

    SSC Guru

    Points: 76658

    The main argument I have with this article is that he uses frames without ever defining the term.  Also, I've never found a need to use the frame UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.  If you are going to use the entire window, why define a frame?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

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