T SQL Cursor 僅更新具有相同值的行

我正在使用 MS SQL Server，並且有下表“tmp_AVG_Weekly_Sales”：

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
 1     ABC   200    2       1          2        0.13     NULL
 2     DEF   250    2       2          4        0.018    NULL
 3     XYZ   300    3       3          6        0.0024   NULL

我需要根據以下邏輯計算出 Value 欄位 - 我正在使用游標和循環：

DECLARE @CUMULATIVE AS FLOAT = 0;
DECLARE @COUNT AS INT = 0;
DECLARE @LAMBDA AS FLOAT;
DECLARE @RowID AS INT;
DECLARE @PoissonCursor AS CURSOR;
DECLARE @THRESHOLD AS FLOAT = 0.99;
DECLARE @PMF AS FLOAT --= EXP(-@LAMBDA)


SET @PoissonCursor = CURSOR FOR

SELECT RowID
FROM 
[tmp_AVG_Weekly_Sales]

OPEN @PoissonCursor;

FETCH NEXT FROM @PoissonCursor INTO @RowID;

WHILE @@FETCH_STATUS = 0

BEGIN

   SELECT @LAMBDA = LAMBDA FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID

   SELECT @PMF = PMF FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID

   WHILE (@CUMULATIVE < @Threshold)

   BEGIN
       SET @CUMULATIVE += @PMF
       SET @COUNT += 1
       SET @PMF = @PMF * (@LAMBDA / @COUNT)

       END

       UPDATE [tmp_AVG_Weekly_Sales] SET [Value] = @COUNT - 1 WHERE RowID = @RowID

   FETCH NEXT FROM @PoissonCursor  INTO @RowID;

END

但是，上面只是用相同的值填充 Value 欄位：

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
 1     ABC   200    2       1          2        0.13     6
 2     DEF   250    2       2          4        0.018    6
 3     XYZ   300    3       3          6        0.0024   6

當我期待以下內容時：

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
 1     ABC   200    2       1          2        0.13     6
 2     DEF   250    2       2          4        0.018    9
 3     XYZ   300    3       3          6        0.0024   12

我哪裡錯了？

問題是您僅設置@CUMULATIVE為0第一行而不是下一行，因此一旦超過門檻值，所有後續行都滿足條件。

@COUNT也應該重置，不確定那裡的邏輯。

您應該在UPDATE和之後FETCH NEXT或在 internal 之前執行此操作（重置為 0） WHILE：

BEGIN

   SELECT @LAMBDA = LAMBDA FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID;

   SELECT @PMF = PMF FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID;

   -- setting cumulative to 0
   SET @CUMULATIVE = 0.0;

   WHILE (@CUMULATIVE < @Threshold)

   BEGIN 
       ...
   END;

   UPDATE [tmp_AVG_Weekly_Sales] SET [Value] = @COUNT - 1 WHERE RowID = @RowID;

   FETCH NEXT FROM @PoissonCursor  INTO @RowID;

   -- or here
   SET @CUMULATIVE = 0.0;

END;

引用自：https://dba.stackexchange.com/questions/239203