V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
Morii
V2EX  ›  问与答

请教一个 SQL 查询

  •  
  •   Morii · 163 天前 · 766 次点击
    这是一个创建于 163 天前的主题,其中的信息可能已经有所发展或是发生改变。
    我有一张故障表

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

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

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

    突然就不会写了


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


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

    如果直接用 sql 写,有什么比较好的查询方式吗?
    第 1 条附言  ·  162 天前

    @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

    8 条回复    2021-12-15 15:05:59 +08:00
    ZzhRen
        1
    ZzhRen  
       163 天前
    直接故障结束时间-开始时间 然后 sum 起来
    Morii
        2
    Morii  
    OP
       163 天前
    @ZzhRen #1

    你这里没考虑多个故障会在同一时间发生的问题。。
    deplivesb
        3
    deplivesb  
       163 天前
    @Morii 所以同时出现的两个故障你是想算成一个故障计时还是展开计时
    Morii
        4
    Morii  
    OP
       163 天前
    @deplivesb #3

    一个计时
    liufude66
        5
    liufude66  
       163 天前
    递归,查询全年第一次发生故障的开始时间和结束时间 1a,1b ,@接着查询故障早于 1b 的数据。如果这样的的数据存在,那么取故障结束的时间做为新的 1b ,递归下去;如果这样的数据不存在,1a-1b 的时间计入,则查询故障开始时间晚于 1b ,取开始时间最早的数据开始结束时间作为 2a ,2b,重复前面 @处开始的步骤,递归下去。
    umissthestars
        6
    umissthestars  
       163 天前
    取出来处理吧,真打算大力 io 出奇迹吗
    RRRoger
        7
    RRRoger  
       162 天前 via iPhone
    取出来自己写逻辑
    startisan
        8
    startisan  
       162 天前   ❤️ 1
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1312 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 23:14 · PVG 07:14 · LAX 16:14 · JFK 19:14
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.