SQL学习（10）：微观到宏观的困难题目解答 | 大厂真题实例-每天新用户的次日留存率

SQL学习（10）：微观到宏观的困难题目解答 | 大厂真题实例-每天新用户的次日留存率

题目来源：《牛客题霸：SQL大厂真题》02用户增长场景（某度信息流） SQL164 2021年11月每天新用户的次日留存率

前言：微观到宏观——困难题目的新解题思路和代码框架

经过前几个章节的学习和对应题目的锻炼，相信文章所描述的解题思路和代码框架都已经被掌握。总结来看，先书写select-from-where代码框架，再分解题目，逐步填充的解题方法具有宏观到微观的特点，适合思路清晰的简单题目和部分中等题目，解答清晰、书写方便、不容易出现语法遗漏问题。

但当题目比较复杂，尤其需要多层子查询时，这种方法带来的代码书写思路却往往容易出现遗漏，导致错误。原因在于，我们解决困难问题的常见方法是微观到宏观的，即将困难问题拆分为简单的小问题再进行组合。 如果还使用前几章提到的框架，则在宏观下找微观时容易出现遗漏。

本章开始，将从微观到宏观介绍适合困难问题的新解题思路和代码框架。

1.题目描述

用户行为日志表tb_user_log：

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

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

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

2.问题分析

第一步：拆解问题并分块

困难问题通常需要使用子查询，涉及join、union等函数。我们需要根据最终要求的提取数据，反推其来自的查询条件、子查询部分等。

问题拆分：

1. 2021年11月每日新用户次日留存率：

• 2021年11月——新用户和次日留存都需要在11月内，即用户第一次进入和再次进入都需要在11月。如果使用min得到的新用户子查询中使用where条件，只会得到11月中首次进入，并不等于在11月首次注册；需要使用having
• 新用户——2021年11月之前未进入过
• 次日留存——只关注新用户注册后的次日。题目注意事项中说明，如果首次进入停留时间跨天，则直接记录为次日留存，因此需要离开时间的数据，不能舍去
• 留存率——新用户中次日留存人数/前一天新用户，共同点在于都来自于新用户集合

2. 保留2位小数——round
3. 需要使用join，注意null
4. group by和order by不要忘记

问题思路总结：

1. 筛选新注册用户——子查询

2. 筛选活跃时间——子查询。

   问题层面看需要再次进入的时间和注册时间相差1天，特殊情况下离开时间和注册时间差1，总体来看相当于寻找比注册时间多1天的对应数据，此时in和out无实际差别，因此使用union将in_time和out_time合为一体

3. 子查询join——需要使用外连接，将新用户子查询全部保留。注意可能出现NULL，总体查询时需要注意处理。条件：uid相同和时间差，注意时间来自的子表。

4. 留存率计算：注意uid来自的子表，注意distinct使用

第二步：根据拆解分块书写代码

由于解题思路分块，那么建议代码书写也进行分块，最终再次组合。子查询多时，推荐使用with…as书写，就不需要分块后复制粘贴成整体，而可以直接呈现思路

1.表a：筛选新用户

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

2. 表b：获取活跃子查询

select uid, date(in_time) as dt
from tb_user_log
union
select uid, date(out_time) as dt
from tb_user_log)

3. 主体框架：和过去一样进行主体框架书写

select 
	a.dt, 
	ifnull(round(count(distinct b.uid) / count(a.uid), 2), 0) as uv_left_rate
from a left join b 
on a.uid = b.uid and b.dt = date_add(a.dt, interval 1 day)
where a.dt like '2021-11%'
group by a.dt
order by a.dt

第三步：拼接完成完整代码

with a as (
  select uid, min(date(in_time)) as dt
  from tb_user_log
  group by uid) 
, b as (
  select uid, date(in_time) as dt
  from tb_user_log
  union
  select uid,date(out_time) as dt
  fromtb_user_log) 
select
  a.dt,
  ifnull(round(count(distinct b.uid) / count(a.uid), 2), 0) as uv_left_rate
from a left join b 
on a.uid = b.uid and b.dt = date_add(a.dt, interval 1 day)
where a.dt like '2021-11%'
group by a.dt
order by a.dt

3.知识点总结

with as语句，也叫做子查询部分（subquery factoring），作用未定义一个SQL片断，该SQL片断会被整个SQL语句所用到。

• 用途：
  提高让SQL语句的可读性
  在UNION ALL的不同部分，作为提供数据的部分，尤其可以进行成本优化，提高速度。

    UNION ALL的每个部分可能相同，如果都去执行一遍的话，成本太高。使用WITH  AS短语，则只要执行一遍即可。
    如果WITH  AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
  

• 基本用法：

  with <表名> as (子查询语句)
  

• 注意事项：

  1. with as只能被select查询块引用后，且需要紧跟使用该子查询的select语句
  2. 在同级select前有多个查询定义的时候，多个子表只能使用一个with，使用逗号分割

     with <表名1> as (子查询语句),<表名2> as (子查询语句)
     

  3. with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率。
  4. 最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来