Shulou(Shulou.com)06/01 Report--

Using over (), calculate the statistics, and then filter the result set directly

Declare @ t table (ProductID int,ProductName varchar (20), ProductType varchar (20), Price int) insert @ tselect 1 repartee name1, p1p1, p1p1, 3 union allselect 2, name2, p1, p1, 5 union allselect, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3,

Query requirements: find out the information with the highest price in each category of products

-- practice 1: find the value with the highest price in each group, and then find out that the price in each group is equal to this value

-- disadvantages: to have 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 2: use over () to calculate the statistics, and then filter the result set directly.

-- over () allows functions (including aggregate functions) to be output with lines.

; 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 of-over () is: over ([patition 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 paging in conjunction with row_number ().

Now let's introduce the windowing function.

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

The windowing function can group data without using GROUP BY, and it can return both the columns of the underlying row and the aggregate columns.

1. Ranking windowing function

ROW_NUMBER, DENSE_RANK, RANK, NTILE belong to the ranking function.

The ranking windowing function can be used either alone or with PARTITION BY.

PARTITION BY is used to group result sets, and windowing functions are applied to each group.

ODER BY specifies the order in which windowing functions are ranked. The ORDER BY statement must be used in the ranking windowing function.

For example, query the order of each employee and sort it 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 the rows of data by employee ID according to the PARTITION BY statement, and then sorts them by the ORDER BY statement, and the ranking function ROW_NUMBER () generates a sequence number for each group of data starting at 1.

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

RANK () also generates a sequence number for each set of rows, unlike ROW_NUMBER (), which is sorted by ORDER BY, the same sequence number is generated if there is the same value, and the next sequence number is discontiguous. For example, if two identical lines are generated with the sequence number 3, then the sequence number 5 will be generated.

DENSE_RANK () is similar to RANK (), except that if you have the same sequence number, the next sequence number will not be interrupted. That is, if two identical lines are generated with the sequence number 3, then the sequence number generated next is still 4.

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

2. Aggregate windowing function

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

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

For example, query the total number of orders and order information of employees

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))

Summary

The above is a detailed explanation of the use of the Sql Server windowing function Over () introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply to you in time. Thank you very much for your support to the website!

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.