mysql - 如何计算表的连续范围

我有一张桌子如下：

VideoId    StartTime EndTime EntityMid
1001         1         2        a
1001         2         3        a
1001         7         8        b
1001         10        11       a
1001         11        12       a
1002         4         5        c
1002         7         8        c

我想得到以下结果：

VideoId    EntityMid duration
1001        a         2
1001        b         1
1001        a         2
1002        c         1
1002        c         1

我试过窗口功能和自连接，但看起来不起作用。非常感谢你的帮助！
我的错误sql代码：

SELECT *
FROM (
  SELECT *
    LEAD(EntityMid) OVER(PARTITION BY videoId ORDER BY StartTime) AS lead_EntityMid,
    LEAD(EndTime) OVER(PARTITION BY videoId ORDER BY StartTime) AS lead_EndTime
)
FROM a
) b
WHERE EntityMid = lead_EntityMid
AND EndTime + 1 = lead_EndTime

最佳答案

这是一种缺口和孤岛问题。
对于您的特定数据，您可以使用left join来确定“孤岛”是否开始然后使用累积和和和：

select t.videoid, t.EntityMid,
       max(t.EndTime) - min(t.StartTime) as duration
from (select t.*,
             sum(case when tprev.EntityMid is null then 1 else 0 end) over
                 (partition by t.videoid order by t.StartTime) as grp
      from t left join
           t tprev
           on t.videoid = tprev.videoid and
              t.EntityMid = tprev.EntityMid and
              t.StartTime = tprev.EndTime
     ) t
group by t.videoid, t.EntityMid, grp;

Here是一把分贝小提琴

关于mysql - 如何计算表的连续范围，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58073379/