SQL Server 开窗函数 Over()代替游标的使用详解
作者:为之守望 发布时间:2024-01-25 00:35:40
前言:
今天在优化工作中遇到的sql慢的问题,发现以前用了挺多游标来处理数据,这样就导致在数据量多的情况下,需要一行一行去遍历从而计算需要的数据,这样处理的结果就是数据慢,容易卡死。
语法介绍:
1、与Row_Number() 函数结合使用,对结果进行排序,这个是我们使用的非常多的
2、与聚合函数结合使用,利用over子句的分组和排序,对需要的数据进行操作
例如:SUM() Over() 累加值、AVG() Over() 平均数
MAX() Over() 最大值、MIN() Over() 最小值
具体介绍:
下面模拟工作中通过开窗函数代替游标的例子,通过期初余额与单据的预收金额、应收金额、实收金额来计算截止本单的期末余额,在以往就是通过游标一行一行去遍历,计算需要的期末余额,现在使用SUM() Over()来代替,最终要实现的效果图如下:
第一行表示标题;第二行表示客户,是一行空行;第三行是期初余额,只显示期末余额的数据,第四至第六行表示的是每种单据的余额情况,并逐步汇总当前行的期末余额数据;最后一行表示的是对客户的合计。
1、构建需要用到的表和数据(简略版)
--客户表
CREATE TABLE Organization(
FItemID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FNumber NVARCHAR(255),
FName NVARCHAR(255)
)
--期初数据表
CREATE TABLE InitialData(
FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FCustId INT NOT NULL,
FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --预收金额
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --应收金额
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --实收金额
)
--单据明细表
CREATE TABLE DetailData(
FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FCustId INT NOT NULL,
FDate DATETIME NOT NULL,
FBillType NVARCHAR(64) NOT NULL,
FBillNo NVARCHAR(64) NOT NULL,
FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --预收金额
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --应收金额
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --实收金额
)
INSERT INTO Organization(FNumber,FName) VALUES('001','北京客户')
INSERT INTO Organization(FNumber,FName) VALUES('002','上海客户')
INSERT INTO Organization(FNumber,FName) VALUES('003','广州客户')
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,0,0,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,8000,7245,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,0,1068.21,1068.00)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算','XSD20200700008',0,1221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算','XSD20200700009',0,373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','委托结算退货','XSD20200700010',0,-427.05,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-07-30','销售商品返利','XSFL20200700005',0,-17.9,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-25','预收退款','SKD20200700002',-755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','销售发货','XSD20200700006',0,6169.50,6169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-30','销售总额返利','XSFL20200700002',0,-493.56,-421.85)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-31','其他应收','QTYS20200900001',0,6000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','预收冲应收','HXD20200700006',-7245.00,0,7245.00)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','销售收款','SKD20200700003',0,0,2386.96)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','应收转应收','HXD20200700007',0,2386.75,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-07-08','销售退货','XSD20200700014',0,-46.80,0)
GO
2、以往的游标写法
SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FClassTypeId INT NOT NULL,
FCustId INT NOT NULL,
FNumber NVARCHAR(255),
FName NVARCHAR(255),
FDate DATETIME NULL,
FBillType NVARCHAR(64) NULL,
FBillNo NVARCHAR(64) NULL,
FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --预收金额
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --应收金额
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --实收金额
FBalanceAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --期末余额
)
Declare @Id INT
Declare @CustId INT
Declare @PreAmount decimal(28,10)
Declare @ReceivableAmount decimal(28,10)
Declare @ReceiveAmount decimal(28,10)
Declare @OldCustId int
Declare @Count int
Declare @LastAmount decimal(28,10)
Declare @SumPreAmount decimal(28,10)
Declare @SumReceivableAmount decimal(28,10)
Declare @SumReceiveAmount decimal(28,10)
Declare @SumBalanceAmount decimal(28,10)
--使用游标
Declare Data_cursor Cursor
For Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount
From DetailData
Order By FCustId,FDate,FID
OPEN Data_cursor
FETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
SET @OldCustId = @CustId
SET @Count = 0
SET @LastAmount = 0
SET @SumPreAmount = 0
SET @SumReceivableAmount = 0
SET @SumReceiveAmount = 0
SET @SumBalanceAmount = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Count > 0
BEGIN
IF @OldCustId <> @CustId
BEGIN
--表示客户已经变了,要插入小计
SET @Count = 0
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT -9999,FName + '小计',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
FROM Organization
WHERE FItemID = @OldCustId
Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
END
END
IF @Count = 0
BEGIN
Set @OldCustId=@CustId
--插入一行空行
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
SELECT -1000,FName,FItemID,FNumber,FName
FROM Organization
WHERE FItemID = @CustId
--获取期初的期末余额
SELECT @LastAmount=isnull(FReceivableAmount,0) - isnull(FPreAmount,0) - isnull(FReceiveAmount,0),@PreAmount=isnull(FPreAmount,0),@ReceivableAmount=isnull(FReceivableAmount,0),@ReceiveAmount=isnull(FReceiveAmount,0)
FROM InitialData
WHERE FCustId = @CustId
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
VALUES(-1000,'期初余额',@CustId,'','',@LastAmount)
SELECT @Count = 1
SELECT @SumBalanceAmount = @LastAmount
END
--插入单据明细
INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT 0,d.FCustId,o.FNumber,o.FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,@LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount
FROM DetailData d
INNER JOIN Organization o ON d.FCustId = o.FItemID
WHERE d.FCustId = @CustId AND FID = @Id
SELECT
@LastAmount = @LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount,
@SumPreAmount=@SumPreAmount + FPreAmount,@SumReceivableAmount=@SumReceivableAmount + FReceivableAmount,
@SumReceiveAmount=@SumReceiveAmount + FReceiveAmount
FROM DetailData
WHERE FCustId = @CustId AND FID = @Id
FETCH NEXT FROM Data_cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
END
IF @Count > 0
BEGIN
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT -9999,FName + '小计',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
FROM Organization
WHERE FItemID = @OldCustId
Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
END
CLOSE Data_cursor
DEALLOCATE Data_cursor
SELECT * FROM #DATA
ORDER BY FCustId,FID
DROP TABLE #DATA
代码说明:创建了一个临时表,使用游标遍历我们的DetailData数据表,为了呈现我们最终需要的数据样式,插入客户空行、期初余额、单据信息、客户小计等,逐行计算期末余额值的情况,最终效果如下:
3、使用SUM() Over()的写法
SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
FID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
FClassTypeId INT NOT NULL,
FCustId INT NOT NULL,
FNumber NVARCHAR(255),
FName NVARCHAR(255),
FDate DATETIME NULL,
FBillType NVARCHAR(64) NULL,
FBillNo NVARCHAR(64) NULL,
FPreAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --预收金额
FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --应收金额
FReceiveAmount DECIMAL(28,10) NOT NULL DEFAULT(0), --实收金额
FBalanceAmount DECIMAL(28,10) NOT NULL DEFAULT(0) --期末余额
)
--插入空行
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
SELECT -1000,FName,FItemID,FNumber,FName
FROM Organization o
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
--插入期初余额
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
SELECT -1000,'期初余额',FItemID,'','',i.FReceivableAmount - i.FPreAmount -i.FReceiveAmount
FROM Organization o
INNER JOIN InitialData i ON o.FItemID = i.FCustId
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
--插入单据明细(关键代码SUM() Over() )
INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT 0,d.FCustId,o.FNumber,o.FName,d.FDate,d.FBillType,d.FBillNo,d.FPreAmount,d.FReceivableAmount,d.FReceiveAmount,
SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)
+ i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM DetailData d WITH(NOLOCK)
INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustId
INNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustId
ORDER BY d.FCustId,d.FDate,d.FID
--插入小计
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT -9999,FName + '小计',d.FCustId,FNumber,FName,SUM(FPreAmount),SUM(FReceivableAmount),SUM(FReceiveAmount),0
FROM dbo.DetailData d
INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID
GROUP BY d.FCustId,o.FName,o.FNumber
--更新小计的期末余额
UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM #DATA d
INNER JOIN InitialData i ON d.FCustId = i.FCustId
WHERE d.FClassTypeId = -9999
SELECT * FROM #DATA
ORDER BY FCustId,FID
DROP TABLE #DATA
代码说明:相比第二种,去除了游标的写法,通过了
SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)
来计算我们需要的值,这个语法说明一下,sum是累加计算,计算应收金额 - 预收金额 - 实收金额(第二行计算出来的结果要加上第一行计算出来的结果,第三行计算出来的结果要加上第二行计算出来的结果,依次类推,所以,其他聚合函数也是这种用法哦),PARTITION BY分组统计客户,并通过Order by指定排序
这个PARTITION BY和Order By结果的用法就很关键了,不然计算就不是预期想要的
再举个例子:比如使用Count() Over() 计算客户的订单号
SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData
总结:
1、游标的使用场景可以很广,但是在数据量大的时候,就会显得很慢,一行一行遍历的速度还是挺久的
2、使用开窗函数来实现一些功能,还是很方便能实现效果,并且它的速度也是很快,值得推荐。
来源:https://www.cnblogs.com/cxt618/archive/2020/10/16/13826000.html
猜你喜欢
- 使用UNION多数SQL查询都只包含一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句),并将
- python中有很多字符串连接方式,今天在写代码,顺便总结一下:最原始的字符串连接方式:str1 + str2python 新字符串连接语法
- 一、数据类型1.数据类型的判断Number => int float complex bool容器 => str list tu
- 1.静态方法(staticmethod)静态方法:@staticmethod也是一个类方法,是可以直接类调用的。个人认为的使用场景是:只要要
- 主成分分析,即Principal Component Analysis(PCA),是多元统计中的重要内容,也广泛应用于机器学习和其它领域。它
- 开发动机:最近用手机QQ浏览器下载了一些视频,视频越来越多,占用了手机内存,于是想把下载的视频传到电脑上保存,可后来发现这些视频都是m3u8
- 本文实例讲述了php中$_GET与$_POST过滤sql注入的方法,分享给大家供大家参考。具体分析如下:此函数只能过滤一些敏感的sql命令了
- Mysql数据库备份和还原常用的命令是进行Mysql数据库备份和还原的关键,没有命令,什么都无从做起,更谈不上什么备份还原,只有给系统这个命
- (一)深入浅出理解索引结构实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered
- 🥩数据采集🍖确定网址王者新赛季马上就要开始了,大家都开始冲榜了,准备拿一个小省标,那么,本文,就来练习获取各地最低战力的爬虫采集实战。确定好
- radians()方法把角度转化为弧度角x。语法以下是radians()方法的语法:radians(x)注意:此函数是无法直接访
- python中一切皆为对象,所谓对象:我自己就是一个对象,我玩的电脑就是对象,坐着的椅子就是对象,家里养的小狗也是一个对象。。。。。。我们通
- 一、Pythont如何打开 txt 格式的文件?1.首先我使用pycharm创建一个项目,然后在这个项目里面再创建一个python的包,然后
- 一、需求说明需要使用Python实现将内容转为base64编码,解码,方便后续的数据操作。二、base64简介Base64是一种二进制到文本
- 在知识库的建立的时候,用普通VARCHAR2存放文章是显然不够的,只有区区4000的字节,放不了多少字, 而CLOB数据类型,则能最多存放8
- ping 基本概念ping (Packet Internet Groper)是一种因特网包探索器,用于测试网络连接量的程序。Ping是工作在
- 一、前言提到 limit 优化,大多数 MySQL DBA 都不会陌生,能想到各种应对策略,比如延迟关联,书签式查询等等,之前我也写过一篇优
- 一、时间戳实际开发中,我们一般希望create_time和update_time这两个属性能自动设置,所以在建表的时候需要在model上特殊
- 最近社会猪可是火遍了大江南北,不蹭下热度可对不起它。见过手画的佩奇,见过用代码画的吗?没有?那就来看我大显身手。用python的turtle
- 废话不多说,直接上代码create database mydbuse mydbgocreate table account( i