时间戳

首页 > 解决方案 > 逐行计算查询运行总计

sql - 逐行计算查询运行总计

问题描述

我有一个问题，工作在一周开始时“到期”，每周都有一定数量的“空位”可用于完成任何未完成的工作。如果没有足够的插槽，则工作将滚动到下周。

我的初始表如下所示：

星期 插槽 到期的
23/8/2021 0 1
2021 年 8 月 30 日 2 3
2021 年 6 月 9 日 5 2
2021 年 13 月 9 日 1 4

我想在每周结束时保持“到期”工作的总数。每周到期的数量将添加到上周的运行总数中，然后将减去本周的插槽数量。如果有足够的插槽来完成所需的所有作业，则运行总数将为 0（从不为负）。

作为一个例子 - 下面显示了我将如何在 javascript 中实现这一点：

var Total = 0;
data.foreach(function(d){
    Total += d.Due;
    Total -= d.Slots;
    Total = Total > 0 ? Total : 0;
    d.Total = Total;
});

结果如下：

星期 插槽 到期的 全部的
23/8/2021 0 1 1
2021 年 8 月 30 日 2 3 2
2021 年 6 月 9 日 5 2 0
2021 年 13 月 9 日 1 4 3

我是否可以在 SQL 中实现这一点（特别是 SQL Server 2012）

我尝试过各种形式的sum(xxx) over (order by yyy)

我最近管理的是：

sum(Due) over (order by Week) - sum(Slots) over (order by Week) as Total

这提供了一个运行总计，但当有多余的插槽时，将提供一个负总计。

是用光标做到这一点的唯一方法吗？如果是这样 - 有什么建议吗？

谢谢。

标签： sqlsql-servertsqlcommon-table-expressionrecursive-query

解决方案

根据评论中的建议对我自己的问题的可能答案。

Thorsten Kettner 提出了一个递归查询：

with cte as (

select [Week], [Due], [Slots]
,case when Due > Slots then Due - Slots else 0 end as [Total]
from [Data]
where [Week] = (select top 1 [Week] from [Data])

union all

select  e.[Week], e.[Due], e.[Slots]
, case when cte.Total + e.Due - e.Slots > 0 then cte.Total + e.Due - e.Slots else 0 end as [Total]
from [Data] e
inner join cte on cte.[Week] = dateadd(day,-7,e.[Week])
)

select * from cte

OPTION (MAXRECURSION 200)

Thorsten - 这是你的建议吗？（如果您有任何改进，请作为答案发布，以便我接受！）

大概我必须确保将 MAXRECURSION 设置为高于我将要处理的行数？

我对加入有点紧张dateadd(day,-7,e.[Week])。我会更好地使用 Row_Number() 来获取之前的记录吗？我可能想使用几周以外的东西，或者可能缺少几周？

George Menoutis 提出了一个“while”查询，当我遇到这篇文章时，我正在寻找实现该查询的方法：https ://stackoverflow.com/a/35471328/1372848

这表明与一段时间相比，光标可能不会那么糟糕？

这是我想出的基于光标的版本：

SET NOCOUNT ON;
DECLARE @Week Date,
        @Due Int,
        @Slots Int,
        @Total Int = 0;

DECLARE @Output TABLE ([Week] Date NOT NULL, Due Int NOT NULL, Slots Int NOT NULL, Total Int);

DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR  SELECT [Week], Due, Slots
     FROM   [Data]
    ORDER BY [Week] ASC;

OPEN crs;

FETCH NEXT
FROM  crs
INTO  @Week, @Due, @Slots;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    Set @Total = @Total + @Due;
    Set @Total = @Total - @Slots;
    Set @Total = IIF(@Total > 0, @Total , 0)

    INSERT INTO @Output ([Week], [Due], [Slots], [Total])
    VALUES (@Week, @Due, @Slots, @Total);

    FETCH NEXT
    FROM  crs
    INTO  @Week, @Due, @Slots;
END;

CLOSE crs;
DEALLOCATE crs;

SELECT *
FROM   @Output;

这两个似乎都按预期工作。递归查询感觉更好（光标=坏等），但它是否设计为以这种方式使用（每个输入行都有递归，因此可能有非常多的递归？）

非常感谢大家的投入:-)

推荐阅读