The new Analytic functions in SQL Server 2012

  • WayneS

    SSC Guru

    Points: 95341

    Comments posted to this topic are about the item The new Analytic functions in SQL Server 2012

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • BOR15K

    SSCertifiable

    Points: 5757

    Finally they will have what Oracle had delivered ages ago....

  • ETL_Guy

    SSC Journeyman

    Points: 81

    I finally see SQL Server catching up, I am so used to of Lead/Lag in DB2. It will make my life little easier. Thanks

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    WayneS (1/18/2012)


    Comments posted to this topic are about the item <A HREF="/articles/SQL+Server+2012/76704/">The new Analytic functions in SQL Server 2012</A>

    Great information, thanks for that.

    To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end MAX(x) OVER (PARTITION BY y) should return the same as FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)

    But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)

    So the whole point of FIRST_VALUE and LAST_VALUE seem to be:

    FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY SomeOtherColumn DESC)

    Any other points I may have missed?

    Best Regards,

    Chris Büttner

  • RichB

    SSCrazy Eights

    Points: 9651

    Any idea what the performance of these things is like?

  • SQLRNNR

    SSC Guru

    Points: 281210

    Good Stuff Wayne

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Geoff A

    SSChampion

    Points: 11407

    Great article Wayne. This is great info to have when management wants to know why we are always upgrading 😀

  • stephen99999

    Ten Centuries

    Points: 1087

    PERCENTILE_CONT / PERCENTILE_DISC

    Lets recall from statistics that continuous variables (PERCENTILE_CONT) are those that "cannot not be exactly counted," while discrete variables (PERCENTILE_DISC) "have an exact amount."

    PERCENTILE_CONT would be better used when performing estimation or predictive calculations, as you are trying to determine a value from a sample of data (where the entire data set is unknown).

    PERCENTILE_DISC would be better for when we need an exact measure, and the entire data set is known.

    **Note: I am not a statistician, nor an analytics guru, so hopefully someone with greater experience could shine some better light on this, or at least validate my statement.

    Hopefully this helps some trying to understand the purposes behind these functions.

    Stephen

  • Jack Corbett

    SSC Guru

    Points: 184360

    Good article Wayne. One thing I like to see in articles like this though is how you might solve the same problem without using the new functions. Just to see how much the new functions help.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • mtillman-921105

    SSCertifiable

    Points: 7049

    Thank you for this Wayne - I'll definitely come back to it later. I like your concise writing style with helpful examples too.

    - Mark

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • j-1064772

    SSCertifiable

    Points: 5316

    How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?

  • tim.hulse

    SSC Veteran

    Points: 257

    Thanks - this is a great summary.

  • Ken Wymore

    SSCoach

    Points: 16358

    Nice article, very helpful. It's nice to see articles like this with examples. Definitely helps to prove to others why upgrades are needed. 🙂

  • WayneS

    SSC Guru

    Points: 95341

    Christian Buettner-167247 (1/19/2012)


    WayneS (1/18/2012)


    Comments posted to this topic are about the item <A HREF="/articles/SQL+Server+2012/76704/">The new Analytic functions in SQL Server 2012</A>

    Great information, thanks for that.

    To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end MAX(x) OVER (PARTITION BY y) should return the same as FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)

    But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)

    So the whole point of FIRST_VALUE and LAST_VALUE seem to be:

    FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY SomeOtherColumn DESC)

    Any other points I may have missed?

    Actually...

    this article only talks about the Analytic functions. You might want to look at my previous article The OVER Clause enhancements in SQL Server code named “Denali”, CTP3[/url].

    Basically, the MIN/MAX functions DO allow you to order your partition by a different column. But let's talk about this "over there"... it's more appropriate there.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS

    SSC Guru

    Points: 95341

    RichB (1/19/2012)


    Any idea what the performance of these things is like?

    Did you click on that "TSQL Challenges Winner" icon in my signature? (Well, that is using the aggregate functions and not the analytic, but I believe them to be similar.)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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