当一个用户同时观看一部电影和另一部电影的片头时，我该如何判断？

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