SQL SERVER 日记 – 通过OVER实现分组聚合

对于通过OVER来做聚合，已经基本掌握了。但是要详细解释其原理，还是感觉有些说不清晰。所以只能结合实例来看了。在我们的查询中用到了一张表SalesOrder。下面是表结构和创建该表的T-SQL语句。

字段名称	数据类型	说明
SalesOrderID	bigint	identity primary key
SalesID	bigint	not null
CustomerID	bigint	not null
SalesValue	decimal(18,2)
OrderDate	date

?

创建SalesOrder表的TSQL

1 2 3 4 5 6 7 8 9 10 11 12

IF OBJECT_ID(N 'dbo.SalesOrder' ,N 'U' ) IS NOT NULL    DROP TABLE dbo.SalesOrder GO CREATE TABLE [dbo].[SalesOrder] (      [SalesOrderID] [ bigint ] IDENTITY(1,1) NOT NULL Primary Key ,      [SalesID] [ bigint ] NOT NULL ,      [CustomerID] [ bigint ] NOT NULL ,      [SalesValue] [ decimal ](18, 2) NOT NULL ,      [OrderDate] [ date ] NOT NULL , ) GO insert into SalesOrder (SalesID,CustomerID,SalesValue,OrderDate) select 1,2,50.0,'2013-6-6' union all select 1,2,30.0,'2013-6-7' union all select 1,2,30.0,'2013-6-7' union all select 1,2,40.0,'2013-6-6'

首先说聚集函数。聚集函数是指那些对一组数据进行运算，然后输出标量值的函数。例如SUM,COUNT,AVG,MIN和MAX等等。我们通常将聚集函数和GROUP BY子句联合使用。例如，下面的T-SQL可以用来查询[每人每天的销售额占当天销售额的比例]

?

使用CTE和Join的查询

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH SP AS (    --计算每天的销售额总和    SELECT    OrderDate, SUM (SalesValue) AS SumSales    FROM      dbo.[SalesOrder]    GROUP BY  OrderDate ) SELECT  O.OrderDate,O.SalesID,          O.SalesValue AS PersonalDailySales,          SP.SumSales AS DailyTotalSales,          CONVERT ( decimal (18,2),100*O.SalesValue/SP.SumSales)        AS PersonalContribution FROM    dbo.[SalesOrder] O JOIN SP ON O.OrderDate=SP.OrderDate

 

在上面的查询中，CTE表达式通过在OrderDate上做Group By操作，来实现按天统计销售额的功能。然后将CTE和SalesOrder表做Join 才能取得最后的结果。这个查询看起来有点复杂，读起来也不直观。使用OVER子句，就是为了简化这种TSQL写法。

必须要说明的是，OVER有好几种用法，这里列举的只是其中一种：在SELECT阶段和聚集函数合作。以前一直以为聚集函数只能和Group By合作。其实也可以和OVER合作。OVER的本质是定义了一个窗口函数。实质上就是将数据行划分为组。这样就和聚集函数的要求匹配上了。

下面的TSQL和上面给出的TSQL执行效果是一样的。而且在SQL SERVER中显示查询计划也完全一样。

?

使用OVER的查询

1 2 3 4 5 6 7

SELECT  OrderDate,SalesID,      SalesValue as PersonalDailySales,      SUM (SalesValue) OVER(PARTITION BY OrderDate) AS DailyTotalSales,      CONVERT ( decimal (18,2),          100*SalesValue/ SUM (SalesValue) OVER(PARTITION BY OrderDate))        AS PersonalContribution FROM    dbo.[SalesOrder]