Detailed usage of MYSQL row_number() and over() functions

Detailed usage of MYSQL row_number() and over() functions

Syntax format: row_number() over(partition by grouping column order by sorting column desc)

row_number() over() grouping sorting function:

When using the row_number() over() function, the grouping and sorting in over() are executed later than the where, group by, and order by.

Example 1:

Table data:

create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);

Sort once: Sort the query results (no grouping)

select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t

result:

Further sorting: sort by id group

select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t

result:

Sort again: Find the data with sequence number 1 in each group

select * from(select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t)
where rank <2

result:

Sort to find data between the ages of 13 and 16, sort by salary

select id,name,age,salary,row_number()over(order by salary desc) rank
from TEST_ROW_NUMBER_OVER t where age between '13' and '16'

Result: The rank number in the result actually indicates that over(order by salary desc) is executed after where age between and

Example 2:

1. Use the row_number() function to number, such as

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

Principle: Sort by psd first, and after sorting, number each data.

2. Sort the orders in ascending order of price and sort each record. The code is as follows:

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3. Count all orders from each household and sort them in ascending order according to the amount of each customer's order, and number each customer's order. This way you can know how many orders each customer has placed:

select ROW_NUMBER() over(partition by customerID order by totalPrice)
 as rows,customerID,totalPrice,DID from OP_Order

4. Count the number of orders each customer has placed recently:

with tabs as  
(  
select ROW_NUMBER() over(partition by customerID order by totalPrice)
 as rows,customerID,totalPrice,DID from OP_Order  
 )  
select MAX(rows) as 'Number of orders',customerID from tabs 
group by customerID

5. Count the smallest purchase amount among all orders of each customer, and also count the number of purchases made by the customer in this order:

Idea: Use a temporary table to perform this operation.

1. First group by customer, then sort by the time the customer placed the order, and number them.

2. Then use the subquery to find out the minimum price for each customer's purchase.

3. Find the corresponding records based on the minimum price of each customer.

    with tabs as  
     (  
    select ROW_NUMBER() over(partition by customerID order by insDT) 
as rows,customerID,totalPrice,DID from OP_Order  
    )  
     select * from tabs  
    where totalPrice in   
    (  
    select MIN(totalPrice)from tabs group by customerID  
     )

6. Filter out the first orders placed by customers.

Ideas. Use rows=1 to query the customer's first order record.

    with tabs as  
    (  
    select ROW_NUMBER() over(partition by customerID order by insDT) as rows,* from OP_Order  
    )  
    select * from tabs where rows = 1 
    select * from OP_Order

7. Note: When using window functions such as over, the grouping and sorting in over are executed later than the execution of "where, group by, order by".

    select   
    ROW_NUMBER() over(partition by customerID order by insDT) as rows,  
    customerID,totalPrice,DID  
    from OP_Order where insDT>'2011-07-22'

This is the end of this article about the detailed usage of MYSQL row_number() and over() functions. For more information about MYSQL row_number() and over() functions, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • PostgreSQL ROW_NUMBER() OVER() usage explanation
  • row_number() and distinct usage in postgreSQL
  • Postgresql rank() over, dense_rank(), row_number() usage differences
  • Implementation process of row_number in MySQL
  • Detailed examples of common usage of row_number function in SQL Server
  • Introduction to the use of the four major sql ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE
  • SQL ROW_NUMBER() and OVER() method case study

<<:  Detailed explanation of meta tags (the role of meta tags)

>>:  Detailed explanation of Nginx version smooth upgrade solution

Recommend

Analyze the difference between ES5 and ES6 apply

Table of contents Overview Function signature Opt...

Example code for implementing background transparency and opaque text with CSS3

Recently, I encountered a requirement to display ...

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

Table of contents 1. Background 2. Table creation...

Perfect solution to the problem of webpack packaging css background image path

Inside the style tag of the vue component, there ...

A brief discussion on the magical slash in nginx reverse proxy

When configuring nginx reverse proxy, the slashes...

How to use the flash plug-in to call the PC's camera and embed it into the TML page

Preface The reason for writing this article is mai...

MySQL 8.0.12 installation steps and basic usage tutorial under Windows

This article shares the installation steps and us...

JavaScript canvas realizes dynamic point and line effect

This article shares the specific code for JavaScr...

How to use VirtualBox to simulate a Linux cluster

1. Set up HOST on the host Macbook The previous d...

In-depth understanding of Mysql logical architecture

MySQL is now the database used by most companies ...

Use Angular CDK to implement a Service pop-up Toast component function

Table of contents 1. Environmental Installation 2...

HTML table mouse drag sorting function

Effect picture: 1. Import files <script src=&q...

How to build a tomcat image based on Dockerfile

Dockerfile is a file used to build a docker image...

Vue.js uses Element-ui to implement the navigation menu

This article shares the specific code for impleme...

Detailed explanation of MySQL and Spring's autocommit

1 MySQL autocommit settings MySQL automatically c...