MySQL From version 8.0 start , Support window function . Window functions allow you to use the new , A simpler way to solve the query problem , And has better performance .
Suppose we have one sales surface , Store sales by employee and fiscal year , As shown below ：
CREATE TABLE sales( sales_employee VARCHAR(50) NOT NULL, fiscal_year INT NOT
NULL, sale DECIMAL(14,2) NOT NULL, PRIMARY KEY(sales_employee,fiscal_year) );
INSERT INTO sales(sales_employee,fiscal_year,sale) VALUES('Bob',2016,100),
('Bob',2017,150), ('Bob',2018,200), ('Alice',2016,150), ('Alice',2017,100),
('Alice',2018,200), ('John',2016,200), ('John',2017,150), ('John',2018,250);
SELECT * FROM sales;
Understanding window functions may be easier to start with aggregate functions .
Aggregate functions aggregate data from multiple rows into a single result row . for example , following SUM() Function returns the total sales of all employees in the record year ：
SELECT SUM(sale) FROM sales;
The GROUP BY Clause allows you to apply aggregate functions to a subset of rows . for example , You may want to calculate total sales by fiscal year ：
SELECT fiscal_year, SUM(sale) FROM sales GROUP BY fiscal_year;
In both cases , Aggregate functions reduce the number of rows returned by the query .
With GROUP BY Clause has the same aggregate function , Window functions also operate on subsets of rows , But they do not reduce the number of rows returned by the query .
for example , The following query returns sales per employee , And total employee sales by fiscal year ：
SELECT fiscal_year, sales_employee, sale, SUM(sale) OVER (PARTITION BY
fiscal_year) total_sales FROM sales;
In this example , The SUM() Function is used as a window function , This function is applied to the OVER Clause content defines a set of rows to operate on .SUM() The set of rows to which this function is applied is called a window .
The SUM() Window function by fiscal year like it and query report not only total sales GROUP BY clause , And every row in the result , Instead of the total number of rows .
It should be noted that the result set of window function is carried out after all JOIN,WHERE,GROUP BY, as well as HAVING Clause and before ORDER BY,LIMIT and SELECT
DISTINCT.
<> Window function syntax
The general syntax for calling window functions is as follows ：
window_function_name(expression) OVER ( [partition_defintion]
[order_definition] [frame_definition] )
In this grammar ：
1, first , Specifies the window function name , Follow the expression .
2, secondly , appoint OVER Clause with three possible elements ： Partition definition , Sequence definition and frame definition .
The OVER Open and right parentheses after clause are mandatory , Even if there is no expression , for example ：
window_function_name(expression) OVER()
partition_clause syntax
take partition_clause Rows are divided into blocks or partitions . The two zones are separated by zone boundaries .
Window functions are executed within a partition , And reinitializes when crossing partition boundaries .
Of partition_clause The syntax is as follows ：
PARTITION BY <expression>[{,<expression>...}]
You can use the PARTITION BY Clause to specify one or more expressions . Multiple expressions are separated by commas .
order_by_clause syntax
The order_by_clause The grammar is as follows ：
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
The ORDER BY Clause specifies how rows are sorted in the partition . You can sort data within partitions on multiple keys , Each key is specified by an expression . Multiple expressions are also separated by commas .
With the PARTITION BY Clauses are similar ORDER BY, All window functions also support this clause . however , Only for ORDER BY It makes sense for order sensitive window functions to use this clause .
frame_clause syntax
A frame is a subset of the current partition . To define a subset , Please use frame clause , As shown below ：
frame_unit {<frame_start>|<frame_between>}
Defines the frame relative to the current line , This allows the frame to move within the partition according to the position of the current line in its partition .
Frame unit specifies the type of relationship between the current row and the frame row . It can be ROWS or RANGE. The offset of the current line and frame line is the line number , If the frame unit is ROWS Row value , The row value is in frame units RANGE.
The frame_start and frame_between Define frame boundaries .
take frame_start Contains one of the following ：
1,UNBOUNDED PRECEDING：frame Start with the first row of the partition .
2,N PRECEDING： Physics before the first current line N That's ok .N It can be a literal number or an expression of the calculated result .
3,CURRENT ROW： Currently calculated rows
The frame_between as follows ：
BETWEEN frame_boundary_1 AND frame_boundary_2
The frame_boundary_1 and frame_boundary_2 Each may contain one of the following ：
1,frame_start： As mentioned above
2,UNBOUNDED FOLLOWING： The frame ends on the last line of the partition .
3,N FOLLOWING： Physics after current line N That's ok .
If not frame_definition stay OVER Clause , be MySQL The following frames are used by default ：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
<>MySQL Window function list
The following table shows MySQL Window function in ：
Name Description
CUME_DIST Calculate the cumulative distribution of the median of a set of values
DENSE_RANK according to ORDER
BY Clause assigns a rank to each row in its partition . It assigns the same ranking to rows with the same value . If two or more rows have the same level , There is no gap in the sort value sequence
FIRST_VALUE Returns the value of the specified expression relative to the first line in the window frame
LAG Returns the first row before the current row in the partition N The value of the row . If there is no previous line , Return to NULL
LAST_VALUE Returns the value of the specified expression relative to the last line in the window frame
LEAD Returns the first row after the current row in the partition N The value of the row . If there are no subsequent lines , Return to NULL
NTH_VALUE From the N Row returns the value of the parameter
NTILE Assign the rows of each window partition to a specified number of ranked groups
PERCENT_RANK Calculates the percentile of rows in a partition or result set
RANK And DENSE_RANK() The function is similar , Except when two or more rows have the same rank, there is a gap in the sort value sequence
ROW_NUMBER Assign a continuous integer to each row in its partition
Technology