【SQL学习笔记】排名开窗函数，聚合开窗函数（Over by）

 

处理一些分组后，该组按照某列排序后 ，取其中某条完整数据的问题。 或 按照其中不同列分组后的聚合 比如 sum，avg之类。

 

MSDN上语法：

Ranking Window Functions 
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
< OVER_CLAUSE > :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

一共两种应用场景。

场景1：

          按某列进行重新分区，然后区内排序后，取其中某条数据。例：

     

1   select * from (  
2    select id,name,counts,row_number() over(partition by name order by counts desc) rn  
3    from Table1  
4   ) t where t.rn <=1  

 

　　   含义：

over(partition by name order by counts desc)

      意思是 把表Table1 中的数据按照 name列进行分区，每个区按照counts进行排序。

        row_number() over(partition by name order by counts desc) rn

　　   意思是 每个区排序后 取到其中排序后的序列号 。并起名字rn 

    

     

select * from (  
     select id,name,counts,row_number() over(partition by name order by counts desc) rn  
     from Table1  
    ) t where t.rn =1 

 　　意思是  每个分区排序后，获取第一行数据，其他行舍弃。

 

   场景二：

             结合聚合函数，获取分区聚合后的值，性能比子查询还要高。

　　

1 SELECT SalesOrderID, ProductID, OrderQty
2     ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
3     ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
4     ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
5     ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
6     ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
7 FROM Sales.SalesOrderDetail 
8 WHERE SalesOrderID IN(43659,43664);

 

            这段是MSDN上的，这的意思是按不同的需要，重新分区（这里是按照SalesOrderID字段分区），获取相应数据。聚合函数就不多说了。。亮点是，比子查询性能高（MSDN说的）。

           

　　MSDN的链接地址：https://msdn.microsoft.com/zh-cn/library/ms189461(v=sql.105).aspx

 

转载于:https://www.cnblogs.com/sword85/p/4510141.html