我有下表:
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
我想分析一下在上次通话后的一定时间内有多少人回电(例如,假设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.
我有什么办法可以在MySQL中执行一个查询呢?还是我必须为此运行脚本?(我知道如何分别为每一行执行此操作,我似乎无法找到一种通过一个查询执行此操作的方法)。
您可以使用相关的子查询来做到这一点:
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
该查询用于TIMESTAMPDIFF
计算两个whenCreated
值之间的分钟差。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句