SQL 2008 累积计数

Question

我有一个查询返回按日期分组的行数：

SELECT  convert(date, run.TimeStamp) as TimeStamp, count(*)
FROM  ScriptResult AS res INNER JOIN
    ScriptRun AS run ON run.ScriptRunID = res.ScriptRunID INNER JOIN
    WorkListItems AS wli ON wli.WorkListItemID = res.WorklistItemID INNER JOIN
    WorkList AS wl ON wl.WorkListID = wli.WorkListID
WHERE (wli.WorkListID = @WLID)
GROUP by convert(date, run.TimeStamp)
ORDER BY convert(date, run.TimeStamp);

这将生成如下所示的结果集：

TimeStamp   (ItemCount)
2015-03-10  5364
2015-03-11  22027
2015-03-12  18037

现在我想要的是累积汇总项目计数，如下所示：

TimeStamp   ItemCount   TotalCount
2015-03-10  5364    5364
2015 -03-11 22027   27391
2015-03-12  18037   45428

查询需要与 2008R2 兼容。我玩过[计数...多。。划分依据]在几种变体中，但问题是窗口函数边界应该变化。而且我不能使用行或范围。

请问有什么想法吗？提前谢谢。

Answer 1

尝试使用相关的子查询：

;WITH cte as(
               SELECT  convert(date, run.TimeStamp) as TimeStamp, count(*) AS S
               FROM  ScriptResult AS res INNER JOIN
                     ScriptRun AS run ON run.ScriptRunID = res.ScriptRunID INNER JOIN
                     WorkListItems AS wli ON wli.WorkListItemID = res.WorklistItemID INNER JOIN
                     WorkList AS wl ON wl.WorkListID = wli.WorkListID
               WHERE (wli.WorkListID = @WLID)
               GROUP by convert(date, run.TimeStamp)
)
SELECT TimeStamp, 
       S, 
       (SELECT SUM(S) FROM cte t2 WHERE t2.TimeStamp <= t1.TimeStamp) AS TS  
FROM cte  t1

Answer 2

您可以尝试创建一个临时表来保存第一个查询结果，您可以进一步聚合这些结果以返回 ItemCount 字段上的累积总和：

CREATE TABLE #TempTable(
    [SeqNo] [int] NULL,
    [TimeStamp] [Date] NULL,
    [ItemCount] [int] NULL     
) ON [PRIMARY]
SELECT
    ROW_NUMBER() OVER (PARTITION BY res.ScriptRunID ORDER BY run.TimeStamp) AS SeqNo,
    CONVERT(Date, run.TimeStamp) AS TimeStamp, 
    COUNT(*) AS ItemCount
INTO #TempTable
FROM ScriptResult AS res 
INNER JOIN ScriptRun AS run 
    ON run.ScriptRunID = res.ScriptRunID 
INNER JOIN WorkListItems AS wli 
    ON wli.WorkListItemID = res.WorklistItemID 
INNER JOIN  WorkList AS wl 
    ON wl.WorkListID = wli.WorkListID
WHERE (wli.WorkListID = @WLID)
GROUP BY CONVERT(Date, run.TimeStamp)
ORDER BY CONVERT(Date, run.TimeStamp);
SELECT 
     t1.TimeStamp, 
     t1.ItemCount, 
     SUM(t2.ItemCount) AS TotalCount
FROM #TempTable AS t1
INNER JOIN #TempTable AS t2 
    on t1.SeqNo >= t2.SeqNo 
GROUP BY t1.TimeStamp, t1.ItemCount
ORDER BY t1.TimeStamp

SQL 小提琴示例注意：这链接到Microsoft SQL Server 2014数据库版本的SQL fiddle，它也应该与SQL Server 2008一起使用。