Hive专题：数据开发面试高频题（TopN、留存、连续登录等）
本文聚焦Hive SQL在数据分析面试中的高频考点，每道题提供业务场景、核心思路、完整SQL示例及关键点解析。所有代码均基于Hive窗口函数、日期函数、条件聚合等特性编写，可直接在Hive环境运行测试。
📑 目录
- 分组TopN问题
- 1.1 每个部门薪资最高的3名员工
- 1.2 每个品类销量前2的商品（并列处理）
- 用户留存率计算
- 2.1 次日/7日/30日留存率
- 2.2 多日连续留存（留存矩阵）
- 连续登录/活跃天数问题
- 3.1 用户最大连续登录天数
- 3.2 连续3天登录的用户
- 3.3 连续登录超过N天的用户及起止日期
- 用户行为序列与漏斗分析
- 4.1 页面访问路径（Clickstream）
- 4.2 转化漏斗（浏览→加购→支付）
- 行列转换
- 5.1 行转列（多行合并为一行）
- 5.2 列转行（一行拆成多行）
- 拉链表设计与回滚查询
- 6.1 拉链表的每日增量更新
- 6.2 查询指定时间点的维度值
- UV、PV、留存等常见指标统计
- 7.1 每日新增用户数
- 7.2 用户行为漏斗（GROUP BY + 条件计数）
1. 分组TopN问题
1.1 每个部门薪资最高的3名员工
业务场景：查询每个部门中薪资排名前三的员工，若薪资相同则按工号排序。
核心思路：使用窗口函数ROW_NUMBER()（无并列）或RANK()（并列跳跃）/DENSE_RANK()（并列不跳跃）。
SQL示例：
-- 示例表结构CREATETABLEemp(emp_idINT,name STRING,dept STRING,salaryDECIMAL(10,2));-- 查询每个部门薪资最高的3人（无并列，若薪资相同按emp_id升序）SELECTdept,name,salary,rnFROM(SELECTdept,name,salary,ROW_NUMBER()OVER(PARTITIONBYdeptORDERBYsalaryDESC,emp_id)ASrnFROMemp)tWHERErn<=3;关键点解析：
PARTITION BY dept：按部门分组ORDER BY salary DESC：薪资降序- 若要求并列处理（如两个第一，则跳过第二名），使用
RANK()；若并列不跳过，使用DENSE_RANK()。
1.2 每个品类销量前2的商品（并列处理）
业务场景：电商平台，每个商品品类下按销量排序，销量相同时均入选。
SQL示例：
SELECTcategory,product_name,salesFROM(SELECTcategory,product_name,sales,DENSE_RANK()OVER(PARTITIONBYcategoryORDERBYsalesDESC)ASdrFROMproduct_sales)tWHEREdr<=2;2. 用户留存率计算
2.1 次日/7日/30日留存率
业务场景：计算某日新增用户在次日、第7日、第30日仍活跃的比例。
核心思路：
- 定义“新增用户”：首次登录日期（或指定注册日期）。
- 定义“活跃”：当天有登录行为（记录在登录日志表）。
- 通过自关联或左连接计算留存。
表结构假设：
user_active：user_id, dt（每个用户每天一条记录，去重）
SQL示例（计算2026-04-01新增用户的后续留存）：
-- 第一步：获取每日新增用户WITHnew_usersAS(SELECTuser_id,MIN(dt)ASfirst_dtFROMuser_activeGROUPBYuser_idHAVINGMIN(dt)='2026-04-01'-- 也可筛选指定日期),-- 第二步：计算各留存日期的活跃用户数retentionAS(SELECTn.first_dt,COUNT(DISTINCTn.user_id)ASnew_cnt,COUNT(DISTINCTCASEWHENa1.dt=DATE_ADD(n.first_dt,1)THENn.user_idEND)ASday1_ret,COUNT(DISTINCTCASEWHENa7.dt=DATE_ADD(n.first_dt,7)THENn.user_idEND)ASday7_ret,COUNT(DISTINCTCASEWHENa30.dt=DATE_ADD(n.first_dt,30)THENn.user_idEND)ASday30_retFROMnew_users nLEFTJOINuser_active a1ONn.user_id=a1.user_idANDa1.dt=DATE_ADD(n.first_dt,1)LEFTJOINuser_active a7ONn.user_id=a7.user_idANDa7.dt=DATE_ADD(n.first_dt,7)LEFTJOINuser_active a30ONn.user_id=a30.user_idANDa30.dt=DATE_ADD(n.first_dt,30)GROUPBYn.first_dt)SELECTfirst_dt,new_cnt,day1_ret/new_cntASday1_retention_rate,day7_ret/new_cntASday7_retention_rate,day30_ret/new_cntASday30_retention_rateFROMretention;优化写法（使用LATERAL VIEW或多次左连接均可，上述清晰易读）。
2.2 多日连续留存（留存矩阵）
业务场景：批量计算某段时间内每天新增用户的次日、3日、7日留存，生成留存矩阵报表。
SQL示例：
WITHnew_usersAS(SELECTuser_id,MIN(dt)ASfirst_dtFROMuser_activeGROUPBYuser_idHAVINGfirst_dtBETWEEN'2026-04-01'AND'2026-04-07'),active_retAS(SELECTn.first_dt,DATEDIFF(a.dt,n.first_dt)ASday_gap,COUNT(DISTINCTn.user_id)ASret_cntFROMnew_users nJOINuser_active aONn.user_id=a.user_idWHEREa.dtBETWEENn.first_dtANDDATE_ADD(n.first_dt,30)GROUPBYn.first_dt,DATEDIFF(a.dt,n.first_dt))SELECTfirst_dt,MAX(CASEWHENday_gap=1THENret_cntELSE0END)/MAX(CASEWHENday_gap=0THENret_cntELSE0END)ASday1_rate,MAX(CASEWHENday_gap=3THENret_cntELSE0END)/MAX(CASEWHENday_gap=0THENret_cntELSE0END)ASday3_rate,MAX(CASEWHENday_gap=7THENret_cntELSE0END)/MAX(CASEWHENday_gap=0THENret_cntELSE0END)ASday7_rateFROMactive_retGROUPBYfirst_dtORDERBYfirst_dt;3. 连续登录/活跃天数问题
3.1 用户最大连续登录天数
业务场景：计算每个用户历史上最长的连续登录天数。
核心思路：
- 对每个用户按日期排序，计算与前一日的日期差。
- 如果日期差=1，则连续；否则中断。
- 使用
SUM(flag)构建连续分组ID，再按分组计数。
SQL示例：
WITHuser_datesAS(SELECTuser_id,dt,LAG(dt,1,dt)OVER(PARTITIONBYuser_idORDERBYdt)ASprev_dtFROM(SELECTDISTINCTuser_id,dtFROMuser_active)t-- 去重),date_diffAS(SELECTuser_id,dt,CASEWHENDATEDIFF(dt,prev_dt)=1THEN0ELSE1ENDASis_new_groupFROMuser_dates),group_idAS(SELECTuser_id,dt,SUM(is_new_group)OVER(PARTITIONBYuser_idORDERBYdt)ASgroup_seqFROMdate_diff)SELECTuser_id,MAX(continuous_days)ASmax_continuous_daysFROM(SELECTuser_id,group_seq,COUNT(*)AScontinuous_daysFROMgroup_idGROUPBYuser_id,group_seq)tGROUPBYuser_id;简化版（利用日期减排名技巧）：
WITHuser_datesAS(SELECTDISTINCTuser_id,dtFROMuser_active),rankedAS(SELECTuser_id,dt,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdt)ASrnFROMuser_dates)SELECTuser_id,MAX(continuous_days)ASmax_continuous_daysFROM(SELECTuser_id,COUNT(*)AScontinuous_daysFROMrankedGROUPBYuser_id,DATE_SUB(dt,rn)-- 关键：连续登录时 dt-rn 为常量)tGROUPBYuser_id;3.2 连续3天登录的用户
业务场景：找出至少连续3天登录的用户。
SQL示例（基于日期减排名技巧）：
WITHuser_datesAS(SELECTDISTINCTuser_id,dtFROMuser_active),rankedAS(SELECTuser_id,dt,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdt)ASrnFROMuser_dates),groupedAS(SELECTuser_id,DATE_SUB(dt,rn)ASgroup_flagFROMranked)SELECTDISTINCTuser_idFROMgroupedGROUPBYuser_id,group_flagHAVINGCOUNT(*)>=3;3.3 连续登录超过N天的用户及起止日期
业务场景：不仅找出用户，还要显示每次连续登录的起始和结束日期。
SQL示例（在分组基础上取min/max）：
WITHuser_datesAS(SELECTDISTINCTuser_id,dtFROMuser_active),rankedAS(SELECTuser_id,dt,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdt)ASrnFROMuser_dates),groupedAS(SELECTuser_id,DATE_SUB(dt,rn)ASgroup_flag,MIN(dt)ASstart_date,MAX(dt)ASend_date,COUNT(*)ASdaysFROMrankedGROUPBYuser_id,DATE_SUB(dt,rn))SELECTuser_id,start_date,end_date,daysFROMgroupedWHEREdays>=3ORDERBYuser_id,start_date;4. 用户行为序列与漏斗分析
4.1 页面访问路径（Clickstream）
业务场景：统计用户从首页到商品详情页的路径，计算各步骤转化。
核心思路：使用LAG或LEAD获取前后页面，或对每个用户的页面按时间排序后拼接。
SQL示例（获取用户一次会话内的页面跳转序列）：
WITHuser_actionsAS(SELECTuser_id,page,action_time,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYaction_time)ASseqFROMclickstream)SELECTuser_id,COLLECT_LIST(page)ASpage_pathFROMuser_actionsGROUPBYuser_id;4.2 转化漏斗（浏览→加购→支付）
业务场景：统计某日所有用户中，完成浏览商品、加入购物车、支付三个步骤的人数及转化率。
核心思路：通过条件聚合，判断每个用户是否完成各步骤。
SQL示例：
WITHuser_funnelAS(SELECTuser_id,MAX(CASEWHENaction='view'THEN1ELSE0END)AShas_view,MAX(CASEWHENaction='cart'THEN1ELSE0END)AShas_cart,MAX(CASEWHENaction='pay'THEN1ELSE0END)AShas_payFROMuser_behaviorWHEREdt='2026-04-01'GROUPBYuser_id)SELECTCOUNT(user_id)AStotal_users,SUM(has_view)ASview_cnt,SUM(has_cart)AScart_cnt,SUM(has_pay)ASpay_cnt,SUM(has_cart)/SUM(has_view)ASview_to_cart_rate,SUM(has_pay)/SUM(has_cart)AScart_to_pay_rateFROMuser_funnel;5. 行列转换
5.1 行转列（多行合并为一行）
业务场景：将每个用户的多个标签合并为一个逗号分隔的字符串。
SQL示例（使用COLLECT_SET+CONCAT_WS）：
SELECTuser_id,CONCAT_WS(',',COLLECT_SET(tag))AStags_strFROMuser_tagsGROUPBYuser_id;5.2 列转行（一行拆成多行）
业务场景：将逗号分隔的标签字符串拆分为多行。
SQL示例（使用LATERAL VIEW EXPLODE）：
SELECTuser_id,tagFROM(SELECTuser_id,SPLIT(tags_str,',')AStags_arrayFROMuser_tags_table)t LATERALVIEWEXPLODE(tags_array)tmpAStag;6. 拉链表设计与回滚查询
6.1 拉链表的每日增量更新
业务场景：用户维度表（如会员等级）缓慢变化，需要保留历史状态。
核心思路：使用类型2缓慢变化维度，每日从ODS获取最新数据，与现有拉链表对比，关闭变化的旧记录，新增当前记录。
SQL示例（见前文1.1节拉链表示例，此处简化）：
-- 拉链表结构：user_id, level, start_date, end_date, is_current-- 每日合并逻辑INSERTOVERWRITETABLEdim_userSELECT*FROMdim_userWHEREis_current='N'-- 保留已关闭历史UNIONALL-- 新增/变化的当前记录（从ods_new取）SELECTuser_id,level,current_dateASstart_date,'9999-12-31'ASend_date,'Y'FROMods_userUNIONALL-- 关闭需要过期的旧记录SELECTuser_id,level,start_date,DATE_SUB(current_date,1),'N'FROMdim_user oldWHEREold.is_current='Y'ANDEXISTS(SELECT1FROMods_user newWHEREnew.user_id=old.user_idANDnew.level!=old.level);6.2 查询指定时间点的维度值
业务场景：查询2026-03-15当天用户等级。
SQL示例：
SELECTuser_id,levelFROMdim_userWHEREis_current='Y'-- 如果是查询今天，直接取当前UNIONALL-- 查询历史日期SELECTuser_id,levelFROMdim_userWHEREstart_date<='2026-03-15'ANDend_date>='2026-03-15';7. UV、PV、留存等常见指标统计
7.1 每日新增用户数
业务场景：统计每天首次登录的用户数。
SQL示例：
WITHfirst_loginAS(SELECTuser_id,MIN(dt)ASfirst_dtFROMuser_activeGROUPBYuser_id)SELECTfirst_dt,COUNT(user_id)ASnew_usersFROMfirst_loginGROUPBYfirst_dtORDERBYfirst_dt;7.2 每日活跃用户数（DAU）及周同比
SQL示例：
SELECTdt,COUNT(DISTINCTuser_id)ASdauFROMuser_activeWHEREdt>=DATE_SUB(CURRENT_DATE,30)GROUPBYdtORDERBYdt;以上题目覆盖了Hive SQL面试中80%的高频考点。建议读者在理解思路后，结合实际数据表进行练习，并熟练使用EXPLAIN优化执行计划。
