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

前言： 今天在优化工作中遇到的sql慢的问题，发现以前用了挺多游标来处理数据，这样就导致在数据量多的情况下，需要一行一行去遍历从而计算需要的数据，这样处理的结果就是数据慢，容易卡死。 语法介绍： 1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的 　　 2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作 例如：SUM() Over() 累加值、AVG() Over() 平均数 MAX() Over() 最大值、MIN() Over() 最小值 具体介绍： 下面模拟工作中通过开窗函数代替游标的例子，通过期初余额与单据的预收金额、应收金额 【SQL Server 开窗函数 Over() 代替游标的使用详解】 在SQL Server中，开窗函数（Window Function）是一种强大的工具，它可以替代传统的游标来处理数据，尤其在数据量大的情况下，能显著提高查询效率。游标通常需要一行一行地遍历数据，这在大数据量的场景下会导致性能低下，甚至可能造成系统卡死。本文将详细介绍如何使用开窗函数Over()来优化这类问题。 1. **Row_Number() 函数结合使用** Row_Number() 函数与Over()一起使用，可以为结果集中的每一行分配一个唯一的序列号。这在需要按特定顺序对数据进行排序时非常有用，比如在分页查询或者按条件排序时。例如： ```sql SELECT *, ROW_NUMBER() OVER(ORDER BY SomeColumn) AS RowNum FROM YourTable ``` 2. **与聚合函数结合使用** 聚合函数如SUM(), AVG(), MAX(), MIN()与Over()结合，可以在不使用游标的情况下计算累加值、平均数、最大值和最小值。例如： - 累加值：计算累计余额 ```sql SELECT FCustId, SUM(FReceivableAmount) OVER (PARTITION BY FCustId ORDER BY FDate) AS CumulativeBalance FROM DetailData ``` - 平均数：计算每个客户的平均预收金额 ```sql SELECT FCustId, AVG(FPreAmount) OVER (PARTITION BY FCustId) AS AvgPreAmount FROM DetailData ``` - 最大值和最小值：找出每个客户的最大或最小应收金额 ```sql SELECT FCustId, MAX(FReceivableAmount) OVER (PARTITION BY FCustId) AS MaxReceivable, MIN(FReceivableAmount) OVER (PARTITION BY FCustId) AS MinReceivable FROM DetailData ``` 3. **案例分析：计算期末余额** 以下是一个具体的示例，展示了如何使用开窗函数计算客户的期末余额。假设我们有客户表Organization，期初数据表InitialData，以及单据明细表DetailData。期初余额加上所有单据的预收、应收和实收金额，减去单据的实收金额，即可得到截止到当前单据的期末余额。 我们需要创建这些表并插入数据，然后使用开窗函数计算期末余额。以下是简化后的创建表和插入数据的SQL语句： ```sql -- 创建客户表 CREATE TABLE Organization(FItemID INT PRIMARY KEY, FNumber NVARCHAR(255), FName NVARCHAR(255)) -- 创建期初数据表 CREATE TABLE InitialData(FID INT PRIMARY KEY, FCustId INT, FPreAmount DECIMAL(28,10), FReceivableAmount DECIMAL(28,10), FReceiveAmount DECIMAL(28,10)) -- 创建单据明细表 CREATE TABLE DetailData(FID INT PRIMARY KEY, FCustId INT, FDate DATETIME, FBillType NVARCHAR(64), FBillNo NVARCHAR(64), FPreAmount DECIMAL(28,10), FReceivableAmount DECIMAL(28,10), FReceiveAmount DECIMAL(28,10)) -- 插入数据 INSERT INTO Organization(FNumber, FName) VALUES('001', '北京客户') INSERT INTO InitialData(FCustId, FPreAmount, FReceivableAmount, FReceiveAmount) VALUES(1, 0, 0, 0) -- ...更多插入数据的语句... ``` 接下来，我们可以编写查询，使用SUM() Over()计算期末余额： ```sql WITH CTE AS ( SELECT FCustId, FDate, SUM(FPreAmount) - SUM(FReceiveAmount) OVER (PARTITION BY FCustId ORDER BY FDate) AS Balance FROM DetailData GROUP BY FCustId, FDate ) SELECT * FROM CTE ORDER BY FCustId, FDate ``` 这个查询会返回每个客户的每个单据日期及其对应的期末余额，从而避免了游标逐行计算的低效。 总结，SQL Server的开窗函数提供了高效且灵活的数据处理方式，能够有效地替代游标，优化SQL查询性能。通过熟练掌握Row_Number()、聚合函数如SUM(), AVG(), MAX(), MIN()与Over()的组合使用，开发者可以编写出更高效、更易于维护的SQL代码，提升数据库应用的运行效率。