SQL最近X条记录的总和

Question

我已经寻找一段时间了，但是找不到这个问题的答案（也许我没有搜索正确的术语）。基本上，我有一个数据库，每天可能会有任意数量的条目。我需要对包含条目的最后X天求和（忽略没有条目的日期）。我知道如何找到每天的总和。

假设我有一个名为"Yield"的表：

UnitID  Date        TestTime    NumMeasured NumPassing
1       2013-04-05  10:15       25          3
2       2013-03-31  10:12       30          1
3       2013-03-30  10:12       26          2
4       2013-03-29  10:30       11          1
5       2013-03-31  10:20       6           2
6       2013-04-05  10:30       5           0

我随后使用以下查询：

SELECT DISTINCT 
    Date, 
    (
        SELECT SUM([NumMeasured]) 
        FROM [dbo].[Yield] T1 
        WHERE T1.Date = T2.Date
    ) AS 'NumMeasured', 
    (
        SELECT SUM([NumPassing]) 
        FROM [dbo].[Yield] T1 
        WHERE T1.Date = T2.Date
    ) AS 'NumPassing' 
FROM [dbo].[Yield] T2 

获取每天的总通过/测量数：

Date        NumMeasured NumPassing
2013-03-29      11          1
2013-03-30      26          2
2013-03-31      36          3
2013-04-05      30          3

我需要一个查询语句，可以根据日期（比如说4/05）获取最近X天（比如说100天）内包含记录的NumMeasured和NumPassing列的总和。但是我无法让这部分代码正常运行，一直遇到SQL问题，而我没有足够的经验来解决。我需要像下面这样的代码：

SELECT
    (
        SELECT TOP 100 SUM(T3.[NumMeasured]) 
        FROM T3 
        WHERE T3.Date <= '4/05/2013' 
        ORDER BY T3.Date
    ) AS 'NumMeasured', 
    (
        SELECT TOP 100 SUM(T3.[NumPassing]) 
        FROM T3 
        WHERE T3.Date <= '4/05/2013' 
        ORDER BY T3.Date
    ) AS 'NumPassing', 
    (
        SELECT DISTINCT 
            Date, 
            (
                SELECT SUM([NumMeasured]) 
                FROM [PhaseNoiseMonitoring].[dbo].[Yield] T1 
                WHERE T1.Date = T2.Date
            ) AS 'NumMeasured', 
            (
                SELECT SUM([NumPassing]) 
                FROM [PhaseNoiseMonitoring].[dbo].[Yield] T1 
                WHERE T1.Date = T2.Date
            ) AS 'NumPassing' 
        FROM [PhaseNoiseMonitoring].[dbo].[Yield] T2 
    )
AS T3

预期的查询将返回：

NumMeasured NumPassing
103         9

我知道这样做是错误的，但我真的不知道该怎么让它工作。

- Matt Stone

---

除非我漏看了什么，否则在我看来，“GROUP BY” 子句可以极大地简化您的生活。 - hometoast

所以你需要最近100个日期的总和，而不一定是在100天内的日期总和？ - hometoast

Answer 1

这是一个使用相关子查询的想法：

with bydays as (<your query here>)
select bd.*,
       (select sum(NumMeasured) from (select top 100 * from bydays bd2 where bd2.date <= bd.date order by date desc) t
       ) as Measured100,
       (select sum(NumPassingd) from (select top 100 * from bydays bd2 where bd2.date <= bd.date order by date desc) t
       ) as Measured100
from bydays

- Gordon Linoff

---

谢谢，这正是我所需要的（除了几个错别字），感谢您的快速回复。我以前并没有真正使用过相关子查询，但这似乎比我之前做的要容易得多。 - Matt Stone

@Gordon-Linoff 当你需要跨多个值进行聚合时（例如，在Matt的上面的示例中按UnitID分组），该怎么办？ - CRSouser

Answer 2

获取你最近的100个日期：

SELECT TOP(100) DISTINCT date FROM yield ORDER BY date desc

你可以通过以下简单的方式简化你的第一个查询

SELECT date, SUM(passing) as numpassing, SUM(Measured) as nummeasured
FROM yield
GROUP BY date

如果您需要从现在到100天前的总和

SELECT SUM(passing) as npassing, SUM(measured) as nmeasured
FROM yield
WHERE date >= DATEADD(d,100,CURRENT)

如果你需要最近100个日期的总和

SELECT SUM(passing) as npassing, SUM(measured) as nmeasured
FROM yield
WHERE date IN (SELECT TOP 100 date FROM yield WHERE date <= '04/05/2013' ORDER BY date desc) 

- hometoast