SQL Server Open Window function

Today will introduce SQL Sever open window function, what is the window function, do not understand it. Anyway, for me, I was puzzled, and I heard it for the first time. So, what is the window function, in fact, can be understood as a reinforcing version of the aggregation function. Because the aggregate function is used (excluding the case of subqueries), the entire query can only be the aggregate column return value, not the return value of the underlying row. It is necessary to use complex subqueries or stored procedures to resolve the return value of the underlying row. However, it is easy to solve with the window function, which can return both the column of the underlying row and the aggregate column in the same row. In ISO SQL, this function is a window-opening function, and in Oracle it is called an analytic function, whereas in DB2 it is called an OLAP function.

The window function, like an aggregate function, is an aggregate calculation of the set of rows. It is used to define a window for a row (where the window refers to the collection of rows to be manipulated), which operates on a set of values, does not need to group the data using the GROUP BY clause, and can return both the column and the aggregate column of the underlying row in the same row. Anyway, I understand that this function has used a good subquery or other way to get the values of the aggregated columns to merge with me.

Let's take a step-by-step example in the book, assuming that to calculate the total number of people, we can execute the following SQL statement:

SELECT COUNT (FName) from T_person

This method is more straightforward, returning only the value of an aggregate column, without any column values for the underlying row. However, it is sometimes necessary to access these aggregated computed values (that is, the columns of the underlying rows) from the column of the rows in the aggregation function. For example, we would like to inquire about employee information (city and age) with a salary of less than $5000, and to show the number of employees in each row that are less than $5000, try writing the following SQL statement:

SELECT FName, Fcity, Fage, Fsalary, COUNT (FName) from T_personwhere fsalary<5000

After executing the above SQL we will get the following error message :
The column ' T_person.fcity ' in the select list is not valid because the column is not contained in an aggregate function or a GROUP BY clause.
This is because all columns that are not included in the aggregate function must be declared in the GROUP BY clause , and the subquery can be resolved as follows:

Select FName, Fcity, Fage, Fsalary, (select COUNT (FName) from T_personwhere fsalary<5000) from T_personwhere fsalary <5000

Although the use of subqueries can solve this problem, but the use of subqueries is very cumbersome, using the window function can be greatly simplified implementation, the following SQL statement shows if you use the window function to achieve the same effect:

SELECT FName, Fcity, Fage, Fsalary, COUNT (FName) over () from T_personwhere fsalary<5000

As you can see, unlike aggregate functions, the window function adds an over keyword after the aggregation function.
The call format for the window function is: function name (column) over (option)

I am using SQL Server R2, and I do not know when to start, SQL Server also supports the use of the ORDER BY clause in the window function (note: The book says that MSSQLServer is not supported by the use of an ORDER BY clause in the window function). Anyway, I've integrated the online content. Because the window function supports the ORDER BY clause, the window function is divided into two main classes.

The first Category: aggregate window function = = = "Aggregate function (column) over (option), where the option can be a partition by clause, but not an ORDER BY clause

The second category: sort the window function = = = "Sort function (column) over (option), where the option can be an ORDER BY clause, or the over (the PARTITION by clause ORDER by clause), but not the PARTITION by clause

Aggregate open Window functions

The over keyword means that the aggregate function is treated as an aggregate window function instead of an aggregate function. The SQL standard allows all aggregate functions to be used as aggregation window functions.
In the example above, the open Window function count (*) over () returns the number of all qualifying rows for each row of the query result. In parentheses after the over keyword, options are often added to change the window scope for the aggregation operation. If the option in parentheses after the over keyword is empty, the open window function aggregates all rows in the result set.

PARTITION BY clause

The Over keyword of the window function can use the partition by clause in parentheses to define the partition of the row for aggregation calculations. Unlike the GROUP BY clause, the partition created by the PARTITION by clause is independent of the result set, and the partitions created are for aggregation only, and the partitions created by different window functions do not affect each other. The following SQL statement is used to display information about each individual member and the number of people in the city:

SELECT FName, Fcity, Fage, Fsalary,count (FName) over (PARTITION by fcity) from T_person

Over (PARTITION by fcity) indicates that the result set is partitioned by fcity, and the aggregation calculation results of the group to which the current row belongs are calculated. Multiple window functions can be used simultaneously in the same SELECT statement, and these windowing functions do not interfere with each other. For example, the following SQL statement is used to display information about each individual member, the number of people in the city, and the number of peers:

SELECT fname,fcity, Fage, Fsalary,count (FName) over (PARTITION by fcity), COUNT (FName) through (PARTITION by Fage) from T_ Person

Sort open Window functions

For the Sort window function, the window functions it supports are: Row_number (line number), rank (rank), Dense_rank (dense rank), and Ntile (group rank).

Let's look at a SQL statement:

Select  FName, Fsalary, fcity, Fage,  row_number () over (order by fsalary) as RowNum,  rank () over (order by Fsala RY) as rank,  Dense_rank () over (order by fsalary) as Dense_rank,  ntile (6) Through (order by fsalary) as Ntile from  T _person ORDER BY  FName  

The results are as follows (for friends who want to try it out, you have to work hard, download ebooks or buy books to learn.) Because I am confined to the space, omitted to most of the content OH):

See the above results, below to introduce the relevant content. The end result we get is shown in ascending order according to FName.

For Row_number () over (order by fsalary) as rownum, this sort window function is sorted by fsalary ascending order, and the ordinal of the sorted result is derived

For rank () over (order by fsalary) as rank, this sort descriptor is sorted by Fsalary ascending order and the rank number of the sorted result is obtained. This function to find the ranking results can be ranked, and ranked after the ranking will be tied to the rank plus the number of side-by-side (simply say everyone only one ranking, and then appear in the case of two tie first, this time ranked in the first two people will be the third, that is, no second, but two first)

For Dense_rank () over (order by fsalary) as Dense_rank, this sort function is sorted in fsalary ascending order, and the rank number of the sorted result is obtained. This function differs from the rand () function in that the rank after rank is only a side rank plus 1 (simply saying that each person has only one rank, and then there are two side-by-side first-name cases, when the two first-ranked people will be second, two first, one second)

For Ntile (6) Over (order by fsalary) as ntile, this sort function is sorted in ascending order of fsalary and the number of groups of sorted results is obtained.

The sort function is similar to the aggregate window function and also supports the use of the partition by statement in the over clause. For example:

Select  FName, Fsalary, fcity, Fage,  row_number () over (partition by FName  ORDER by Fsalary) as RowNum,  Rank () over (partition by FName ORDER by Fsalary) as rank,  Dense_rank () over (partition by FName ORDER by Fsalary) as De Nse_rank,  ntile (6) over (partition by FName ORDER by Fsalary) as Ntile from  t_person order by  FName

For the partition by clause, see the above introduction, here is no longer cumbersome. It is important to note, however, that the partition by clause in the Sort window function needs to be placed before the ORDER BY clause.

SQL Server window-opening function