mysql每天查找第一个和最后一个事件

6ss1mwsb  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(474)

在mysql数据库中,我每天有一个5分钟的事件记录。我需要确定当天的第一个事件(其中logid>0)和最后一个事件(其中logid=0),但很难找到一个简单的sql解决方案。
从午夜开始,每行的logid字段中都会存储一个0,直到触发第一个事件,此时它将变为一个大于0的数字。然后会触发各种事件,记录一天剩余时间内大于0的数字,此时该字段将再次被记录为0,直到午夜,当该过程再次开始。
有没有一种快速而简单的方法来提取标识事件开始时间的行,以及显示事件结束时间的另一个结果?

CREATE TABLE logs( 
  id INT AUTO_INCREMENT, 
  date DATETIME, 
  logid INT, 
  PRIMARY KEY (id) 
) ENGINE=INNODB;

这是测试数据:

id  date                logid
1   2018-11-12 01:05:00 0   
2   2018-11-12 01:10:00 0   
3   2018-11-12 01:15:00 0
4   2018-11-12 01:20:00 0   
5   2018-11-12 01:05:00 0   
…
84  2018-11-12 06:35:00 0   
85  2018-11-12 06:35:00 1   
86  2018-11-12 06:40:00 1   
87  2018-11-12 06:45:00 1   
88  2018-11-12 06:50:00 1   
…
164 2018-11-12 15:20:00 1   
165 2018-11-12 15:25:00 0   
166 2018-11-12 15:30:00 0   
167 2018-11-12 15:35:00 0

期望结果集:

85  2018-11-12 06:35:00 1   
165 2018-11-12 15:25:00 0

我不关心logid,直到它大于0的第一个示例。但是我需要确定logid>0的第一个示例,然后是logid=0的下一个按时间顺序排列的示例。
我的主要尝试是根据日期和logid进行分组和订购(编辑:为清晰起见,删除了失败的尝试)
这是我的最新尝试

(SELECT *
        FROM logs
        WHERE logid>0
        GROUP BY date
        ORDER BY date
        limit 1
    )UNION ALL(   
        SELECT *
        FROM logs
        WHERE logid>0
        GROUP BY date
        ORDER BY date DESC
        limit 1)

越来越近了,但还没到。这给了我正确的第一行,其中logid=1,但它给了我logid=1(id 164)的最后一行,而不是logid=0(id=165)的下一行。
如果我将限制1改为2,是否可以选择集合的倒数第二行?还有什么能让我继续前进的建议吗?

mkshixfv

mkshixfv1#

这个问题对其他人来说似乎不是问题,但我想我会把我的答案贴出来,以防将来有人遇到类似的情况。

SET @v1 := (SELECT date 
        FROM logs
        WHERE logid > 0
        GROUP BY date
        ORDER BY date
        limit 1);

(SELECT *
        FROM logs
        WHERE date>@v1 and logid>0
        GROUP BY date
        ORDER BY date
        limit 1
) UNION ALL (
SELECT *
        FROM logs
        WHERE date>@v1 and logid=0
        GROUP BY date
        ORDER BY date
        limit 1
)

相关问题