大数据最新高频笔试题

一、连续问题计算每个用户的连续登录天数题目用户登录表login包含字段user_id、login_date日期格式yyyy-MM-dd。要求输出每个用户的最大连续登录天数以及最近一次连续登录的起始日期和结束日期。示例数据user_idlogin_date12024-01-0112024-01-0212024-01-0312024-01-0522024-01-0122024-01-02期望输出user_idmax_consecutive_daysstart_dateend_date132024-01-012024-01-03222024-01-012024-01-02SQLHive/Spark SQLWITHuser_datesAS(SELECTDISTINCTuser_id,login_dateFROMlogin),date_rankAS(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)ASrnFROMuser_dates),group_flagAS(SELECTuser_id,login_date,DATE_SUB(login_date,rn)ASgroup_date-- 连续登录的组标识FROMdate_rank),consecutive_groupAS(SELECTuser_id,group_date,MIN(login_date)ASstart_date,MAX(login_date)ASend_date,COUNT(1)ASconsecutive_daysFROMgroup_flagGROUPBYuser_id,group_date)SELECTuser_id,MAX(consecutive_days)ASmax_consecutive_days,MAX(start_date)KEEP(DENSE_RANKLASTORDERBYconsecutive_days)OVER(PARTITIONBYuser_id)ASstart_date,MAX(end_date)KEEP(DENSE_RANKLASTORDERBYconsecutive_days)OVER(PARTITIONBYuser_id)ASend_dateFROMconsecutive_groupGROUPBYuser_id;注部分 SQL 方言如 Hive不支持KEEP可使用子查询ROW_NUMBER按consecutive_days DESC排序取第一条。核心思路使用ROW_NUMBER()按日期排序生成序号rn。计算login_date - rn连续登录的该差值相同。按用户和差值分组得到每组起止日期和天数。取最大天数对应的起止日期。关键点需要先对用户去重同一用户同一天只算一次。日期减法函数用DATE_SUB或date_add取决于 SQL 引擎。二、TopN问题每个品类销售额最高的前三名商品题目订单表orders包含字段category品类、product_id商品、sales销售额。要求输出每个品类下销售额排名前3的商品并列按商品ID升序。示例数据categoryproduct_idsales手机A100手机B200手机C200手机D50电脑E300电脑F150期望输出categoryproduct_idsalesrank手机B2001手机C2002手机A1003电脑E3001电脑F1502SQLWITHproduct_salesAS(SELECTcategory,product_id,SUM(sales)AStotal_salesFROMordersGROUPBYcategory,product_id),rankedAS(SELECTcategory,product_id,total_sales,ROW_NUMBER()OVER(PARTITIONBYcategoryORDERBYtotal_salesDESC,product_id)ASrnFROMproduct_sales)SELECTcategory,product_id,total_sales,rnFROMrankedWHERErn3;核心思路先聚合得到每个商品的总销售额。使用ROW_NUMBER()在品类内按销售额降序、商品ID升序排序。筛选排名 ≤3。关键点若允许并列如第二名有两个可用RANK()或DENSE_RANK()但注意会影响前3的数量。一般笔试题要求“销售额相同按商品ID升序”故ORDER BY中加product_id。三、用户留存计算每日新增用户的次日、7日留存率题目登录表user_login字段user_id、login_date。要求计算2024年1月1日到1月10日每天新增用户的次日留存率和7日留存率第7天是否活跃。示例数据user_idlogin_date12024-01-0112024-01-0222024-01-0122024-01-08……SQLWITHfirst_loginAS(SELECTuser_id,MIN(login_date)ASinstall_dateFROMuser_loginWHERElogin_dateBETWEEN2024-01-01AND2024-01-10GROUPBYuser_id),active_datesAS(SELECTDISTINCTuser_id,login_dateFROMuser_loginWHERElogin_dateBETWEEN2024-01-01AND2024-01-17-- 覆盖1月1日后的7天)SELECTf.install_date,COUNT(DISTINCTf.user_id)ASnew_users,ROUND(COUNT(DISTINCTCASEWHENa.login_dateDATE_ADD(f.install_date,1)THENa.user_idEND)*1.0/COUNT(DISTINCTf.user_id),4)ASretention_1d,ROUND(COUNT(DISTINCTCASEWHENa.login_dateDATE_ADD(f.install_date,7)THENa.user_idEND)*1.0/COUNT(DISTINCTf.user_id),4)ASretention_7dFROMfirst_login fLEFTJOINactive_dates aONf.user_ida.user_idGROUPBYf.install_dateORDERBYf.install_date;核心思路计算每个用户的首次登录日新增日。关联用户的活跃日期表。用条件聚合判断在install_date1和install_date7是否有登录。注意日期范围需要覆盖到第7天之后。关键点必须使用LEFT JOIN否则会丢失未回访的用户。分母是新增用户数分子是留存用户数注意去重。日期加减用DATE_ADDMySQL或DATEADDSQL Server等。四、行列转换学生成绩行转列题目成绩表scorestudent_id、subject科目、score。要求输出每个学生各科目的成绩一行展示列名为科目名称语文、数学、英语。示例数据student_idsubjectscore1语文901数学851英语882语文782数学92期望输出student_id语文数学英语190858827892NULLSQLSELECTstudent_id,MAX(CASEWHENsubject语文THENscoreEND)AS语文,MAX(CASEWHENsubject数学THENscoreEND)AS数学,MAX(CASEWHENsubject英语THENscoreEND)AS英语FROMscoreGROUPBYstudent_id;核心思路使用CASE WHEN将科目作为列值。配合聚合函数MAX或SUM把多行压缩为一行。每个学生每个科目只有一个值聚合函数不影响结果。关键点如果每个学生每个科目可能有多个成绩如多次考试需要先聚合或使用SUM求和。列数固定若科目不确定需动态拼接但笔试通常写静态。五、直播最高同时在线人数题目直播间用户进出记录表live_loguser_id、room_id、event_time、event_type‘enter’ 或 ‘exit’。要求计算每个直播间在当天最高同时在线人数。示例数据user_idroom_idevent_timeevent_type11012024-01-01 10:00:00enter11012024-01-01 10:15:00exit21012024-01-01 10:05:00enter21012024-01-01 10:20:00exit期望输出room_idmax_online1012SQLWITHonline_changeAS(SELECTroom_id,event_time,CASEWHENevent_typeenterTHEN1ELSE-1ENDASdeltaFROMlive_logWHEREDATE(event_time)2024-01-01-- 指定日期),online_cntAS(SELECTroom_id,event_time,SUM(delta)OVER(PARTITIONBYroom_idORDERBYevent_time)AScurrent_onlineFROMonline_change)SELECTroom_id,MAX(current_online)ASmax_onlineFROMonline_cntGROUPBYroom_id;核心思路进入事件为 1离开事件为 -1。按时间排序计算累积和窗口函数SUM OVER得到每个时刻的在线人数。取最大值。关键点若同一用户同房间进出时间重叠需要确保离开时间 进入时间一般数据已保证。如果有多个事件同一时间通常先加后减即进入先计算离开后计算可用ORDER BY event_time, event_type DESC调整顺序。六、算法题滑动窗口最大值题目给定整数数组nums和窗口大小k求每个滑动窗口的最大值。示例nums [1,3,-1,-3,5,3,6,7], k3输出[3,3,5,5,6,7]Python 解法双端队列fromcollectionsimportdequedefmaxSlidingWindow(nums,k):dqdeque()# 存储索引保证队首是当前窗口最大值result[]fori,numinenumerate(nums):# 移除不在窗口内的队首索引ifdqanddq[0]i-k1:dq.popleft()# 移除队尾所有小于当前值的索引它们不可能成为最大值whiledqandnums[dq[-1]]num:dq.pop()dq.append(i)# 窗口形成后记录最大值ifik-1:result.append(nums[dq[0]])returnresult核心思路使用单调递减双端队列队首始终是窗口最大值。新元素从队尾加入前弹出所有比它小的元素。每次窗口滑动后队首过期则弹出。复杂度时间 O(n)空间 O(k)。