Sql经典案例学习0.

1.求某一时刻最大人数

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	101	9001	2021-11-01 10:00:00	2021-11-01 10:00:11	0
2	102	9001	2021-11-01 10:00:09	2021-11-01 10:00:38	0
3	103	9001	2021-11-01 10:00:28	2021-11-01 10:00:58	0
4	104	9002	2021-11-01 11:00:45	2021-11-01 11:01:11	0
5	105	9001	2021-11-01 10:00:51	2021-11-01 10:00:59	0
6	106	9002	2021-11-01 11:00:55	2021-11-01 11:01:24	0
7	107	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

artical_id	max_uv
9001	3
9002	2

解法：

一直以来，都觉得这种球最大的同时在线/阅读人数很难，但是在参考大家的思路后，感觉很清晰，一下子就通透了。具体的步骤是：

①将用户的进入时间单独拎出来，同时记为1；离开时间单独拎出来，同时记为-1，这样就聚合这两个表，按照时间排序，意思就是：进去一个加1，离开一个减1。

②然后利用窗口函数对计数（1或者-1）求累计和，因为题目规定：同一时间有就有出的话先算进来的后算出去的，所以排序的时候就要看好了先按时间排序，再按计数排序！

③然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了！

select b.artical_id,max(sub) max_uv
from (select a.artical_id,sum(a.num) over (partition by a.artical_id order by dt,a.num desc) as sub
from (
    select artical_id,in_time dt,1 num
    from tb_user_log
    where artical_id != 0
    union all 
    select artical_id,out_time dt,-1 num
    from tb_user_log
    where artical_id != 0
)  as a) as b
group by artical_id
order by max_uv desc

引申问题：

1.为什么不能直接在sum( ) over( ) 外面直接套一个max（sum( ) over( ))？

是因为分区（partition by​）不是分组(group by),分组会对 artical_id去重，而分区不会。分区后artical_id还是有很多行，不能直接用max（）这个聚合函数

2.为什么用union all？

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

2.求次日用户的留存率

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	101	0	2021-11-01 10:00:00	2021-11-01 10:00:42	1
2	102	9001	2021-11-01 10:00:00	2021-11-01 10:00:09	0
3	103	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0
4	101	9002	2021-11-02 10:00:09	2021-11-02 10:00:28	0
5	103	9002	2021-11-02 10:00:51	2021-11-02 10:00:59	0
6	104	9001	2021-11-02 11:00:28	2021-11-02 11:01:24	0
7	101	9003	2021-11-03 11:00:55	2021-11-03 11:01:24	0
8	104	9003	2021-11-03 11:00:45	2021-11-03 11:00:55	0
9	105	9003	2021-11-03 11:00:53	2021-11-03 11:00:59	0
10	101	9002	2021-11-04 11:00:55	2021-11-04 11:00:59	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：

• 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
• 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

输出示例：

示例数据的输出结果如下

dt	uv_left_rate
2021-11-01	0.67
2021-11-02	1.00
2021-11-03	0.00

解法：

1.求出每个用户的首次活跃日期：

select uid,min(date(in_time)) dt 
from tb_user_log
group by uid

2.求出每个用户每次活跃的日期：

select uid,date(in_time) df 
from tb_user_log
where artical_id !=0
union
select uid,date(out_time) df 
from tb_user_log
where artical_id !=0

用union连接起去重作用，用户单日多次登录只计一次活跃

3.左连接二表，用第二张表日期-1=第一张表日期，用户名相同为连接条件，若新用户次日未活跃，则左连接未匹配到为null

(select uid,min(date(in_time)) dt 
from tb_user_log
group by uid) as a 
left join 
(select uid,date(in_time) df 
from tb_user_log
where artical_id !=0
union
select uid,date(out_time) df 
from tb_user_log
where artical_id !=0 ) as b
on a.uid=b.uid and a.dt=date_sub(b.df,interval 1 day)

我们便得到了一张新用户次日仍然留存的表，未留存的为null

4.在该表中进行查询，聚合即可

select a.dt,
       round(count(b.uid)/ count(a.uid),2) as uv_left_rate
from (select uid,min(date(in_time)) dt 
from tb_user_log
group by uid) as a 
left join 
(select uid,date(in_time) df 
from tb_user_log
where artical_id !=0
union
select uid,date(out_time) df 
from tb_user_log
where artical_id !=0 ) as b
on a.uid=b.uid and a.dt=date_sub(b.df,interval 1 day)
where date_format(a.dt,"%Y-%m")='2021-11'
group by a.dt

拓展：

DATE_SUB() 函数

DATE_SUB() 函数从日期减去指定的时间间隔。

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

本次使用日期减一天：date_sub(dt,interval 1 day)

                                    adddate(dt,interval -1 day)

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

3.求连续签到积分问题

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_in
1	101	0	2021-07-07 10:00:00	2021-07-07 10:00:09	1
2	101	0	2021-07-08 10:00:00	2021-07-08 10:00:09	1
3	101	0	2021-07-09 10:00:00	2021-07-09 10:00:42	1
4	101	0	2021-07-10 10:00:00	2021-07-10 10:00:09	1
5	101	0	2021-07-11 23:59:55	2021-07-11 23:59:59	1
6	101	0	2021-07-12 10:00:28	2021-07-12 10:00:50	1
7	101	0	2021-07-13 10:00:28	2021-07-13 10:00:50	1
8	102	0	2021-10-01 10:00:28	2021-10-01 10:00:50	1
9	102	0	2021-10-02 10:00:01	2021-10-02 10:01:50	1
10	102	0	2021-10-03 10:00:55	2021-10-03 11:00:59	1
11	102	0	2021-10-04 10:00:45	2021-10-04 11:00:55	0
12	102	0	2021-10-05 10:00:53	2021-10-05 11:00:59	1
13	102	0	2021-10-06 10:00:45	2021-10-06 11:00:55	1

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：

• artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
• 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
• 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

输出示例：

示例数据的输出结果如下：

uid	month	coin
101	202107	15
102	202110	7

解释：

101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；

102在10.01~10.03连续签到3天获得5金币

10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

解法：

关于连续第几天，其实只是一个小技巧，一点都不难理解

1. 假设一组数据为[0,1,2,4,7,8]，我们想按照连续性分组为[0,1,2]、[4]、[7,8]
2. 我们先做序号列[1,2,3,4,5,6]（通过RANK OVER(uid)实现）
3. 然后序号-数据=[1,1,1,0,-2,-2]，就是这么简单的技巧，得到能实现连续性分组的特征标签
4. 按分组再加序号[1,2,3,1,1,2]（通过RANK OVER(uid,特征标签) 实现

   
   with a as( -- 取出有效记录
       SELECT distinct uid,date(in_time) as dt -- 同一用户一天可能有多条日志记录，去重一下
       FROM tb_user_log
       where artical_id = 0 and sign_in = 1 and date(in_time) between '2021-07-07' and '2021-10-31'
   )
   ,b as( -- 得到组内分组标签
       select *,
           adddate(dt,INTERVAL - 
               rank() over (partition by uid order by dt)  -- 每个用户，把日期标上序号
               day) as group_tag  -- 用日期减序号,得到连续性分组
       from a
   )
   ,c as( -- 得出每天赚的金币数
       select uid,date_format(dt,'%Y%m') as month,
           case rank() over(partition by uid,group_tag order by dt)%7  -- 按连续性分组加序号，然后除以7求余数
               when 3 then 3  -- 每到第三天3枚
               when 0 then 7  -- 每到第七天7枚
               else 1 -- 其他天数1枚
           end as coin_eachday
       from b
   )
   select uid,month,sum(coin_eachday) as coin
   from c
   group by uid,month
   order by month,uid