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