SQL Server's implementation of Window Functions

Question

SQL Server's implementation of Window Functions

Phil Factor

SSC-Insane

Points: 20244
More actions
September 25, 2010 at 11:31 am

#148901

Comments posted to this topic are about the item SQL Server's implementation of Window Functions
Best wishes,
Phil Factor

Viewing 14 posts - 1 through 13 (of 13 total)

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

chrisn-585491 SSCoach Points: 16006 More actions · Answer 1

PostgreSQL has been on our radar for a while. While focused on Oracle at the high-end, the folks in Redmond shouldn't forgot about PostgreSQL on the free end.

WayneS SSC Guru Points: 95460 More actions · Answer 2

Itzik Ben-Gan has several connect items in an effort to get full support for the windowing functions added to SQL Server. If you're interested, see the following links:

(If you agree, vote Yes. It might just make a difference as to what features get into the next version of SQL.)

Ordering for aggregates (used to allow subsequent framing options)

Framing (ROWS and RANGE window sub-clauses)

DISTINCT clause for aggregate functions

LAG and LEAD offset functions

PERCENT_RANK and CUME_DIST Distribution Functions

FIRST_VALUE, LAST_VALUE offset functions

Reuse of window definition using the WINDOW clause

QUALIFY filtering clause

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

Itzik has written a very fine blog on the subject of the missing functions Window Functions (OVER Clause) – Help Make a Difference. Also worth a look is PostgreSQL's documentation for Window Functions just so you can see what you're missingWindow Functions and Running totals and sums using PostgreSQL 8.4 Windowing functions . This is a cause that is well-worth supporting, so please vote on Connect for the implementation of all these functions. The most important of all is the first one 'Ordering for Aggregates' as this would allow a simple solution to a whole lot of 'running total' SQL problems.

Best wishes,
Phil Factor

WayneS SSC Guru Points: 95460 More actions · Answer 4

Phil Factor (9/26/2010)
Itzik has written a very fine blog on the subject of the missing functions Window Functions (OVER Clause) – Help Make a Difference. Also worth a look is PostgreSQL's documentation for Window Functions just so you can see what you're missingWindow Functions and Running totals and sums using PostgreSQL 8.4 Windowing functions . This is a cause that is well-worth supporting, so please vote on Connect for the implementation of all these functions. The most important of all is the first one 'Ordering for Aggregates' as this would allow a simple solution to a whole lot of 'running total' SQL problems.

Thanks for the PostgreSQL links - they are indeed interesting and illuminating.

I didn't include a link for the blog since you need to have a paid membership to view it.

I agree with the most important missing function. I would say that IMHO, the next two are the ROWS/RANGE window sub-clauses, and the WINDOW clause. And if I understand all of these correctly, wouldn't you need the ROWS/RANGE into order to do the 'running total'?

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

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Phil,

Thanks for the editorial and bringing this back to peoples attention.

I like how a number of the connect items have comments from Microsoft in 2007 saying that they couldn't get it in 2008 but that it was a priority for the next version. Well 2008 R2 came and they still haven't gotten them in. We can only hope that they will make it into v11...

IceDread SSCertifiable Points: 5050 More actions · Answer 6

Interesting read. Somehow I've managed not to find the time to look into postgresql. It is annoying to read about it and sit with sql server that microsoft seams to take ages to update.

Mauve SSChampion Points: 11316 More actions · Answer 7

Having worked with Oracle for 25+ years, I'm continually frustrated with the lack of "core" functionality that Microsoft is so slow to implement. This is just another one as Oracle implemented the windowing functionality quite a while ago. Microsoft spends more time on the "eye candy" than the stuff that really matters. It's like designing more cup holders for a car rather than what is "under the hood".

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Makes sense to me - time to visit the Connect items.

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

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 9

Mauve (9/27/2010)
Having worked with Oracle for 25+ years, I'm continually frustrated with the lack of "core" functionality that Microsoft is so slow to implement. This is just another one as Oracle implemented the windowing functionality quite a while ago. Microsoft spends more time on the "eye candy" than the stuff that really matters. It's like designing more cup holders for a car rather than what is "under the hood".

Is it "core"?

I have to say that I haven't missed these functions often. Perhaps it's ignorance of what they can do for me, but I haven't seen this as a big issue. More a nice to have.

I have yet to see people give me a good, wide range of where these things help in an every day situation. Will this help a lot of people regularly? I have asked some of the T-SQL people that I respect and have pushed for this, but they haven't given me enough practical examples. It seems that this is something that will help in some domains, but I'm not sure it's a lot of domains.

WayneS SSC Guru Points: 95460 More actions · Answer 10

I'm not sure that this would fall into the "wide range" that you're looking for Steve, but here's my take on these:

Ordering for aggregates (used to allow subsequent framing options)
Framing (ROWS and RANGE window sub-clauses)

These two will allow one to perform "running totals", in a SUPPORTED, SET-BASED way. Now, running totals are frequently coming up here, and I definitely see these two as very highly desired. I personally would be happy to see just these two fully implemented to where running totals can be performed.

The other one that is highly desirable to me is:

Reuse of window definition using the WINDOW clause

This is just code re-use - it allows me to use the same windowing definition in multiple areas without having to redefine it everywhere. And if something needs changed, it only needs changed once instead of everywhere it's being used.

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 11

I have yet to see people give me a good, wide range of where these things help in an every day situation.

Window functions are great for any 'inter-row' calculations where you need to refer to values in adjacent or nearly adjacent rows based on the order you define. They are the easy way to calculate moving averages. They are usually the best way of paging through results in the order you define (e.g. getting row 30 to 40, row 40 to 50 etc.) cumulative totals as already mentioned, calculating rankings within sets of data, calculating relative contributions to a total. Sots of statistical stuff as well, but I know you're not into that, Steve!

Best wishes,
Phil Factor

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 12

Wayne/Phil,

Those are good examples, but how often do you write them? It seems that you "rarely" solve these issues, get the queries, and often do other things.

ganotedp SSC-Addicted Points: 492 More actions · Answer 13

I have yet to see people give me a good, wide range of where these things help in an every day situation.

Depends on your "every day". If it's OLTP, well, that's been a "known quantity" for a long time and Windowing Functions would be merely a curiosity item.

But Windowing Functions, I hear, were originally promoted by Teradata, which gives you a clue where they're valuable.

If your "every day" is OLAP, then Windowing Functions, partitioning, bitmap indexing, compression, and such keep you from being swamped by ever-growing data sizes and query execution times. OLAP is where most new DBMS features are aimed, in my opinion.

I use Windowing Functions every day in my ETL. I just used them in this query to find some duplicates and identify the preferred ID to replace them with:

select * from (

select ROW_NUMBER() OVER (partition by policy_id, unit_Number order by unit_id desc ) r#

, MAX(unit_ID) over (partition by policy_id, unit_Number ) preferred_unit_id

, m.* from unit_table m

) where r# > 1

The FIRST_VALUE Windowing Function is a real blessing, and I know I'd miss it if I had to switch to SS (I even have a certification from Oracle that calls me an SQL Expert, so I know I could "code" around the gap, but it'd be slower to both write and to run).

I've thoroughly converted all of my ETL team into "true believers" in the value of Windowing (Analytic) Functions. As Tom Kyte has said: Analytics rock! 😎