SQL Server's Partition By and row_number functions are described in detail,

SQL Server's Partition By and row_number functions are described in detail,

The partition by keyword is part of the analytics function. Unlike the aggregate function, it can return multiple records in a group. Aggregate functions generally have only one record that reflects the statistical value, partition by is used to group A result set. If not specified, the entire result set is used as a group.

Today, we see a problem in the group. Here we will summarize: query the latest records under different categories. Isn't that easy? To classify data, use Group By; To keep the latest records, use Order. Then try to make it in your own table:

First, I sorted the data in the table in descending order of the submission time:

"Corp_name" is the GUID of a category (please forgive me for its randomness ). OK. Here, based on the initial idea, add Group By to check the effect:

Uh, um. This Nima is different from the expected results. It seems that the problem still needs to be analyzed rationally when writing code, and the idea is that the results cannot be controlled!

Since data of different categories is required, can other functions be used besides Group? What is the difference between the over (partition by) function and the Group By function used in peacetime? In addition to grouping results, Group By is generally used together with Aggregate functions. Partition By also has the grouping function, which is an Oracle analysis function, it's not detailed here. No, it's not.

Check the Code:

Over (partition by corp_name order by submit_time desc) as t. It is classified by corp_name and sorted in reverse chronological order. "t" here is the number of occurrences of different corp_name classes. The requirement is to query only the latest submitted data of different categories, then we only need to filter "t" again:

Well, the results have come out. I don't want you to look at the official site, but please look at the chest in my profile picture and hope to have a thumbs up. Good people will be safe in their life !!!

Ps: SQL Server database partition by and ROW_NUMBER () Functions

Some Usage experiences on the partition by field of SQL

First look at the example:

if object_id('TESTDB') is not null drop table TESTDBcreate table TESTDB(A varchar(8), B varchar(8))insert into TESTDBselect 'A1', 'B1' union allselect 'A1', 'B2' union allselect 'A1', 'B3' union allselect 'A2', 'B4' union allselect 'A2', 'B5' union allselect 'A2', 'B6' union allselect 'A3', 'B7' union allselect 'A3', 'B3' union allselect 'A3', 'B4'

-- All Information

SELECT * FROM TESTDBA  B-------A1 B1A1 B2A1 B3A2 B4A2 B5A2 B6A3 B7A3 B3A3 B4

-- After using the partition by function

SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) NUM FROM TESTDBA  B  NUM-------------A1 B1 1A1 B2 2A1 B3 3A2 B4 1A2 B5 2A2 B6 3A3 B7 1A3 B3 2A3 B4 3

We can see that the NUM column in The result indicates the number of the same rows. For example, if there are three A1 columns, it indicates the number of the first row for each A1.

-- Only use the ROW_NUMBER () OVER result

SELECT *,ROW_NUMBER() OVER(ORDER BY A DESC)NUM FROM TESTDB A  B   NUM------------------------A3 B7  1A3 B3  2A3 B4  3A2 B4  4A2 B5  5A2 B6  6A1 B1  7A1 B2  8A1 B3  9

We can see that it only marks the row number.

-- In-depth Application

SELECT A = CASE WHEN NUM = 1 THEN A ELSE '' END,BFROM (SELECT A,NUM = ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) FROM TESTDB) TA  B---------A1 B1  B2  B3A2 B4  B5  B6A3 B7  B3  B4

Next we will introduce the use of the ROW_NUMBER () function through several instances.

Example:

1. Use the row_number () function for serial numbers, as shown in figure

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 as follows:

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:

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:

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 'order times ', 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:

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 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:

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. rows_number () can be used for paging

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

8. 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:

select  ROW_NUMBER() over(partition by customerID order by insDT) as rows, customerID,totalPrice, DID from OP_Order where insDT>'2011-07-22'

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

Articles you may be interested in:

• SQL Server General Stored Procedure paging code (with ROW_NUMBER () and no ROW_NUMBER () For performance analysis)
• Usage of ROW_NUMBER, RANK, and DENSE_RANK in SQLSERVER 2005
• Use row_number () in SqlServer 2005 to delete duplicate records in a query.
• Sqlserver2005 use row_number () over paging Implementation Method
• Sqlserver uses row_number and partition by grouping to retrieve top data
• SQL Server CONVERT () function usage Summary
• Solution for SQL Server to use ROW_NUMBER without sorting
• Differences between SQL Server user-defined functions and stored procedures