推荐阅读文章列表

大数据开发面经汇总【持续更新...】

我的大数据学习之路

大数据开发面试笔记V6.0

SQL题目

来自拼多多数据研发一面

• 有一张用户行为日志表ods_usr_log, 包含用户id（user_id）和页面id（page_id）以及进入页面时间（in_ts）
• 问题：统计每天进入A页面后，立刻进入B页面，又进入C页面的用户数【注意：进入C页面之前可能进入过其他页面】

答案解析

模拟数据

insert into ods_usr_log(user_id, page_id, in_ts) values 
(1, 'A', '2020-1-1 12:01:03'),
(2, 'A', '2020-1-1 12:01:04'),
(3, 'A', '2020-1-1 12:01:05'),
(1, 'B', '2020-1-1 12:03:03'),
(1, 'A', '2020-1-1 12:04:03'),
(1, 'C', '2020-1-1 12:06:03'),
(1, 'D', '2020-1-1 12:11:03'),
(2, 'A', '2020-1-1 12:07:04'),
(3, 'C', '2020-1-1 12:02:05'),
(2, 'C', '2020-1-1 12:09:03'),
(2, 'A', '2020-1-1 12:10:03'),
(4, 'A', '2020-1-1 12:01:03'),
(4, 'C', '2020-1-1 12:11:05'),
(4, 'D', '2020-1-1 12:15:05'),
(1, 'A', '2020-1-2 12:01:03'),
(2, 'A', '2020-1-2 12:01:04'),
(3, 'A', '2020-1-2 12:01:05'),
(1, 'B', '2020-1-2 12:03:03'),
(1, 'A', '2020-1-2 12:04:03'),
(1, 'C', '2020-1-2 12:06:03'),
(2, 'A', '2020-1-2 12:07:04'),
(3, 'B', '2020-1-2 12:08:05'),
(3, 'E', '2020-1-2 12:09:05'),
(3, 'D', '2020-1-2 12:11:05'),
(2, 'C', '2020-1-2 12:09:03'),
(4, 'E', '2020-1-2 12:05:03'),
(4, 'B', '2020-1-2 12:06:03'),
(4, 'E', '2020-1-2 12:07:03'),
(2, 'A', '2020-1-2 12:10:03');

思路分析

• 需求拆解如下：
  • 要想求按照 A->B->C 序列的用户，就需要知道每个用户的行为路径
  • 这个不难求得，只需要将每个用户进入的页面按照进入时间进行concat即可
  • 那么如何保证A到B是直接到达的，并且B到C是可能间接到达的呢？
  • 显然可以使用正则匹配 like %A,B%C%

具体代码

select date(in_ts) as dt
      ,count(distinct user_id) as cnt
from
(
    select user_id
          ,in_ts
          ,concat_ws(',',collect_set(page_id) over(partition by user_id order by in_ts)) as page_list
    from ods_usr_log
) t
where page_list like '%A,B%C%'
group by date(in_ts)
;