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

本文通过具体案例介绍了SQL中OVER子句的使用方法及其如何简化复杂的T-SQL查询语句,特别是与聚合函数配合使用时的优势。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

字段名称数据类型说明
SalesOrderIDbigintidentity primary key
SalesIDbigintnot null
CustomerIDbigintnot null
SalesValuedecimal(18,2) 
OrderDatedate 
?
创建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]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值