sql 分析函数 over

除了ORDER BY（按…排序）语句外，分析函数是一条查询被执行的操作。

所有合并、WHERE、GROUP BY、HAVING语句都是分析函数处理之前完成的。

因此，分析函数只出现在选择目录或ORDER BY（按…排序）语句中。 

 

使用Over语句的情况 ：

A. 等级函数如， ROW_NUMBER, DENSE_RANK, RANK, NTILE 使用 OVER(ORDER BY) 语句

example.

 

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

 

ROW_NUMBER() 增添顺序序号，即时存在相同的也递增序号

RANK()相同的数据序号相同，接下来为跳号（是跳跃排序，有两个第二名时接下来就是第四名）

dense_rank()相同的数据序号相同，接下来顺序递增序号(是连续排序，有两个第二名时仍然跟着第三名)

ntile(N)将记录分为N组。

 

 

B. 聚合函数如,  SUM,AVG,COUNT,MIN,MAX等使用OVER(PARTITION BY)语句

Example.

 

 Copy Code 
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO