计算运行总计时出错(在以前的期间累积) [英] Error when calculating a running total (cumulative over the previous periods)

问题描述

我有一个表，我们称它为 My_Table，它有一个 Created 日期时间列(在 SQL Server 中)，我试图提取一份报告，显示历史上如何许多行是按月在特定时间My_Table.现在我知道我可以显示每个月添加了多少:

I have a table, let's call it My_Table that has a Created datetime column (in SQL Server) that I'm trying to pull a report that shows historically how many rows were to My_Table by month over a particular time. Now I know that I can show how many were added each month with:

SELECT YEAR(MT.Created), MONTH(MT.Created), COUNT(*) AS [Total Added]
FROM My_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)

这会返回如下内容:

YEAR    MONTH     Total Added
-----------------------------
2009    01        25
2009    02        127
2009    03        241

但是，我想获得给定时间段内的 total 列表大小(随意调用它；运行总计、累积总和、历史报告):

However, I want to get the total list size over the given period (call it what you will; a running total, a cumulative sum, a historical report):

   YEAR    MONTH     Total Size
   -----------------------------
-- 2008    12        325
   2009    01        350
   2009    02        477
   2009    03        718

我正在尝试这个:

SELECT YEAR(MT.Created)
    , MONTH(MT.Created)
    ,(
    SELECT COUNT(*) FROM My_Table MT_int
    WHERE MT_int.Created BETWEEN 
        CAST('2009/01/01' AS datetime)
        AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MT.Created)+1,0))
        -- the last day of the current month
        -- (Additional conditions can go here)
    ) AS [Total added this month]
FROM My_Table MT
WHERE MT.Created > CAST('2009/01/01' AS datetime)
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
ORDER BY YEAR(MT.Created), MONTH(MT.Created)

但是，SQL Server 响应此错误:

However, SQL Server is responding with this error:

Msg 8120, Level 16, State 1, Line 1
Column 'My_Table .Created' is invalid in the select list because 
it is not contained in either an aggregate function or the GROUP BY clause.

我只是知道我错过了一些明显的东西，但是在走开并回来盯着它看了一会儿之后，我不知所措.因此，如果有人能指出我在世界中缺少什么(或指出更好的方法)，我将永远感激不尽.

I just know I'm missing something obvious, but after walking away and coming back and staring at it for a while I'm at a loss. So if someone would be so kind as to point out what on earth I'm missing here (or point me at a better way of doing it) I'd be eternally grateful.

推荐答案

运行"意味着逐行.因此，一种方法是将前几个月相加并将其添加到当前月份.为了处理年份界限，您还需要每组的最小/最大日期.CROSS APPLY 略带 RBAR，但清楚(呃?)发生了什么.

"Running" implies row by row. So one way is to sum previous months and add it to current month. To deal with year boundaries, you also take min/max date per group. The CROSS APPLY is slightly RBAR but makes it clear(er?) what is happening.

;WITH cTE AS
(
SELECT
     MIN(Created) AS FirstPerGroup,
     MAX(Created) AS LastPerGroup,
     YEAR(MT.Created) AS yr, MONTH(MT.Created) AS mth, COUNT(*) AS [Monthly Total Added]
FROM MY_Table MT
GROUP BY YEAR(MT.Created), MONTH(MT.Created)
)
SELECT
   C1.yr, c1.mth, SUM(C1.[Monthly Total Added]),
   ISNULL(PreviousTotal, 0) + SUM(C1.[Monthly Total Added]) AS RunningTotal
FROM
 cTE c1
 CROSS APPLY
 (SELECT SUM([Monthly Total Added]) AS PreviousTotal FROM cTE c2 WHERE c2.LastPerGroup < C1.FirstPerGroup) foo
GROUP BY
  C1.yr, c1.mth, PreviousTotal
ORDER BY
   C1.yr, c1.mth

这篇关于计算运行总计时出错(在以前的期间累积)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！