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

时间：2022-07-12 08:50:17

处理一些分组后，该组按照某列排序后 ，取其中某条完整数据的问题。 或 按照其中不同列分组后的聚合 比如 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：

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

   select * from (

    select id,name,counts,row_number() over(partition by name order by counts desc) rn

    from Table1

   ) 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

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

场景二：

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

　　

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

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

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

相关文章