Shulou(Shulou.com)05/31 Report--

This article shows you how to use the over() function in sql server to achieve grouping statistics, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

Demand: Find out the product information with the highest price in each group by product category.

The implementation process is as follows:

declare @t table(ProductID int,ProductName varchar(20),ProductType varchar(20),Price int)

--Test data

insert @tselect 1,'name1','P1',3 union allselect 2,'name2','P1',5 union allselect 3,'name3','P2',4 union allselect 4,'name4','P2',4

--Method 1: Find the highest price value in each group and then find the price equal to this value in each group.

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

--Method 2: Use over() to calculate the statistical information, and then directly filter the result set. -- over() allows functions (including aggregate functions) to be output with rows.

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

Now let's talk about the window function.

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

The windowing function does not need GROUP BY to group data and returns both the columns of the base row and the aggregated columns.

1. rank windowing function

ROW_NUMBER, DENSE_RANK, RANK, NTILE are ranking functions.

The ranking windowing function can be used with ORDER BY statements alone or in combination with PARTITION BY.

PARTITION BY is used to group the result set, and a windowing function is applied to each group.

DER BY Specifies the order of ranking windowing functions. The ORDER BY statement must be used in ranking windowing functions.

For example, query the orders of each employee and sort them 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 ,OrderDateFrom OrderInfo WHERE Number BETWEEN 0 AND 10

The window function groups rows of data by employee ID according to the PARTITION BY statement and then sorts them according to the ORDER BY statement, and the ranking function ROW_NUMBER() generates a sequence number starting at 1 for each group of data points.

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

RANK () also generates a sequence number for each group of rows, unlike ROW_NUMBER(), which generates the same sequence number if the same value is sorted according to ORDER BY, and the next sequence number is not consecutive. For example, two identical rows generate sequence number 3, then sequence number 5 is generated next.

DENSE_RANK () is similar to RANK (), except that if there is the same sequence number, then the next sequence number will not be interrupted. That is to say, if two identical rows generate sequence number 3, then the sequence number generated next is still 4.

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

2. aggregate windowing function

Many aggregate functions can be used as window functions, such as SUM,AVG,MAX, MIN.

Aggregate windowing functions can only use PARTITION BY clauses or none, ORDER BY cannot be used with aggregate windowing functions.

For example, query the employee's order count 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,TotalCountFrom 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)) The above content is how to use the over() function to realize grouping statistics in sql server. Have you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.