Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

The new Analytic functions in SQL Server 2012 Expand / Collapse
Author
Message
Posted Wednesday, January 18, 2012 10:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 6,582, Visits: 8,862
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
Posted Thursday, January 19, 2012 2:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
Finally they will have what Oracle had delivered ages ago....
Post #1238506
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
Posted Thursday, January 19, 2012 6:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
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: Tuesday, July 29, 2014 3:07 AM
Points: 1,060, Visits: 876
Any idea what the performance of these things is like?


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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 21,350, Visits: 15,027
Good Stuff Wayne



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


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
Posted Thursday, January 19, 2012 8:14 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
Great article Wayne. This is great info to have when management wants to know why we are always upgrading
Post #1238740
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
Posted Thursday, January 19, 2012 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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
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: Monday, July 14, 2014 10:08 AM
Points: 754, Visits: 3,816
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 1234»»»

Permissions Expand / Collapse