How SQL Server uses the open window function over () for grouping statistics _mssql

This is a common face test, which is often used in practical projects.

Requirements: To find the product category for the group, the highest prices in each group of product information.

The implementation process is as follows:

Declare @t table (
ProductID int,
ProductName varchar (),
producttype varchar (), price
int)

--Test data

Insert @t
Select 1, ' name1 ', ' P1 ', 3 UNION ALL
Select 2, ' name2 ', ' P1 ', 5 union ALL
Select 3, ' Name3 ', ' P2 ', 4 union All
Select 4, ' Name4 ', ' P2 ', 4

--Practice One: find the most expensive value in each group, and then find out what price is equal to this value in each group.
--Disadvantage: to have a join

Select t1.*
 from @t T1
 join (select ProductType,
  max (Price), and
  @t
  GROUP by ProductType ) T2 on T1. ProductType = t2. ProductType
 where T1. Price = t2. Price ORDER BY
 ProductType

--Practice two: use over (), calculate the statistic information, and then directly filter the result set.
--over () allows functions (including aggregate functions) to be exported along with rows.

, with a CTE
 as (SELECT *, max (price) over (partition by (ProductType)) Maxprice
 from @t)
Select ProductID, Productname,producttype,price from CTE where Price = Maxprice ORDER BY
 ProductType

The syntax for--over () is:over ([patition by] <order by >). It should be noted that over () is preceded by a function, and if it is an aggregate function, then the order by cannot be used together.
Another common scenario for--over () is used with row_number () for paging.

Now let's introduce the open window function.

window function over () specifies a set of rows, and the window function calculates the values of each row in the result set output from the window function.

The open window function does not require group by to group data, and it can also return columns and aggregated columns for the underlying row.

1. Rank open Window function

Row_number, Dense_rank, rank, ntile belong to the rank function.

The Rank open window function can be used either separately by the order BY statement or with partition by.

PARTITION by is used to group the result set, and the open window function is applied to each group.

oder by specifies the order of the rank open window functions. The order BY statement must be used in the ranking window function.

For example, check each employee's order and sort by time

with OrderInfo as (

 SELECT row_number ()-PARTITION by EmployeeID Order by OrderDate) as number,

 orderid,c Ustomerid, employeeid,orderdate from Orders (NOLOCK)

)

 

SELECT Number,orderid,customerid, EmployeeID, OrderDate from

OrderInfo WHERE number BETWEEN 0 and 10

The window function groups The data rows by employee ID according to the partition by statement, and then sorts by the order by statement, and the Rank function row_number () generates an ordinal number for each group of data starting from 1.

Row_number () generates a unique ordinal number in sequence for each group of rows

RANK () also generates an ordinal number for each group of rows, unlike row_number () if the same value produces the same sequence number, and the following sequence number is not sequential, if ordered by. For example, two identical rows generate ordinal 3, then the ordinal number 5 is generated.

Dense_rank () is similar to RANK (), but if the same sequence number is the same, the following sequence number is not interrupted. That is, if two identical rows generate ordinal 3, then the resulting ordinal number is 4.

Ntile (integer_expression) groups The data by a specified number and generates an ordinal number for each group.

2. Aggregate open Window function

Many aggregate functions can be used as Windows function operations, such as Sum,avg,max,min.

The Aggregate open Window function can only use the partition by clause or none of the statements, and the order by cannot be used in conjunction with the Aggregate open window function.

For example, check the employee's total order and order information

with OrderInfo as (

SELECT COUNT (OrderID) over (PARTITION by EmployeeID) as Totalcount,orderid,customerid, Employeeid,orderdate from Orders (NOLOCK)

)

SELECT Orderid,customerid, EmployeeID, Orderdate,totalcount

From OrderInfo ORDER by EmployeeID 

If the window function does not use the partition by statement, then the data is not grouped, and the aggregate function calculates the value of all the rows.

with OrderInfo as (

 SELECT COUNT (OrderID) over () as Count,orderid,customerid, employeeid,orderdate from Orders ( NOLOCK)

)

The above is the entire content of this article, I hope you can learn SQL Server using the Open window function of the method of grouping statistics, thank you for your reading.