Introduction to the Partition By Window Function

• Additional Articles

  Newbie

  Points: -25

  More actions

  October 22, 2019 at 12:00 am

  #3688053

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

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  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

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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

• drew.allen

  SSC Guru

  Points: 77012

  More actions

  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