MySQL 8.0 Window Function Introduction and Summary

MySQL 8.0 Window Function Introduction and Summary

Preface

Before MySQL 8.0, it was quite painful to do data ranking statistics, because there were no window functions like other databases such as Oracle, SQL SERVER, PostgreSQL, etc. However, with the addition of window functions in MySQL 8.0, this type of statistics is no longer a problem. This article introduces MySQL window functions using commonly used sorting examples.

1. Preparation

Create tables and test data

mysql> use testdb;
Database changed
/* Create table */
mysql> create table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1),key idx_stuNo_course(stu_no,course));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
|tb_score|
+------------------+

/* Add a new batch of test data*/
mysql> insert into tb_score(stu_no,course,score)values('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

2. Calculate the ranking of each course score

The scores of each course are ranked from high to low. At this time, the problem of how to deal with the same scores will arise. The following uses different window functions to handle the needs of different scenarios.

ROW_NUMBER

From the results, we can see that if the scores are the same, they are ranked according to the order of student numbers.

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
 -> from tb_score;
+---------+---------+-------+----+
| stu_no | course | score | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | English | 100.0 | 1 |
| 2020004 | English | 100.0 | 2 |
| 2020002 | English | 99.0 | 3 |
| 2020013 | English | 88.0 | 4 |
| 2020008 | English | 86.0 | 5 |
| 2020009 | English | 86.0 | 6 |
| 2020011 | English | 84.0 | 7 |
| 2020010 | English | 81.0 | 8 |
| 2020003 | English | 80.0 | 9 |
| 2020007 | English | 76.0 | 10 |
| 2020012 | English | 75.0 | 11 |
| 2020005 | English | 70.0 | 12 |
| 2020006 | English | 70.0 | 13 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 3 |
| 2020011 | mysql | 90.0 | 4 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 11 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
 -> from tb_score;
+---------+---------+-------+----+
| stu_no | course | score | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 2 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 5 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 11 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | English | 100.0 | 1 |
| 2020004 | English | 100.0 | 2 |
| 2020002 | English | 99.0 | 3 |
| 2020013 | English | 88.0 | 4 |
| 2020008 | English | 86.0 | 5 |
| 2020009 | English | 86.0 | 6 |
| 2020011 | English | 84.0 | 7 |
| 2020010 | English | 81.0 | 8 |
| 2020003 | English | 80.0 | 9 |
| 2020007 | English | 76.0 | 10 |
| 2020012 | English | 75.0 | 11 |
| 2020005 | English | 70.0 | 12 |
| 2020006 | English | 70.0 | 13 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 3 |
| 2020011 | mysql | 90.0 | 4 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 11 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

DENSE_RANK

In order to make the ranking the same when the scores are the same, you can use the DENSE_RANK function, the result is as follows:

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn 
 -> from tb_score; 
+---------+---------+-------+----+
| stu_no | course | score | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 1 |
| 2020006 | C++ | 90.0 | 2 |
| 2020001 | C++ | 85.0 | 3 |
| 2020012 | C++ | 85.0 | 3 |
| 2020003 | C++ | 81.0 | 4 |
| 2020010 | C++ | 76.0 | 5 |
| 2020002 | C++ | 70.0 | 6 |
| 2020008 | C++ | 69.0 | 7 |
| 2020007 | C++ | 66.0 | 8 |
| 2020009 | C++ | 66.0 | 8 |
| 2020004 | C++ | 60.0 | 9 |
| 2020003 | English | 100.0 | 1 |
| 2020004 | English | 100.0 | 1 |
| 2020002 | English | 99.0 | 2 |
| 2020013 | English | 88.0 | 3 |
| 2020008 | English | 86.0 | 4 |
| 2020009 | English | 86.0 | 4 |
| 2020011 | English | 84.0 | 5 |
| 2020010 | English | 81.0 | 6 |
| 2020003 | English | 80.0 | 7 |
| 2020007 | English | 76.0 | 8 |
| 2020012 | English | 75.0 | 9 |
| 2020005 | English | 70.0 | 10 |
| 2020006 | English | 70.0 | 10 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 2 |
| 2020011 | mysql | 90.0 | 2 |
| 2020004 | mysql | 80.0 | 3 |
| 2020003 | mysql | 78.0 | 4 |
| 2020010 | mysql | 75.0 | 5 |
| 2020009 | mysql | 70.0 | 6 |
| 2020006 | mysql | 60.0 | 7 |
| 2020002 | mysql | 50.0 | 8 |
| 2020007 | mysql | 50.0 | 8 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

RANK

The result of DENSE_RANK is that when the scores are the same, the ranking is the same, but the next ranking is immediately after the previous ranking. If there are two tied for first place, the next one I want is third place, you can use the RANK function to achieve

mysql> select stu_no,course,score, rank()over(partition by course order by score desc ) rn 
 -> from tb_score;
+---------+---------+-------+----+
| stu_no | course | score | rn |
+---------+---------+-------+----+
| 2020005 | C++ | 96.0 | 1 |
| 2020013 | C++ | 96.0 | 1 |
| 2020006 | C++ | 90.0 | 3 |
| 2020001 | C++ | 85.0 | 4 |
| 2020012 | C++ | 85.0 | 4 |
| 2020003 | C++ | 81.0 | 6 |
| 2020010 | C++ | 76.0 | 7 |
| 2020002 | C++ | 70.0 | 8 |
| 2020008 | C++ | 69.0 | 9 |
| 2020007 | C++ | 66.0 | 10 |
| 2020009 | C++ | 66.0 | 10 |
| 2020004 | C++ | 60.0 | 12 |
| 2020003 | English | 100.0 | 1 |
| 2020004 | English | 100.0 | 1 |
| 2020002 | English | 99.0 | 3 |
| 2020013 | English | 88.0 | 4 |
| 2020008 | English | 86.0 | 5 |
| 2020009 | English | 86.0 | 5 |
| 2020011 | English | 84.0 | 7 |
| 2020010 | English | 81.0 | 8 |
| 2020003 | English | 80.0 | 9 |
| 2020007 | English | 76.0 | 10 |
| 2020012 | English | 75.0 | 11 |
| 2020005 | English | 70.0 | 12 |
| 2020006 | English | 70.0 | 12 |
| 2020005 | mysql | 98.0 | 1 |
| 2020001 | mysql | 90.0 | 2 |
| 2020008 | mysql | 90.0 | 2 |
| 2020011 | mysql | 90.0 | 2 |
| 2020004 | mysql | 80.0 | 5 |
| 2020003 | mysql | 78.0 | 6 |
| 2020010 | mysql | 75.0 | 7 |
| 2020009 | mysql | 70.0 | 8 |
| 2020006 | mysql | 60.0 | 9 |
| 2020002 | mysql | 50.0 | 10 |
| 2020007 | mysql | 50.0 | 10 |
+---------+---------+-------+----+
36 rows in set (0.01 sec)

This achieves various sorting requirements.

NTILE

The function of the NTILE function is to rank each group and then divide the corresponding group into N groups, for example

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group from tb_score;
+---------+---------+-------+----+----------+
| stu_no | course | score | rn | rn_group |
+---------+---------+-------+----+----------+
| 2020005 | C++ | 96.0 | 1 | 1 |
| 2020013 | C++ | 96.0 | 1 | 1 |
| 2020006 | C++ | 90.0 | 3 | 1 |
| 2020001 | C++ | 85.0 | 4 | 1 |
| 2020012 | C++ | 85.0 | 4 | 1 |
| 2020003 | C++ | 81.0 | 6 | 1 |
| 2020010 | C++ | 76.0 | 7 | 2 |
| 2020002 | C++ | 70.0 | 8 | 2 |
| 2020008 | C++ | 69.0 | 9 | 2 |
| 2020007 | C++ | 66.0 | 10 | 2 |
| 2020009 | C++ | 66.0 | 10 | 2 |
| 2020004 | C++ | 60.0 | 12 | 2 |
| 2020003 | English | 100.0 | 1 | 1 |
| 2020004 | English | 100.0 | 1 | 1 |
| 2020002 | English | 99.0 | 3 | 1 |
| 2020013 | English | 88.0 | 4 | 1 |
| 2020008 | English | 86.0 | 5 | 1 |
| 2020009 | English | 86.0 | 5 | 1 |
| 2020011 | English | 84.0 | 7 | 1 |
| 2020010 | English | 81.0 | 8 | 2 |
| 2020003 | English | 80.0 | 9 | 2 |
| 2020007 | English | 76.0 | 10 | 2 |
| 2020012 | English | 75.0 | 11 | 2 |
| 2020005 | English | 70.0 | 12 | 2 |
| 2020006 | English | 70.0 | 12 | 2 |
| 2020005 | mysql | 98.0 | 1 | 1 |
| 2020001 | mysql | 90.0 | 2 | 1 |
| 2020008 | mysql | 90.0 | 2 | 1 |
| 2020011 | mysql | 90.0 | 2 | 1 |
| 2020004 | mysql | 80.0 | 5 | 1 |
| 2020003 | mysql | 78.0 | 6 | 1 |
| 2020010 | mysql | 75.0 | 7 | 2 |
| 2020009 | mysql | 70.0 | 8 | 2 |
| 2020006 | mysql | 60.0 | 9 | 2 |
| 2020002 | mysql | 50.0 | 10 | 2 |
| 2020007 | mysql | 50.0 | 10 | 2 |
+---------+---------+-------+----+----------+
36 rows in set (0.01 sec)

3. Summary of Window Functions

There are many other window functions in MySQL. This article lists some of them and you can test them yourself.

category function illustrate
Sorting ROW_NUMBER Assign a sequence number to each row in the table, and specify the grouping (or not) and sorting fields
DENSE_RANK Assign a sequence number to each row in each group based on the sort field. When the ranking values ​​are the same, the serial numbers are the same, and there are no gaps in the serial numbers (such as 1,1,2,3)
RANK Assign a sequence number to each row in each group based on the sort field. When the ranking values ​​are the same, the serial numbers are the same, but there are gaps in the serial numbers (such as 1,1,3,4)
NTILE According to the sorting field, each group is divided into corresponding groups according to the sorting of the specified field.
distributed PERCENT_RANK Calculates percentile ranks for each group or row in a result set
CUME_DIST Calculate the cumulative distribution of a value in a set of ordered data
Before and After LEAD Returns the value of the Nth row after the current row in the group. If no corresponding row exists, NULL is returned. For example, when N=1, the value corresponding to the first place is the second place, and the result of the last place is NULL
LAG Returns the value of the row N rows before the current row in the group. If no corresponding row exists, NULL is returned. For example, when N=1, the value corresponding to the first place is NUL, and the last place is the second-to-last value.
Beginning and End FIRST_VALUE Returns the value of the field (or expression) corresponding to the first place in each group. For example, in this article, it can be the value of any field such as the first place's score, student ID, etc.
LAST_VALUE Returns the value of the field (or expression) corresponding to the last person in each group. For example, in this article, it can be the value of any field such as the last person's score or student ID.
NTH_VALUE

Returns the value of the corresponding field (or expression) ranked Nth in each group, but the corresponding value of the row less than N is NULL

This is the summary of the main window functions in MySQL. It is recommended to practice them. In addition, many people have summarized the implementation of sorting methods for MySQL 5.7 and earlier versions, and it is also recommended to practice it.

Summarize

This is the end of this article about the introductory practice and summary of MySQL 8.0 window functions. For more relevant MySQL 8.0 window function practice content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Mysql8.0 uses window functions to solve sorting problems
  • Case analysis of SQL window functions to achieve efficient paging query
  • Specific use of MySQL window functions
  • Quickly learn about SQL window functions

<<:  Interpretation of the module for load balancing using nginx

>>:  Detailed explanation of vue keepAlive cache clearing problem case

Recommend

Understand the principles and applications of JSONP in one article

Table of contents What is JSONP JSONP Principle J...

Practical record of Vue3 combined with TypeScript project development

Table of contents Overview 1. Compositon API 1. W...

Nginx dynamic and static separation implementation case code analysis

Separation of static and dynamic Dynamic requests...

Detailed explanation of Vue filters

<body> <div id="root"> <...

Solve the problem of managing containers with Docker Compose

In Docker's design, a container runs only one...

MySQL database table and database partitioning strategy

First, let's talk about why we need to divide...

Node.js+postman to simulate HTTP server and client interaction

Table of contents 1. Node builds HTTP server 2. H...

MySQL database development specifications [recommended]

Recently, we have been capturing SQL online for o...

Detailed explanation of the function and usage of keepAlive component in Vue

Preface During the interview, many interviewers m...

js to achieve simple drag effect

This article shares the specific code of js to ac...

Simple implementation of ignoring foreign key constraints when deleting MySQL tables

Deleting a table is not very common, especially f...

Detailed explanation of MySQL to obtain statistical data for each day and each hour of a certain period of time

Get daily statistics When doing a project, you ne...

JavaScript to implement login slider verification

This article example shares the specific code of ...

Introduction to ufw firewall in Linux

Let's take a look at ufw (Uncomplicated Firew...

HTML+CSS box model example (circle, semicircle, etc.) "border-radius" is simple and easy to use

Many friends found that the box model is a square...