@RRRoger #7 @umissthestars #6 @startisan #8 @liufude66 #5 @deplivesb #3 @ZzhRen #1
感谢大家回复,最后用一个相对比较简单的逻辑完成了
SELECT TIMESTAMPDIFF(SECOND, MIN(online_complete_time), MAX(occurred_time)) -
SUM(IF(SIGN(TIMESTAMPDIFF(SECOND, next_occurred_time, max_online_complete_time)) > 0,
TIMESTAMPDIFF(SECOND, next_occurred_time, max_online_complete_time), 0))
FROM (
SELECT occurred_time,
online_complete_time,
MAX(online_complete_time)
OVER (ORDER BY occurred_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_online_complete_time,
LEAD(occurred_time, 1)
OVER ( ORDER BY occurred_time) next_occurred_time
FROM `accident`
LEFT JOIN `accident_detail` detail ON `detail`.`id` = `accident`.`id`
WHERE `accident`.`app_id` IN (347113307225732213, 347112617447273589)
AND `detail`.`occurred_time` >= '2021-01-01 00:00:00'
AND `accident`.`deleted_at` IS NULL
) tmp
使用了一些开窗函数解决了 occurred_time:事故开始 online_complete_time:事故结束
如果需要分组 需要在 OVER( ) 函数中传递 PARTITION BY 参数 + 源表 GROUP BY
1
ZzhRen 2021-12-14 15:43:49 +08:00
直接故障结束时间-开始时间 然后 sum 起来
|
5
liufude66 2021-12-14 16:32:39 +08:00
递归,查询全年第一次发生故障的开始时间和结束时间 1a,1b ,@接着查询故障早于 1b 的数据。如果这样的的数据存在,那么取故障结束的时间做为新的 1b ,递归下去;如果这样的数据不存在,1a-1b 的时间计入,则查询故障开始时间晚于 1b ,取开始时间最早的数据开始结束时间作为 2a ,2b,重复前面 @处开始的步骤,递归下去。
|
6
umissthestars 2021-12-14 17:24:15 +08:00
取出来处理吧,真打算大力 io 出奇迹吗
|
7
RRRoger 2021-12-15 08:01:55 +08:00 via iPhone
取出来自己写逻辑
|
8
startisan 2021-12-15 15:05:59 +08:00 1
|