Shulou(Shulou.com)06/01 Report--

Brief introduction of MySQL window function

MySQL has supported window functions since 8.0, which has long been supported in most commercial databases and some open source databases, some of which are also called analysis functions.

What is a window?

The concept of window is very important, it can be understood as a set of records, and window functions are special functions executed on a set of records that meet certain conditions. For each record, you have to execute functions in this window, some functions have a fixed window size with different records, which belongs to a static window; some functions, on the contrary, different records correspond to different windows. The window of this dynamic change is called sliding window.

Window functions and ordinary aggregate functions are also easy to be confused, and the differences are as follows:

The aggregate function aggregates multiple records into one, while the window function executes each record, and how many records have been executed or several.

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

1. Mysql5.0

For example, we want to calculate the sales of salespeople, the results are sorted from highest to lowest, and the ranking of sales should be included in the query results.

1. Calculate the sales of the salesperson, and the results are sorted 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 use order by to sort the sales as a result. 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 result should include the ranking of sales.

In mysql5.0, we want to define a sort self-incrementing variable to implement automatic + 1 as a new column. The statement is as follows:

SET @ rank = 0 *! *

Here: = means assignment. Here A means to give the subquery an alias to facilitate the previous call.

The results are as follows:

II. Mysql8.0

For this problem, there is a special window function in mysql8.0 that can be called, and the complex problem is simplified.

The statement is as follows:

SELECTsales_name,sum (sales), row_number () over (ORDER BY sum (sales) DESC) AS 'rank'FROMspm_orderGROUP BYsales_name

Results:

Here we use [row_number () over ()], which directly writes what we want to sort in over ().

Enterprises basically use version 5.0 at work, so learn more.

Summary

The above is the Mysql8.0 introduced by the editor to solve the sorting problem using window functions. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.