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

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关键字可以方便的获得累计销售额、销售额排名前几位的销售商品等等分析数据。