SQL ROW_NUMBER() and OVER() method case study

SQL ROW_NUMBER() and OVER() method case study

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 case analysis of the sql ROW_NUMBER() and OVER() methods. For more information about the sql ROW_NUMBER() and OVER() methods, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Detailed usage of MYSQL row_number() and over() functions
  • 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

<<:  About the problem of offline installation of Docker package on CentOS 8.4

>>:  Implementing WeChat tap animation effect based on CSS3 animation attribute

Recommend

Detailed examples of the difference between methods watch and computed in Vue.js

Table of contents Preface introduce 1. Mechanism ...

Analysis of the implementation method of modifying the default network segment of Docker

background All of the company's servers are p...

Detailed explanation of how to pass values ​​between react hooks components (using ts)

Table of contents From father to son From son to ...

Detailed analysis of MySQL 8.0 memory consumption

Table of contents 1. innodb_buffer_pool_size 2. i...

How to use VUE and Canvas to implement a Thunder Fighter typing game

Today we are going to implement a Thunder Fighter...

Vue implements tree table through element tree control

Table of contents Implementation effect diagram I...

Detailed example of using useState in react

useState useState adds some internal state to a c...

vue front-end HbuliderEslint real-time verification automatic repair settings

Table of contents ESLint plugin installation in H...

Solve the problem of MYSQL connection port being occupied and introducing file path errors

This morning I planned to use Wampserver to build...

4 solutions to mysql import csv errors

This is to commemorate the 4 pitfalls I stepped o...

A tutorial for beginners to install and log in to mysql-8.0.19-winx64 (must-read for beginners)

Table of contents 1. Download the installation pa...

How to implement online hot migration of KVM virtual machines (picture and text)

1. KVM virtual machine migration method and issue...

Appreciation of the low-key and elegant web design in black, white and gray

Among classic color combinations, probably no one...

HTTP Status Codes

This status code provides information about the s...

Use h1, h2, and h3 tags appropriately

In the process of making web pages, it is inevita...