269
Chapter 10: Aggregating, Windowing, and Ranking Data
10
following year’s total.This is accomplished by replacing UNBOUNDED in the OVER() clause
with a positive integer value. Instead of aggregating the value to the end of the set, the
integer specifi es the number of rows or values to precede or follow the current row. In other
words, add the value of the next n row(s) or the previous n row(s) to the current row, where
n represents the number of rows to aggregate with the current row. To fi nalize accessing
the previous or next row, simply subtract the value of the current row from the product of
the over() clause, and this yields the expected results.Summary ...........................................................................................................
SQL Server excels in aggregate functions, with the proverbial rich suite of features, and it is
capable of calculating sums and aggregates to suit nearly any need. From the simple COUNT()
aggregate function to the complex dynamic crosstab query and the new PIVOT command,
these query methods enable you to create powerful data analysis queries for impressive
reports. The most important points to remember about aggregation are as follows:■ (^) Aggregate queries generate a single summary row, so every column must be an
aggregate function.
■ (^) No performance difference exists between COUNT(*) and COUNT(pk).
■ Aggregate functions, such as COUNT(column) and AVG(column), ignore nulls,
which can be a good thing, and a reason why nulls make life easier for the database
developer.
■ (^) GROUP BY queries divide the data source into several segmented data sets and
then generate a summary row for each group. For GROUP BY queries, the
GROUP BY columns can and should be in the column list.
■ In the logical fl ow of the query, the GROUP BY occurs after the FROM clause and
the WHERE clause, so when coding the query, get the data properly selected and
then add the GROUP BY.
■ (^) Complex aggregations (for example, nested aggregations) often require CTEs or sub-
queries. Design the query from the inside out — that is, design the aggregate sub-
query fi rst and then add the outer query.
■ The OVER() clause generates the sort order for the ranking functions.
■ (^) The new T-SQL functions extend the existing capabilities of the Windowing and
Ranking functions.
c10.indd 269c10.indd 269 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM
http://www.it-ebooks.info