登录
当前位置： 首页 > 编程笔记 >

SQL Server 开窗函数 Over()代替游标的使用详解

蒋华美
2023-03-14
本文向大家介绍SQL Server 开窗函数 Over()代替游标的使用详解，包括了SQL Server 开窗函数 Over()代替游标的使用详解的使用技巧和注意事项，需要的朋友参考一下

前言：

今天在优化工作中遇到的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、使用开窗函数来实现一些功能，还是很方便能实现效果，并且它的速度也是很快，值得推荐。

到此这篇关于SQL Server 开窗函数 Over()代替游标的使用的文章就介绍到这了,更多相关SQL Server 开窗函数 Over()内容请搜索小牛知识库以前的文章或继续浏览下面的相关文章希望大家以后多多支持小牛知识库！

 类似资料：
  • Sql Server 开窗函数Over()的使用实例详解

    本文向大家介绍Sql Server 开窗函数Over()的使用实例详解，包括了Sql Server 开窗函数Over()的使用实例详解的使用技巧和注意事项，需要的朋友参考一下 利用over()，将统计信息计算出来，然后直接筛选结果集 查询要求：查出每类产品中价格最高的信息 --做法一：找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的 --缺点:要进行一次join     --做法二：

  • SQL中的开窗函数详解可代替聚合函数使用

    本文向大家介绍SQL中的开窗函数详解可代替聚合函数使用，包括了SQL中的开窗函数详解可代替聚合函数使用的使用技巧和注意事项，需要的朋友参考一下  在没学习开窗函数之前，我们都知道，用了分组之后，查询字段就只能是分组字段和聚合的字段，这带来了极大的不方便，有时我们查询时需要分组，又需要查询不分组的字段，每次都要又到子查询，这样显得sql语句复杂难懂，给维护代码的人带来很大的痛苦，然而开窗函数出现了，

  • SQLServer中merge函数用法详解

    本文向大家介绍SQLServer中merge函数用法详解，包括了SQLServer中merge函数用法详解的使用技巧和注意事项，需要的朋友参考一下 Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入，它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。 M

  • 详解SqlServer数据库中Substring函数的用法

    本文向大家介绍详解SqlServer数据库中Substring函数的用法，包括了详解SqlServer数据库中Substring函数的用法的使用技巧和注意事项，需要的朋友参考一下 功能：返回字符、二进制、文本或图像表达式的一部分 语法：SUBSTRING ( expression, start, length ) 1、substring(操作的字符串，开始截取的位置，返回的字符个数) 例如： 从'

  • SQL开窗函数的具体实现详解

    本文向大家介绍SQL开窗函数的具体实现详解，包括了SQL开窗函数的具体实现详解的使用技巧和注意事项，需要的朋友参考一下 开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Orac

  • sql server如何利用开窗函数over()进行分组统计

    本文向大家介绍sql server如何利用开窗函数over()进行分组统计，包括了sql server如何利用开窗函数over()进行分组统计的使用技巧和注意事项，需要的朋友参考一下 这是一道常见的面试题，在实际项目中经常会用到。 需求：求出以产品类别为分组，各个分组里价格最高的产品信息。 实现过程如下： --测试数据 --做法一：找到每个组里,价格最大的值；然后再找出每个组里价格等于这个值的 -

相关阅读

SQL开窗函数的具体实现详解sql server如何利用开窗函数over()进行分组统计游标vs. While循环-SQLServer详解C语言gets()函数与它的替代者fgets()函数Oracle数据库中SQL开窗函数的使用

相关文章

MySQL游标Oracle游标用Python开发一个猜数字游戏PL/SQL游标SQLServer教程

相关问答

使用游标 Oracle 插入数据ApacheFlink：窗口函数和时间的开始Oracle打开游标ora-1000错误，“超过最大打开游标数。”[重复]使用SQL变量替换函数的问题用计数代替的奇数序列

相关工具

Label Overnode-sqlserversqlserver managerssh-over-ssmHDFS over WEBDAV

相关文档

Linux C 函数 使用手册小米函数计算开发文档HAProxy 用法详解Java 函数速查SQL 合计函数
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.