时间戳

首页 > 解决方案 > SQL根据第一个表中的时间差连接第二个表

mysql - SQL根据第一个表中的时间差连接第二个表

问题描述

我有两个表，一个是事件的开始时间，第二个是事件的结束时间，我想加入这两个表

然而，挑战在于每个开始事件并不总是有相应的结束事件，如果是这样，我希望输出中有一个 NULL。这可能吗？

编辑：每个 ID 代表一个人，每天可以有多个事件开始和停止。对于每个事件，我只希望将单个“正确”结束时间加入到开始时间（如果存在）。目前没有单独的事件级别标识符。

例如：

表1：开始时间

id      ts_start
123     01:00
123     03:00
123     05:00
123     09:00

表2：结束时间

id      ts_end
123     02:00
123     07:00

输出：

id      ts_start    ts_end
123     01:00       02:00
123     03:00       NULL
123     05:00       07:00
123     09:00       NULL

我在 MySQL 5.7 上，所以还不能访问窗口/分析函数，虽然如果这是最好的解决方案的一部分，那么我很乐意迁移（虽然必须是开源的，所以新版本的 MySQL 或 Postgres )

谢谢

标签： mysqlsqlpostgresqljoin

解决方案

您必须查看下一个开始是否晚于下一个结束。一种方法使用两个相关的子查询：

select id, ts_start,
       (case when next_start > next_end then next_end
        end) as ts_end
from (select s.*,
             (select max(s2.ts_start)
              from starts s2
              where s2.id = s.id and s2.ts_start > s.ts_start
             ) as next_start,
             (select min(e2.ts_end)
              from ends e2
              where e2.id = s.id and e2.ts_end > s.ts_end
             ) as next_end
      from starts s
     ) s;

使用窗口函数，我会将所有时间组合在一起并查看下一个值：

with t as (
      select id, ts_start as time, 'start' as which
      from starts
      union all
      select id, ts_end, 'end'
      from ends
     )
select t.id, t.time as ts_start,
       (case when next_which = 'end' then next_time
        end) as ts_end
from (select t.*,
             lead(time) over (partition by id order by time) as next_time,
             lead(which) over (partition by id order by time) as next_which
      from t
     ) t
where which = 'start';

推荐阅读