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