SQL面试题---查询浏览时长

给定user_time表，表中字段分别是user_id ， time（用户访问时间），要求查询每个用户相邻两次浏览时间之差小于三分钟的次数。

     `user_time`   
     +----------+---------+     
     | user_id  | int     |     
     | time     | datetime|       
     +----------+---------+

 

解题思路：1，将用户的每个浏览时间与该用户的下一个浏览时间放在同一行中。使用窗口函数LEAD达到此效果。

                  2，将各用户的下一个浏览时间减去之前的浏览时间，得到一个差值。使用TIMESTAMPDIFF函数计算此差值。

                  3，计算各用户相邻两次浏览时间之差小于三分钟的次数。

 

答案：

WITH next_view AS (
    SELECT user_id,
           time,
           LEAD(time, 1) OVER (PARTITION BY user_id ORDER BY time) AS next_time
    FROM user_time),

view_diff AS (
    SELECT user_id, 
           TIMESTAMPDIFF(SECOND, time, next_time) AS diff
    FROM next_view);

SELECT user_id, COUNT(*) 
FROM view_diff 
WHERE diff < 180
GROUP BY user_id;

 

需要注意的是，上述写法只把有“浏览时间差小于3分钟”情况的用户显示出来，对于次数为0的用户是不显示的。如果需要把所有用户都显示出来，那么需要把最后一个查询语句换成以下：

SELECT user_id,
       COUNT(CASE WHEN diff < 180 THEN user_id ELSE NULL END) AS count
FROM view_diff 
GROUP BY user_id;