sql-server - 有没有办法使用窗口函数来保持最小总数为 0 而不必循环？

Question

我试图避免使用循环来计算总计。这是一个简化版本；真实版本包括几个类别的小计。我知道我可以使用窗口函数 [@Xata2] 来做到这一点，但如果将总数限制为非负数(即，如果总数为负数，则使用 0)，我想不出一种非循环的方法来做到这一点相反)[@Xata3]，因为我放置的任何条件都在当前行，而不是累计总数。

    DECLARE   @Xata TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        )

    DECLARE   @Xata2 TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        , total         INTEGER
        )

    DECLARE   @Xata3 TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        , total         INTEGER
        )

    DECLARE   @result   INTEGER
            , @total    INTEGER
            , @Counter  INTEGER

    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)

    -- @Xata2: WINDOWING FUNCTION

    INSERT INTO @Xata2
        SELECT    result
                , SUM(result) OVER (ORDER BY ID)
            FROM @Xata

    SELECT    ID
            , result
            , total AS total_x2_neg
        FROM @Xata2

    SET @Counter = 0

    WHILE @Counter < (SELECT MAX(ID) FROM @Xata)
        BEGIN
            SET @Counter += 1

            SELECT @result = result FROM @Xata WHERE ID = @Counter
            SET @total = ISNULL((SELECT total FROM @Xata3 WHERE ID = @Counter - 1), 0)

            INSERT INTO @Xata3
                SELECT    @result
                        , IIF(@result + @total < 0, 0, @result + @total)

        END

    SELECT    ID
            , result
            , total AS total_x3_noneg
        FROM @Xata3

Answer 1

我的第一次尝试没有正确返回，谢谢@Menno 的提示。

使用递归 CTE 尝试这种方法

DECLARE   @Xata TABLE (ID INTEGER IDENTITY
                      ,result INTEGER);

INSERT INTO @Xata (result) VALUES(-3),(1),(2),(2),(0),(0),(-4),(-3),(2),(3);

WITH recCte AS
(
    SELECT ID
          ,result
          ,CASE WHEN result>0 THEN result ELSE 0 END AS runningTotalNoNeg 
    FROM @Xata 
    WHERE ID=1

    UNION ALL

    SELECT d.ID
          ,d.result
          ,CASE WHEN r.runningTotalNoNeg + d.result > 0 then r.runningTotalNoNeg + d.result ELSE 0 END
    FROM @Xata d
    INNER JOIN recCte r ON d.ID=r.ID+1
)
SELECT *
FROM recCte;

结果

ID  rt  runningTotalNoNeg
1  -3   0
2   1   1
3   2   3
4   2   5
5   0   5
6   0   5
7  -4   1
8  -3   0
9   2   2
10  3   5