MySQL面试题

练习题1：concat group_concat

用户号对应前两个不同场景，场景重复的话选择场景的第一个访问时间，场景号不足两个的输出为止输出结果示例：

create table test
(userid varchar(20),
changjing varchar(20),
inttime varchar(20)
);
insert into test values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400),
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404),
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);

————————————————
版权声明：本文为CSDN博主「Jelly-ha」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39669739/article/details/108324042

1. 解答步骤

• 关键词：每位用户，前两个不同场景
• 注意：若场景重复，选择第一个访问时间的场景
• 思路：找出每位用户在每个场景下的最早访问时间，对最早访问时间进行排序，以便找出前两个场景
  第一步：找出每位用户 是第一个访问时间的所有场景

select userid,changjing,min(inttime) inttime
from test
group by userid,changjing
order by userid,inttime;

第二步：对每位用户的所有场景按照时间进行排名

select *,
row_number() over(
partition by userid
order by inttime
) ranking
from(
	select userid,changjing,min(inttime) inttime
	from test
	group by userid,changjing
	order by userid,inttime) t

第三步：筛选出前两个场景

select *
from(
select *,
row_number() over(
partition by userid
order by inttime
) ranking
from(
	select userid,changjing,min(inttime) inttime
	from test
	group by userid,changjing
	order by userid,inttime) t1) t2
where ranking <=2

第四步：对用户进行分组，使用concat函数和group_concat函数得到所要求的结果形式

select concat(userid,'-',group_concat(changjing SEPARATOR '-')) result
from(
select *,
row_number() over(
partition by userid
order by inttime
) ranking
from(
	select userid,changjing,min(inttime) inttime
	from test
	group by userid,changjing
	order by userid,inttime) t1) t2
where ranking <=2
group by userid;

练习题2：次日、三日、七日留存

相机是深受大家喜爱的应用之一，现在我们需要研究相机的活跃情况，需统计如下数据：某日活跃的用户（uid）在后续的一周内的留存情况（计算次留、三留、七留）
指标定义：

• 某日活跃用户数：某日活跃的去重用户数；
• N日留存用户数：某日活跃的用户数在之后的第N日活跃用户数；
• N日活跃留存率：N日留存用户数/某日活跃用户数
  例：20180501日去重用户数10000，这批用户20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%。
  
  第一步：计算活跃度

select dayno,count(distinct uid) '活跃度'
from aui
where app_name = '相机'
group by dayno;

第二步：次日留存
（两表自交，利用case when找到符合相差日期为1天的id，计数，得出次日留存人数，最后用distinct去重）

select a.day1,
count(distinct case when day2-day1=1 then a.uid end) '次留'
from
(select uid,date_format(dayno,'%Y%m%d'