2、app使用率分析（2星）

2.1 题目介绍

一张用户使用app时间表app_login，包括三个字段，user_id（用户ID）：varchar，start_time（登录app时间）：DATETIME，end_time（退出app时间）：DATETIME。

统计每个用户每次退出app与下次登录app时间间隔的平均时间，如果用户只登录过一次app，则不统计。输出用户id（user_id）和平均时间间隔（avg_time）（单位为分钟，四舍五入保留一位小数）。

2.2 注释（思路想法）：

写完之后，发现直接按照自己理解的做了。直接理解错误，这就是自以为啊。直接求了用户的平均在线时长。

然后又开始研究该需求怎么做，研究前一次的end_time和后一次的start_time的差值，最开始使用的就是直接做减法，但是结果是不对的。后来，又用的timestampdiff(second,前一次的end_time, 后一次的start_time)，发现与前者结果不一致。

这里还有一点就是如果直接使用second参数的话，最终结果和图示有点误差，这应该是计算口径的问题。所以，这里使用了minute参数，最终的计算结果和图示相同。

这里分析上面问题的原因：

-- 如果直接使用两个日期格式的数据进行做减法，那么就是将日期转化为时间戳，再做减法。 
-- 所以，如果想得到对应时间之间的分钟、秒、小时等不应该这样操作。
select *,
start_time + 0,end_time + 0,
end_time - start_time ,
(end_time + 0) - (start_time + 0)
from app_login 
;

可以使用timediff()函数，返回一定时间格式的数据，如下所示：

select *,
datediff(end_time,start_time),
timediff(end_time,start_time),
hour(timediff(end_time,start_time)),
minute(timediff(end_time,start_time)),
second(timediff(end_time,start_time))
from app_login 
;

然后后续操作，可以使用hour()、minute()函数。

再就是timestampdiff()函数，

select *,
timediff(end_time,start_time),
timestampdiff(hour, start_time, end_time),
timestampdiff(minute, start_time, end_time),
timestampdiff(second, start_time, end_time)
from app_login 
;

这里需要注意的是，如果参数是hour，那么他就不会考虑minute和second。注意计算方式。

建表语句：

DROP TABLE IF EXISTS app_login; 
CREATE TABLE app_login(
user_id VARCHAR(8), 
start_time DATETIME, 
end_time DATETIME 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
app_login (user_id,start_time,end_time) 
VALUE ('u001','2021-04-01 10:12:30','2021-04-01 11:13:21') 
,('u002','2021-04-02 08:40:21','2021-04-02 10:13:41') 
,('u003','2021-04-02 15:31:01','2021-04-02 15:54:42') 
,('u001','2021-04-04 13:25:40','2021-04-04 17:52:46') 
,('u003','2021-04-06 07:10:20','2021-04-06 08:03:15') 
,('u001','2021-04-09 18:20:34','2021-04-09 18:23:58') 
,('u001','2021-04-10 14:25:55','2021-04-10 15:01:25');

脚本：

-- 需求：统计每个用户每次退出app与下次登录app时间间隔的平均时间，如果用户只登录过一次app，则不统计。
select tt.user_id,
round((sum(tt.e_s_time)/count(tt.user_id) ),1) as ccc 
from (
        select t.*,
        timestampdiff(minute,end_time, aaa) as e_s_time 
        from (
                select *,
                lead(start_time,1) over(partition by user_id order by end_time asc) as aaa ,
                count(user_id) over(partition by user_id) as num 
                from app_login 
        )t 
        where t.num <> 1 
        and t.aaa is not null 
)tt 
group by tt.user_id 
order by tt.user_id 
;