loading..
Русский    English
18:30

Window functions

In fact, we met with these functions, when considered ranking functions. Only now we'll use the aggregate functions instead of functions that specify the number/rank of the row. There is another difference (in the implementation of Microsoft SQL Server 2005/2008) - the OVER() clause does not contain additional clause ORDER BY, because the aggregated value does not depend on sorting rows in the “window”.

As mentioned early, clause PARTITION BY defines a "window", i.e. set of rows, which are characterized by the equality of values of list of expressions, specified in this clause. If the clause PARTITION BY absent, then the aggregate functions applied to the entire result row-set of query. In contrast to the classical groups, where we get one row for each group, which may include aggregated values calculated for each group, here we can add the aggregated value to the detailed (ungrouped) row. Consider a few examples of the use of window functions.

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100