SQL Server open Window function over () use

Use over () to calculate the statistics and then filter the result set directly


DECLARE @t table (
ProductID int,
ProductName varchar (20),
ProductType varchar (20),
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.
--Cons: To make a join

Select t1.*
From @t T1
Join (select ProductType,
Max (price) Price
From @t
Group by ProductType) T2 on T1. ProductType = t2. ProductType
where T1. Price = t2. Price
ORDER BY ProductType

-Practice Two: Use Over (), calculate the statistics, and then directly filter the result set.
--over () allows the function (including aggregate functions) to be output with the line.


; with 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 is important to note that over () is preceded by a function, and if it is an aggregate function, then order by cannot be used together.
Another common scenario for--over () is for pagination with row_number ().

Now let's introduce the window-opening function.

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

The window function does not need to use Group by to group the data, but it can also return the columns and aggregate columns of the underlying row.

1. Ranking Window function

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

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

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

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

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

With OrderInfo as

(

SELECT row_number () over (PARTITION by EmployeeID ORDER by OrderDate) as number,

Orderid,customerid, employeeid,orderdate from Orders (NOLOCK)

)



SELECT Number,orderid,customerid, EmployeeID, OrderDate

From OrderInfo WHERE number between 0 and 10

The window function groups 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 a sequence number starting with 1 for each group of data.

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

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

Dense_rank () is similar to RANK (), and the difference is that if you have the same ordinal number, the next sequence number will not be interrupted. That is, if two identical lines generate sequence number 3, then the next generated sequence number is 4.

NTILE (integer_expression) groups The data by a specified number and generates a sequence number for each group.

2. Aggregate open Window functions

Many aggregation functions can be used as operations for window functions, such as sum,avg,max,min.

The aggregate window function can only be used with the partition by clause or without any statements, and ORDER by cannot be used with the aggregate window function.

For example, query the total number of orders and order information for an employee

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 values of all rows

With OrderInfo as

(

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

)

SQL Server open Window function over () use