SQL SERVER Open Window function

Introduced

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

Requirements: To find product categories for the group, the most expensive product information in each group.

Declare @t Table(ProductIDint, ProductNamevarchar( -), ProductTypevarchar( -), priceint) Insert @tSelect 1,'name1','P1',3 Union  AllSelect 2,'name2','P1',5 Union  AllSelect 3,'Name3','P2',4 Union  AllSelect 4,'Name4','P2',4

The implementation process is as follows: procedure One

找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的

--缺点:要进行一次join

SelectT1.*  from @tT1Join(SelectProductType,Max(Price) Price from @t Group  byproducttype) T2 onT1. ProductType=T2. ProductTypewhereT1. Price=T2. PriceOrder  byProductType

Procedure two:

Using over (), the statistics are calculated, and then the result set is filtered directly.

--over () allows the function (including aggregate functions) to be output with the line.

; with  CTE as 
  (* , max  (price) over  (partition  by  (ProductType)) maxprice from  @t 
  ) 
  select  productid,productname,producttype,price 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 ().

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. 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, the order of each employee is queried and sorted by time.
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.

; withOrderInfo as( SELECTRow_number () Over(PARTITION byEmployeeIDORDER  byOrderDate) as  Number, Orderid,customerid, Employeeid,orderdate fromOrders (NOLOCK))SELECT  Number, Orderid,customerid, EmployeeID, OrderDate fromOrderInfoWHERE  Number between 0  and Ten

• Row_number (line number) A unique ordinal number is generated sequentially for each group of rows.
• Rank (rank) also generates a sequence number for each group of rows, unlike row_number () if the order by is ordered by, the same value will generate 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 (dense rank) is similar to rank (rank), and the difference is that if you have the same serial 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 (group rank) groups the data by a specified number and generates a sequence number for each group.

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  as (SELECTCOUNT through as from Orders (NOLOCK))  SELECTfromORDER 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  as (  SELECTcountover ascount from Orders ( NOLOCK))

SQL SERVER Open Window function