大数据笔试必看SQL篇(牛刀小试)

大家好，我是老兵。

本期将为大家介绍大数据面试中常见的SQL真题。我尽量选取了几种不同场景的SQL计算方法，案例具有很强的代表性，在此基础可以衍生出很多场景，希望伙伴们看后可以进行思考。

文章旨在帮助大家抱团取暖，希望对小伙伴笔试会有所帮助。

1 app连续三天登录

题目介绍

用户登录行为日志中记录用户id、登录时间字段，统计日志中连续三天登录的用户数，数据示意如下:

uid	login_time
001	2022-11-01
001	2022-11-03
002	2022-11-01
002	2022-11-02
002	2022-11-03

题目分析

这是个很经典的SQL笔试题，也是出镜率最高的类型。连续登录的含义如上图（用户002在2022-11-01~2022-11-03均有记录)，指的是用户在某一时间段内每天均有登录记录。

通过分析可知连续登录时用户登录时间和其对应排名差值相等，这里使用uid开窗排序 + date_sub(dt,rank)来计算。

解题方法

1) 实现思路

• 将用户分组并按照时间排序，并记录rank排名

• 计算dt-rank的差值，差值与用户共同分组

• 统计count并找出 count > 3的用户

2）SQL代码

select
  userid
  ,min(dt) as start_date
  ,max(dt) as end_date
  ,count(1) as times
from
(
  select 
    userid
    ,dt
    ,date_sub(dt, rn) as date_diff
    from
    (
      select 
        userid
        ,dt
        ,row_number() over(partition by
          userid order by dt) as rn
      from 
        user_tables
    )
)
group by 
  userid, date_diff
having times >= 3

2 日/周/月留存

题目介绍

用户登录行为日志中记录用户id、登陆时间、 登录app字段，计算app某天的3日/7日/14日/30日用户留存。数据示意如下:

uid	dt	appid
001	2022-11-01	app01
002	2022-11-03	app01
001	2022-11-03	app02
002	2022-11-02	app01
001	2022-11-05	app01

题目分析

在互联网场景中用户留存是衡量APP用户活跃的重要指标之一，留存越高，则代表用户粘性越好。留存的含义为用户在时间t1活跃同时在时间t2再次活跃。

计算留存需要找到两个时间点，一个是统计日期t1（如2022-11-01），另一个点为留存时间t2(2022-11-01后的7日/14日/30日区间)，两段数据进行关联且均有记录，最后取日期差即可得到留存。

解题方法

1）实现思路

• 获取统计日期时间段数据并根据dt,appid,uid分组groupby

• 获取留存时间段数据并根据dt,appid,uid分组groupby

• join关联获取时间差，分别统计3日/14日/21日/30日留存

2）SQL代码

select 
  t1.appid,
  count(t1.uid) as active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=1 then t2.uid end) as day2_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=2 then t2.uid end) as day3_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=6 then t2.uid end) as day7_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=13 then t2.uid end) as day14_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=29 then t2.uid end) as day30_active_users,
  t1.dt
from 
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from user_tables
where dt='${startDate}'
group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')
)t1
left join
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from user_tables
where dt>'${startDate}' and dt<='${startDate+29d}'
group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')
) t2
on t1.appid=t2.appid and t1.uid=t2.uid
group by t1.appid,t1.dt

3 某时刻app最多在线人数

题目介绍

用户行为日志中记录用户id、appid、进入时间、退出时间字段，计算同一时刻的app的最大人数，数据示意如下:

uid	app_id	in_time	out_time
001	app01	2022-11-01 11:00:01	2022-11-01 11:01:01
002	app02	2022-11-01 11:03:02	2022-11-01 11:05:02
003	app01	2022-11-01 11:03:09	2022-11-01 11:05:10
004	app02	2022-11-01 11:07:10	2022-11-01 11:11:10
004	app01	2022-11-01 11:09:10	2022-11-01 11:08:10

题目分析

此为经典状态计算SQL，常用于统计某时刻某状态的最大在线人数（直播、商场、车进出流等）。

此类问题最重要的是计算瞬时的最大计数，采用编码+合并的方法，将进标志为+1，出标志为-1，最后union结果并根据appid进行sum（）开窗并排序。

解题方法

1）实现思路

• 计算瞬时计数。查询进出记录同时设置标志位，结果union合并

• 根据appid开窗sum，组内根据dt升序和标志位降序

2）SQL代码

SELECT
  app_id,
  MAX(cnt) max_uv
FROM (
  SELECT
    appid,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) cnt
  FROM (
    SELECT 
      app_id, in_time dt, 1 diff
    FROM user_tables
    WHERE appid != 0
    UNION ALL
    SELECT 
      app_id, out_time dt, -1 diff
    FROM user_tables
    WHERE appid != 0) t1 
) t2
GROUP BY 1
ORDER BY 2 DESC

4 计算部门除去最高和最低的平均收入

题目介绍

部门员工基本信息表中记录部门id、员工id、收入字段，计算部门员工的平均收入，数据示意如下:

dep_no	uid	salary
d_001	001	18700
d_001	001	23000
d_001	002	15000
d_001	002	11000
d_001	002	30000

题目分析

使用窗口函数降序和升序分别排序取出最高和最低收入，计算平均收入。

解题方法

1）实现思路

• 使用开窗函数，分别根据收入组内正序倒序排序

• 计算平均收入

2）SQL代码

select a.dep_no，avg(a.salary)
from  
 (
 select *, rank() over( partition by dep_no order by salary ) as rk_1
 , rank() over( partition by dep_no order by salary desc) as rk_2 
 from user_tables
 )  a 
group by a.dep_no
where a.rk_1 >1 and a.rk_2 >1 

5 计算分类top3销售商品

题目介绍

商品浏览表记录：商品id、用户id字段，计算每个商品类别下最受欢迎的Top3产品，数据示意如下:

p_id	uid
p_001	001
p_002	002
P_001	003
p_002	004
P_001	005

题目分析

这是一个非常常见计算前几名TopN的场景，一般先计算每个商品被每个用户浏览的次数，最后开窗计算商品浏览次数排序。

解题方法

1）实现思路

• 计算每个商品下每个用户的浏览次数，放在临时表

• 开窗函数计算每个商品被浏览次数排名

• 计算每个商品浏览前3的用户

2）SQL代码

select
p_id,
uid,
cnt
from
(select
    p_id,
    uid,
    cnt,
    rank() over(partition by p_id order by cnt) rn
  from (
     select
       p_id,
       uid,
       count(*) cnt
    from user_tables
    group by
       p_id,
       uid
  )t1
) t2
where rn<=3;

本期为笔试系列初试牛刀，后续将持续更新更多SQL笔试真题，欢迎大家踊跃投稿和建议。