mysql - 如何从每 12 小时发生的事件的每个 "group"中提取第一个时间戳

Question

假设我们有一个包含两列的表:station_id 和 timestamp。我实现了两个站点，它们设置为大约每 12 小时播放一次天气变化。这些流持续几分钟。

我们以这张表为例:

station_id          timestamp
----------------------------------
station_1      2019-06-02 09:01:10
station_1      2019-06-02 09:01:13
station_1      2019-06-02 21:05:10
station_1      2019-06-02 21:08:15
station_2      2019-06-02 09:30:10
station_2      2019-06-02 09:31:10
station_2      2019-06-02 21:40:11
station_2      2019-06-02 21:40:12

我的目标是分析流式传输数据并检查我的两个主播是否按预期定期进行流式传输。我所说的定期是指每 12 小时一次。

输出应该是这样的

station_id          timestamp
----------------------------------
station_1      2019-06-02 09:01:10
station_1      2019-06-02 21:05:10
station_2      2019-06-02 09:30:10
station_2      2019-06-02 21:40:11

我只考虑了一天，但它应该能够处理几天的数据。

我想我需要以某种方式按 station_id 和间隔进行分组，并仅显示间隔开始时间戳

Answer 1

我认为您只需要 lag()。像这样:

select t.*
from (select t.*,
             lag(timestamp) over (partition by station order by timestamp) as prev_timestamp
      from t
     ) t
where prev_timestamp is null or
      prev_timestamp < timestamp - interval 11 hour;

这使用 11 小时而不是 12 小时只是为了为重复测量提供一些余地。

请注意，日期/时间函数因数据库而异。以上在 MySQL 中有效。它在另一个数据库中可能略有不同。