【题目】
某游戏公司为了监测新上市游戏APP的受欢迎程度,通过数据来分析用户的总数、用户的平均年龄及活跃用户(连续两天访问)的总数和平均年龄。以下表格为用户登录信息表明细。
字段解释:
登录日期:记录用户登录游戏APP的时间
用户ID:用户的唯一标识
用户年龄:用户在APP登记的年龄
需要分析出如下结果:
1.统计用户的总数、用户的平均年龄
2.统计活跃用户(连续两天访问)的总数和平均年龄
【解题思路】
1.统计用户的总数、用户的平均年龄
观察表一可以看到同一用户同一天有多条登录记录,如果直接进行聚合查询的话会造成重复数据计算,所以应该先按照用户ID对重复数据进行去重在分析用户的总数及平均年龄。
SQL写法一:
select distinct 用户ID,用户年龄
from 用户登录信息表;
SQL写法二:
select 用户ID,用户年龄
from 用户登录信息表
group by 用户ID;
查询结果:
查询结果作为临时表a,接下来用计算用户数量(count函数)和求用户的平均年龄(avg函数)
SQL写法如下:
select count(用户ID) as 用户总数 ,avg(用户年龄) as 用户平均年龄
from(
select 用户ID,用户年龄
from 用户登录信息表
group by 用户ID
) a;
查询结果:
2.统计活跃用户(连续两天访问)的总数和平均年龄
活跃用户:定位为连续两天都有访问,大白话解释为在今日登录,明天也有登录的用户数。也就是时间间隔=1。
一个表如果涉及到时间间隔,就需要用到自联结,也就是将两个相同的表进行联结。将表a理解为用户的登录时间,将表b理解为用户的再次登录时间,通过计算登录时间与再次登录时间的时间差,时间差等于1即表示今天也登录,明天也登录(连续两天登录)
select
a.登录日期 as 登录时间,
a.用户ID,
a.用户年龄,
b.登录日期 as 再次登录时间
from
用户登录信息表 as a
left join 用户登录信息表 as b on a.用户ID = b.用户ID;
查询结果:
联结后的临时表记为表c,那么如何从表c中查找出时间间隔(明天登陆时间-今天登陆时间)=1的数据呢?
(1)这涉及到计算两个日期之间的差值,《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。
SQL书写如下:
select *,timestampdiff(day,c.登录时间,c.登出时间) as 时间间隔
From c
group by c.用户ID,c.登录时间;
查询结果:
查询结果作为临时表d,用case when语句选出时间间隔=1的数据,计算活跃用户的总数(count)和活跃用户的平均年龄(avg)
SQL书写如下:
select count(distinct case when 时间间隔=1 then 用户ID else null end ) as 活跃用户总数,
avg( case when 时间间隔=1 then 用户年龄 else null end ) as 活跃用户平均年龄
from d;
将临时表d的SQL代入,最终SQL代码如下:
select
count(distinct case when 时间间隔=1 then 用户ID else null end ) as 活跃用户总数,
avg( case when 时间间隔=1 then 用户年龄 else null end ) as 活跃用户平均年龄
from
(
select *,timestampdiff(day,c.登录时间,c.再次登录时间) as 时间间隔
from
(
select
a.登录日期 as 登录时间,
a.用户ID,
a.用户年龄,
b.登录日期 as 再次登录时间
from用户登录信息表 as a
left join 用户登录信息表 as b on a.用户ID = b.用户ID
) c
group by c.用户ID,c.登录时间
) d;
查询结果:
【本题考点】
1.常用指标的理解,例如留存用户数、留存率。
2.灵活使用case来统计when 函数与group by 进行自定义列统计。
3.遇到只有一个表,但是需要计数时间间隔的问题,就要想到用自联结来求时间间隔,类似的有找出连续出现N次的内容、滴滴2020求职真题、链家面试题:如何分析留存率。