SQL库存分配优先逻辑问题处理

场景：计算出SKU当前可用库存的实际库龄
难点：实际情况，当前库存，不会是同一批入库的，不同批次入库

处理方法：

1. 按照入库记录先进先出逻辑，入库早的先出库；
2. 取SKU最近入库的记录作为当前库存的入库时间，再分别计算库龄。

当然，其他类似的先进先出，先进后出的分配问题均可用类似的逻辑处理。

比如：产品XB000001当前可用库存为217，按照出入库的先进先出逻辑，应该取最近一次入库的时间作为入库时间，不足库存的向上取，直至取完，因此217的库存应该是有165是5/19入库的，52个是4/21入库的。

DROP TABLE IF EXISTS #temp_a,#temp_b,#temp_c,#temp_d

/*
场景：计算出SKU当前可用库存的实际库龄
难点：实际情况，当前库存，不会是同一批入库的，不同批次入库
处理方法：
	1、按照入库记录先进先出逻辑，入库早的先出库；
	2、取SKU最近入库的记录作为当前库存的入库时间，再分别计算库龄。
*/


-- 当前可用库存数
SELECT
	a.当前库存数,
	a.SKU
INTO #temp_a
FROM(
	SELECT 'XB000001' SKU, 217 当前库存数 union all
	SELECT 'XB000002' SKU, 29  当前库存数
) a


-- 入库记录
SELECT
	b.入库数,
	CAST(b.入库时间 AS DATETIME) 入库时间,
	b.SKU
INTO #temp_b
FROM(
	SELECT 'XB000001' SKU, 165 入库数,'2022-05-19 12:00:42' 入库时间 union all
	SELECT 'XB000001' SKU, 100 入库数,'2022-04-21 12:00:37' 入库时间 union all
	SELECT 'XB000001' SKU, 20  入库数,'2022-03-04 18:00:31' 入库时间 union all
	SELECT 'XB000001' SKU, 250 入库数,'2020-05-20 18:41:53' 入库时间 union all
	SELECT 'XB000001' SKU, 50  入库数,'2020-03-24 06:02:15' 入库时间 union all
	SELECT 'XB000002' SKU, 39  入库数,'2022-05-10 12:00:32' 入库时间 union all
	SELECT 'XB000002' SKU, 50  入库数,'2021-03-12 06:01:29' 入库时间  
) b
ORDER BY b.入库时间 DESC

SELECT * FROM #temp_a
SELECT * FROM #temp_b


SELECT
	b.入库数,
	b.入库时间,
	b.SKU,
	a.当前库存数,

	-- 按照入库时间降序，计算每个SKU的累计入库数
	SUM(b.入库数) OVER(PARTITION BY a.SKU ORDER BY b.入库时间 DESC) [累计入库数],	

	-- 按照SKU的入库时间降序排序
	ROW_NUMBER() OVER(PARTITION BY a.SKU ORDER BY b.入库时间 DESC) [rank],	
	CASE
		WHEN a.当前库存数 >= SUM(b.入库数) OVER(PARTITION BY a.SKU ORDER BY b.入库时间 DESC)
		THEN 1
		ELSE 0	
	END [分配库存]
INTO #temp_c
FROM #temp_a a
INNER JOIN #temp_b b ON b.SKU = a.SKU


-- 剔除多余不需要分配的记录
SELECT
	a.入库数,
    a.入库时间,
    a.SKU,
    a.当前库存数,
    a.累计入库数,
    a.rank,
    a.分配库存,
    CASE
		WHEN a.分配库存=1 THEN a.累计入库数
		ELSE 0
	END [累计分配]

INTO #temp_d
FROM #temp_c a
LEFT JOIN(
	SELECT SKU,MIN(rank) rank FROM #temp_c WHERE 分配库存=0 GROUP BY SKU
) b ON b.SKU = a.SKU AND b.rank = a.rank

WHERE a.分配库存 = 1 OR b.rank IS NOT NULL
ORDER BY a.入库时间 DESC


--重新计算分配，计算库龄
SELECT
	a.入库数,
    a.入库时间,
    a.SKU,
    a.当前库存数 当前总库存数,
	CASE
		WHEN a.分配库存=1 THEN a.入库数
		WHEN a.当前库存数 > SUM(a.累计分配) OVER(PARTITION BY a.SKU ORDER BY a.rank ASC) THEN a.当前库存数 - SUM(a.累计分配) OVER(PARTITION BY a.SKU ORDER BY a.rank ASC)
		ELSE 0
	END 当前库存数,
	DATEDIFF(DAY, a.入库时间, GETDATE()) [当前库存-库龄]

FROM #temp_d a