Mysql8.0 uses window functions to solve sorting problems

Introduction to MySQL Window Functions

MySQL has supported window functions since MySQL 8.0. This feature has long been supported in most commercial databases and some open source databases, and some are also called analytical functions.

What is a window?

The concept of window is very important. It can be understood as a set of records. Window function is a special function executed on a set of records that meet certain conditions. For each record, a function must be executed within this window. For some functions, the window size is fixed depending on the record, which is a static window. For some functions, on the contrary, different records correspond to different windows. This dynamically changing window is called a sliding window.

Window functions and ordinary aggregate functions are also easily confused. The differences between the two are as follows:

Aggregate functions aggregate multiple records into one; whereas window functions are executed on each record, regardless of the number of records that need to be executed.

Aggregate functions can also be used in window functions, which will be illustrated with examples later.

1. MySQL 5.0

For example: we want to calculate the sales of sales personnel, the results should be sorted from high to low, and the query results should include the sales ranking.

1. Calculate the sales of sales personnel and sort the results from high to low

In this part, we can directly use group by to group the sales staff, use the aggregate function sum to sum the sales, and then use order by to sort the sales results. The statement is as follows:

SELECT sales_name, sum( profit ) FROM spm_order GROUP BY sales_name ORDER BY sum( profit ) DESC 

2. If the query results are to include sales rankings.

In MySQL 5.0, we need to define a sorting auto-increment variable so that it can automatically add +1 as a new column. The statement is as follows:

SET @rank = 0;
SELECT
A.*,
@rank := @rank + 1 AS rank_no
FROM
( SELECT sales_name, sum( profit ) FROM spm_order GROUP BY sales_name ORDER BY sum( profit ) DESC ) A

The := here means assignment, and the A here is to give the subquery an alias for easy calling.

The results are as follows:

2. MySQL 8.0

For this problem, there are special window functions that can be called in MySQL 8.0 to simplify complex problems.

The statement is as follows:

SELECT
sales_name,
sum( sales ),
row_number ( ) over ( ORDER BY sum( sales ) DESC ) AS 'rank'
FROM
spm_order
GROUP BY
sales_name

result:

Here we use [ row_number() over () ] and directly write the content we want to sort in over().
Most companies at work use version 5.0, so learn more about it.

Summarize

The above is what I introduced to you about using window functions in Mysql8.0 to solve sorting problems. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!