Click on “SQL database development”, \

Set it as “top or star mark” to deliver dry goods in the first time

OVER the definition of

OVER is used to define a window for rows that operates on a set of values, does not require the GROUP BY clause to GROUP the data, and can return both the base row column and the aggregate column in the same row.

The grammar of the OVER

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY clause;

The ORDER BY clause sorts.

The window function OVER() specifies a set of rows, and the windowing function evaluates the values of each row in the result set output from the window function.

Windowing functions can GROUP data without using GROUP BY, and can return both the base row column and the aggregate column.

OVER the use of the

The OVER windowing function must be used with the aggregate function or sorting function, which generally refers to the SUM(),MAX(),MIN,COUNT(),AVG() and other common functions. RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE(), etc.

An example of OVER used in an aggregate function

Let’s use the SUM and COUNT functions as examples.

CREATE TABLE Employee (ID INT PRIMARY KEY, Name VARCHAR(20), 
GroupName VARCHAR(20),
Salary INT
) 
INSERT INTO  Employee 
VALUES(1.'Ming'.'Development Department'.8000), 
      (4.'zhang'.'Development Department'.7600), 
      (5.'white'.'Development Department'.7000), 
      (8.'wang'.'Finance Department'.5000), 
      (9, null,'Finance Department',NULL), 
      (15.'liu'.'Finance Department'.6000), 
      (16."Gao".Administration Department.4500), 
      (18.'wang'.Administration Department.4000), 
      (23.'xiao li'.Administration Department.4500), 
      (29.'xiao wu'.Administration Department.4700);
Copy the code

The windowing function after SUM

SELECT *, SUM(Salary) OVER(PARTITION BY Groupname) SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) SUM(Salary) OVER(ORDER BY ID) Salary = SUM(Salary) OVER(Copy the code

(Tip: Slide code left and right)

The results are as follows:

Each meaning of the windowing function is different, so let’s interpret it concretely:

SUM(Salary) OVER (PARTITION BY Groupname)

Group only Groupname column after PARTITION BY, and calculate the sum of Salary after grouping.

SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

Select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname, select * from Groupname.

****SUM(Salary) OVER (ORDER BY ID)

Only the ID content after ORDER BY is sorted, and the Salary after sorted is accumulated.

****SUM(Salary) OVER ()

Salary is summarized

The window function after COUNT

SELECT *, COUNT(*) OVER(PARTITION BY Groupname) number of groups, COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) number of groups, COUNT(*) OVER(ORDER BY ID) COUNT(*) OVER() from EmployeeCopy the code

The result returned is shown below:

Each windowing function after SUM will not be interpreted individually, but can be compared with the windowing function after SUM.

An example of OVER used in sorting functions

We demonstrate the four sorting functions one by one

SELECT * from t AS (SELECT * from t)1 StuID,'one' ClassName,70 Score
UNION ALL
SELECT 2.'one'.85
UNION ALL
SELECT 3.'one'.85
UNION ALL
SELECT 4.'second class'.80
UNION ALL
SELECT 5.'second class'.74
UNION ALL
SELECT 6.'second class'.80
)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores
Copy the code

ROW_NUMBER()

The ROW_NUMBER() function is used to sort the data from the SELECT tables by adding a serial number to each row. It is not used to rank students. It is usually used for paging query, such as query the top 10 10 to 100 students. ROW_NUMBER() must be used with ORDER BY or an error will be reported.

Rank students’ grades

SELECT *, ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS FROM Scores;Copy the code

The results are as follows:

The PARTITION BY and ORDER BY functions are used for grouping and sorting just like the aggregate functions we saw above.

Additionally, the ROW_NUMBER() function can take data in a specified order.

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS Scores FROM Scores) SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS Scores FROM Scores) Total sorts =2;
Copy the code

The results are as follows:

RANK()

ROW_NUMBER(); ROW_NUMBER(); ROW_NUMBER(); ROW_NUMBER(); ROW_NUMBER() is a sort. When there are students with the same score, ROW_NUMBER() is sorted in order. They have different numbers and Rank() is not the same. If they are the same, they rank the same. Here’s an example:

The sample

SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
Copy the code

Results:

The top is the result of ROW_NUMBER() and the bottom is the result of RANK(). When there are two students with the same score, there is a change in the inside. RANK() is 1-1-3-3-5-6, while ROW_NUMBER() is 1-2-3-4-5-6.

DENSE_RANK()

DENSE_RANK() : DENSE_RANK() : DENSE_RANK() : DENSE_RANK() : DENSE_RANK() : DENSE_RANK() : DENSE_RANK() : DENSE_RANK() In particular, DENSE_RANK() is a continuous ranking function, and RANK() is a jump RANK. In general, the ranking function used is RANK().

The sample

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],* 
FROM Scores;

SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],* 
FROM Scores;
Copy the code

The results are as follows:

\

The top is the result of RANK() and the bottom is the result of DENSE_RANK()

NTILE()

Definition: The NTILE() function distributes rows from an ordered partition into a specified number of groups. Each group is numbered, starting at 1. * * * *

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS FROM Scores; SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS FROM Scores; SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS FROM Scores;Copy the code

The results are as follows:

\

Partition the queried records according to the parameters in the NTILE function.

conclusion

The OVER windowing function is often used in our work, especially when we do data analysis and calculation, we often need to group and sort the data. In addition, there are many functions that are used together with OVER, such as LEAD function, LAG function, STRING_AGG function, etc., all use the windowing function OVER, and the usage method must be mastered.

I am my brother Yue, and finally I would like to share with you the PDF electronic version of MY SQL two-piece set: "SQL Basics 2nd Edition" and "SQL Advanced 2nd Edition". There are various grammar explanations, a large number of examples and annotations, and so on, very easy to understand, convenient for everyone to follow together to practice. There is a need for readers can download learning, in the following public number "data front" (not this number) back keyword: SQL, on the line data front -- End -- back keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Goodbye, the father of anti-virus software, Wang Jiangmin! Another programmer, arrested! My computer is not connected to the Internet, so it is secure. Hacker: You have a fan... CentOS is dead, "free" RHEL without a set of simple and useful SQL scripts, there is always your needCopy the code