SQL Server database partition by and row_number () function use (reprint)

Some usage of SQL partition by field

First look at the example:

If object_id (' TESTDB ') is not null drop table TESTDB

CREATE TABLE TESTDB (A varchar (8), B varchar (8))

INSERT INTO TESTDB

Select ' A1 ', ' B1 ' UNION ALL

Select ' A1 ', ' B2 ' UNION ALL

Select ' A1 ', ' B3 ' UNION ALL

Select ' A2 ', ' B4 ' UNION ALL

Select ' A2 ', ' B5 ' UNION ALL

Select ' A2 ', ' B6 ' UNION ALL

Select ' A3 ', ' B7 ' UNION ALL

Select ' A3 ', ' B3 ' UNION ALL

Select ' A3 ', ' B4 '

--All the information

SELECT * from TESTDB

A B

-------

A1 B1

A1 B2

A1 B3

A2 B4

A2 B5

A2 B6

A3 B7

A3 B3

A3 B4

--After using the partition by function

SELECT *,row_number () over (PARTITION by a ORDER by a DESC) NUM from TESTDB

A B NUM

-------------

A1 B1 1

A1 B2 2

A1 B3 3

A2 B4 1

A2 B5 2

A2 B6 3

A3 B7 1

A3 B3 2

A3 B4 3

You can see that the number of NUM in the results is the NUM, which shows the number of identical rows, for example, A1 has 3, and he gives each A1 the first number.

--using only the results of row_number () over

SELECT *,row_number () over (ORDER by A DESC) NUM from TESTDB

A B NUM

------------------------

A3 B7 1

A3 B3 2

A3 B4 3

A2 B4 4

A2 B5 5

A2 B6 6

A1 B1 7

A1 B2 8

A1 B3 9

You can see that it simply marks the line number.

--Dive into a little bit of application

SELECT A = case if NUM = 1 then A ELSE ' end,b

From (SELECT a,num = Row_number () over (PARTITION by a ORDER by a DESC) from TESTDB) T

A B

---------

A1 B1

B2

B3

A2 B4

B5

B6

A3 B7

B3

B4

Next we will introduce the use of the Row_number () function in a few examples.

Examples are as follows:

1. Use the Row_number () function for numbering, as

Select Email,customerid, Row_number () over (order by PSD) as rows from Qt_customer

Principle: First sort by psd, after sorting, each piece of data is numbered.

2. Sort the orders in ascending order by price, and sort each record with the following code:

Select Did,customerid,totalprice,row_number () over (order by Totalprice) as rows from Op_order

3. Count all orders for each of the households and order them according to the amount of each customer's orders, and number each customer's order. This will know each customer next few orders.

The code is as follows:

Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, do from Op_order

4. The most recent orders for each customer are orders placed under the first few times.

The code is as follows:

1. With tabs as
2. (
3. Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, do from Op_order
4. )
5. Select MAX (rows) as ' Number of orders ', CustomerID from tabs group by CustomerID

5. Count the minimum amount purchased for each customer's order, and the number of times the customer purchased the order.

: Rows indicates that the customer is the first purchase.

Idea: Use temporal tables to perform this operation.

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

2. Then use a subquery to find out the minimum price for each customer purchase.

3. Find the appropriate record based on the minimum price found for each customer.

The code is as follows:

1. With tabs as
2. (
3. Select Row_number () over (partition by CustomerID ORDER by INSDT) as Rows,customerid,totalprice, do from Op_order
4. )
5. SELECT * FROM tabs
6. where Totalprice in
7. (
8. Select MIN (totalprice) from tabs GROUP by CustomerID
9. )

6. Filter out the orders placed by the customer for the first time.

Ideas. Use Rows=1 to check the customer's order record for the first time.

The code is as follows:

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. SELECT * FROM Op_order

7.rows_number () can be used for pagination

Idea: first to filter out all the products, and then the products are numbered. The filter is then filtered in the WHERE clause.

8. Note: When using the over window function, the grouping and sorting execution in over is later than "Where,group By,order by".

The following 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-22 '

The above code executes the WHERE clause first, and then each record is numbered.

SQL Server database partition by and row_number () function use (reprint)