时间戳

首页 > 解决方案 > 如何找到所有行的时间值在其他行之间？

sql - 如何找到所有行的时间值在其他行之间？

问题描述

我正在尝试创建一个 SQL 查询来收集在“c4”事件期间发生的所有事件。换句话说，我想在 c4 期间获取所有具有 startTime 或 endTime 的行。

事件 ID 机器编号 开始时间 时间结束
a3 1 9:19 9:41
c4 1 9:15 9:42
a9 1 9:16 9:18
b3 1 9:40 9:46
j5 1 9:51 9:52
g6 2 9:57 9:59

在上表中，我希望返回 a3、a9 和 b3。

我的尝试如下，但我不知道如何区分被检查行的时间或 c4 行的时间。

COUNTIF(
    eventId = 'c4' 
    AND UNIX_SECONDS(startTime) > UNIX_SECONDS(startTime) 
    AND UNIX_SECONDS(endTime) < UNIX_SECONDS(endTime)) OVER (
        PARTITION BY machineId
) AS is_during_c4

任何指导表示赞赏。

标签： sqlgoogle-bigquery

解决方案

您可以使用join：

select t.*
from t join
     t tc4
     on (t.starttime between tc4.starttime and tc4.endtime or
         t.endtime between tc4.starttime and tc4.endtime
        ) and
        tc4.eventid = 'c4';

请注意，在这种情况下通常需要的是任何重叠，并且上述逻辑没有捕获：

C4         -----XXXXXXX------
A9         --AAAAAAAAAAAA----

为此，逻辑是：

select t.*
from t join
     t tc4
     on t.starttime < =tc4.endtime and
        t.endtime >= tc4.starttime and
        tc4.eventid = 'c4';

推荐阅读