Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » The new Analytic functions in SQL Server 2012...
Add to briefcase
26 posts, Page 1 of 3
123»»»

The new Analytic functions in SQL Server 2012 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WayneS
Posted Wednesday, January 18, 2012 10:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 6,369, Visits: 8,232
Comments posted to this topic are about the item The new Analytic functions in SQL Server 2012

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1238434
Zeev Kazhdan
Posted Thursday, January 19, 2012 2:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:24 AM
Points: 70, Visits: 196
Finally they will have what Oracle had delivered ages ago....
Post #1238506
ETL_Guy
Posted Thursday, January 19, 2012 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 20, 2013 2:30 PM
Points: 1, Visits: 12
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
Post #1238581
Christian Buettner-167247
Posted Thursday, January 19, 2012 6:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 2,525, Visits: 3,618
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
Post #1238586
RichB
Posted Thursday, January 19, 2012 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:06 AM
Points: 1,026, Visits: 751
Any idea what the performance of these things is like?


Post #1238629
SQLRNNR
Posted Thursday, January 19, 2012 7:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332
Good Stuff Wayne


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1238714
Geoff A
Posted Thursday, January 19, 2012 8:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:10 AM
Points: 485, Visits: 1,568
Great article Wayne. This is great info to have when management wants to know why we are always upgrading
Post #1238740
stephen99999
Posted Thursday, January 19, 2012 8:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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
Post #1238753
Jack Corbett
Posted Thursday, January 19, 2012 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check 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 help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1238762
mtillman-921105
Posted Thursday, January 19, 2012 12:41 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749, Visits: 3,767
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
Post #1239015
« Prev Topic | Next Topic »

Add to briefcase
26 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.