SQL Server partition by and Row_number functions in the use of detailed _mssql

The partition by keyword is part of an analytic function that differs from an aggregate function in that it returns multiple records in a group, and aggregate functions typically have only one record that reflects the statistic, partition by is used to group the result set, If not specified then it takes the entire result set as a grouping.

Today we see a problem in the group, which is summarized below: Check out the latest records under different classifications. Look at this is not very simple, to classify that use group by, to the latest record on the order by Bai. Then try to do it in your own table:

First of all, I put the data in the table in reverse order of submission time:

"Corp_name" is the GUID of the category (please forgive my arbitrary name). OK, here's the first idea, plus group by to see the display effect:

Uh, um. This Nima and imagined the result is not the same ah, it seems to write code or to rational analysis of the problem, the idea is unable to control the result drop!

Since requirements are data of different classifications, are there other functions that can be used in addition to group by? The Niang the result also really has, over (partition by) function, then it and peacetime uses group by have what difference? Group BY In addition to the results of a simple grouping, and commonly used with aggregate functions, Partition by also has a grouping function, belongs to the Oracle analysis function, here is not detailed no no, no.

Look at the code:

Over (partition by corp_name ORDER BY submit_time Desc) as T. is according to Corp_name classification and by time in reverse order, "T" Here is a column is different Corp_name class occurrence, the demand is only to query the latest submission data of different classifications, then we only need to "T" to be screened again:

Well, the result has come out, do not ask you reader like, but look at my head in the chest look at a praise, Good Life peace Oh!!!

Ps:sql Server database partition by and row_number () functions using the detailed

Some tips on how to use 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))
inserts into T Estdb
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 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 the results of a column of num This num is to explain the number of the same line, such as A1 has 3, he gave each A1 mark is the first few.

--using only the results of row_number () over

SELECT *,row_number () 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.

--A little deeper application

Select A = case when NUM = 1 THEN an ELSE ' end,b from
(select a,num = Row_number () over (PARTITION by a order by a DE SC) 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 through several 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, according to the PSD to sort, after sorting, the number of each piece of data.

2. Sort the order by the ascending price, and sort the code for each record as follows:

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

3. All orders of each household are counted and sorted in ascending order by each customer, and the order for each customer is numbered. So you know the next few orders for each customer .

As shown in figure:

The code is as follows:

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

4. The most recent orders for each customer are the orders placed on the first few occasions.

The code is as follows:

 With tabs as 
( 
select Row_number ()-Partition by CustomerID order by Totalprice) as Rows,customerid,totalpric E, DID from Op_order 
select MAX (rows) as ' order Times ', CustomerID from tabs group by CustomerID

5. Statistics of each customer's purchase of all orders of the smallest amount, and the change in the order, the customer is the first several times purchased.

As shown in figure:

Above: rows indicates how many times the customer purchased the first.

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 out the minimum price per customer when buying.

3. Find the appropriate records according to the minimum price for each customer.

The code is as follows:

 
 with tabs as ( 
select Row_number ()-Partition by CustomerID order by INSDT) as Rows,customerid,totalprice, DID From Op_order 
) 
 select * from tabs 
where Totalprice to  
( 
select MIN (totalprice) from tabs Group B Y CustomerID 
 )

6. Filter out the first orders from customers.

Ideas. Use Rows=1 to query the customer's first order record.

The code is as follows:

With tabs as 
( 
select Row_number ()-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 sift through all the products and then number the products. Then filter in the WHERE clause.

8. Note: In the use of over and so open window functions, over inside the grouping and sorting execution later than "Where,group by,order by" implementation.

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 the number of each record is executed.