SQL Server 2012高性能T-SQL与窗口函数实战

5星 · 超过95%的资源需积分: 9 161 浏览量更新于2024-07-28 1 收藏 3.6MB PDF 举报

"SQL Server 2012 高性能 T-SQL 使用窗口函数" 本资源是一本关于在 Microsoft SQL Server 2012 中利用窗口函数实现高性能查询的指南，作者是 Itzik Ben-Gan。这本书由 O'Reilly Media 出版，并获得了微软公司的授权。书中详细介绍了如何有效地运用 T-SQL（Transact-SQL）中的窗口函数来提高查询效率，这对于数据库管理员和开发人员来说是非常有价值的参考资料。窗口函数是 SQL Server 中的一个强大特性，它允许在单个查询中对一组相关的行进行计算，而不仅仅是当前行。这些函数使得复杂的分析操作变得简单，例如计算排名、移动平均、累积求和等，无需使用子查询或自连接，从而显著提升查询性能。书中的内容可能包括以下主题： 1. **窗口函数基础**：介绍窗口函数的基本概念，包括 ROW_NUMBER()、RANK() 和 DENSE_RANK()，它们用于为数据集中的行分配顺序号。 2. **LEAD() 和 LAG()**：这两个函数允许访问当前行的前一行或后一行的值，对于时间序列分析和趋势预测非常有用。 3. **OVER() 子句**：窗口函数的核心部分，用于定义“窗口”，即函数运算时考虑的行集合。 4. **PARTITION BY**：根据指定的列对数据进行分区，每个分区内的窗口函数独立计算。 5. **ORDER BY**：在窗口内排序行，以便在计算时按照特定顺序处理。 6. **SUM()、AVG()、MIN() 和 MAX() 窗口函数**：这些聚合函数在窗口内的应用，可以实现行级别的累计和滚动统计。 7. **COUNT() 窗口函数**：计算窗口内满足条件的行数，包括 COUNT(*) 和 COUNT(expression) 的用法。 8. **PERCENT_RANK()、CUME_DIST()**：这两个函数提供排名的百分比和累积分布，常用于数据分析。 9. **NTILE()**：将结果集分成多个等大小或近似等大小的部分，用于分桶或分区。 10. **实际案例与最佳实践**：通过真实世界的情景展示如何使用窗口函数解决问题，以及优化查询性能的技巧。书中还可能包含大量示例代码和练习，帮助读者深入理解和掌握这些功能。此外，书中提供的反馈渠道（http://www.microsoft.com/learning/booksurvey）让读者能够直接向出版社提供对书籍内容的意见和建议。这本书是 SQL Server 数据库专业人士的宝贵资源，特别是那些需要处理复杂查询和优化性能的开发者和管理员。通过学习和应用书中介绍的窗口函数技术，他们将能够更高效地管理数据并提升系统性能。

Introduction xvii

The survey is short, and we read every one of your comments and ideas. Thanks in

advance for your input!

If you have comments, questions, or ideas regarding the book, or questions that are

not answered by visiting the sites above, please send them to me via e-mail at:

itzik@SolidQ.com

Stay in Touch

Let’s keep the conversation going! We’re on Twitter: http://twitter.com/MicrosoftPress

CHAPTER 1

SQL Windowing

indow functions are functions applied to sets of rows dened by a clause called OVER. They are

used mainly for analytical purposes allowing you to calculate running totals, calculate moving

averages, identify gaps and islands in your data, and perform many other computations. These func-

tions are based on an amazingly profound concept in standard SQL (which is both an ISO and ANSI

standard)—the concept of windowing. The idea behind this concept is to allow you to apply various

calculations to a set, or window, of rows and return a single value. Window functions can help to solve

a wide variety of querying tasks by helping you express set calculations more easily, intuitively, and

efciently than ever before.

There are two major milestones in Microsoft SQL Server support for the standard window func-

tions: SQL Server 2005 introduced partial support for the standard functionality, and SQL Server 2012

added more. There’s still some standard functionality missing, but with the enhancements added in

SQL Server 2012, the support is quite extensive. In this book, I cover both the functionality SQL Server

implements as well as standard functionality that is still missing. Whenever I describe a feature for the

rst time in the book, I also mention whether it is supported in SQL Server, and if it is, in which version

of the product it was added.

From the time SQL Server 2005 rst introduced support for window functions, I found myself using

those functions more and more to improve my solutions. I keep replacing older solutions that rely on

more classic, traditional language constructs with the newer window functions. And the results I’m

getting are usually simpler and more efcient. This happens to such an extent that the majority of my

querying solutions nowadays make use of window functions. Also, standard SQL and relational data-

base management systems (RDBMSs) in general are moving toward analytical solutions, and window

functions are an important part of this trend. Therefore, I feel that window functions are the future in

terms of SQL querying solutions, and that the time you take to learn them is time well spent.

This book provides extensive coverage of window functions, their optimization, and querying solu-

tions implementing them. This chapter starts by explaining the concept. It provides the background

of window functions, a glimpse of solutions using them, coverage of the elements involved in window

specications, an account of the query elements supporting window functions, and a description of

the standard’s solution for reusing window denitions.

2 CHAPTER 1 SQL Windowing

Background of Window Functions

Before you learn the specics of window functions, it can be helpful to understand the context and

background of those functions. This section provides such background. It explains the difference

between set-based and cursor/iterative approaches to addressing querying tasks and how window

functions bridge the gap between the two. Finally, this section explains the drawbacks of alternatives

to window functions and why window functions are often a better choice than the alternatives. Note

that although window functions can solve many problems very efciently, there are cases where there

are better alternatives. Chapter 4, “Optimization of Window Functions,” goes into details about opti-

mizing window functions, explaining when you get optimal treatment of the computations and when

treatment is nonoptimal.

Window Functions Described

A window function is a function applied to a set of rows. A window is the term standard SQL uses to

describe the context for the function to operate in. SQL uses a clause called OVER in which you pro-

vide the window specication. Consider the following query as an example:

See Also See the book’s Introduction for information about the sample database TSQL2012 and companion

content.

USE TSQL2012;

SELECT orderid, orderdate, val,

RANK() OVER(ORDER BY val DESC) AS rnk

FROM Sales.OrderValues

ORDER BY rnk;

Here’s abbreviated output for this query:

orderid orderdate val rnk

-------- ----------------------- --------- ---

10865 2008-02-02 00:00:00.000 16387.50 1

10981 2008-03-27 00:00:00.000 15810.00 2

11030 2008-04-17 00:00:00.000 12615.05 3

10889 2008-02-16 00:00:00.000 11380.00 4

10417 2007-01-16 00:00:00.000 11188.40 5

10817 2008-01-06 00:00:00.000 10952.85 6

10897 2008-02-19 00:00:00.000 10835.24 7

10479 2007-03-19 00:00:00.000 10495.60 8

10540 2007-05-19 00:00:00.000 10191.70 9

10691 2007-10-03 00:00:00.000 10164.80 10

...

The OVER clause is where you provide the window specication that denes the exact set of rows

that the current row relates to, the ordering specication, if relevant, and other elements. Absent any

elements that restrict the set of rows in the window—as is the case in this example—the set of rows in

the window is the nal result set of the query.

Background of Window Functions 3

Note More precisely, the window is the set of rows, or relation, given as input to the logical

query processing phase where the window function appears. But this explanation probably

doesn’t make much sense yet. So to keep things simple, for now I’ll just refer to the nal

result set of the query, and I’ll provide the more precise explanation later.

For ranking purposes, ordering is naturally required. In this example, it is based on the column val

ranked in descending order.

The function used in this example is RANK. This function calculates the rank of the current row

with respect to a specic set of rows and a sort order. When using descending order in the ordering

specication—as in this case—the rank of a given row is computed as one more than the number

of rows in the relevant set that have a greater ordering value than the current row. So pick a row in

the output of the sample query—say, the one that got rank 5. This rank was computed as 5 because

based on the indicated ordering (by val descending), there are 4 rows in the nal result set of the

query that have a greater value in the val attribute than the current value (11188.40), and the rank is

that number plus 1.

What’s most important to note is that conceptually the OVER clause denes a window for the

function with respect to the current row. And this is true for all rows in the result set of the query. In

other words, with respect to each row, the OVER clause denes a window independent of the other

rows. This idea is really profound and takes some getting used to. Once you get this, you get closer

to a true understanding of the windowing concept, its magnitude, and its depth. If this doesn’t mean

much to you yet, don’t worry about it for now—I wanted to throw it out there to plant the seed.

The rst time standard SQL introduced support for window functions was in an extension docu-

ment to SQL:1999 that covered, what they called “OLAP functions” back then. Since then, the revisions

to the standard continued to enhance support for window functions. So far the revisions have been

SQL:2003, SQL:2008, and SQL:2011. The latest SQL standard has very rich and extensive coverage of

window functions, showing the standard committee’s belief in the concept, and the trend seems to be

to keep enhancing the standard’s support with more window functions and more functionality.

Note You can purchase the standards documents from ISO or ANSI. For example, from

the following URL, you can purchase from ANSI the foundation document of the SQL:2011

standard, which covers the language constructs: http://webstore.ansi.org/RecordDetail.aspx?

sku=ISO%2fIEC+9075-2%3a2011.

Standard SQL supports several types of window functions: aggregate, ranking, distribution, and

offset. But remember that windowing is a concept; therefore, we might see new types emerging in

future revisions of the standard.

Aggregate window functions are the all-familiar aggregate functions you already know—like SUM,

COUNT, MIN, MAX, and others—though traditionally, you’re probably used to using them in the

context of grouped queries. An aggregate function needs to operate on a set, be it a set dened by

4 CHAPTER 1 SQL Windowing

a grouped query or a window specication. SQL Server 2005 introduced partial support for window

aggregate functions, and SQL Server 2012 added more functionality.

Ranking functions are RANK, DENSE_RANK, ROW_NUMBER, and NTILE. The standard actually puts

the rst two and the last two in different categories, and I’ll explain why later. I prefer to put all four

functions in the same category for simplicity, just like the ofcial SQL Server documentation does. SQL

Server 2005 introduced these four ranking functions, with already complete functionality.

Distribution functions are PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, and PERCENTILE_DISC.

SQL Server 2012 introduces support for these four functions.

Offset functions are LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE. SQL Server 2012

introduces support for the rst four. There’s no support for the NTH_VALUE function yet in SQL Server

as of SQL Server 2012.

Chapter 2, “A Detailed Look at Window Functions,” provides the meaning, the purpose, and details

about the different functions.

With every new idea, device, and tool—even if the tool is better and simpler to use and imple-

ment than what you’re used to—typically, there’s a barrier. New stuff often seems hard. So if win-

dow functions are new to you and you’re looking for motivation to justify making the investment in

learning about them and making the leap to using them, here are a few things I can mention from my

experience:



Window functions help address a wide variety of querying tasks. I can’t emphasize this

enough. As mentioned, nowadays I use window functions in most of my query solutions. After

you’ve had a chance to learn about the concept and the optimization of the functions, the last

chapter in the book (Chapter 5) shows some practical applications of window functions. But

just to give you a sense of how they are used, querying tasks that can be solved with window

functions include:

•

Paging

•

De-duplicating data

•

Returning top n rows per group

•

Computing running totals

•

Performing operations on intervals such as packing intervals, and calculating the maximum

number of concurrent sessions

•

Identifying gaps and islands

•

Computing percentiles

•

Computing the mode of the distribution

•

Sorting hierarchies

•

Pivoting

•

Computing recency

剩余237页未读，继续阅读

tiger_skin

粉丝: 0

SQL Server 2012高性能T-SQL与窗口函数实战

Microsoft.SQL.Server.2012.High-Performance.T-SQL.Using.Window.Functions

Expert T-SQL Window Functions in SQL Server(Apress,2015)

Microsoft SQL Server 2012 高级开发

MariaDB and MySQL Common Table Expressions and Window Functions Revealed

微软内部资料-SQL性能优化2

Professional.MFC.with.VC6

PLSQL.Developer v11.0.4.1774 主程序+ v11中文包+keygen

plsqldev13.0.6.1911x32主程序+ v12中文包+keygen.rar

plsqldev13.0.6.1911x64主程序+ v12中文包+keygen.rar

优化SQL Server 2012：高效T-SQL与窗口函数应用

最新资源