introduction
This section mainly introduces several window functions commonly used in MySQL and their usage
Text link
Task05: window functions, etc. - Tianchi Dragon Ball plan SQL training camp
Window function
< window function > over ([partition by < column name >]
Order by < column name for sorting >)
PARTITION BY is used for grouping, that is, selecting which window to view, which is similar to the grouping function of the GROUP BY clause. However, the PARTITION BY clause does not have the summary function of the GROUP BY clause and does not change the number of rows recorded in the original table.
ORDER BY is used to sort, that is, to determine the sort rule (field) in the window.
SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product
PARTITION BY can set the window object range. In this example, in order to sort by product category, we specify product_type. That is, a commodity category is a small "window".
ORDER BY can specify which column and order to sort. In order to sort the sales unit price in ascending order, we specify sales_ price. In addition, the ORDER BY in the window function is the same as the ORDER BY at the end of the SELECT statement. You can specify the ascending / descending order through the keyword ASC/DESC. If this keyword is omitted, ASC will be followed by default, that is
Window function type
First, aggregate functions such as SUM, MAX and MIN are used in window functions
The second is RANK and DENSE_RANK and other special window functions for sorting
RANK function
When calculating sorting, if there are records with the same order, the subsequent order will be skipped.
Example) when there are 3 records in the first place: 1, 1, 1, 4
DENSE_RANK function
The same sort is calculated. Even if there are records with the same order, the subsequent order will not be skipped.
Example) when there are 3 records in the first place: 1, 1, 1, 2
ROW_NUMBER function
Assign unique consecutive bits.
Example) when there are 3 records in the first place: 1, 2, 3 and 4
SELECT product_name ,product_type ,sale_price ,RANK() OVER (ORDER BY sale_price) AS ranking ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num FROM product
Use of aggregate function in window function
The use of aggregate functions in windowing functions is the same as that of previous special window functions, except that the result is an accumulated aggregate function value.
SELECT product_id ,product_name ,sale_price ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg FROM product;
Application of window function - Calculation of moving average
As mentioned above, when the aggregation function is used in the window function, it calculates the aggregation of all the data accumulated to the current row. In fact, you can also specify a more detailed summary range. This summary range becomes a frame.
< window function > over (order by < column name for sorting >
ROWS n PRECEDING )
< window function > over (order by < column name for sorting >
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
Precise ("before"), specify the frame as "n lines before" plus its own line
FOLLOWING ("after"), specify the frame as "n lines after the deadline", plus its own line
Between 1 predicting and 1 following, specify the frame as "1 line before" + "1 line after" + "self"
SELECT product_id ,product_name ,sale_price ,AVG(sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg ,AVG(sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM product
ROLLUP - calculate total and subtotal
Regular GROUP BY can only get the subtotal of each category. Sometimes, you need to calculate the total of the categories. You can use the ROLLUP keyword.
SELECT product_type ,regist_date ,SUM(sale_price) AS sum_price FROM product GROUP BY product_type, regist_date WITH ROLLUP