The implementation here is based on MySQL 8.
1. Meaning and understanding of window function
Window functions perform aggregate like operations on a series of rows. The difference is that the aggregate function returns only one row result for multiple rows, while the window function returns multiple rows for multiple rows.
#Original data query SELECT * FROM sales;
#Aggregate function example #Find the sum of profiles SELECT SUM(profit) AS profit_total FROM sales; #Find the sum of each country's profit SELECT country,SUM(profit) AS profit_total FROM sales GROUP BY country;
#Window function example SELECT country, SUM(profit) OVER() AS profit_total, SUM(profit) OVER(PARTITION BY country) AS country_profit_total FROM sales;
- From this example, we can see that the window function adds a field content to each row of records, while the aggregation function combines multiple rows of data into one row according to the return of aggregation.
- The window function has a scope, which is specified by the OVER () keyword. The scope here is the window.
2. Syntax of window function
SELECT column_names, window_function([column]) OVER (window_name [ORDER clause] [frame clause]) AS alise FROM table_name WINDOW window_name AS (PARTITION BY column [ORDER BY column_names]);
2.1 understanding of window name syntax
It is to extract the contents in the OVER() bracket as a window and give an alias for the window to reduce the complexity of reuse of the window.
#Understanding of window aliases #Use window alias SELECT country, SUM(profit) OVER window_all AS profit_total, SUM(profit) OVER window_country AS country_profit_total FROM sales WINDOW window_all AS (), window_country AS (PARTITION BY country );
#Do not use window aliases SELECT country, SUM(profit) OVER() AS profit_total, SUM(profit) OVER(PARTITION BY country) AS country_profit_total FROM sales;
- Alias window with window_all and window_country replaces () and PARTITION BY country.
- The WINDOW alias is created by combining the WINDOW keyword and the AS keyword.
- Create multiple window aliases separated by commas.
2.2 understanding of sorting statements in Windows
When we perform some window function operations, sometimes we need to calculate the results according to certain sorting rules, so we must specify sorting rules for the window at this time.
#Understanding of sorting in Windows SELECT `year`, country, profit, RANK ( ) OVER window_country_noorder AS rank_noorder, RANK ( ) OVER window_country_order AS rank_order FROM sales WINDOW window_country_noorder AS ( PARTITION BY country ), window_country_order AS ( PARTITION BY country ORDER BY profit DESC );
- The rank function returns the ranking. When the window does not use order by, it is considered that all records are of the same level, so 1 is displayed. When order by is used, it is sorted according to the order by.
- ORDER BY is optional in window definition. It is mainly related to the window functions used and the arrangement rules.
2.3 understanding of window subset
Subsets are usually used as sliding windows, which can be understood as the result of window functions acting on subsets.
Subsets can be understood as selecting a part of records in the window. There are two methods of selection, based on row ROWS or RANGE. When creating a row or RANGE subset, you usually use BETWEEN start AND end, where start and end are created using the following keywords.
Keywords used for subset creation
|CURRENT ROW||The boundary is the current row, which is generally used with other range keywords|
|UNBOUNDED PRECEDING||The boundary is the first row in the partition|
|UNBOUNDED FOLLOWING||The boundary is the last row in the partition|
|expr PRECEDING||The boundary is the value of the current row minus expr|
|expr FOLLOWING||The boundary is the value of the current row plus expr|
#Create a subset of rows SELECT `year`, country, profit, AVG(profit) OVER w AS AVG_Follwing1 FROM sales WINDOW w AS (PARTITION BY country ORDER BY profit ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
2.4 window function
For detailed window functions supported by MySQL, see Official documents.
Some common window functions are as follows:
Sequence number function: row_number() / rank() / dense_rank()
Distribution function: percent_rank() / cume_dist()
Before and after functions: lag() / lead()
Head and tail function: first_val() / last_val()
Other functions: nth_value() / nfile()
Aggregation window function: the same as aggregation function