In-depth discussion of window functions (1)

 Before the emergence of window functions, there were very many SQL statements that were difficult to solve, and many of them had to be completed through complicated subqueries or stored procedures. To solve these problems, the iso SQL standard added Window Function in 2003. The use of window function makes these classic problems easy to solve. Currently, window functions are supported in mainstream databases such as MSSQLServer, Oracle, and DB2, but it is a pity that MySQL has not yet provided support for window functions.

For a clearer understanding, we create a table and perform related queries (the result of MSSQLServer)

MySQL, MSSQLServer, DB2:

CREATE TABLE T_Person (FName VARCHAR(20),FCity VARCHAR(20), FAge INT,FSalary INT) 

ORACLE:
Create Table t_person (fname varchar2 (20), fcity varchar2 (20), Fage int, fsalary INT)

Note: The following results are only demonstrated in MSSQLServer:

The t_person table stores the personnel information. The fname field is the personnel name, And the fcity field is the city name of the person,

The Fage field indicates the person's age, and the fsalary field indicates the person's salary. Then run the following SQL statement

Insert some demo data in the table:

INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Tom‘,‘BeiJing‘,20,3000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Tim‘,‘ChengDu‘,21,4000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Jim‘,‘BeiJing‘,22,3500); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Lily‘,‘London‘,21,2000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘John‘,‘NewYork‘,22,1000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘YaoMing‘,‘BeiJing‘,20,3000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Swing‘,‘London‘,22,2000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Guo‘,‘NewYork‘,20,2800); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘YuQian‘,‘BeiJing‘,24,8000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Ketty‘,‘London‘,25,8500); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Kitty‘,‘ChengDu‘,25,3000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Merry‘,‘BeiJing‘,23,3500); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Smith‘,‘ChengDu‘,30,3000); INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES(‘Bill‘,‘BeiJing‘,25,2000); INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES(‘Jerry‘,‘NewYork‘,24,3300); 

View the table content:

Select * From t_person

Window Function Introduction
Like a clustering function, the window function performs aggregate computing on row set groups, but it is not like a common aggregate function.

Each group returns only one value. The window function can return multiple values for each group.

A set group is a form. In iso SQL, this function is defined as a window function, which is called an analysis function in Oracle,

In DB2, OLAP functions are called.

To calculate the total number of all personnel, we can run the following SQL statement:
Select count (*) from t_person
In addition to this simple method of use, you sometimes need to explain these Aggregate functions from the rows in the aggregate function.

Value. For example, we want to query the information of every employee whose salary is less than 5000 yuan (city and age ).

The number of employees whose total salary is less than 5000 yuan is displayed in each row. Write the following SQL statement:
Select fcity, Fage, count (*)
From t_person
Here fsalary <5000
After running the preceding SQL statement, we will get the following error message:
The't _ person. fcity' column in the selected list is invalid because it is not included in the aggregate function or

Group by clause.
This is because all columns not included in aggregate functions must be declared in the group by clause,

The following changes can be made:
Select fcity, Fage, count (*)
From t_person
Where fsalary: <5000
Group by fcity, Fage
After the execution is complete, the following running results are displayed in the output results:

The running result is totally different from what we imagined, because the group by clause

So the objects computed by Aggregate functions are not all result sets, but each group.

Subqueries can be used to solve the problem. The SQL statement is as follows:
Select fcity, Fage,
(
Select count (*) from t_person
Where fsalary: <5000
)
From t_person
Where fsalary: <5000
After the execution is complete, the following running results are displayed in the output results:

Although subqueries can solve the problem, subqueries are difficult to use.

The implementation can be greatly simplified. The following SQL statements demonstrate the same effect using the window function:

Select fcity, Fage, count (*) over ()
From t_person
Where fsalary: <5000
After the execution is complete, the following running results are displayed in the output results:

Different from Aggregate functions, the window function adds over keyword after the aggregate function.
The Calling format of window function is as follows:
Function Name (column) over (option)
Over keyword indicates that the function is treated as a window function rather than an aggregate function. SQL standard agrees to aggregate all
The over keyword function is used to distinguish between the two methods.
In the preceding example, the count (*) over () function returns all values for each row of the query results.
Number of rows that meet the condition. In the brackets after overkeyword, options are often added to change the window for aggregation operations.
Port range. If the option in the brackets after overkeyword is null, the window opening function performs all rows in the result set.
Aggregation.

Conclusion: The above describes the basic usage of window function. I hope it will be helpful to you!

In-depth discussion of window functions (1)