T-SQL Window Functions - Chapter 1
In what order are SQL Clauses executed? <<phases in which window functions are allowed>>
1 - FROM 2 - WHERE 3 - GROUP BY 4 - HAVING 5 - SELECT <<5-1. Evaluate Expressions>> 5-2. Remove Duplicates 6 - <<ORDER BY>>
How can you visualize a Window Function for the RANK() Function? RANK() OVER ( ORDER BY val DESC ) AS rnk
Absent any restrictions in the window specification, each window consists of the set of all rows from the result set of the query as the starting point. But you can add elements to the window specification that will further restrict the set of rows in each window.
What does an Aggregate work on?
An aggregate function needs to operate on a set, be it a set defined by a grouped query or a window specification.
Define a Set
By a "set" we mean any collection M into a whole of definite, distinct objects m (which are called the "elements" of M) of our perception or of our thought.
Framing
Framing is essentially another filter that further restricts the rows in the partition. It is applicable to aggregate window functions as well as to three of the offset functions: FIRST_VALUE, LAST_VALUE, and NTH_VALUE. Think of this windowing element as defining two points in the current row's partition based on the given ordering, framing the rows that the calculation will apply to
What happens if a PARTITION BY clause is not specified?
If a PARTITION BY clause is not specified, the window is not restricted. (i.e. there is not filter applied to the window). NOTE: different functions in the same query can have different partitioning specifications.
Offset Functions
LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
SELECT custid , orderid , val , RANK() OVER ( ORDER BY val DESC ) AS rnk_all , RANK() OVER ( PARTITION BY custid ORDER BY val DESC ) AS rnk_cust FROM Sales.OrderValues;
Observe that the first RANK function (which generates the attribute rnk_all) relies on the default partitioning, and the second RANK function (which generates rnk_cust) uses explicit partitioning by custid. The arrows point from the result values of the functions to the window partitions that were used to compute them.
Distribution Functions
PERCENT_RANK, CUME_DIST, PERCENTLIE_CONT, PERCENTILE_DISC
Ranking Functions
RANK, DENSE_RANK, ROW_NUMBER, NTILE
Partitioning
Restricts the window of the current calculation to only those rows from the result set of the query that have the same values in the partitioning columns as in the current row. [partitioning suggests filtering rather than grouping.]
Describe the query that ranks orders by a customer by the grand total, as well as customer total.
SELECT custid , orderid , val , RANK() OVER ( ORDER BY val DESC ) AS rnk_all , RANK() OVER ( PARTITION BY custid ORDER BY val DESC ) AS rnk_cust FROM Sales.OrderValues;
Using a window function, how do you calculate the sum of all values?
SUM(val) OVER()
Using a window function, how do you calculate the sum of all values from the result set of the query where the customer ID is the same as in the current row? [use the partitioning capabilities of window functions]
SUM(val) OVER(PARTITION BY custid)
Aggregate Functions
SUM, COUNT, MIN, MAX etc.
Framing with The ROWS option
The ROWS option allows you to indicate the points in the frame as an offset in terms of the number of rows with respect to the current row.
Ordering
The ordering element defines the ordering for the calculation, if relevant, within the partition. The ordering element is an aid to define which rows to restrict in the window
Which query clauses are window functions supported in ? (SELECT, FROM, WHERE, GROUP BY, ORDER BY)
Window functions aren't supported in all query clauses; rather, they're supported only in the SELECT and ORDER BY clauses. The reason for the limitation is to avoid ambiguity by operating on (almost) the final result set of the query as the starting point for the window.
What 4 types of window functions are supported in SQL 2012
aggregate, ranking, distribution, offset
