SQL: Sum in partition with condition

Question

我正在尝试理解如何在表格上使用条件进行部分求和。

我有一张桌子：

CREATE TABLE [dbo].[test](
    [Id] [int] NOT NULL,
    [part] [varchar](50) NULL,
    [value] [int] NULL,
    [TimeValue] [int] NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (1, N'part1', 50, 15)
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (2, N'part1', 8, 12)
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (3, N'part1', 12, 9)
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (4, N'part1', 10, 20)
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (5, N'part2', 6, 4)
GO
INSERT [dbo].[test] ([Id], [part], [value], [TimeValue]) VALUES (6, N'part2', 9, 15)
GO

所以

Id  part    value   TimeValue
1   part1    50       15
2   part1    8        12
3   part1    12       9
4   part1    10       20
5   part2    6        4
6   part2    9        15

并且我应该编写一个select语句，用TimeValue AS INT <= of current row TimeValue上的part AS VARCHAR(50)列的分区总和添加一个pSum AS INT列

我写了以下内容：

SELECT *, SUM(value) OVER (PARTITION BY part) AS pSum FROM test

自然而然地：

Id  part    value   TimeValue   pSum
1   part1   50          15       80
2   part1   8           12       80
3   part1   12          9        80
4   part1   10          20       80
5   part2   6           4        15
6   part2   9           15       15

但这是对整个分区求和，我必须考虑TimeValue条件才能得到以下结果：

Id  part    value   TimeValue   pSum
1   part1   50      15          70      (sum in part1 where TimeValue <= 15 : 12 + 8 + 50)
2   part1   8       12          20      (sum in part1 where TimeValue <= 12 : 12 + 8)
3   part1   12      9           12      (sum in part1 where TimeValue <= 9 : just 12)
4   part1   10      20          80      (sum in part1 where TimeValue <= 20 : 12 + 8 + 50)
5   part2   6       4           6       (sum in part2 where TimeValue <= 4 : just 6)
6   part2   9       15          15      (sum in part2 where TimeValue <= 15 : 9 + 6)

我已经阅读了一个针对SQL2012的可能的解决方案a solution here

SUM(value) OVER (PARTITION BY part ORDER BY TimeValue rows between unbounded preceding and current row ) AS pSum

但我使用的是SQL2008

请帮帮忙。提前谢谢。

Answer 1

在SQL Server2008中，没有真正有效的方法来做到这一点。一种方法是使用outer apply

select t.*, v.cumesum
from test t outer apply
     (select sum(t2.value)
      from test t2
      where t2.part = t.part and t2.timevalue <= t.timevalue
     ) v(cumesum);