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

Introduction to root directory expansion under Linux system

1. Check Linux disk status df -lh The lsblk comma...

Detailed explanation of the difference between device-width and width in CSS3 media queries

1.device-width Definition: Defines the screen vis...

How to view available network interfaces in Linux

Preface The most common task after we install a L...

Tomcat reports an error when starting the springboot project war package: Error when starting the child

Today, the company's springboot project is re...

Summary of several situations in which MySQL indexes fail

1. Indexes do not store null values More precisel...

Example code for using CSS to darken the font color of the selected area when scrolling

When encapsulating the date picker, you need to d...

VSCode+CMake+Clang+GCC environment construction tutorial under win10

I plan to use C/C++ to implement basic data struc...

Docker Tutorial: Using Containers (Simple Example)

If you’re new to Docker, take a look at some of t...

MySQL multi-table join query example explanation

In actual projects, there are relationships betwe...

Several solutions for forgetting the MySQL password

Solution 1 Completely uninstall and delete all da...

MySQL detailed summary of commonly used functions

Table of contents MySQL Common Functions 1. Numer...

Comparison of div and span in HTML_PowerNode Java Academy

1. <div></div> and <span></s...

Display mode of elements in CSS

In CSS, element tags are divided into two categor...

Detailed example of using CSS to beautify HTML form controls (form beautification)

1. Basic syntax structure of HTML submit and bott...

Detailed explanation of Vue filter implementation and application scenarios

1. Brief Introduction Vue.js allows you to define...