对于通过OVER来做聚合,已经基本掌握了。但是要详细解释其原理,还是感觉有些说不清晰。所以只能结合实例来看了。在我们的查询中用到了一张表SalesOrder。下面是表结构和创建该表的T-SQL语句。
字段名称 | 数据类型 | 说明 |
SalesOrderID | bigint | identity primary key |
SalesID | bigint | not null |
CustomerID | bigint | not null |
SalesValue | decimal(18,2) | |
OrderDate | date |
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)
|
首先说聚集函数。聚集函数是指那些对一组数据进行运算,然后输出标量值的函数。例如SUM,COUNT,AVG,MIN和MAX等等。我们通常将聚集函数和GROUP BY子句联合使用。例如,下面的T-SQL可以用来查询[每人每天的销售额占当天销售额的比例]
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中显示查询计划也完全一样。
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]
|