时间戳

首页 > 解决方案 > 在 SQL 中选择连续的时间间隔

sql - 在 SQL 中选择连续的时间间隔

问题描述

我有日期时间表，我需要选择连续的时间间隔

我的桌子：

ID 时间
1 2021-01-01 10:00:00
1 2021-01-01 10:01:00
1 2021-01-01 10:02:00
1 2021-01-01 10:04:00
2 2021-01-01 10:03:00
2 2021-01-01 10:04:00
2 2021-01-01 10:06:00
2 2021-01-01 10:07:00

结果我需要：

ID date_from date_to
1 2021-01-01 10:00:00 2021-01-01 10:02:00
1 2021-01-01 10:04:00 2021-01-01 10:04:00
2 2021-01-01 10:03:00 2021-01-01 10:04:00
2 2021-01-01 10:06:00 2021-01-01 10:07:00

我试过这样，但不能这样做

select id, 
  min(date_from) over 
    (partition by id, date_to 
       order by id) 
    as date_from, 
    max(date_to) over 
      (partition by id, date_from 
      order by id) 
    as date_to
    from (
      select id, 
      MIN(time) over 
        (PARTITION by id, 
        diff2 between 0 and 60
        ORDER BY id, time) 
      as date_from, 
      max(MINUTE) over 
        (PARTITION by id, 
        diff between 0 and 60
        ORDER BY id, time) 
      as date_to 
        from (
           select *, 
           unix_timestamp(date_lead) - unix_timestamp(time) 
              as diff, 
           unix_timestamp(time) - unix_timestamp(date_lag) 
              as diff2 
                    from (
                        select id, time,
                            NVL(LEAD(time) over 
                                (PARTITION by id 
                                ORDER BY id, time), time) 
                            as date_lead,
                            NVL(LAG(time) over 
                                (PARTITION by id 
                                ORDER BY id, time), time) 
                            as date_lag
                        from my_table) 
                    )
                )

标签： sqlpostgresql

解决方案

假设您的时间戳是精确的（没有秒或几分之一秒），您可以从time列中减去枚举的分钟数。这对于“相邻”行是恒定的：

select id, min(time), max(time)
from (select t.*,
             row_number() over (partition by id order by time) as seqnum
      from t
     ) t
group by id, time - seqnum * interval '1 minute';

如果您有秒和小数秒，那么您可能需要使用date_trunc(). 如果这是一个问题，我建议您使用适当的样本数据和期望的结果提出一个新问题。

推荐阅读