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

问题描述

我试图在 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') typerow的不同时间戳。

考虑到@table 按 TimeStamp 升序排序，我需要找到一种方法来获取一个表，其中typerow = 'in'在val列中包含其当前值的每一行加上val字段 where中所有先前整数的总和typerow = '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 where更新val列。我不知道如何正确创建新的 id 列以及这是否是一个好的解决方案，同时考虑我的 SQL Server 版本。typerow = 'in'

提前感谢您的支持，任何建议表示赞赏。

标签： sqlsql-serversql-server-2008sumgaps-and-islands

这是一个间隙和岛屿问题，其中每个岛屿都以“in”记录结尾，并且您希望对每个岛屿中的值求和。

这是一种使用以下“in”计数来定义组的方法，然后是每个组的窗口总和。

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 上的演示：

时间戳 | 值 | 打字机
:------------------------ | --: | :------
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 | 出去

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

问题描述

解决方案

推荐阅读