MySQL Database Basics SQL Window Function Example Analysis Tutorial

Introduction

Some time ago, Huang wrote an article titled "MySQL Window Practice" (the article is as follows), but most of the content in it was based on practical exercises without any detailed explanations.

Portal: MySQL practical window function SQL analysis of class students' test scores and living expenses

So, I sent a private message to the beautiful girl Yueya to see if she could write an article entitled “The Basics of Window Functions” which would be a good complement to the previous article. Look, she finished writing it very quickly, and today she is sharing it with you, aiming to communicate and learn with you all!

Below is a joke introduction to Crescent Moon, with a large picture attached.

Introduction

Window functions, also known as "window opening functions", can be used after MySQL 8.0.

When we are doing questions on Likou, we will find that the more difficult questions often involve the application of window functions. It can be said that window functions are a yardstick to test whether our SQL level has reached a proficient level.

The format of the window function is: aggregate function + over()

The window describes the content delineated within the over() brackets. This content is the scope of the window function, that is, the data operated on is within the scope of over().

My personal understanding of window functions is to open a sliding window for the source data. When the window moves, additional calculations can be performed on the data in it, such as moving average, group sorting, etc. The window can be one row, multiple rows, or even all rows.

Window functions can also perform operations such as synchronous sorting and aggregation on multiple groups of data, and operate on the results after group by clauses or where processing. They can only be written into the select clause.

Soul Painter is online, and a simple demo of the window function is made using Excel:

Aggregate functions + over()

Basic syntax:

sum/avg(name of the processed field) over(partition by name of the grouped field order by name of the sorted field rows between … and …)

Meaning: Indicates to sum or average each group after grouping by partition by.

--Includes rows between 6 preceding and current row
--Includes rows between current row and 3 following rows: rows between current row and 3 following
--Includes this row and all previous rows: rows between unbounded preceding and current row
--Includes this row and all subsequent rows: rows between current row and unbounded following
--From the first 3 rows to the next row (a total of 5 rows of data): rows between 3 preceding and 1 following

There are other aggregate functions, such as max, min, and count, which have similar grammatical structures.

Sorting function + over()

The three functions row_number(), rank(), and dense_rank() all sort the results of the select query. Let's take a look at the differences between them.

row_number() : For non-repeating continuous sorting, starting from 1, a non-repeating serial number is generated for the queried data for sorting

Basic syntax - row_number() over (order by the field to be sorted asc/desc);

rank() : It is a jump sorting. Two data with the same result are placed side by side, and the rank occupied by the next data is vacated, that is, the same ranking will occupy a place

Basic syntax - rank() over (order by the field to be sorted asc/desc);

dense_rank() : For repeated continuous sorting, two data with the same result are placed side by side, and the rank occupied by the next data is not vacated, that is, the same ranking does not occupy a place

Basic syntax - dense_rank() over (order by the field to be sorted asc/desc);

We use a diagram to show the relationship between these three:

ntile() function + over()

Basic syntax: ntile(n) over(partition by…order by…) where n represents the number of segments to be divided.

ntile(n) is used to divide the grouped data into n pieces evenly. If the number of groups divided is not equal, the first group will get more data.

The ntile() function is usually used, for example, to find the top 10% of students in the grade. Then n is taken as 10, and where is used to filter out the first group of data.

Offset function + over()

Basic syntax 1: First N rows: lead(str, n, default) over(partition by …order by …)

Basic syntax 2: N lines later: lag(str, n, default) over(partition by …order by …)

str represents the field name, n represents the first/last n rows of data, the default value is 1, and default means that if the value range exceeds the return value of the entire table, it can be left blank. If left blank, N/A will be returned by default.

The offset function is used to extract the first N rows or the last N rows of data of the same field as separate columns. It should be noted here that lead represents the first N rows and lag represents the last N rows.

The above is the detailed content of the MySQL Database Basics Window Function Example Analysis Tutorial. For more information about the basics of MySQL window functions, please pay attention to other related articles on 123WORDPRESS.COM!