Post reply

Introduction to the Partition By Window Function

  • October 22, 2019 at 12:00 am

    #3688053

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

  • October 22, 2019 at 10:26 am

    #3690908

    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

  • October 22, 2019 at 2:43 pm

    #3691004

    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

  • October 22, 2019 at 3:12 pm

    #3691029

    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 3 (of 3 total)

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