在我的表的范围内计数发生 [英] Counting occurences within a range of my table

问题描述

当我尝试对特定时间范围内的发生次数求和时，我有以下问题。

I have the following question when I try to sum the number of occurrences within an specific time frame.

我有以下表格：

     Date                New
     2013-01-01           1
     2013-01-01           0
     2013-01-01           0                                  
     2013-01-01           1
     2013-01-02           1
     2013-01-02           0
     2013-01-03           1
     2013-01-03           1
     2013-01-04           0
     2013-01-04           1
     2013-01-05           1

我想计算每两天发生的新数量。

Where I want to count the number of 'New' that occur every two days.

在上面提供的示例中，结果我会期望得到如下：

In the example provided above, the result I'd be expecting to get would be as follows:

     Date                 Result
     2013-01-01/02           3
     2013-01-02/03           3
     2013-01-03/04           3
     2013-01-04/05           2

您可以看到我在计算每两天发生的次数。

Where you can see that I am counting the number of occurrences that happen every two days.

请注意我正在使用一个大表（> 10m线），我需要> 50个不同的范围（考虑每组2天作为范围）。

Please note that I am working with a big table (> 10m lines) and I would need > 50 different ranges to be produced (considering each grouping of 2 days as a range).

我正在使用SQL Server 2012。

I am working with SQL Server 2012.

感谢您的帮助！

推荐答案

Gordon Linoff的答案是更好的SQL 2012，但我想我会采取一个替代品，将在以前的版本中工作，因为这是一个刺一个有趣的问题。

Gordon Linoff's answer is better for SQL 2012, but I thought I'd take a stab at an alternative that would work in previous versions because this is an interesting problem.

SELECT #table1.[Date], (SUM(new) + t.cnt2) AS cnt
FROM #table1
INNER JOIN (SELECT [Date], SUM(new) AS cnt2 
           from #table1 
           GROUP BY #table1.[date]) t ON #table1.[date] = t.[date]-1
GROUP BY #table1.[date], t.cnt2

这篇关于在我的表的范围内计数发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！