How does SQL server use the over () function to perform grouping statistics? sqlover

How does SQL server use the over () function to perform grouping statistics? sqlover

This is a common interview question, which is often used in actual projects.

Requirement: Obtain the product information with the highest price in each group based on the 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

-- Practice 1:Find the maximum price value in each group, and then find the value that the price in each group is equal to this value.
-- Disadvantage: A join operation is required.

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 2:Use over () to calculate the statistical information and then filter the result set directly.
-- Over () allows functions (including Aggregate functions) to be output together 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

-- Over () Syntax:Over ([patition by] <order by> ). Note that over () is a function. If it is an aggregate function, order by cannot be used together.
Another common scenario of -- over () is that it is used together with row_number () for paging.

Now we will introduce the window function.

Window Function OVER ()Specify a group of rows. The window function calculates the values of each row in the result set output from the window function.

The window function can GROUP data without using group by. It can also return the columns and aggregate columns of the basic row.

1. Ranking Window Function

ROW_NUMBER, DENSE_RANK, RANK, and NTILE are ranking functions.

The ranking window function can use the order by statement or partition by statement.

Partition by is used to group result sets. The window function is applied to each group.

Oder by specifies the order of the ranking window function. The order by statement must be used in the ranking window function.

For example, query the orders of each employee and sort the orders 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

Window functionsGroup data rows BY employee ID based on the partition by statement, and sort the data by order by statement. The rank function ROW_NUMBER () generates a sequence number starting from 1 for each group of data.

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

RANK () also generates an sequence number for each group of rows. Unlike ROW_NUMBER (), if order by is used and if there are the same values, the same sequence number is generated, in addition, the subsequent sequence numbers are non-sequential. For example, if two identical rows generate serial number 3, then serial number 5 is generated.

DENSE_RANK () and RANK () are similar. The difference is that if the same sequence number exists, the subsequent sequence number will not be interrupted. That is to say, if two identical rows generate sequence number 3, the sequence number generated next is still 4.

NTILE (integer_expression) groups data according to the specified number and generates an Sn for each group.

2. Aggregate Window Function

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

The aggregate window function can only use the partition by clause or without any statements. order by cannot be used together with the aggregate window function.

For example, query the total number of orders and order information of 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,TotalCountFrom OrderInfo ORDER BY EmployeeID 

If the window function does not use the partition by statement, 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 is all about this article. I hope you can learn how to use the window function of SQL server to perform grouping statistics. Thank you for reading this article.

Articles you may be interested in:

• Grouping statistics query (grouping by month and hour)
• SQL2005 learning notes window function (OVER)
• Sqlserver2005 use row_number () over paging Implementation Method
• SQL2005 uses ROW_NUMBER () OVER to implement the paging Function
• SQL Server grouping statistics and total count and applications WITH ROLLUP
• SQL Server: Merge multiple rows into one row and perform grouping statistics.
• 10 new tips for sorting, grouping, and counting SQL statements
• Detailed introduction to window function (1)