Windowing Ranges

Question

Windowing Ranges

Viewing 9 posts - 16 through 23 (of 23 total)

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

marcia.j.wilson (12/9/2013)
Any examples of how these would be used?

Running totals. Traditionally done with slow set-based code, with a (relatively!) fast cursor, or with some dangerous unsupported side effect of a language feature.

In SQL Server 2012, very simple:

SELECT SalesPersonName,

SaleDate,

Amount,

SUM(Amount) OVER (PARTITION BY SalesPersonName

ORDER BY SaleDate

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal

FROM YourTable

ORDER BY SalesPersonName, SaleDate;

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Marcia J SSCertifiable Points: 5649 More actions · Answer 2

Hugo Kornelis (12/9/2013)
marcia.j.wilson (12/9/2013)
Any examples of how these would be used?
Running totals. Traditionally done with slow set-based code, with a (relatively!) fast cursor, or with some dangerous unsupported side effect of a language feature.
In SQL Server 2012, very simple:
SELECT SalesPersonName,
SaleDate,
Amount,
SUM(Amount) OVER (PARTITION BY SalesPersonName
ORDER BY SaleDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM YourTable
ORDER BY SalesPersonName, SaleDate;

Thanks. That helped me get a better picture of why you would use this.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

WayneS (12/9/2013)
KWymore (12/9/2013)
Nice question Steve! I wish we would upgrade to 2012 around here so we could use these new functions.
Good stuff.
Ken
The upgrades to the OVER clause alone are, IMHO, worth the upgrade.

+1

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Love the access to other rows in the set. Still waiting for 2012. Sigh.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ken SSCoach Points: 17191 More actions · Answer 5

Ed Wagner (12/10/2013)
Love the access to other rows in the set. Still waiting for 2012. Sigh.

I was doing some BI work in QlikView a couple years ago and they had some great built-in analytic functions for lead, lag, percent_rank and a few others. Saved me a bunch of coding. Since that application did everything in memory, access to other rows in the set was a built in feature. Very handy.

Supposedly upgrading to 2012 next year. We shall see...:-)

jfgoude SSCrazy Points: 2586 More actions · Answer 6

Special dedicace to MSDN for refreshing my memory loss 🙂

matt.akers.1 SSC Veteran Points: 211 More actions · Answer 7

Good Question. Maybe specify database version though as that only became valid in 2012.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

Hugo Kornelis (12/9/2013)
marcia.j.wilson (12/9/2013)
Any examples of how these would be used?
Running totals. Traditionally done with slow set-based code, with a (relatively!) fast cursor, or with some dangerous unsupported side effect of a language feature.

Unsupported... Yes. Dangerous? No and I do wish you'd get over that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

Some say this was an easy one. For me I had to do some research. I hadn't used this with ROWS and RANGE before. I learned something today.