mysql - 如何从每十二小时发生的每个“组”事件中提取第一个时间戳

Question

假设我们有一个包含两列的表：station_id 和时间戳。我实现了两个站点，它们设置为大约每 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 和间隔以某种方式分组，并且只显示间隔开始时间戳

标签： mysqlsqlapache-spark

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。在另一个数据库中可能会略有不同。