运行总计 按月、年分组的数据集

Question

我正在尝试使用 OVER（ PARTITION BY ） 创建一个运行总计来创建运行总计。

我的原始查询：

SELECT DATEPART(MONTH, t.received_date) AS [Month],
DATEPART(YEAR, t.received_date) AS [Year],
SUM(rdai.number_of_pages) AS [Count]
FROM dbo.request_document_additonal_information AS [rdai]
INNER JOIN #TempRequestIDs AS [t]
    ON rdai.request_id = t.id
GROUP BY DATEPART(MONTH, t.received_date),
DATEPART(YEAR, t.received_date)
ORDER BY Year,
Month;

结果是：

Month  Year  Count
10     2015  1202342
11     2015  1059471
12     2015  1142629
1      2016  1081412
2      2016  1181385
3      2016  1334966

我

的目标是为每个月创建一个运行的小计，我尝试这样做：

SELECT DATEPART(MONTH, t.received_date) AS [Month],
DATEPART(YEAR, t.received_date) AS [Year],
SUM(rdai.number_of_pages) AS [Count]
,SUM(rdai.number_of_pages) OVER (PARTITION BY DATEPART(MONTH, t.received_date), DATEPART(YEAR, t.received_date)
                                ORDER BY DATEPART(MONTH, t.received_date), DATEPART(YEAR, t.received_date)
                                RANGE UNBOUNDED PRECEDING
                               ) as [RunningTotal]
FROM dbo.request_document_additonal_information AS [rdai]
INNER JOIN #TempRequestIDs AS [t]
    ON rdai.request_id = t.id
GROUP BY DATEPART(MONTH, t.received_date),
DATEPART(YEAR, t.received_date)
ORDER BY Year,
Month;

但返回的错误状态为：

Column 'dbo.request_document_additonal_information.number_of_pages' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

如果我加上GROUP BY .... rdai.number_of_pages，则列出了运行总计，但所有列的数字都相同。

我可以在这个窗口函数中获得一些关于我出错的地方的帮助吗？

谢谢

Answer 1

一种选择是嵌套原始查询

Select A.*
      ,RunningTotal = sum(count) over (Order by Year ,Month)
 From (
        SELECT DATEPART(MONTH, t.received_date) AS [Month],
        DATEPART(YEAR, t.received_date) AS [Year],
        SUM(rdai.number_of_pages) AS [Count]
        FROM dbo.request_document_additonal_information AS [rdai]
        INNER JOIN #TempRequestIDs AS [t]
            ON rdai.request_id = t.id
        GROUP BY DATEPART(MONTH, t.received_date),
        DATEPART(YEAR, t.received_date)
      ) A
ORDER BY Year,Month;