Hive专题：数据开发面试高频题（TopN、留存、连续登录等）

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 页面访问路径Clickstream4.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)tWHERErn3;关键点解析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)tWHEREdr2;2. 用户留存率计算2.1 次日/7日/30日留存率业务场景计算某日新增用户在次日、第7日、第30日仍活跃的比例。核心思路定义“新增用户”首次登录日期或指定注册日期。定义“活跃”当天有登录行为记录在登录日志表。通过自关联或左连接计算留存。表结构假设user_activeuser_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.dtDATE_ADD(n.first_dt,1)THENn.user_idEND)ASday1_ret,COUNT(DISTINCTCASEWHENa7.dtDATE_ADD(n.first_dt,7)THENn.user_idEND)ASday7_ret,COUNT(DISTINCTCASEWHENa30.dtDATE_ADD(n.first_dt,30)THENn.user_idEND)ASday30_retFROMnew_users nLEFTJOINuser_active a1ONn.user_ida1.user_idANDa1.dtDATE_ADD(n.first_dt,1)LEFTJOINuser_active a7ONn.user_ida7.user_idANDa7.dtDATE_ADD(n.first_dt,7)LEFTJOINuser_active a30ONn.user_ida30.user_idANDa30.dtDATE_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_dtBETWEEN2026-04-01AND2026-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_ida.user_idWHEREa.dtBETWEENn.first_dtANDDATE_ADD(n.first_dt,30)GROUPBYn.first_dt,DATEDIFF(a.dt,n.first_dt))SELECTfirst_dt,MAX(CASEWHENday_gap1THENret_cntELSE0END)/MAX(CASEWHENday_gap0THENret_cntELSE0END)ASday1_rate,MAX(CASEWHENday_gap3THENret_cntELSE0END)/MAX(CASEWHENday_gap0THENret_cntELSE0END)ASday3_rate,MAX(CASEWHENday_gap7THENret_cntELSE0END)/MAX(CASEWHENday_gap0THENret_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/maxWITHuser_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,daysFROMgroupedWHEREdays3ORDERBYuser_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(CASEWHENactionviewTHEN1ELSE0END)AShas_view,MAX(CASEWHENactioncartTHEN1ELSE0END)AShas_cart,MAX(CASEWHENactionpayTHEN1ELSE0END)AShas_payFROMuser_behaviorWHEREdt2026-04-01GROUPBYuser_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_SETCONCAT_WSSELECTuser_id,CONCAT_WS(,,COLLECT_SET(tag))AStags_strFROMuser_tagsGROUPBYuser_id;5.2 列转行一行拆成多行业务场景将逗号分隔的标签字符串拆分为多行。SQL示例使用LATERAL VIEW EXPLODESELECTuser_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_currentN-- 保留已关闭历史UNIONALL-- 新增/变化的当前记录从ods_new取SELECTuser_id,level,current_dateASstart_date,9999-12-31ASend_date,YFROMods_userUNIONALL-- 关闭需要过期的旧记录SELECTuser_id,level,start_date,DATE_SUB(current_date,1),NFROMdim_user oldWHEREold.is_currentYANDEXISTS(SELECT1FROMods_user newWHEREnew.user_idold.user_idANDnew.level!old.level);6.2 查询指定时间点的维度值业务场景查询2026-03-15当天用户等级。SQL示例SELECTuser_id,levelFROMdim_userWHEREis_currentY-- 如果是查询今天直接取当前UNIONALL-- 查询历史日期SELECTuser_id,levelFROMdim_userWHEREstart_date2026-03-15ANDend_date2026-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_activeWHEREdtDATE_SUB(CURRENT_DATE,30)GROUPBYdtORDERBYdt;以上题目覆盖了Hive SQL面试中80%的高频考点。建议读者在理解思路后结合实际数据表进行练习并熟练使用EXPLAIN优化执行计划。