BI工程师招聘笔试题与参考答案2025年

BI工程师招聘笔试题与参考答案2025年一、SQL与数据处理（本部分共3题，总分30分）题目1（10分）：用户行为数据分析某电商平台用户行为表`user_behavior`结构如下（字段：user_idINT,item_idINT,behavior_typeSTRING,tsTIMESTAMP），其中`behavior_type`包含'pv'（浏览）、'cart'（加购）、'buy'（购买）三种行为。要求计算2024年1月用户的“7日留存率”，定义为：1月1日首次访问的用户中，在首次访问后的7天内（含第7天）至少有1次活跃行为（pv/cart/buy）的用户占比。参考答案：步骤1：筛选2024年1月1日的首次访问用户。使用窗口函数按`user_id`分区，按`ts`排序，取第一条记录为首次访问时间。步骤2：关联用户后续7天内的行为记录，判断是否存在活跃行为。步骤3：计算留存用户数与总用户数的比值。SQL代码示例：```sqlWITHfirst_visitAS(SELECTuser_id,MIN(ts)ASfirst_visit_tsFROMuser_behaviorWHEREDATE(ts)='2024-01-01'ANDbehavior_typeIN('pv','cart','buy')GROUPBYuser_id),retention_usersAS(SELECTfv.user_id,CASEWHENCOUNT(ub.user_id)>0THEN1ELSE0ENDASis_retainedFROMfirst_visitfvLEFTJOINuser_behaviorubONfv.user_id=ub.user_idANDub.tsBETWEENfv.first_visit_tsANDfv.first_visit_ts+INTERVAL'7days'ANDub.behavior_typeIN('pv','cart','buy')GROUPBYfv.user_id)SELECTSUM(is_retained)100.0/COUNT(user_id)ASretention_rate_7dFROMretention_users;```题目2（10分）：大表查询性能优化现有一张订单表`orders`（字段：order_idBIGINT,user_idINT,order_amountDECIMAL(10,2),create_timeTIMESTAMP,statusTINYINT），数据量约5000万条。业务需要查询“2024年Q1客单价（订单金额/用户数）周环比变化”，当前查询耗时12秒，需优化。请说明优化思路并写出优化后的SQL。参考答案：优化思路：1.索引优化：在`create_time`和`user_id`上创建复合索引（或覆盖索引），减少全表扫描；2.分区表：按`create_time`做时间分区（如按月分区），缩小查询范围；3.预计算汇总：每日/周预计算用户订单金额，存储到汇总表`weekly_sales`（字段：week_startDATE,user_idINT,total_amountDECIMAL(10,2)），减少实时计算量。优化后SQL（假设使用汇总表）：```sqlWITHweekly_dataAS(SELECTDATE_TRUNC('week',create_time)ASweek_start,user_id,SUM(order_amount)AStotal_amountFROMordersWHEREcreate_timeBETWEEN'2024-01-01'AND'2024-03-31'ANDstatus=1--仅统计有效订单GROUPBY1,2),weekly_avgAS(SELECTweek_start,SUM(total_amount)/COUNT(DISTINCTuser_id)ASavg_priceFROMweekly_dataGROUPBYweek_start)SELECTweek_start,avg_price,(avg_price-LAG(avg_price)OVER(ORDERBYweek_start))/LAG(avg_price)OVER(ORDERBYweek_start)100ASqoq_growthFROMweekly_avgORDERBYweek_start;```题目3（10分）：数据清洗与异常值处理某平台会员表`members`存在以下问题：-手机号（mobile）字段存在重复（同一用户不同记录）；-年龄（age）字段有缺失值（NULL）和异常值（如-5、200）；-注册时间（reg_time）部分记录格式错误（如'2024/02/30'）。请设计数据清洗方案，写出关键步骤及对应的SQL逻辑（或工具操作）。参考答案：关键步骤：1.去重：按`user_id`分组，保留`reg_time`最新的记录（假设`user_id`为唯一标识）；2.处理年龄异常：-缺失值：用同城市/同等级会员的年龄中位数填充；-异常值：将小于0或大于120的年龄标记为NULL，再按上述方法填充；3.修复注册时间：过滤格式错误的记录（如月份>12或日期>当月最大天数），或用`TRY_CAST`转换，失败则标记为NULL后人工核查。SQL示例（去重+年龄处理）：```sql--去重WITHdedupedAS(SELECT,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYreg_timeDESC)ASrnFROMmembers)SELECTuser_id,mobile,CASEWHENage<0ORage>120THENNULLELSEageENDASage,reg_timeINTOcleaned_membersFROMdedupedWHERErn=1;--填充年龄缺失值（假设用城市分组的中位数）UPDATEcleaned_memberscmSETage=(SELECTPERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYage)FROMcleaned_membersWHEREcity=cm.cityANDageISNOTNULL)WHEREageISNULL;```---二、BI工具应用（本部分共2题，总分20分）题目1（10分）：Tableau动态看板设计某零售企业需要制作“2024年上半年区域销售趋势看板”，需求如下：-支持按区域（华北/华东/华南）、月份动态筛选；-展示指标：销售额、毛利率、目标完成率（实际/目标）；-标注异常值（如某区域某月销售额较上月波动超过±20%）。请描述设计步骤，并说明如何实现动态筛选和异常值标注。参考答案：设计步骤：1.数据准备：连接销售事实表（区域、月份、销售额、成本）和目标表（区域、月份、目标销售额）；2.计算字段：-毛利率=(销售额-成本)/销售额；-目标完成率=实际销售额/目标销售额；-月环比波动=(本月销售额-上月销售额)/上月销售额；3.动态筛选：-创建区域筛选器（使用“区域”字段的筛选操作）；-创建月份参数（日期范围选择器），绑定到视图的月份轴；4.异常值标注：-添加参考线：在销售额趋势图中，使用“月环比波动”字段，设置条件格式（如波动>20%标红，<-20%标蓝）；-或使用数据标签：在标记卡中添加“月环比波动”，通过颜色编码区分异常。题目2（10分）：PowerBIDAX高级计算某电商订单表`orders`（字段：order_dateDATE,categorySTRING,amountDECIMAL），需计算“2024年各品类季度同比增长率”（Q12024vsQ12023，Q22024vsQ22023）。要求用DAX写出计算逻辑，并解释“度量值（Measure）”与“计算列（CalculatedColumn）”的区别。参考答案：DAX计算逻辑：```dax销售额_当前季度=SUM(orders[amount])销售额_去年同期季度=CALCULATE([销售额_当前季度],SAMEPERIODLASTYEAR(orders[order_date]))季度同比增长率=DIVIDE(([销售额_当前季度]-[销售额_去年同期季度]),[销售额_去年同期季度])```使用时，按`category`和季度分组，筛选2024年数据即可。度量值与计算列的区别：-度量值：在查询时动态计算，基于当前筛选上下文（如用户选择的时间、区域），适用于聚合类指标（如总和、平均值）；-计算列：在数据加载时预计算，每行提供一个值，依赖行上下文，适用于固定逻辑的衍生字段（如订单月份、分类标签）。---三、数据建模与ETL设计（本部分共2题，总分25分）题目1（12分）：维度建模与缓慢变化维处理某教育平台需构建用户行为数据仓库，核心业务流程为“用户登录→浏览课程→加入收藏→报名课程”。请设计维度模型（画出简化的星型模型图，标注事实表与维度表字段），并说明“课程维度表”中“课程价格”字段发生变更时（如促销降价），应采用哪种缓慢变化维（SCD）处理方式，写出具体实现步骤。参考答案：星型模型设计：-事实表：`user_behavior_fact`（用户ID、课程ID、行为类型ID、行为时间戳、是否报名（0/1））；-维度表：-`user_dim`（用户ID、性别、注册时间、会员等级）；-`course_dim`（课程ID、课程名称、原价、当前价格、课程类型）；-`time_dim`（时间ID、日期、月份、季度）；-`behavior_type_dim`（行为类型ID、类型名称（如浏览/收藏/报名））。课程价格变更的SCD处理：选择SCD2（保留历史版本），因需追踪价格变化对历史行为的影响（如用户在降价前浏览，降价后报名）。实现步骤：1.在`course_dim`中添加`effective_start_date`（生效开始时间）和`effective_end_date`（生效结束时间）字段；2.当检测到课程价格变更时（通过ETL的增量抽取），将原记录的`effective_end_date`设为变更前一天；3.插入新记录，`effective_start_date`为变更当天，`effective_end_date`设为'9999-12-31'（默认有效至未来）；4.事实表通过`course_id`和`behavior_time`关联维度表时，使用`behavior_time`介于`effective_start_date`和`effective_end_date`的条件。题目2（13分）：实时ETL流程设计某物流平台需将订单数据（来自MySQL，增量更新）实时同步到数据仓库（AWSRedshift），并支持分钟级延迟的BI分析。要求设计ETL流程，说明工具选择、关键节点（抽取→清洗→转换→加载）的实现逻辑，以及异常处理机制。参考答案：ETL流程设计：1.抽取（Extract）：-工具：使用Debezium（基于MySQLBinlog的CDC工具）捕获增量变更，通过Kafka消息队列传输，保证数据不丢失；-逻辑：监听`orders`表的INSERT/UPDATE事件，提取变更的行数据（如`order_id`、`create_time`、`status`）。2.清洗（Clean）：-工具：FlinkStreamProcessing（实时计算引擎）；-逻辑：过滤无效状态（如`status=-1`），校验必填字段（如`order_id`非空），转换`create_time`格式为UTC时间。3.转换（Transform）：-工具：FlinkSQL；-逻辑：计算衍生指标（如运输时效=当前时间-`create_time`），关联字典表（如`region_dim`获取收货区域）。4.加载（Load）：-工具：AWSKinesisFirehose（或自定义FlinkSink）；-逻辑：按分钟批次写入Redshift（使用COPY命令提升效率），或直接写入RedshiftSpectrum支持的S3存储（适用于超大规模数据）。异常处理机制：-数据丢失：Kafka设置`replication-factor=3`，确保消息持久化；-脏数据：在Flink中捕获解析异常，将错误数据写入死信队列（DeadLetterQueue），人工核查；-加载失败：Redshift记录加载日志，失败批次回滚并重新发送，同时触发警报（如Slack通知ETL负责人）。---四、业务分析与场景题（本部分共2题，总分25分）题目1（12分）：转化漏斗诊断某电商APP用户行为数据如下（2024年6月）：|环节|访问用户数||------------|------------||启动APP|100,000||浏览商品|60,000||加入购物车|24,000||提交订单|12,000||支付成功|9,000|（1）计算各环节转化率及整体转化漏斗（启动→支付成功）的转化率；（2）定位瓶颈环节，并提出2条优化建议。参考答案：（1）转化率计算：-启动→浏览：60,000/100,000=60%；-浏览→加购：24,000/60,000=40%；-加购→提交订单：12,000/24,000=50%；-提交订单→支付成功：9,000/12,000=75%；整体转化率：9,000/100,000=9%。（2）瓶颈环节：浏览→加购（转化率40%，低于行业平均50%）。优化建议：-商品详情页优化