统计小白er

V1

2023/01/16阅读:15主题:草原绿

SQL刷题-T2:参与优惠活动的商品

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) ),1as ccc 
from (
        select t.*,
        timestampdiff(minute,end_time, aaa) as e_s_time 
        from (
                select *,
                lead(start_time,1over(partition by user_id order by end_time ascas 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 
;

分类:

后端

标签:

数据库

作者介绍

统计小白er
V1