4-3 SQL经典真题解析

在笔试面试中，会有一些反复出现的，要求我们掌握的题目，本章中会介绍六类经典的问题及解法。在第一次看见问题时，可能并不能马上想到思路，但多加练习后还是能够快速解决的。

1、连续问题

假如有一张表table1，记录了每个用户登录APP的时间，

 

（1）取出连续数据。

现在想取出连续3天以上登录了该APP的用户名单。

针对这种连续的问题，最常规的思路是借助row_number()over()的开窗函数。我们先看一下使用开窗函数的效果：

select *, row_number()over(partition by id order by date)as rk
from table1

select b.id, b.results, count(1)as num
from
    (select a.*,(a.date - a.rk)as results
    from
        (select *,row_number()over(partition by id order by date)as rk
        fromm table1
        )a
     )b
group by b.id, b.results
having num>=3 

（2）取出不连续数据

在上面一题中，我们通过用日期减去排序结果的方式得到了连续数据，如果我想取出连续3天及以上未登录的用户，应该怎么做呢？

我们试着用一个新窗口函数，lag()over()。lag窗口函数可以将指定列记录向下平移，例如:

select *, lag(date)over(partition by id order by date)as date2
from table1 

通过观察date和date2列，我们发现两者之差和用户连续未登录天数有关。例如，对于用户2来说，date=20200106时，date2=20200102，就是说用户2在1月2日登陆后，3、4、5三天都没有登陆，第二次登陆已经是1月6日了。因此，我们可以写成：

select b.*
from
    (select a.*,(a.date-a.date2-1)as num
     from
         (select *,lag(date)over(partition by id order by date)as date2
         from table1
         )a
     )b
where b.num>=3 

2、新增与留存问题

现有table1,记录了用户id以及登录日期，表结构如下：

 

（1）新增用户数统计

在进行用户指标监控时，拉新人数往往是很重要的指标之一。我们来看一下如何获取每天新增用户的数量。以table1为例，最简单的思路就是先用row_number开窗函数获取用户的登陆时间的排序，取出排序为1的记录后，在时间维度对用户id进行去重聚合即可：

select a.date, count(distinct id)as num
from
   (select *, row_number()over(partition by id order by date)as rk)
    from table1
    )a
where a.rk=1
group by a.date 

（2）用户留存统计

若想统计3日留存人数（即在第一次登陆后，3天内有过第二次登陆行为的用户数量）可以考虑将table2表做自连接，在on的条件中通过限定date的差值控制在1-3的范围内进行筛选。

select a.*, b.date
from table1 a
left join table1 b on a.id=b.id and b.date-a.date<=3 and b.date-a.date>=1  

select aa.date, count(a