查找发生次数最多的日期范围

Question

我有一个MySql表，如下所示：

Column1   Column2   Column3   DateTime
14         10         15      2015-01-01 21:45:00
0          0          0       2015-01-01 21:46:00
12         8          16      2015-01-015 21:46:30
13         7          15      2015-01-01 21:47:00
0          0          0       2015-01-01 21:48:10
.          .          .       .
.          .          .       .
.          .          .       .
// Many non-zero rows here
.          .          .       .

14         10         15      2015-01-02 20:04:00
0          0          0       2015-01-02 20:04:30
12         8          16      2015-01-02 20:04:40
0          0          0       2015-01-02 20:04:50
10         5          2       2015-01-02 20:04:55
0          0          0       2015-01-02 20:05:00
11         4          8       2015-01-02 20:05:05
0          0          0       2015-01-02 20:05:10
12         15         16      2015-01-02 20:05:30
.          .          .       .
.          .          .       .
.          .          .       .
// Many other rows here where zeros doesn't happen so often.

这代表了用户在给定时刻的互联网连接质量。全零行意味着连接被删除(请注意，在给定的行中，不可能只有一个列的值为零--它们要么全部为零，要么全部为非零)。这就意味着，从这个示例数据来看，这个用户最糟糕的时间段是从2015-01-02 20:04:30到2015-01-02 20:05:30，因为连接在一分钟内下降了四次。如何在C#中找到这段时间(或者mysql，如果不是太麻烦的话)？

顺便问一下，这有什么特别的名字吗？我在谷歌搜索解决方案时运气不佳，因为我发现的大多数问题都是关于试图找到最长的连列(只有连续发生的，而不是像我需要找到的最接近的事件)，或者类似的问题。

更新:今天我要和我的一位CS老师谈话，但我没能。我明天再和他谈。与此同时，一些朋友和我一直在考虑如何解决这个问题，我们已经找到了一些我们不确定它是否正确的解决方案(正如你所看到的，我们对数学/统计不是很在行)。我们考虑过这样做：

对于表中的每个全零列，我们将其与前面的最后一列和前面的所有列进行比较。如下所示：

Compare first all-zero row with last one; 
Compare first all-zero row with second last one;
 ... 
Compare first all-zero row with the second one. 
Do it all over again starting on the second all-zero row this time.

然后，我们得到这个用户连接的最坏的时间间隔，这是(Number of times the connection dropped in time interval T) divided by T具有更大价值的最坏的时间间隔。但是，正如我之前说过的，我们甚至不知道这是否会给我们带来正确的答案。而且，这似乎是相当昂贵的计算，现在我们有一个有几千行的数据库。

Answer 1

好吧，有更多的时间来考虑这个。在面向对象的伪代码中很容易想到这个问题，因为它基本上归结为在数组中找到一个最大值：

int timeInterval = 30 (or however many seconds you want)
Sort all rows in ascending date/time order
Row worstStartRow = rows[0]
int worstNumBadConnections = 0
For each row X
    If X is defined as a dropped connection
        int tempNumBadConnections = 0
        For every subsequent row Y
            If (Y.time - X.time) > timeInterval
                break
            Else if Y is defined as a dropped connection
                tempNumBadConnections++
        If tempNumBadConnections > worstNumBadConnections
            worstNumBadConnections = tempNumBadConnections
            worstStartRow = X
// worst time interval starts at worstStartRow.time,
// ends at worstStartRow.time + timeInterval

但当然，SQL并不能很好地进行逐行处理。为了避免这种情况，我们可以将表连接到自己，确保两个“表”中成对行的次数在一定范围内，并聚合输出。

假设我们有一个表Demo，如下所示：

Id  Zero  Time
0   0     '2007-12-31 11:11:11'
1   0     '2008-01-01 00:00:00'
2   0     '2008-01-01 00:00:30'
3   1     '2008-01-01 00:00:30'
4   0     '2008-01-01 00:00:31'
5   1     '2008-01-01 00:00:31'
6   0     '2008-01-01 00:00:32'
7   0     '2008-01-01 11:11:11'

对于每一行具有row.Zero = 0的行，我们希望找到零=0的所有行，而另一行的时间不超过第一行的时间N秒。因此，如果间隔为30秒，则查询可能如下所示：

SELECT a.Id, a.Time, b.Id, b.Time
FROM Demo a
INNER JOIN Demo b
  ON a.Zero = b.Zero
  AND a.Time <= b.Time
WHERE a.Zero = 0
  AND TIMESTAMPDIFF(SECOND, a.Time, b.Time) <= 30
ORDER BY a.Id, b.Time
;

这给出了一系列行，包括1)定义间隔开始的零行的Id，2)起始行的时间，3)该间隔中另一行的Id，4)另一行的时间：

Id  Time                            Id  Time
0   'December, 31 2007 11:11:11'    0   'December, 31 2007 11:11:11'
1   'January, 01 2008 00:00:00'     1   'January, 01 2008 00:00:00'
1   'January, 01 2008 00:00:00'     2   'January, 01 2008 00:00:30'
2   'January, 01 2008 00:00:30'     2   'January, 01 2008 00:00:30'
2   'January, 01 2008 00:00:30'     4   'January, 01 2008 00:00:31'
2   'January, 01 2008 00:00:30'     6   'January, 01 2008 00:00:32'
4   'January, 01 2008 00:00:31'     4   'January, 01 2008 00:00:31'
4   'January, 01 2008 00:00:31'     6   'January, 01 2008 00:00:32'
6   'January, 01 2008 00:00:32'     6   'January, 01 2008 00:00:32'
7   'January, 01 2008 11:11:11'     7   'January, 01 2008 11:11:11'

我们可以看到，非零行被完全排除在外，并且只有当行的时间比第一行晚0到30秒(包括)时，它们才被匹配到起始行。到现在为止还好!但是，我们也希望根据起始行的Id来计算这些结果。因此，我们将使查询聚合结果，如下所示：

SELECT a.Id, a.Time, COUNT(b.Id) numDropped
FROM Demo a
INNER JOIN Demo b
  ON a.Time <= b.Time
  AND a.Zero = b.Zero
WHERE a.Zero = 0
  AND TIMESTAMPDIFF(SECOND, a.Time, b.Time) <= 30
GROUP BY a.Id
;

它给出了包含以下内容的行: 1)定义间隔开始的零行的Id；2)起始行的时间；3)间隔中的零行数，包括起始行：

Id  Time                            numDropped
0   'December, 31 2007 11:11:11'    1
1   'January, 01 2008 00:00:00'     2
2   'January, 01 2008 00:00:30'     3
4   'January, 01 2008 00:00:31'     2
6   'January, 01 2008 00:00:32'     1
7   'January, 01 2008 11:11:11'     1

为了只得到“最坏的”，我们可以简单地接受前面的查询，按numDropped的降序顺序排序，然后得到第一行：

ORDER BY numDropped DESC
LIMIT 1
;

这给了我们：

Id  Time                            numDropped
2   'January, 01 2008 00:00:30'     3

现在，您有了最坏间隔的开始时间，以及第一次连接尝试的Id和该间隔中删除的连接数！如果您希望在查询中返回最坏间隔的结束时间(而不是在消费程序中计算它)，则可以在a.Time + INTERVAL 30 SECOND上添加a.Time + INTERVAL 30 SECOND。再一次，用30秒来交换你的间隔时间。

几个简短的旁白：

1)您会注意到，零行本身就连接在一起，这与前面处理后续行的概念不太相符。但是我们需要这个，因为如果最坏的间隔只有一个断开连接呢？因此，每个零行都需要能够将自己包含在其附近的零行列表中。

2)在a.Time <= b.Time上加入避免创建我们知道无论如何都不想要的重复连接行，所以查询以后不必浪费时间处理它们。但是，您可以删除该子句，并将时间戳检查替换为更显式的TIMESTAMPDIFF(SECOND, a.Time, b.Time) BETWEEN 0 AND 30，您将得到相同的结果。

Answer 2

如果你想找半分钟的日历与最差的连接，那么这是一个简单的聚合查询。就像这样：

select FROM_UNIXTIME(floor(UNIX_TIMESTAMP(datetime) / (30))) as periodstart,
       count(*) as numrows,
       sum(column1 = 0 and column2 = 0 and column3 = 0) as numallzeros
from table t
group by floor(UNIX_TIMESTAMP(datetime) / (30))
order by numallzeros desc;

如果你想灵活地定义这个时期，那就更难了。如果是这样的话，你需要在问题中解释一下该如何去做。