使用SQL 2008的不同记录之间的部分和

Question

我正试图在SQL 2008中解决这个问题。我有一张这样的桌子：

DECLARE @table TABLE (
TimeStamp        DATETIME,
val              INT,
typerow          VARCHAR(3)
);
INSERT INTO @table(TimeStamp, val, typerow)
VALUES
('2018-06-03 13:30:00.000', 6, 'out'),
('2018-06-03 14:10:00.000', 8, 'out'),
('2018-06-03 14:30:00.000', 3, 'in'),
('2018-06-03 15:00:00.000', 9, 'out'),
('2018-06-03 15:30:00.000', 4, 'out'),
('2018-06-03 16:00:00.000', 2, 'out'),
('2018-06-03 17:05:00.000', 8, 'in'),
('2018-06-03 17:30:00.000', 0, 'out'),
('2018-06-03 18:15:00.000', 7, 'out'),
('2018-06-03 18:30:00.000', 1, 'in'),
('2018-06-03 19:00:00.000', 5, 'out')

此表包含不同的时间戳，具有相对值val和一个二进制列('in'/out'(类型行。

考虑到@table按TimeStamp升序排序，我需要找到一种方法来获得一个表，其中typerow = 'in'的每一行都包含在val列中，其当前值加上valtyperow = 'out'字段中所有先前整数的总和，直到上一个typerow = 'in'记录。自然，对于typerow = 'in'的第一条记录，总和将被扩展到@table 的第一个记录

2018-06-03 13:30:00.000    6      out
2018-06-03 14:10:00.000    8      out
2018-06-03 14:30:00.000    17     in  -- 6 + 8 + 3
2018-06-03 15:00:00.000    9      out
2018-06-03 15:30:00.000    4      out
2018-06-03 16:00:00.000    2      out
2018-06-03 17:05:00.000    23     in  -- 9 + 4 + 2 + 8
2018-06-03 17:30:00.000    0      out
2018-06-03 18:15:00.000    7      out
2018-06-03 18:30:00.000    8      in  -- 0 + 7 + 1
2018-06-03 19:00:00.000    5      out

考虑到@table将有数百条记录以这种方式生成，我的第一个想法是创建一个新的id列，并将相同的id与同一求和中涉及的所有记录相关联(也许可以通过递归CTE来实现这一点？(，以获得以下结果：

2018-06-03 13:30:00.000    6      out    1
2018-06-03 14:10:00.000    8      out    1
2018-06-03 14:30:00.000    17     in     1
2018-06-03 15:00:00.000    9      out    2
2018-06-03 15:30:00.000    4      out    2
2018-06-03 16:00:00.000    2      out    2
2018-06-03 17:05:00.000    23     in     2
2018-06-03 17:30:00.000    0      out    3
2018-06-03 18:15:00.000    7      out    3
2018-06-03 18:30:00.000    8      in     3
2018-06-03 19:00:00.000    5      out    don't care for this element

并有一个像这样的新列

SELECT SUM(vals) OVER (PARTITION BY id ORDER BY id) AS partial_sum

用partial_sum更新val列，其中typerow = 'in'。我不知道如何正确创建新的id列，考虑到我的SQL Server版本，这是否是一个好的解决方案。

提前感谢您的支持，如有任何建议，我们将不胜感激。

Answer 1

这是一个缺口和孤岛问题，其中每个孤岛都以一个"在"；记录，并且您希望对每个岛中的值求和。

这里有一种方法，它使用下面的计数"；在"；s来定义组，然后对每个组进行窗口求和。

select timestamp,
case when val = 'out' 
then val
else sum(val) over(partition by grp order by timestamp)
end as val,
typerow
from (
select t.*,
sum(case when typerow = 'in' then 1 else 0 end) over(order by timestamp desc) grp
from @table t
) t
order by timestamp

DB Fiddle上的演示：

时间戳|val|typerow：------------------|--：|：------2018-06-03 13:30:00.000|6|出局2018-06-03 14:10:00.000|8|出局2018-06-03 14:30:00.000|17|英寸2018-06-03 15:00:00.000|9|出局2018-06-03 15:30:00.000|4|出局2018-06-03 16:00:00.000|2|出局2018-06-03 17:05:00.000|23|英寸2018-06-03 17:30:00.000|0|出局2018-06-03 18:15:00.000|7|出局2018-06-03 18:30:00.000|8|英寸2018-06-03 19:00:00.000|5|out