使用SQL计算最长的狂欢观看连胜

Question

我正在尝试处理一些观看狂欢的统计信息，我想找出 最长的狂欢 连胜纪录（ 暴跌
是指多个节目以优惠的方式观看，一个又一个，相距不超过2个小时）。数据如下所示：

datetime                user_id program
2013-09-01 00:01:18     1       A
2013-09-10 14:03:14     1       B
2013-09-20 17:02:12     2       A  
2013-09-21 00:03:22     2       C  <-- user 2 binge start
2013-09-21 01:23:22     2       M
2013-09-21 03:03:22     2       E
2013-09-21 04:03:22     2       F  
2013-09-21 06:03:22     2       G  <-- user 2 binge end
2013-09-21 09:03:22     2       H
2013-09-03 18:21:09     3       D
2013-09-21 09:03:22     2       H
2013-09-24 19:21:00     2       X  <-- user 2 second binge start
2013-09-24 20:21:00     2       Y
2013-09-24 21:21:00     2       Z  <-- user 2 second binge end

sqlfiddle

在此示例中，用户2持续了6个小时的狂欢，后来又持续了2个小时。

我想要的最终结果是这样的：

user_id     binge     length
2           1         6 hours
2           2         2 hours

可以直接在数据库中计算吗？

Answer 1

这是识别数据中的序列/条纹的问题。我这样做的首选方式是，

• 使用LAG功能识别每个条纹的开始
• 使用SUM函数为每个条纹分配一个唯一的数字
• 然后按此唯一编号分组以进行进一步处理

询问：

with start_grp as (
  select dt, user_id, programme,
         case when dt - lag(dt,1) over (partition by user_id order by dt) 
                   > interval '0 day 2:00:00'
              then 1
              else 0
         end grp_start
  from binge
  ),
assign_grp as (
  select dt, user_id, programme,
  sum(grp_start) over (partition by user_id order by dt) grp
  from start_grp)
select user_id, grp as binge, max(dt) - min(dt) as binge_length
from assign_grp
group by user_id, grp
having count(programme) > 1

在这里，狂欢列可能不会以顺序的方式出现。您可以在最终查询中使用ROW_NUMBER函数进行更正。

演示于 [sqlfiddle](http://sqlfiddle.com/#!12/cb1a2/5)