MySQL supports window functions since version 8.0, which greatly improves the processing efficiency of complex queries. 1. The window function can calculate a group of rows without merging and paralleling, such as ranking, cumulative statistics, etc.; 2. Common functions include ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER(), AVG() OVER(), LAG(), LEAD(); 3. When using it, you must cooperate with the OVER() clause to specify PARTITION BY and ORDER BY; 4. Typical application scenarios include ranking query (such as Top N per Group), cumulative statistics (such as Running Total), context comparison (such as month-on-month analysis); 5. Pay attention to version compatibility (only 8.0), performance optimization, and GROUP BY The difference and frame settings are as well as the settings of the frames to ensure accurate results and efficient query.

MySQL supports window functions starting with version 8.0, which greatly enhances its ability to handle complex queries. If you could only rely on subqueries or temporary tables to implement ranking, cumulative statistics and other functions, now using window functions will be more concise and efficient.

What is a window function?

A window function is a function that can perform calculations on a set of rows, but does not merge multiple rows into one row like an aggregate function. It keeps the data of each row and analyzes them, such as "what does each row rank within the current group".

Common window functions include:

• ROW_NUMBER() : Assign a unique sequence number to each line
• RANK() : The subsequent ranking will be skipped when there are parallel values.
• DENSE_RANK() : The subsequent ranking will not be skipped when there are parallel values.
• SUM() OVER() , AVG() OVER() : You can do moving average, cumulative summing, etc.
• LAG() , LEAD() : Get the value of a certain line before and after the current line

These functions must be used in conjunction with OVER() clause, you can specify the sorting method ( ORDER BY ) and the grouping method ( PARTITION BY ).

How to write a basic window function?

The syntax structure is roughly as follows:

 function_name(...) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

To give a simple example, you want to know how each employee ranks in their department:

 SELECT 
    emp_id,
    Dept_id,
    Salarary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

The meaning of this code is: divide the data windows by department, rank them from high to low in each department, and then number each person. In this way, you can know who is the first and second highest salary in the department.

Notice:

• PARTITION BY is optional, and if it is not added, the entire result set will be a big window.
• ORDER BY is generally required, otherwise the expected results may not be obtained.
• Different functions have different requirements for whether ORDER BY is required. For example, SUM() OVER() may not be required, but RANK() must be required.

Common application scenarios and techniques

1. Ranking questions (Top N per Group)

For example, find out the three people with the highest salary in each department:

 SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

Here we will first place the rankings and then filter out the top three. If you want to allow a tie-up (such as two first places), you can use RANK() or DENSE_RANK() .

2. Cumulative statistics (Running Total)

For example, counting the cumulative sum of sales per month:

 SELECT 
    month,
    Sales,
    SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly_sales;

This ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the beginning to the current row", which means adding up month by month.

3. Context comparison (month-on-month, year-on-year)

For example, want to know how much this month has increased compared to last month:

 SELECT 
    month,
    Sales,
    LAG(sales, 1) OVER (ORDER BY month) AS last_month_sales,
    sales - LAG(sales, 1) OVER (ORDER BY month) AS diff
FROM monthly_sales;

LAG() function can get the value of the previous line, which is suitable for month-on-month change analysis.

Things to note when using window functions

• Version compatibility : Window functions are only supported in MySQL 8.0 and cannot be used in old versions.
• Performance issues : Window functions may bring additional overhead, especially when there is a large amount of data, pay attention to indexing and partitioning design.
• Avoid misuse of GROUP BY : Window functions are not tools that replace GROUP BY , and the two use differently. If you just want statistics, you should still use aggregate functions first.
• Understand the concept of frames (Frame) : ROWS and RANGE control the window range, affecting the accuracy of the result, especially when it comes to time series.

Basically that's it. After mastering several common functions and syntax structures, you will find that window functions are clearer and more efficient than the original writing method in many scenarios.

The above is the detailed content of how to use window functions in mysql. For more information, please follow other related articles on the PHP Chinese website!