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

Use vue3 to implement a human-cat communication applet

Table of contents Preface Initialize the project ...

Summary of a CSS code that makes the entire site gray

In order to express the deep condolences of peopl...

How to use Docker+DockerCompose to encapsulate web applications

Table of contents Technology Stack Backend build ...

Detailed explanation of the role of brackets in AngularJS

1. The role of brackets 1.1 Square brackets [ ] W...

Let's talk in detail about the props attributes of components in Vue

Table of contents Question 1: How are props used ...

Implementation example of video player based on Vue

When the existing video player cannot meet the ne...

Webpack builds scaffolding to package TypeScript code

Create a folder Directory structure: dabaots Init...

Native js implements regular validation of the form (submit only after validation)

The following functions are implemented: 1. Usern...

vmware workstation12 installation centos prompts VMware Player and Device/Credential Guard are incompatible, reasons and solutions

In the latest version of WIN10, Microsoft introdu...

Docker image creation, uploading, pulling and deployment operations (using Alibaba Cloud)

Since I found that the push image always timed ou...

Detailed analysis and testing of SSD performance issues in MySQL servers

【question】 We have an HP server. When the SSD wri...

Detailed explanation of the use of nohup /dev/null 2>&1

nohup command: If you are running a process and y...

How to display web pages properly in various resolutions and browsers

The key codes are as follows: Copy code The code i...

Nginx handles http request implementation process analysis

Nginx first decides which server{} block in the c...

HTML adaptive table method

<body style="scroll:no"> <tabl...