在T-SQL中运行SUM

Question

不好意思，我不知道该怎么称呼它。

我有一个这样的表:

+-----++-----+
| Id  ||Count|
+-----++-----+
| 1   || 1   |
+-----++-----+
| 2   || 5   |
+-----++-----+
| 3   || 8   |
+-----++-----+
| 4   || 3   |
+-----++-----+
| 5   || 6   |
+-----++-----+
| 6   || 8   |
+-----++-----+
| 7   || 3   |
+-----++-----+
| 8   || 1   |
+-----++-----+

我试图从这个表中选择，每次row1 + row2 + row3(等)的SUM达到10，然后它是一个"HIT"，计数重新开始。

要求输出:

+-----++-----++-----+
| Id  ||Count|| HIT |
+-----++-----++-----+
| 1   || 1   ||  N  | Count = 1
+-----++-----++-----+
| 2   || 5   ||  N  | Count = 6
+-----++-----++-----+
| 3   || 8   ||  Y  | Count = 14 (over 10)
+-----++-----++-----+
| 4   || 3   ||  N  | Count = 3
+-----++-----++-----+
| 5   || 6   ||  N  | Count = 9
+-----++-----++-----+
| 6   || 8   ||  Y  | Count = 17 (over 10..)
+-----++-----++-----+
| 7   || 3   ||  N  | Count = 3
+-----++-----++-----+
| 8   || 1   ||  N  | Count = 4
+-----++-----++-----+

我如何做到这一点，并获得最佳性能?我不知道……

Answer 1

您不能使用窗口/分析函数这样做，因为断点事先不知道。有时，可以计算断点。然而，在这种情况下，断点依赖于前一个值的非线性函数(我想不出比"非线性"更好的词了)。现在)。也就是说，有时会加上"1"到先前的值对当前行的计算没有任何影响。有时它有很大的影响。这意味着计算必须从头开始并遍历数据。

对问题的一个小修改将可以使用这些函数来解决。相反，如果问题是将每个组的多余金额结转(而不是重新开始求和)，则可以使用累积求和(以及其他一些技巧)来解决问题。

递归查询(其他人已经提供了)或顺序操作是解决此问题的最佳方法。不幸的是，它没有一个基于集合的方法来求解它。

Answer 2

你可以使用递归查询

请注意下面的查询假设id值都是顺序的，否则，请使用ROW_NUMBER()创建一个新的id

WITH cte AS (
  SELECT id, [Count], [Count] AS total_count
  FROM Table1 
  WHERE id = 1
  UNION ALL
  SELECT t2.id,t2.[Count], CASE WHEN t1.total_count >= 10 THEN t2.[Count] ELSE t1.total_count + t2.[Count] END
  FROM Table1 t2
  INNER JOIN cte t1 
    ON t2.id = t1.id + 1
  )
SELECT *
FROM cte
ORDER BY id

SQL小提琴

Answer 3

我真的希望有人能告诉我们，如果有可能做到这一点使用直接窗口函数。这才是真正的挑战。

与此同时，下面是我将如何使用递归来实现它。这将处理序列中的间隙，并处理第一行已经是>= 10的边缘情况。

我还添加了maxrecursion提示来删除默认的递归限制。但老实说，我不知道它在处理大量数据时运行得如何。

with NumberedRows as (
  select Id, Cnt,
         row_number() over(order by id) as rn
    from CountTable
), RecursiveCTE as (
  select Id, Cnt, rn, 
         case when Cnt >= 10 then 0 else Cnt end as CumulativeSum,
         case when Cnt >= 10 then 'Y' else 'N' end as hit
    from NumberedRows
   where rn = 1
  union all
  select n.Id, n.Cnt, n.rn,
         case when (n.Cnt + r.CumulativeSum) >= 10 then 0 else n.Cnt + r.CumulativeSum end as CumulativeSum,
         case when (n.Cnt + r.CumulativeSum) >= 10 then 'Y' else 'N' end as hit
    from RecursiveCTE r
    join NumberedRows n
      on n.rn = r.rn + 1
)
select Id, Cnt, hit
from RecursiveCTE
order by Id
option (maxrecursion 0)

SQLFiddle演示

Answer 4

如何使用运行总数:

DECLARE @Data TABLE(
    Id INT
    ,SubTotal INT
)

INSERT INTO @Data
    VALUES(1, 5)
INSERT INTO @Data
    VALUES(2, 3)
INSERT INTO @Data
    VALUES(3, 4)
INSERT INTO @Data
    VALUES(4, 4)
INSERT INTO @Data
    VALUES(5, 7)
DECLARE @RunningTotal INT = 0
DECLARE @HitCount INT = 0    
SELECT  
        @RunningTotal = CASE WHEN @RunningTotal < 10 THEN @RunningTotal + SubTotal ELSE SubTotal END
        ,@HitCount = @HitCount + CASE WHEN @RunningTotal >= 10 THEN 1 ELSE 0 END
        FROM @Data ORDER BY Id
SELECT @HitCount -- Outputs 2

重新阅读问题后，我发现这不符合所需的输出-我将留下答案，因为它可能对寻找这种类型的问题的运行总体解决方案的示例的人有些用处，不需要每行标记为Y或n