SQL row_number () sorting Function

1 use the row_number () function for serial number: as shown in Figure

View code 1 Select email, customerid, row_number () over (order by PSD) As Rows from qt_customer

Principle: first sort by PSD, And then number each piece of data.

 

2. Sort the order by price in ascending order and sort each record

CodeAs follows:

View code 1 Select did, customerid, totalprice, row_number () over (order by totalprice) As Rows from op_order

 

 

3. All orders of each household are collected and sorted in ascending order based on the order amount of each customer. Orders of each customer are numbered. In this way, you will know how many orders each customer has.

 

 

The Code is as follows:

View code 1 Select row_number () over (partition by customerid order by totalprice) As Rows, customerid, totalprice, did from op_order

 

4. count the number of orders placed by each customer recently.

 

 

The Code is as follows:

View code 1 With tabs As
2 (
3 Select row_number () over (partition by customerid order by totalprice) As Rows, customerid, totalprice, did from op_order
4 )
5
6 Select max (rows) As   ' Number of orders ' , Customerid from tabs group by customerid

 

5. Count the minimum amount purchased in all orders of each customer, and count the number of times the customer purchased the order.

 

: Rows indicates the number of purchases made by the customer.

 

Idea: Use a temporary table to perform this operation

1. Group by customer first, and then sort by customer's order time and number.

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

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

 

The Code is as follows:

View code 1 With tabs As
2 (
3 Select row_number () over (partition by customerid order by insdt) As Rows, customerid, totalprice, did from op_order
4 )
5 Select * From tabs
6   Where Totalprice In  
7 (
8 Select min (totalprice) from tabs group by customerid
9 )

 

5. filter the orders placed by the customer for the first time.

 

Train of Thought. Use rows = 1 to query the order record of the customer for the first time.

 

The Code is as follows:

View code 1 With tabs As
2 (
3 Select row_number () over (partition by customerid order by insdt) As Rows, * From op_order
4 )
5 Select * From tabs Where Rows =   1
6
7 Select * From op_order

 

6. rows_number () can be used for paging

Idea: first filter out all products and then number these products. Then, filter in the WHERE clause.

 

7. Note: when using over and other window functions, the execution of grouping and sorting in over is later than that of "Where, group by, order.

The following code:

View code 1 Select
2 Row_number () over (partition by customerid order by insdt) As Rows,
3 Customerid, totalprice, did
4 From op_order Where Insdt > ' 2011-07- '

The above code executes the WHERE clause first, and then numbers each record after execution.

 

 

 

References: row_number ()

: Over clause