时间戳

首页 > 解决方案 > 如何计算连续秒内的不同事件？

mysql - 如何计算连续秒内的不同事件？

问题描述

我有一个包含多个列、日期、时间戳 (HH:MM:SS) 和电话号码的数据库。我试图找到一种在连续几秒钟内找到唯一呼叫的方法。例如：

Date          Timestamp     Phone_number
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:15:24      999-999-9999
10-12-2019    15:15:24      999-999-9999
10-12-2019    15:15:25      999-999-9999
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:20:22      111-111-1111
10-12-2019    15:22:33      999-999-9999

理想情况下，我想将第一个 999-999-9999 计为一个呼叫，将 111-111-1111 计为一个呼叫，第二个 999-999-9999 呼叫作为一个呼叫，基于呼叫具有相同的时间戳或连续几秒钟发生。

输出与此类似，计数为 1，非顺序调用的 min(timestamp)：

Date          Timestamp     Phone_number
10-12-2019    15:15:23      999-999-9999
10-12-2019    15:20:21      111-111-1111
10-12-2019    15:22:33      999-999-9999

我不完全确定从哪里开始。我相信我必须根据前一秒创建时间的子选择。不知道如何实现它......或者它是否会在同一秒内传递时间戳

SELECT t1.time, t1.phone_number, COUNT(DISTINCT t2.x)
FROM myTable AS t1
JOIN myTable AS t2 ON t2.timestamp BETWEEN DATE_SUB(t1.timestamp, INTERVAL 1 second) AND t1.timestamp
GROUP BY t1.timestamp, t1.phone_number

我正在使用 MySql。接受任何帮助！先感谢您

标签： mysqlsqldatecountwindow-functions

解决方案

在 MySQL 8.0 中，这可以使用 window function 来解决lag()。您可以在子查询中恢复上一次呼叫同一号码的日期/时间，然后过滤掉上一秒之前呼叫同一号码的记录：

select date, timestamp, phone_number
from (
    select 
        t.*,
        lag(concat(date, ' ', timestamp)) 
            over(partition by phone_number order by date, timestamp) lag_datetime
    from mytable t
) x
where 
    lag_datetime is null
    or concat(date, ' ', timestamp) > lag_datetime + interval 1 second

注意：将日期和时间存储在单独的列中不是一个好主意，因为当您需要实际比较日期/时间时，它会使逻辑复杂化。

DB Fiddle 上的演示：

| date       | timestamp | phone_number |
| ---------- | --------- | ------------ |
| 2019-10-12 | 15:15:23  | 999-999-9999 |
| 2019-10-12 | 15:20:21  | 111-111-1111 |
| 2019-10-12 | 15:22:33  | 999-999-9999 |

推荐阅读