主页 / dba / 问题 / 178152
Accepted
Ryan
Asked: 2017-07-07 03:39:38 +0800 CST

在 SQL Server 中为滚动总和设置非负下限

  • 772

我需要为滚动和计算设置一个下限。例如，与

PKID    NumValue    GroupID
----------------------------
1       -1          1
2       -2          1
3       5           1
4       -7          1
5       1           2

我想拥有：

PKID   RollingSum    GroupID
----------------------------- ## Explanation: 
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

当添加负数将导致和为负时，将激活限制以将结果设置为零。后续的加法应该基于这个调整后的值，而不是原来的滚动和。

使用加法应该达到预期的结果。如果第四个数字从 -7 变为 -3，则第四个结果应该是 2 而不是 0

如果可以提供一个总和而不是几个滚动数字，那也是可以接受的。我可以使用存储过程来实现非负加法，但这太低级了。

现实生活中的问题是我们将下订单记录为正数，将取消记录为负数。由于连接问题，客户可能会多次单击该cancel按钮，这将导致记录多个负值。在计算我们的收入时，“零”需要作为销售的边界。

这个业务应用程序绝对是愚蠢的，但我对此无能为力。对于这个问题，请仅考虑 DBA 可以使用的解决方案。

我预计GroupID最多每行五十行。

sql-server sql-server-2008

2 个回答

  1. Best Answer

    这是我想出的递归 CTE 示例（似乎可行）。Is 使用 Row_Number() OVER 创建一个没有间隙的序列号。我不知道它对您的数据的执行情况如何，但可以尝试一下。

    --Set up demo data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    drop table #temp
go
create table #temp (PKID int, NumValue int, GroupID int)
insert into #temp values
(1,-1,1), (3,-2,1), (5,5,1), (7,-3,1), (9,1,2)

--here is the real code
;
with RowNumberAddedToTemp as 
(
SELECT 
  ROW_NUMBER() OVER(ORDER BY PKID ASC) AS rn,
  * from #temp
  )
,x
AS (
    SELECT PKID         --Anchor row
        ,NumValue
        ,RunningTotal = CASE 
            WHEN NumValue < 0   --if initial value less than zero, make zero
                THEN 0
            ELSE NumValue
            END
        ,GroupID
        ,rn
    FROM RowNumberAddedToTemp
    WHERE rn = 1      

    UNION ALL

    SELECT y.PKID
        ,y.NumValue
        ,CASE 
            WHEN x.GroupID <> y.groupid     --did GroupId change?
                THEN CASE 
                        WHEN y.NumValue < 0     --if value is less than zero, make zero
                            THEN 0
                        ELSE y.numvalue         --start new groupid totals
                        END
            WHEN x.RunningTotal + y.NumValue < 0    --If adding the current row makes the total < 0, make zero
                THEN 0
            ELSE x.RunningTotal + y.NumValue        --Add to the running total for the current groupid
            END
        ,y.Groupid
        ,y.rn
    FROM x
    INNER JOIN RowNumberAddedToTemp AS y ON y.rn = x.rn + 1
    )
SELECT PKID
    ,Numvalue
    ,RunningTotal
    ,GroupID
FROM x
ORDER BY PKID
OPTION (MAXRECURSION 10000);
    • 3

  2. 下面是一个类似于 Scott Hodgin 的答案的递归解决方案，但它应该能够更好地利用索引。这将根据您的数据的外观提高性能。我用 20000 个组模拟了 100 万行数据。每个都有 50 行与之关联：

    DROP TABLE IF EXISTS biz_application_problems;
CREATE TABLE dbo.biz_application_problems (
    PKID BIGINT NOT NULL,
    NumValue INT NOT NULL,
    GroupID INT NOT NULL,
    PRIMARY KEY (PKID)
);

INSERT INTO dbo.biz_application_problems WITH (TABLOCK)
SELECT TOP (1000000) 
  2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, CAST(CRYPT_GEN_RANDOM(1) AS INT) % 22 - 11
, 1 + (-1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) / 50
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE INDEX gotta_go_fast ON dbo.biz_application_problems (GroupID, PKID)
    INCLUDE (NumValue);

    我添加到表中的非聚集索引应该允许 CTE 的递归部分通过一次索引查找来获取下一行。这也意味着表中数据的顺序PK没有关系。这里的另一个技巧是用来ROW_NUMBER()有效地获取下一行。这是必要的，因为TOP不能在 CTE 的递归部分中使用。这是查询：

    WITH rec_cte AS (
    SELECT TOP 1
      GroupID
    , PKID
    , CASE WHEN NumValue < 0 THEN 0 ELSE NumValue END RunningSum
    , NumValue -- for validation purposes only
    FROM dbo.biz_application_problems
    ORDER BY GroupId, PKID

    UNION ALL

    SELECT t.GroupID
    , t.PKID
    , t.RunningSum
    , t.NumValue
    FROM 
    (
        SELECT 
          b.GroupID
        , b.PKID
        , CASE WHEN ca.RunningSum < 0 THEN 0 ELSE ca.RunningSum END RunningSum
        , b.NumValue
        , ROW_NUMBER() OVER (ORDER BY b.GroupId, b.PKID) rn
        FROM dbo.biz_application_problems b
        INNER JOIN rec_cte r ON
            (b.GroupID = r.GroupID AND b.PKID > r.PKID) OR b.GroupID > r.GroupID
        CROSS APPLY (
            SELECT CASE WHEN b.GroupID <> r.GroupID THEN 0 ELSE r.RunningSum END + b.NumValue
        ) ca (RunningSum)
    ) t
    WHERE t.rn = 1
)
SELECT *
FROM rec_cte
OPTION (MAXRECURSION 0);

    以下是结果示例：

    ╔═════════╦══════╦════════════╦══════════╗
║ GroupID ║ PKID ║ RunningSum ║ NumValue ║
╠═════════╬══════╬════════════╬══════════╣
║       7 ║  700 ║         13 ║       -4 ║
║       8 ║  702 ║          0 ║       -2 ║
║       8 ║  704 ║          7 ║        7 ║
║       8 ║  706 ║          8 ║        1 ║
║       8 ║  708 ║          3 ║       -5 ║
║       8 ║  710 ║          0 ║       -4 ║
║       8 ║  712 ║          0 ║       -7 ║
║       8 ║  714 ║          7 ║        7 ║
║       8 ║  716 ║          2 ║       -5 ║
║       8 ║  718 ║         10 ║        8 ║
║       8 ║  720 ║          0 ║      -11 ║
║       8 ║  722 ║          0 ║       -7 ║
╚═════════╩══════╩════════════╩══════════╝

    在我的机器上，处理一百万行的代码大约需要 10 秒。我们可以看到每次迭代只返回一行的索引搜索：

    寻求

    我也在这里上传了实际计划。

    • 1

相关问题