The implementation here is based on MySQL 8.

catalogue

1. Meaning and understanding of window function

2. Syntax of window function

2.1 understanding of window name syntax

2.2 understanding of sorting statements in Windows

2.3 understanding of window subset

2.4 window function

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;

 

understand:

1. 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.
2. 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;

understand:

1. Alias window with window_all and window_country replaces () and PARTITION BY country.
2. The WINDOW alias is created by combining the WINDOW keyword and the AS keyword.
3. 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 );

Understanding:

1. 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.
2. 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

keyword	meaning
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