如何确定用户何时同时观看电影和另一部电影的演职员表？

Question

我有一个表，看起来像(user_id, movie_id, start_timestamp, end_timestamp, reason_for_end)。电影结束可能是因为用户关掉了它们(reason_for_end == 'user_initiated')，也可能是因为演职员表结束了(reason_for_end == 'inactivity')。演职员表总是整整两个小时。

我想知道用户在另一部电影处于演职员表阶段时开始看一部新电影的总次数。

另外，我还想知道当另一部电影的演职员表还在滚动时，用户花在观看一部电影上的总时间。

编辑:给定的示例表:

user_id   movie_id   start_timestamp        end_timestamp          reason_for_end
1         1          2012-11-18 05:53:36.0  2012-11-18 12:46:40.0  'inactivity'
1         2          2012-11-18 11:34:23.0  2012-12-18 13:21:57.0  'user_initiated'

示例结果表1:

user_id   times_new_started_while_old_in_credits
1         1

示例结果表2:

user_id   total_overlap_in_seconds
1         4320

Answer 1

怎么样?

您应该根据目标数据库语法调整日期时间处理。此外，我不知道你希望如何处理用户同时在多个电影之间跳转的情况-这取决于你;-)

否则，这应该完成工作:

SELECT movie1.user_id, count(*), sum(movie1.end_timestamp - movie2.start_timestamp)
FROM movies movie1
LEFT JOIN movies movie2
    ON movie1.user_id = movie2.user_id
    AND movie1.reason_for_end = 'inactivity'
WHERE movie2.start_timestamp BETWEEN movie1.end_timestamp - '2hours'::interval AND movie1.end_timestamp
    AND movie1.user_id = 1 /* optional */
GROUP BY movie1.user_id