2025年商业智能工程师试题及答案

2025年商业智能工程师试题及答案一、单项选择题（每题2分，共20分）1.某零售企业数据仓库中，用户行为表（user_behavior）包含字段：user_id（用户ID）、item_id（商品ID）、behavior_type（行为类型，取值1-5分别对应点击、收藏、加购、购买、评价）、timestamp（行为时间戳）。若需统计“2024年Q3每个用户的购买次数及最后一次购买时间”，正确的SQL语句是（）。A.SELECTuser_id,COUNT(CASEWHENbehavior_type=4THEN1END)ASpurchase_cnt,MAX(CASEWHENbehavior_type=4THENtimestampEND)ASlast_purchase_timeFROMuser_behaviorWHEREtimestampBETWEEN'2024-07-01'AND'2024-09-30'GROUPBYuser_idB.SELECTuser_id,COUNT(behavior_type=4)ASpurchase_cnt,MAX(timestamp)ASlast_purchase_timeFROMuser_behaviorWHEREbehavior_type=4ANDtimestampBETWEEN'2024-07-01'AND'2024-09-30'GROUPBYuser_idC.SELECTuser_id,SUM(IF(behavior_type=4,1,0))ASpurchase_cnt,MAX(IF(behavior_type=4,timestamp,NULL))ASlast_purchase_timeFROMuser_behaviorWHEREtimestampBETWEEN'2024-07-01'AND'2024-09-30'GROUPBYuser_idD.以上均不正确答案：C解析：选项A中COUNT(CASE...)在behavior_type≠4时会统计NULL值，COUNT不计算NULL，因此正确；但选项C使用SUM(IF(...))更明确，且MAX(IF(...))在无购买记录时返回NULL，符合业务需求。选项B错误在于WHERE过滤了behavior_type=4，导致未购买用户被排除，而业务需求需包含所有用户（购买次数为0的情况）。2.关于数据湖（DataLake）与数据仓库（DataWarehouse）的区别，以下描述错误的是（）。A.数据湖存储原始数据（RawData），数据仓库存储经过清洗、结构化的数据B.数据湖支持多格式（如JSON、CSV、Parquet），数据仓库通常基于关系型数据库C.数据湖的使用场景以分析为主，数据仓库以事务处理（OLTP）为主D.数据湖的元数据管理更灵活，数据仓库的元数据需严格定义答案：C解析：数据仓库主要支持OLAP（在线分析处理），数据湖支持多样化分析（如机器学习、实时分析），而OLTP是事务型数据库的核心场景，因此C错误。3.某电商平台需构建用户分群模型，目标是将用户分为“高价值”“潜力”“流失”三类。以下特征中，最不适合作为输入特征的是（）。A.近30天支付金额B.注册时长（天）C.最近一次登录时间与当前时间的间隔D.用户ID的哈希值答案：D解析：用户ID的哈希值是唯一标识，无业务含义，无法反映用户行为特征，因此不适合作为分群特征。4.在Tableau中，若需实现“当销售额>100万时，标记为红色；50万-100万为黄色；<50万为绿色”的动态颜色编码，应使用（）。A.颜色标记卡的“离散颜色”功能，手动设置颜色范围B.颜色标记卡的“连续颜色”功能，设置断点并自定义颜色C.使用计算字段创建分类，再将该字段拖入颜色标记卡D.在“视图”菜单中选择“颜色”，直接设置条件格式答案：C解析：Tableau中需先通过计算字段（如IFSUM(销售额)>100THEN"高"ELSEIF...END）生成分类字段，再将其拖入颜色标记卡实现条件颜色，因此C正确。5.某企业BI系统中，销售数据需从SAPERP抽取至数据仓库。以下ETL流程中，最合理的顺序是（）。①数据清洗（处理缺失值、异常值）②数据抽取（从SAP数据库读取增量数据）③数据转换（字段映射、计算新指标）④数据加载（写入数据仓库）⑤数据验证（核对抽取记录数与源系统一致性）A.②→⑤→①→③→④B.②→①→③→⑤→④C.②→③→①→⑤→④D.②→⑤→③→①→④答案：A解析：合理流程应为：抽取数据→验证抽取完整性（避免漏抽）→清洗（处理脏数据）→转换（业务逻辑加工）→加载。因此选A。二、简答题（每题8分，共40分）1.简述维度建模中“星形架构”与“雪花架构”的区别及适用场景。答案：星形架构是维度建模的基础，由事实表和多个维度表直接关联组成，维度表不做进一步规范化；雪花架构是星形架构的扩展，将维度表进一步拆分为子维度表（如时间维度拆分为年、月、日表），通过多层关联减少数据冗余。适用场景：星形架构适合需要快速查询、对响应时间要求高的场景（如业务前端报表）；雪花架构适合数据存储成本敏感、维度属性较多且需长期保留历史数据的场景（如企业级数据仓库）。2.数据质量评估的关键指标有哪些？请举例说明如何针对“一致性”指标进行评估。答案：关键指标包括：完整性（如用户表中手机号缺失率）、准确性（如订单金额与商品单价×数量的匹配度）、一致性（如不同系统中同一用户ID的格式是否统一）、及时性（如销售数据T+1导入的延迟时长）、唯一性（如订单表中是否存在重复订单号）。针对一致性的评估方法：以“用户性别”字段为例，需检查不同数据源（如APP端、PC端、CRM系统）中该字段的取值是否统一（如是否存在“男/女”“M/F”“1/0”等多种表示），统计不一致记录的占比（如不一致记录数/总记录数），并分析原因（如ETL映射规则错误、源系统定义差异）。3.设计A/B测试时，如何确定样本量？需考虑哪些关键参数？答案：样本量计算需基于统计学中的假设检验理论，关键参数包括：显著性水平（α）：通常取0.05，表示拒绝原假设时犯第一类错误的概率；统计功效（1-β）：通常取0.8，表示正确拒绝原假设的概率；最小可检测效应（MDE）：期望检测到的指标变化的最小幅度（如转化率提升2%）；基线指标值（如当前转化率为10%）。计算公式（以均值检验为例）：n=[(Zα/2+Zβ)²×(σ₁²+σ₂²)]/Δ²，其中σ为标准差，Δ为MDE。实际中可使用在线工具（如EvanMiller计算器）或SQL/Python脚本计算。4.简述指标体系设计的“SMART原则”，并举例说明如何应用。答案：SMART原则指指标需满足：Specific（具体）：明确衡量对象和内容（如“APP日活用户数”而非“用户活跃度”）；Measurable（可衡量）：有明确的计算口径（如“日活定义为当日启动APP≥1次的独立用户数”）；Achievable（可实现）：基于当前数据采集能力（如无法获取用户地理位置时，不设计“区域转化率”）；Relevant（相关性）：与业务目标对齐（如电商大促期间重点关注“GMV”“客单价”而非“注册用户数”）；Time-bound（有时限）：明确统计周期（如“周留存率”而非“留存率”）。应用示例：某教育APP的核心目标是提升付费转化率，设计指标“7日付费转化率（用户注册后7日内付费的比例）”，符合SMART原则：具体（注册用户的付费行为）、可衡量（通过用户ID关联注册时间与付费时间）、可实现（已有注册和支付日志）、相关（直接反映转化效果）、有时限（7日周期）。5.简述实时BI系统与传统离线BI系统的核心差异，列举3个实时BI的典型应用场景。答案：核心差异：数据处理时效性：实时BI支持秒级或分钟级数据更新（如Kafka+Flink流处理），离线BI通常为T+1（如Hive批处理）；架构设计：实时BI需处理流数据，依赖消息队列、流计算引擎；离线BI基于批处理框架；应用场景：实时BI用于需要即时决策的场景，离线BI用于历史分析。典型场景：①直播电商的实时GMV大屏（每分钟更新销售额、观众互动数据）；②银行反欺诈系统（实时检测异常交易，如10秒内跨地域支付）；③物流监控（实时追踪包裹位置，预警延迟风险）。三、案例分析题（每题15分，共30分）案例1：某电商用户流失分析背景：某电商平台近3个月月活用户数下降5%，管理层要求分析用户流失原因并提出优化建议。现有数据包括：用户基本信息（注册时间、年龄、性别）、行为数据（登录、浏览、加购、购买、退单）、交易数据（订单金额、支付方式）、客服数据（投诉记录）。问题：1.如何定义“用户流失”？需考虑哪些业务因素？2.请设计分析思路（包括关键指标、分析方法、数据验证步骤）。答案：1.用户流失定义需结合业务特性，常见定义为“连续N天未活跃（如登录、购买）的用户”。需考虑因素：行业特性（如高频消费电商N=30天，低频电商N=60天）；用户生命周期（新用户流失阈值低于老用户）；业务目标（若关注付费用户，可定义为“连续N天未购买且未登录”）。本案例建议定义为“过去30天未登录且未产生任何购买行为的用户”，N取30天（符合电商行业常规）。2.分析思路：（1）关键指标：流失率（流失用户数/总活跃用户数）；流失用户画像（年龄、性别、注册时长分布）；流失前行为特征（如最后一次登录到流失的间隔、加购未购买率、退单率）；对比分析（留存用户与流失用户的行为差异，如平均浏览时长、页面跳出率）。（2）分析方法：①cohort分析：按注册月份分组，追踪各月份用户的留存/流失趋势，识别是否存在特定时间段的流失高峰；②漏斗分析：分析用户从登录→浏览→加购→购买的转化漏斗，对比流失用户与留存用户的漏斗转化率差异；③归因分析：通过逻辑回归模型，识别影响流失的关键因素（如退单次数、客服投诉次数、最近一次购买金额）；④聚类分析：将流失用户分为不同群体（如“低活跃流失”“高价值突然流失”），针对性制定策略。（3）数据验证步骤：①数据完整性检查：确认用户行为日志无缺失（如检查各行为类型的记录数是否与埋点系统一致）；②定义一致性验证：抽取100个样本用户，人工核对是否符合流失定义（如某用户30天内有登录则不应被标记为流失）；③模型验证：使用混淆矩阵评估逻辑回归模型的准确率（如准确率>80%则可接受），避免过拟合。案例2：某制造企业供应链优化背景：某制造企业面临原材料库存积压（库存周转率下降15%）和生产缺料（停工待料次数月均10次）的矛盾问题。BI团队需通过数据分析优化供应链管理。现有数据包括：原材料采购订单（供应商、采购量、交货时间）、生产计划（每日生产需求）、库存数据（实时库存量、安全库存）、供应商历史交货表现（准时率、合格率）。问题：1.需构建哪些关键指标衡量供应链健康度？2.如何通过数据挖掘方法识别缺料与积压的根本原因？3.提出至少3条基于数据的优化建议。答案：1.关键指标：库存周转率（月消耗总量/月均库存量）；缺料率（缺料次数/生产计划次数）；供应商准时交货率（准时交货订单数/总订单数）；安全库存满足率（实时库存量≥安全库存的天数/总天数）；原材料需求预测准确率（1-|实际需求-预测需求|/实际需求）。2.数据挖掘方法：①关联分析：通过Apriori算法，识别“缺料”与“供应商A交货延迟”“原材料B需求预测偏差大”等因素的关联规则（如支持度≥10%，置信度≥70%）；②时间序列分析：对原材料需求历史数据（如钢材、塑料）进行ARIMA或Prophet建模，评估预测误差与缺料/积压的关系；③聚类分析：将原材料分为ABCD类（如A类高价值低用量，B类高价值高用量），分析不同类别材料的库存问题差异；④根本原因分析（RCA）：通过5Why法，结合数据验证（如“缺料→某批次交货延迟→供应商产能不足→供应商评估分数低”），定位深层原因。3.优化建议：①动态调整安全库存：基于原材料分类（如A类设置更高安全库存，C类降低）和供应商准时率（准时率<80%的供应商，安全库存增加20%）；②优化需求预测模型：引入外部数据（如天气、行业景气指数）作为特征，提升预测准确率（目标误差从15%降至10%）；③供应商分级管理：对准时率低、合格率低的供应商（如D级）减少采购份额，对优质供应商（A级）签订VMI（供应商管理库存）协议，降低企业库存压力；④建立缺料预警机制：通过实时数据监控（如库存≤安全库存且供应商在途量<需求），提前3天触发采购预警。四、实践操作题（共10分）任务：使用Python编写代码，完成以下数据处理与分析任务数据说明：某电商用户行为数据集（user_behavior.csv），字段包括user_id（用户ID）、item_id（商品ID）、behavior_type（1-点击，2-收藏，3-加购，4-购买）、timestamp（时间戳，单位：秒）。需求：1.读取数据，将timestamp转换为日期时间格式（如“2024-08-0114:30:00”），并提取“日期”（YYYY-MM-DD）和“小时”字段；2.计算每日用户的“点击-购买转化率”（购买用户数/点击用户数）；3.找出“2024年8月”中，每个用户的“首次购买时间”和“首次点击时间”，并计算两者的时间差（精确到分钟）；4.输出结果：将步骤2和步骤3的结果分别保存为CSV文件（命名为daily_conversion.csv和first_behavior_diff.csv）。（要求：代码需包含必要注释，处理缺失值/异常值，确保结果准确性。）答案：```pythonimportpandasaspdfromdatetimeimportdatetime步骤1：读取数据并处理时间字段df=pd.read_csv('user_behavior.csv')处理缺失值（假设无缺失，若有则用df.dropna()或填充）转换时间戳为日期时间格式（时间戳为秒级，需转换为毫秒级再转datetime）df['datetime']=pd.to_datetime(df['timestamp']1000)df['date']=df['datetime'].dt.date.astype(str)提取日期（字符串格式）df['hour']=df['datetime'].dt.hour提取小时步骤2：计算每日点击-购买转化率筛选点击和购买行为click_users=df[df['behavior_type']==1].groupby('date')['user_id'].nunique().reset_index()click_users.columns=['date','click_user_cnt']purchase_users=df[df['behavior_type']==4].groupby('date')['user_id'].nunique().reset_index()purchase_users.columns=['date','purchase_user_cnt']合并并计算转化率daily_conversion=pd.merge(click_users,purchase_users,on='date',how='left')daily_conversion['conversion_rate']=daily_conversion['purchase_user_cnt']/daily_conversion['click_user_cnt']daily_conversion.fillna(0,inplace=True)无购买记录时转化率为0步骤3：计算首次购买与首次点击时间差筛选2024年8月数据aug_data=df[df['date'].str.startswith('2024-08')]首次点击时间（每个用户最早的点击行为时间）first_click=aug_data[aug_data['behavior_type']==1].groupby('user_id')['datetime'].min().reset_index()firs