请教一个 SQL 查询

[Morii]

我有一张故障表

里面有 N 条故障信息，每条信息上记录着故障的开始结束时间

同一时间是有多个故障发生的

需要使用 SQL 查询全年故障时长

突然就不会写了


--------------------


如果是代码的话，我会考虑 bitset 或者过滤器实现，计算每一秒是否有故障然后求和

如果直接用 sql 写，有什么比较好的查询方式吗？

@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

[ZzhRen]

直接故障结束时间-开始时间 然后 sum 起来

[Morii]

@ZzhRen #1

你这里没考虑多个故障会在同一时间发生的问题。。

[deplivesb]

@Morii 所以同时出现的两个故障你是想算成一个故障计时还是展开计时

[Morii]

@deplivesb #3

一个计时

[liufude66]

递归，查询全年第一次发生故障的开始时间和结束时间 1a,1b ，@接着查询故障早于 1b 的数据。如果这样的的数据存在，那么取故障结束的时间做为新的 1b ，递归下去；如果这样的数据不存在，1a-1b 的时间计入，则查询故障开始时间晚于 1b ，取开始时间最早的数据开始结束时间作为 2a ，2b,重复前面 @处开始的步骤，递归下去。

[umissthestars]

取出来处理吧，真打算大力 io 出奇迹吗

[RRRoger]

取出来自己写逻辑

[startisan]

亲测可以用，不过最后的 sql 有点复杂了
https://www.kodyaz.com/t-sql/sql-query-for-overlapping-time-periods-on-sql-server.aspx