SQL Server语言 - 集基于瀑布

Question

我有一个表，它将数字余额存储在不同的桶中(桶1，桶2和桶3)。当金额在系统中发布时，金额必须分配到桶中。事务类型决定了它如何影响桶。类型1、2和3增加各自的桶(没有瀑布)，而类型0使用瀑布减少桶。这意味着类型0的量首先减少桶1，然后任何剩余的减少桶2，任何剩余的减少桶3。

我目前使用游标来应用金额，该游标循环遍历每个已发布的金额。因为是瀑布，所以应用的数量顺序是关键。显然，当处理成千上万的金额时，它非常慢。我想知道是否有一种基于集合的方法来做到这一点?

生成表和记录的代码: http://pastebin.com/XgKrKkbm

分配金额的游标如下:

DECLARE @Instrument int,
        @Type int,
        @Amount numeric(19,2),
        @NewAmount numeric(19,2),
        @Seq int,
        @Bucket1 numeric(19,2),
        @Bucket2 numeric(19,2),
        @Bucket3 numeric(19,2)
DECLARE Waterfall CURSOR Fast_Forward FOR 
        SELECT InstrumentID, TypeID, Amount, Sequence
        FROM Amount
        WHERE EffectiveDate between '2015-06-20' and '2015-06-30'
        ORDER BY InstrumentID, Sequence
OPEN Waterfall;
FETCH NEXT from Waterfall into @Instrument, @Type, @Amount, @Seq
WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @Bucket1 = Bucket1, @Bucket2 = Bucket2, @Bucket3 = Bucket3
    FROM Buckets WHERE InstrumentID = @Instrument
        IF @Type > 0 /*Increase Buckets, no waterfall*/
        BEGIN
            SET @Bucket1 = CASE WHEN @Type = 1 THEN @Bucket1 + @Amount ELSE @Bucket1 END
            SET @Bucket2 = CASE WHEN @Type = 2 THEN @Bucket2 + @Amount ELSE @Bucket2 END
            SET @Bucket3 = CASE WHEN @Type = 3 THEN @Bucket3 + @Amount ELSE @Bucket3 END
        END
        ELSE /*Decrease buckets with waterfall*/
        BEGIN
            SET @NewAmount  = CASE WHEN @Amount >= @Bucket1 THEN @Amount - @Bucket1 ELSE 0 END
            SET @Bucket1    = CASE WHEN @Amount >= @Bucket1 THEN 0 ELSE @Bucket1 - @Amount END
            SET @Amount     = @NewAmount
            SET @NewAmount  = CASE WHEN @Amount >= @Bucket2 THEN @Amount - @Bucket2 ELSE 0 END
            SET @Bucket2    = CASE WHEN @Amount >= @Bucket2 THEN 0 ELSE @Bucket2 - @Amount END
            SET @Amount     = @NewAmount
            SET @Bucket3    = CASE WHEN @Amount >= @Bucket3 THEN 0 ELSE @Bucket3 - @Amount END
        END
/*Record effect of each amount on the balances for audit/undo*/
    UPDATE  Amount
    SET     Bucket1 = @Bucket1 - Buckets.Bucket1, 
            Bucket2 = @Bucket2 - Buckets.Bucket2, 
            Bucket3 = @Bucket3 - Buckets.Bucket3
    FROM    Buckets 
        inner join Amount 
            on Amount.InstrumentID = Buckets.InstrumentID
    where Sequence = @Seq
/*update bucket values in table*/    
    UPDATE Buckets
        SET Bucket1 = @Bucket1,
            Bucket2 = @Bucket2,
            Bucket3 = @Bucket3
    WHERE InstrumentID = @Instrument
FETCH NEXT from Waterfall INTO @Instrument, @Type, @Amount, @Seq
END
CLOSE Waterfall
DEALLOCATE Waterfall

创建和插入脚本是在PasteBin，所以不会让我张贴问题时，他们被包括(文本长度?)。

---

关于瀑布序列的清晰度至关重要的例子:

从下面开始:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     500 |     200 |     3000

两笔金额如下

Instrument | Sequence|    Type |   Amount
1          |       1 |       0 |     800  /*this decreases buckets via waterfall*/
1          |       2 |       1 |     500  /*this increases bucket 1*/

如果金额按顺序施加，则结果如下:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     500 |       0 |     2900

然而，金额以相反的顺序应用它们，结果是不正确的:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     200 |     200 |     3000

Answer 1

这就是您想要的。比CURSOR更高效。

UPDATE
  b
SET
  Bucket1 = CASE WHEN a.[Type] = 1 THEN b.Bucket1 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 THEN 0
                 WHEN a.[Type] = 0 AND Amount <  b.Bucket1 THEN b.Bucket1 - a.Amount
                 ELSE Bucket1  
            END,
  Bucket2 = CASE WHEN a.[Type] = 2 THEN b.Bucket2 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 + Bucket2 THEN 0
                  WHEN a.[Type] = 0 AND Amount BETWEEN  b.Bucket1 AND Bucket2 THEN Bucket2 - (a.Amount - b.Bucket1)
                 ELSE Bucket2
            END,
  Bucket3 = CASE WHEN a.[Type] = 3 THEN b.Bucket3 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 + Bucket2 THEN Bucket3  - (a.Amount - b.Bucket1 -  Bucket2)
                 ELSE Bucket3
            END
FROM
  dbo.Buckets AS b
  JOIN 
  (SELECT TOP 10000000 * FROM dbo.Amounts ORDER BY Sequence) AS a
    ON b.InstrumentID = a.InstrumentID