Expert T-SQL Window Functions in SQL Server.pdf

(6053 KB) Pobierz
www.allitebooks.com
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
www.allitebooks.com
Contents at a Glance
About the Authors.............................................................................xi
About the Technical Reviewer ........................................................xiii
Acknowledgments ...........................................................................xv
Author’s Note .................................................................................xvii
■
Chapter 1: Looking Through the Window .......................................1
■
Chapter 2: Discovering Ranking Functions ..................................17
■
Chapter 3: Summarizing with Window Aggregates ......................33
■
Chapter 4: Tuning for Better Performance ....................................47
■
Chapter 5: Calculating Running and Moving Aggregates .............61
■
Chapter 6: Adding Frames to the Window ....................................71
■
Chapter 7: Taking a Peek at Another Row ....................................83
■
Chapter 8: Understanding Statistical Functions ...........................97
■
Chapter 9: Time Range Calculations and Trends ........................107
Index ..............................................................................................129
v
www.allitebooks.com
CHAPTER 1
Looking Through the
Window
SQL Server is a powerful database platform with a versatile query language called T-SQL.
The most exciting T-SQL enhancement over the past decade is, without a doubt, window
functions. Window functions enable you to solve query problems in new, easier ways, and
with better performance most of the time.
After the release of SQL Server 2000, SQL Server enthusiasts waited five long years
for the next version of SQL Server to arrive. Microsoft delivered an entirely new product
with SQL Server 2005. This version brought SQL Server Management Studio, SQL
Server Integration Services, snapshot isolation, and database mirroring. Microsoft also
enhanced T-SQL with many great features, such as Common Table Expressions (CTEs).
The most exciting T-SQL enhancement of all with 2005 was the introduction of window
functions.
That was just the beginning. Window functions are part of the ANSI Standard SQL
specification, and more functionality according to the standard was released with version 2012
of SQL Server. Even now, the functionality falls short of the entire specification, so there is
more to look forward to in the future.
This chapter provides a first look at two T-SQL window functions,
LAG
and
ROW_NUMBER.
You will learn just what the window is and how to define it with the
OVER
clause. You will also learn how to divide the windows into smaller sections called
partitions.
Discovering Window Functions
Window functions do not let you do anything that was impossible to do with earlier
functionality, and they have nothing to do with the Microsoft Windows API. Using
previously available methods, such as self-joins, correlated subqueries, and cursors,
you can solve just about any T-SQL problem if you work at it long and hard enough. The
main benefit of window functions is the ease in which you can solve these tricky queries.
Most of the time, you also realize a big boost in performance over the older methods.
You can often use a window function to change a solution involving many statements or
subqueries to one easier statement.
1
www.allitebooks.com
CHAPTER 1
■
LOOKING THROUGH THE WINDOW
Window functions can be divided into several categories: ranking functions, window
aggregates, accumulating window aggregates, offset functions, and statistical functions.
You can use these functions to assign a rank to each row, calculate summary values
without grouping, calculate running totals, include columns from different rows in your
results, and calculate percentages over a group. You’ll learn about these functions as you
read this book.
My favorite window function is called
LAG.
It is one of the offset functions, which you
will learn about in Chapter 7.
LAG
allows you to include any column from a different row
in your results. Using
LAG
is easier and performs better than older methods that do the
same thing.
Within the same year (just a few months apart), two different people approached
me for help with essentially the same problem: using data from the stock market, how
can one compare the closing price of a stock from one day to the next? The traditional
solution requires that each row of the data be joined to the prior row to get the closing
price from the previous day. By using the
LAG
function, the solution is not only simpler to
write, it also performs much better.
■
Note
If you would like to follow along with this example, a sample database with the
stock market data can be found along with the code for this chapter on the Apress site.
For a quick look at how to solve this problem using one of the traditional methods
and by using
LAG,
review and run Listing 1-1.
Listing 1-1.
Two Approaches to Solving the Stock Market Problem
USE StockAnalysis;
GO
--1-1.1 Using a subquery
SELECT TickerSymbol, TradeDate, ClosePrice,
(SELECT TOP(1) ClosePrice
FROM StockHistory AS SQ
WHERE SQ.TickerSymbol = OQ.TickerSymbol
AND SQ.TradeDate < OQ.TradeDate
ORDER BY TradeDate DESC) AS PrevClosePrice
FROM StockHistory AS OQ
ORDER BY TickerSymbol, TradeDate;
--1-1.2 Using LAG
SELECT TickerSymbol, TradeDate, ClosePrice,
LAG(ClosePrice) OVER(PARTITION BY TickerSymbol
ORDER BY TradeDate) AS PrevClosePrice
FROM StockHistory
ORDER BY TickerSymbol, TradeDate;
2
www.allitebooks.com

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin