如何在两个变量日期之间找到重复项

I have the following table:

我有下表:

id | whenCreated    | callingNumber |
---|--------------------------------|
1  | 1-1-2016 9:00  | 0612345678    |
2  | 1-1-2016 9:10  | 0623456789    |
3  | 1-1-2016 9:55  | 0612345678    |
4  | 1-1-2016 10:15 | 0623456789    |
5  | 1-1-2016 11:00 | 0623456789    |
etc.

Datatypes:
id = int (AI)
whenCreated = datetime
callingNumber = varchar

I want to do an analysis on how many people call back within a certain period after their previous call (for the example, let's say 1 hour). For this, i want to find the number of times someone has called before in the previous period. In this example, the result should be something like this:

我想分析一下有多少人在他们之前的电话会议后的一段时间内回电(例如,假设1小时)。为此,我想找一个人在之前一段时间之前打过电话的次数。在这个例子中,结果应该是这样的:

id | whenCreated    | callingNumber | prevCalls |
---|--------------------------------|-----------|
1  | 1-1-2016 9:00  | 0612345678    | 0         | < 0, because no previous calls
2  | 1-1-2016 9:10  | 0623456789    | 0         | < 0, because no previous calls
3  | 1-1-2016 9:55  | 0612345678    | 1         | < 1, because one call from this number in the last hour
4  | 1-1-2016 10:15 | 0623456789    | 0         | < 0, there was one earlier call from this number, but it was more than an hour ago
5  | 1-1-2016 11:00 | 0623456789    | 1         | < 1, because one call from this number in the last hour
etc.

Is there any way i can do this with one query in MySQL? Or do i have to run a script for this? (i know how to do it for each row individually, i just can't seem to find a way to do it with one query).

有什么办法可以用MySQL中的一个查询来做到这一点吗?或者我必须为此运行脚本? (我知道如何分别为每一行做这件事,我似乎无法通过一个查询找到一种方法)。

1 个解决方案

#1

You can do it with a correlated subquery:

您可以使用相关子查询来执行此操作:

SELECT id, whenCreated, callingNumber,
       (SELECT COUNT(*)
        FROM mytable AS t2
        WHERE t2.callingNumber = t1.callingNumber AND
              t2.id < t1.id AND 
              TIMESTAMPDIFF(MINUTE, t2.whenCreated, t1.whenCreated) < 60)
FROM mytable AS t1

The query uses TIMESTAMPDIFF to calculate the difference in minutes between two whenCreated values.

该查询使用TIMESTAMPDIFF计算两个whenCreated值之间的分钟差异。

Demo here

#1