【1.查询至少连续3天下单的用户】
思路1(使用lead):
- distinct user_id,create_date去重,确保每个用户每天只有一条访问记录
- lead(create_date,2,‘9999-12-31’) over(partition by user_id order by create_date)根据用户分区,订单日期排序,取后2行的订单日期(取不到则返回’9999-12-31’)
- 用datediff计算【订单日期】与【后2行订单日期】的差值,如果等于2则说明存在连续3天下单
- 筛选出datediff等于2的记录,distinct user_id即可
思路2(使用row_number):
- distinct user_id,create_date去重,确保每个用户每天只有一条访问记录
- row_number() over(partition by user_id order by create_date)按用户分区,按访问日期排序,添加序号
- 每个日期与序号相减获得一个新的日期
- group by user_id,diff对用户和新的日期进行分组,然后count()统计日期,判断count() >=3
【2. 用户登录日志表user_id,visit_date 用sql查询出近30天连续访问7天以上的用户数量】
思路:
- 筛选近30天每个用户的访问记录,并去重确保同一个用户当天只有一条记录
- 对用户分组按访问日期排序,添加序号
- 每条记录的访问日期与对应的序号相减,获得一个新的日期字段
- group by用户,新的日期字段,统计个数,having筛选大于等于7
select user_id,diff,count(*) as cnt
from
(
select
user_id,
visit_date,
date_sub(visit_date,row_number() over(partition by user_id order by visit_date)) as diff
from
(
select distinct user_id,visit_date
from table
where visit_date >= date_sub(current_date,30)
) t1
) t2
group by user_id,diff
having cnt >= 7
【3. 计算某段时间内同时在线人数最大值】
核心思路:
按时间排序,逐条处理(从上到下累加),获取每个时刻在线人数,取最大值
具体实现:
- 筛选出每个人的登陆记录并加一个字段1(id,登陆时间,1) union all 每个人的登出记录并加一个字段-1(id,登出时间,-1)
- 按照登陆/登出时间升序排序
- sum()并开窗,窗口范围首行到当前行,即计算累加值(此时数据表示了每一个时刻的在线总人数)
- max()计算累加值中的最大值(即同时在线人数最大值)
【4. 同一个用户相邻两次访问记录小于60s,则认为属于一个会话。现需对同一会话的访问记录增加会话id字段 】(会话划分问题)
核心思路:
按每个用户的会话时间排序,找到每个用户会话的起点并加标签1,不是起点则加标签0,同一用户对标签进行累加,每个用户不同会话则会有不同的标签
思路:
- 对每个用户开窗并按访问时间排序,用lag()取上一次访问时间,取不到上一条则默认为0
- 每条访问记录减去上一次访问时间,差值>60则赋值1,否则赋值0(用1表示每个会话的起点)
- 对每个用户开窗,窗口范围首行到当前行,用sum()进行累加
- 用user_id拼接上累加值,表示每个会话
【5. 用户登陆记录表(user_id,login_datetime),每行表达一个用户何时登陆,求各用户最长的连续登陆天数(间断1天也算连续)】(间断连续日期判断问题)
核心思路1:
用explode()将中间空1天的记录补充上,转化成连续日期的判断问题
(比如2条记录2021-12-03、2021-12-05 变成3条记录 2021-12-03、2021-12-04、2021-12-05)
思路1:
- 每个用户按照登陆日期去重
- 每个用户按登陆日期升序排列,用lead()取该用户下一次的登陆时间
- 如果下次登陆日期和本次登陆日期相差为2,则用array()创建数组,包含本次登录日期、本次登录日期+1
- 用explode()函数进行炸裂,此时就补充上了空缺日期
- 按照【题目1】进行连续日期判断即可
核心思路2:
找到每次连续区间的起始日期,赋1,其它日期赋0,将问题转化为会话划分问题,取每个会话中的max日期-min日期,即为连续天数
思路2:
- 每个用户按照登陆日期去重
- 每个用户按登陆日期升序排列,用lag()取该用户上一次的登陆时间
- 本次登录日期 减去 上次登录日期 >2的话则赋1,否则赋0
- 对每个用户开窗,窗口范围首行到当前行,用sum()进行累加
- 对每个用户、会话标签进行分组,取每个会话中max(login_date) - min(login_date) + 1即为最长连续天数。
【6. 品牌优惠周期表,记录每个品牌每个优惠活动的周期。需统计每个品牌的优惠总天数,如日期重合则只算1天】(日期交叉问题)
核心思路1:
修改每条活动周期的开始日期,将同一品牌日期重合的部分去除掉(保证每个活动周期时间不重合),然后按品牌分组,汇总(活动结束日期 - 活动开始日期+1)的天数(转化成日期不交叉的统计问题)
思路1:
- 每个品牌按活动开始日期排序,对品牌分区进行开窗,获取最大的结束日期max_end_date,窗口范围:首行到前一行
- 修改每条记录的start_date,如果start_date大于max_end_date,则不变,否则开始日期改为max_end_date+1
- 若new_start_date为空,则取原始的start_date
- 剔除new_start_date > end_date的记录
- 按品牌分组,sum(end_date - new_start_date + 1)获取每个品牌的总优惠天数
核心思路2:
将每个活动周期用explode展开成多个连续的日期,然后按品牌分组统计distinct日期(用distinct去掉日期的交叉),即可获得每个品牌总活动时长
思路2:
- 获取每个活动周期的时长
- 使用suplit(repeat(‘,’,diff),‘,’)建立与活动周期对应的数组
- 使用posexplode()进行炸裂,将其扩充为对应的行数,并添加上序号(也可以使用开窗函数添加序号)
- 每个开始日期与pos相加获得新的日期
- group by品牌,count(distinct event_date)获得每个品牌的总活动时长