代码之家 › 专栏 › 技术社区 › davids YuvShap

使用分区获得工作中心的运行平衡

sql-server-2014 tsql sql-server

davids YuvShap · 技术社区 · 8 年前

我正在编写一个将在SQL Server 2014上运行的脚本。

我有一个记录从一个工作中心到另一个工作中心的转移的交易表。简化表如下:

DECLARE @transactionTable TABLE (wono varchar(10),transferDate date
                      ,fromWC varchar(10),toWC varchar(10),qty float)

INSERT INTO @transactionTable
SELECT '0000000123','5/10/2018','STAG','PP-B',10
UNION
SELECT '0000000123','5/11/2018','PP-B','PP-T',5
UNION
SELECT '0000000123','5/11/2018','PP-T','TEST',3
UNION
SELECT '0000000123','5/12/2018','PP-B','PP-T',5
UNION
SELECT '0000000123','5/12/2018','PP-T','TEST',5
UNION
SELECT '0000000123','5/13/2018','PP-T','TEST',2
UNION
SELECT '0000000123','5/13/2018','TEST','FGI',8
UNION
SELECT '0000000123','5/14/2018','TEST','FGI',2

SELECT *, 
    fromTotal = -SUM(qty) OVER(PARTITION BY fromWC ORDER BY wono, transferdate, fromWC),
    toTotal = SUM(qty) OVER(PARTITION BY toWC ORDER BY wono, transferdate, toWC)
FROM @transactionTable
ORDER BY wono, transferDate, fromWC

我想在每次交易后获得FromWC和TowC的余额。

鉴于上述记录,最终结果应为:

我相信可以用 SUM(qty) OVER(PARTITION BY... 但是我不知道怎么写这个声明。当我试图得到增加和减少时,每行总是得到0。

我怎么写 SUM 实现预期结果的声明?

更新

此图显示每个事务、生成的wc数量,并突出显示相应的 from 和 to 每个交易的工作中心。

例如,查看5/11的第二个记录,3个从PP-T转移到测试。交易后,PP-B中有5个,PP-T中有2个,试验中有3个。

1 回复 | 直到 8 年前

Alex 8 年前

我可以接近,除了起始余额:

SELECT  wono, transferDate, fromWC, toWC, qty,
    SUM( CASE WHEN WC = fromWC THEN RunningTotal ELSE 0 END ) AS FromQTY,
    SUM( CASE WHEN WC = toWC THEN RunningTotal ELSE 0 END ) AS ToQTY
FROM( -- b
    SELECT *, SUM(Newqty) OVER(PARTITION BY WC ORDER BY wono,transferdate, fromWC, toWC) AS RunningTotal
    FROM(-- a
            SELECT wono, transferDate, fromWC, toWC, fromWC AS WC, qty, -qty AS Newqty, 'From' AS RecType
            FROM @transactionTable
            UNION ALL
            SELECT wono, transferDate, fromWC, toWC, toWC AS WC, qty, qty AS Newqty, 'To' AS RecType
            FROM @transactionTable
        ) AS a
    ) AS b
GROUP BY wono, transferDate, fromWC, toWC, qty

我的逻辑假设所有余额都从0开始,因此“stag”余额将为-10。

查询的工作方式:

“unpivot”输入记录,设置为“from”和“to”记录,数量为“from”记录的负数。
计算每个“wc”的运行总数。
将“未透视”记录合并回原始形状

解决方案2

WITH CTE
AS(
SELECT *,
    ROW_NUMBER() OVER( ORDER BY wono, transferDate, fromWC, toWC ) AS Sequence
FROM @transactionTable
 ),
 CTE2
 AS( 
 SELECT *,
    fromTotal = -SUM(qty) OVER(PARTITION BY fromWC ORDER BY Sequence),
    toTotal = SUM(qty) OVER(PARTITION BY toWC ORDER BY Sequence)
FROM CTE
 )
SELECT a.Sequence, b.Sequence, c.Sequence, a.wono, a.transferDate, a.fromWC, a.toWC, a.qty, a.fromTotal + ISNULL( b.toTotal, 0 ) AS FromTotal, a.toTotal + ISNULL( c.fromTotal, 0 ) AS ToTotal
FROM CTE2 AS a
    OUTER APPLY( SELECT TOP 1 * FROM CTE2 WHERE wono = a.wono AND Sequence < a.Sequence AND toWC = a.fromWC ORDER BY Sequence DESC ) AS b
    OUTER APPLY( SELECT TOP 1 * FROM CTE2 WHERE wono = a.wono AND Sequence < a.Sequence AND fromWC = a.toWC ORDER BY Sequence DESC ) AS c
ORDER BY a.Sequence

注: 此解决方案将从“id”列中获益匪浅,该列反映事务顺序,或者至少需要索引 wono, transferDate, fromWC, toWC