← 返回首页目录
# 使用SQL查询计算库存库龄
**作者：吉祥法师**
## 一、问题背景与需求分析
在企业的仓库管理系统中，库存库龄（Stock Ageing）计算是一项至关重要的业务需求。库龄分析能够帮助企业了解库存的滞留时间，从而优化采购策略、识别滞销商品、合理配置仓储资源，并为财务报表提供准确的数据支持。
在SQL Server环境中，实现库存库龄计算面临着独特的挑战。库存数据通常以流水记录的形式存储，每条记录包含物品编号（ItemID）、单据类型（DocType）、仓库位置（Location）、业务日期（Dates）和数量（QTY）等字段。这些记录既包含入库操作（如Receipt收货、Transfer调拨入库），也包含出库操作（如Sale销售出库，数量以负数表示）。
**核心业务场景**：当发生销售出库时，需要按照先进先出（FIFO）原则，将出库数量从最早的一批入库记录中扣除。剩余库存的库龄分布则反映了各批次入库日期对应的结存数量。这一过程在财务和库存管理中被称为"批次追溯"或"库龄分层"。
以典型数据为例：
| itemid | 日期 | 数量 |
|--------|------------|-----|
| 1 | 2009-06-01 | 10 |
| 1 | 2009-07-01 | -5 |
| 1 | 2009-08-01 | 5 |
| 1 | 2009-08-01 | -5 |
| 1 | 2009-09-01 | 10 |
| 1 | 2009-09-01 | -2 |
**期望计算结果**：
- 2009-07-01的销售（-5）应从2009-06-01的入库（10）中扣除，剩余5件；
- 2009-08-01的销售（-5）继续从2009-06-01的剩余库存（5件）中扣除，该批次库存清零；
- 2009-08-01的调拨入库（5）不受影响，仍然留存；
- 2009-09-01的销售（-2）从2009-08-01的入库（5）中扣除，剩余3件；
- 2009-09-01的收货入库（10）完整保留。
最终库龄分布应为：2009-08-01结存3件，2009-09-01结存10件。
## 二、问题解决思路
### 2.1 核心算法逻辑
解决这一问题的关键在于理解FIFO库存结算的数学本质。整个计算过程可以分解为三个核心步骤：
**第一步：汇总入库批次**。将所有正数数量记录（收货、调入等）按照物品、仓库和日期进行汇总，得到各批次的入库总量。同时，利用窗口函数计算每个批次的累计入库量，形成"累计供给曲线"。
**第二步：汇总出库总量**。将所有负数数量记录（销售、调出等）按照物品和仓库进行汇总，得到各物品在每个仓库的累计出库总量。这一总量代表了从开始到当前日期为止，所有消耗的总需求。
**第三步：匹配与结存计算**。将累计入库量与累计出库总量进行比较。对于每个入库批次，如果其累计入库量大于累计出库总量，说明该批次（或该批次的一部分）仍有剩余库存。剩余的判断遵循以下原则：
- 对于最早的批次，剩余量 = 累计入库量 - 累计出库总量；
- 对于后续批次，只要累计出库总量未超过当前批次之前的累计入库量，该批次就完整保留。
### 2.2 窗口函数的应用价值
SQL Server 2012引入了增强的窗口函数支持，包括SUM() OVER (PARTITION BY ... ORDER BY ...)累计求和功能。这一特性使得我们无需使用复杂的自连接或游标，就能高效地计算累计值。具体而言：
- `SUM(qty) OVER (PARTITION BY itemid, location ORDER BY dates)`能够按时间顺序计算每个物品在每个仓库的累计入库量；
- 配合`ROW_NUMBER()`窗口函数，可以为每个批次分配序号，便于最后的条件判断。
## 三、完整解决方案
### 3.1 数据准备
首先，我们创建测试环境并准备包含多物品、多仓库的示例数据：
```sql
USE [tempdb]
GO
IF OBJECT_ID(N'tempdb.dbo.InvAge') IS NOT NULL
DROP TABLE InvAge;
CREATE TABLE [dbo].[InvAge](
[Itemid] int NOT NULL,
[Doctype] varchar(10) NOT NULL,
[Location] varchar(10) NOT NULL,
[Dates] datetime NOT NULL,
[QTY] int NOT NULL
) ON [PRIMARY]
GO
-- 插入多物品、多仓库的测试数据
INSERT INTO [InvAge] VALUES
(1, 'Receipt', '10GEI', '2009-06-01', 10),
(1, 'Sale', '10GEI', '2009-07-01', -5),
(1, 'Transfer','10GEI', '2009-08-01', 5),
(1, 'Sale', '10GEI', '2009-08-01', -5),
(1, 'Receipt', '10GEI', '2009-09-01', 10),
(1, 'Sale', '10GEI', '2009-09-01', -2),
(1, 'Receipt', '10GEI_2', '2009-06-01', 10),
(1, 'Sale', '10GEI_2', '2009-07-01', -5),
(1, 'Transfer','10GEI_2', '2009-08-01', 5),
(1, 'Sale', '10GEI_2', '2009-08-01', -5),
(1, 'Receipt', '10GEI_2', '2009-09-01', 10),
(1, 'Sale', '10GEI_2', '2009-09-01', -2),
(2, 'Receipt', '10GEI', '2009-06-01', 10),
(2, 'Sale', '10GEI', '2009-07-01', -5),
(2, 'Transfer','10GEI', '2009-08-01', 5),
(2, 'Sale', '10GEI', '2009-08-01', -5),
(2, 'Receipt', '10GEI', '2009-09-01', 10),
(2, 'Sale', '10GEI', '2009-09-01', -2);
```
### 3.2 分步解析查询逻辑
#### 第一层CTE：汇总正数入库数据
```sql
;WITH cte0 AS (
SELECT itemid, location, dates, SUM(qty) AS qty
FROM InvAge
WHERE qty > 0
GROUP BY itemid, location, dates
)
```
此步骤过滤出所有入库记录（数量为正），并按物品、仓库、日期进行汇总。这一层确保同一个日期有多次入库时能够合并为一条批次记录。
#### 第二层CTE：计算累计入库量
```sql
, cte AS (
SELECT
itemid,
location,
dates,
qty,
SUM(qty) OVER (
PARTITION BY itemid, location
ORDER BY dates
) AS sum_qty
FROM cte0
)
```
此步骤使用窗口函数计算每个批次累计入库量。`sum_qty`表示从最早日期到当前批次为止的累计入库数量。这是后续与出库总量比较的基础。
#### 第三层CTE：汇总出库总量
```sql
, cte2 AS (
SELECT
itemid,
location,
-SUM(qty) AS sum_qty -- 负负得正，转为正数表示总出库量
FROM InvAge
WHERE qty < 0
GROUP BY itemid, location
)
```
此步骤汇总所有出库记录（数量为负），取负数后得到正数的总出库量。注意这里的汇总不区分日期，因为在FIFO原则下，所有出库统一从最早的入库批次开始消耗。
#### 第四层CTE：筛选有剩余库存的批次
```sql
, cte3 AS (
SELECT
a.itemid,
a.location,
a.dates,
a.qty,
a.sum_qty,
ISNULL(b.sum_qty, 0) AS sum_qty2,
ROW_NUMBER() OVER(
PARTITION BY a.itemid, a.location
ORDER BY a.dates
) AS row_num
FROM cte a
LEFT JOIN cte2 b ON a.itemid = b.itemid AND a.location = b.location
WHERE a.sum_qty > ISNULL(b.sum_qty, 0)
)
```
此步骤将每个入库批次的总累计量与总出库量进行比较。只保留累计入库量大于总出库量的批次，这些批次才有剩余库存。同时，使用`ROW_NUMBER()`为每个物品和仓库下的剩余批次按日期排序。
#### 最终查询：计算各批次结存数量
```sql
SELECT
itemid,
location,
dates,
CASE
WHEN row_num = 1 THEN sum_qty - sum_qty2
ELSE qty
END AS qtybal
FROM cte3;
```
在最终查询中，最关键的处理在于第一行（最早批次）的剩余量计算：
- **最早批次**的结存 = 该批次累计量 - 总出库量。这是因为出库首先消耗最早的批次；
- **后续批次**的结存 = 该批次自身入库量。这是因为只要累计入库量大于总出库量，说明出库全部被之前的批次数量的批次消耗完毕，当前批次完整保留。
### 3.3 完整查询脚本
```sql
;WITH cte0 AS (
SELECT itemid, location, dates, SUM(qty) AS qty
FROM InvAge
WHERE qty > 0
GROUP BY itemid, location, dates
),
cte AS (
SELECT
itemid,
location,
dates,
qty,
SUM(qty) OVER (
PARTITION BY itemid, location
ORDER BY dates
) AS sum_qty
FROM cte0
),
cte2 AS (
SELECT
itemid,
location,
-SUM(qty) AS sum_qty
FROM InvAge
WHERE qty < 0
GROUP BY itemid, location
),
cte3 AS (
SELECT
a.itemid,
a.location,
a.dates,
a.qty,
a.sum_qty,
ISNULL(b.sum_qty, 0) AS sum_qty2,
ROW_NUMBER() OVER(
PARTITION BY a.itemid, a.location
ORDER BY a.dates
) AS row_num
FROM cte a
LEFT JOIN cte2 b ON a.itemid = b.itemid AND a.location = b.location
WHERE a.sum_qty > ISNULL(b.sum_qty, 0)
)
SELECT
itemid,
location,
dates,
CASE
WHEN row_num = 1 THEN sum_qty - sum_qty2
ELSE qty
END AS qtybal
FROM cte3
ORDER BY itemid, location, dates;
```
## 四、执行结果分析
运行上述查询后，结果集如下：
| itemid | location | dates | qtybal |
|--------|----------|------------|--------|
| 1 | 10GEI | 2009-08-01 | 3 |
| 1 | 10GEI | 2009-09-01 | 10 |
| 1 | 10GEI_2 | 2009-08-01 | 3 |
| 1 | 10GEI_2 | 2009-09-01 | 10 |
| 2 | 10GEI | 2009-08-01 | 3 |
| 2 | 10GEI | 2009-09-01 | 10 |
该结果完全符合预期。以物品1在仓库10GEI的数据为例：
- **2009-06-01入库10件**，2009-07-01销售5件，2009-08-01再销售5件，该批次库存全部消耗完毕；
- **2009-08-01调拨入库5件**，2009-09-01销售2件，剩余3件；
- **2009-09-01收货入库10件**，未有出库，完整保留10件。
这一结果准确反映了各批次库存的剩余数量及其对应的入库日期，能够直接用于库龄分析。
## 五、延伸思考与最佳实践
### 5.1 性能优化建议
在实际生产环境中，库存表可能包含数百万条记录。为提升查询性能，建议：
- 在`itemid`、`location`、`dates`字段上建立复合索引；
- 考虑定期物化汇总结果，将出库总量预先计算并存储；
- 如果数据量极大，可考虑使用临时表分步替代CTE进行缓存。
### 5.2 边界条件处理
实际业务中还需考虑以下复杂情况：
- **跨仓库调拨**：调拨单据同时产生出库和入库记录，需确保数据录入无误；
- **负数库存**：某些系统允许负库存过账，此时算法需要额外调整；
- **多币种多计量单位**：不同计量单位间的转换需要在汇总前完成。
### 5.3 Set-Based与Cursor方案的权衡
虽然本文采用基于集合的窗口函数方案，体现了SQL Server 2012的性能优势，但需注意：当批次数量极大且出库频繁时，简单游标方案在极端情况下可能表现更稳定。然而，窗口函数方案在绝大多数场景下都具有显著性能优势，且代码更加简洁优雅。
### 5.4 业务价值延伸
库存库龄计算不仅是财务核算的基础，更对企业经营具有重要指导意义。通过定期分析库龄分布：
- **滞销品识别**：库龄超过90天的库存需重点关注，及时制定促销或退货策略；
- **采购计划优化**：基于各批次库存的消耗速度，精准预测未来采购需求；
- **资金占用分析**：不同库龄的库存对应不同的资金占用成本，支持财务决策。
## 六、总结
本文详细探讨了使用SQL Server窗口函数计算库存库龄的完整解决方案。通过四个层次递进的CTE，实现了FIFO原则下的批次匹配与结存计算。该方案不仅适用于原始问题中的场景，还可灵活扩展至多物品、多仓库的复杂业务环境，具有较高的实用价值和推广意义。掌握这一技术，能够让数据库开发人员更好地应对库存管理中的核心挑战。