SQL Server 开窗函数Over的使用（计算结存金额）

原创已于 2022-03-25 11:38:30 修改 · 1.3k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库开发 #sqlserver #数据库 #sql

于 2022-03-24 17:19:38 首次发布

SQL Server 同时被 2 个专栏收录

6 篇文章

订阅专栏

SQL

6 篇文章

订阅专栏

本文介绍了如何在SQL Server中利用开窗函数Over来计算每个币种在每个交易日的结存金额，通过示例展示了其简单易用的特点，并提到结合Top关键字可用于更多数据分析场景。

SQL Server 开窗函数Over的使用（计算结存金额）

假设我们有一组数据，包含四列（币种,交易日期,收入金额,支出金额）

Select * From
(Values
('CNY','2022-01-01',100,50),
('CNY','2022-01-02',200,50),
('CNY','2022-01-03',300,100),
('CNY','2022-01-04',400,50),
('CNY','2022-01-05',500,60),
('CNY','2022-01-06',1000,500),
('CNY','2022-01-07',600,300),
('CNY','2022-01-08',800,400),
('CNY','2022-01-09',100,50),
('CNY','2022-01-10',0,500),
('USD','2022-01-01',100,50),
('USD','2022-01-02',200,50),
('USD','2022-01-03',300,100),
('USD','2022-01-04',400,500),
('USD','2022-01-05',500,60),
('USD','2022-01-06',400,500),
('USD','2022-01-07',600,300),
('USD','2022-01-08',800,400),
('USD','2022-01-09',100,50),
('USD','2022-01-10',10,500)
) 
TempTable(Currency,TradeDate,InValue,OutValue)

数据内容如下图

在这里插入图片描述
我们如何计算每个币种在每个交易日的结存金额呢？在不使用Over开窗函数的情况下，我们需要编写一个计算余额的函数GetBalance(@Currency char(3),TradeDate Date)来每日实现余额的计算，这里我们就不继续写函数的具体实现了。下面我们看看如何使用Over开窗函数实现余额计算功能。

Select Currency,TradeDate,InValue,OutValue
,Sum(InValue-OutValue) Over(Partition By Currency Order By TradeDate ASC Rows Unbounded Preceding) BalanceValue
From
(Values
('CNY','2022-01-01',100,50),
('CNY','2022-01-02',200,50),
('CNY','2022-01-03',300,100),
('CNY','2022-01-04',400,50),
('CNY','2022-01-05',500,60),
('CNY','2022-01-06',1000,500),
('CNY','2022-01-07',600,300),
('CNY','2022-01-08',800,400),
('CNY','2022-01-09',100,50),
('CNY','2022-01-10',0,500),
('USD','2022-01-01',100,50),
('USD','2022-01-02',200,50),
('USD','2022-01-03',300,100),
('USD','2022-01-04',400,500),
('USD','2022-01-05',500,60),
('USD','2022-01-06',400,500),
('USD','2022-01-07',600,300),
('USD','2022-01-08',800,400),
('USD','2022-01-09',100,50),
('USD','2022-01-10',10,500)
) 
TempTable(Currency,TradeDate,InValue,OutValue)
Order  By Currency,TradeDate

我们来看一下执行结果
在这里插入图片描述
是不是很简单，只是增加了一个计算列。

Sum(InValue-OutValue) Over(Partition By Currency Order By TradeDate Rows Unbounded Preceding) BalanceValue

我们使用Over开窗函数和结合Top关键字可以方便的获得累计销售额、销售额排名前几位的销售商品等等分析数据。