BI工程师笔试真题（含详细答案）

BI工程师笔试真题（含详细答案）考试说明：满分100分，考试时长90分钟，题型包含选择题、多选题、简答题、SQL实操题、业务分析题，侧重考察数据建模、SQL能力、BI可视化、业务数据分析实战能力。一、单项选择题（共10题，每题3分，共30分）1.以下不属于BI数据仓库核心分层的是（）A.ODS层B.DWD明细层C.DWS聚合层D.测试层2.在数据仓库建模中，星型模型的核心特点是（）A.多张事实表关联多张维度表B.一张事实表关联多张维度表C.维度表相互关联D.事实表与维度表递归关联3.用于解决数据分析中重复计算、提升查询效率的BI数据处理方式是（）A.数据脱敏B.数据预聚合C.数据备份D.数据删除4.以下SQL语句中，能够实现分组后筛选条件的关键字是（）A.WHEREB.HAVINGC.ORDERBYD.DISTINCT5.同比分析的核心作用是（）A.对比相邻周期数据B.对比去年同期数据，消除季节影响C.筛选异常数据D.计算数据占比6.在BI报表制作中，漏斗图最适合展示的业务场景是（）A.各区域销量对比B.用户转化全流程流失情况C.销售额趋势变化D.品类销量占比7.数据仓库中缓慢变化维度（SCD）Type1的处理规则是（）A.直接覆盖旧维度数据，不保留历史B.新增维度行，保留新旧历史数据C.新增字段存储新旧数据D.归档旧数据，单独存储8.以下哪种数据指标属于存量指标（）A.日新增用户数B.当日成交额C.实时在线用户数D.月下单次数9.报表数据出现重复行数据，最不可能的原因是（）A.维度表与事实表一对多关联B.未做去重处理C.数据同步重复D.聚合函数使用错误10.以下不属于BI日常数据校验核心内容的是（）A.数据完整性B.数据一致性C.数据冗余性D.数据准确性二、多项选择题（共5题，每题4分，共20分，多选、少选、错选均不得分）1.常见的BI数据建模模型包含（）A.星型模型B.雪花模型C.宽表模型D.网状模型2.以下属于流量类核心指标的有（）A.PVB.UVC.转化率D.客单价3.导致BI报表数据延迟的常见原因有（）A.数据源同步延迟B.任务调度执行超时C.报表查询逻辑复杂D.数据预计算未执行4.常用的BI可视化图表及适配场景匹配正确的有（）A.折线图：展示数据时间趋势B.饼图：展示各维度占比结构C.柱状图：多维度数据对比D.散点图：数据相关性分析5.BI工程师日常核心工作包含（）A.数据仓库分层建模B.SQL数据清洗与计算C.可视化报表与看板开发D.数据异常排查与指标迭代三、简答题（共3题，每题6分，共18分）1.简述数据仓库中ODS层、DWD层、DWS层的核心作用和区别。2.简述环比、同比、定基比的区别，以及各自适用的业务场景。3.日常工作中，BI报表数据和业务前台数据不一致，你会如何排查？四、SQL实操题（共2题，每题8分，共16分）基础数据表说明1.用户表user_info（user_id用户ID，register_time注册时间，area所属区域）2.订单表order_info（order_id订单ID，user_id用户ID，order_time下单时间，amount订单金额，order_status订单状态）订单状态：1-已下单，2-已支付，3-已取消1.编写SQL，统计2025年10月每日的下单用户数、支付订单数、单日总成交额。2.编写SQL，统计各区域2025年累计注册用户数、累计付费用户数、用户付费率，按付费率降序排序。五、业务分析题（共1题，16分）某电商平台9月份整体销售额环比8月下降15%，但平台UV、下单用户数基本持平，请你结合BI数据分析思路，分析可能的核心原因，并给出对应的数据分析落地思路和优化建议。参考答案（详细解析）一、单项选择题答案及解析1.D解析：数据仓库标准分层为ODS原始数据层、DWD明细数据层、DWS聚合数据层、ADS应用层，无测试层，测试属于数据开发流程环节，非仓库分层。2.B解析：星型模型核心为单一事实表，直接关联多个维度表，结构简单、查询效率高，是BI最常用建模方式。雪花模型为维度表二次拆分关联。3.B解析：数据预聚合可提前完成分组、求和、统计等计算，报表查询时直接读取结果，大幅提升BI看板加载和查询效率。4.B解析：WHERE用于分组前筛选数据，HAVING用于GROUPBY分组后的条件筛选，可使用聚合函数判断条件。5.B解析：同比是本期与去年同期对比，能够规避月份、季度等季节因素对数据的影响，真实反映业务增长水平。环比为相邻周期对比。6.B解析：漏斗图用于展示多步骤转化流程的用户流失、留存情况，适配注册、下单、支付等转化场景，其余场景分别适配柱状图、折线图、饼图。7.A解析：SCDType1直接覆盖旧数据，不保留历史版本；Type2新增数据行保留历史；Type3新增字段存储变更前后数据。8.C解析：存量指标是某一时刻的静态数据，实时在线用户数为即时存量数据；其余选项均为一段时间内的增量指标。9.D解析：维度关联一对多、数据重复同步、未去重都会导致重复行；聚合函数使用错误只会导致数据计算错误，不会产生重复行。10.C解析：BI数据校验核心为完整性（数据不缺失）、一致性（多源数据统一）、准确性（数据计算正确），数据冗余是仓库优化规避的问题，非校验内容。二、多项选择题答案及解析1.ABC解析：BI数据仓库常用建模模型为星型、雪花、宽表模型，网状模型多用于传统数据库，不适用于大数据BI场景。2.ABC解析：PV浏览量、UV访客数、转化率均为流量指标；客单价属于交易营收类指标。3.ABCD解析：数据源同步滞后、调度任务超时、查询逻辑复杂、预计算任务未执行，都是报表数据延迟、更新不及时的常见原因。4.ABCD解析：四类图表的场景匹配均为BI可视化标准用法，贴合日常报表开发规范。5.ABCD解析：BI工程师核心工作涵盖数据建模、数据清洗计算、报表看板开发、数据运维与异常排查、指标迭代优化全流程。三、简答题详细答案1.ODS、DWD、DWS层核心作用和区别（1）ODS原始数据层：直接同步业务数据库、日志等原始数据源，保留完整原始数据，不做复杂清洗和计算，主要用于数据备份、溯源，是所有数据加工的基础。数据粒度最细，与业务库数据一致。（2）DWD明细数据层：基于ODS层数据进行清洗、去重、脏数据剔除、字段标准化，梳理业务明细数据，构建明细宽表。保留单条业务原始明细，无聚合计算，用于支撑精细化数据分析和上层聚合计算。（3）DWS聚合数据层：基于DWD明细层，按照时间、区域、用户、品类等业务维度进行轻度聚合、汇总统计，生成常用的汇总指标宽表。提前计算高频使用的统计数据，降低上层报表查询压力，提升查询效率。核心区别：三层数据加工程度、粒度、用途不同，从原始未加工到明细清洗，再到聚合汇总，层层递进，适配不同数据分析场景。2.环比、同比、定基比区别及适用场景（1）环比：本期数据与上一个相邻周期数据对比，如月环比、日环比，反映短期业务波动变化。适用场景：监控近期业务实时波动，快速发现短期异常。（2）同比：本期数据与去年同一周期数据对比，消除季节、节假日、行业周期等固定因素影响。适用场景：判断业务真实增长水平，适合电商、零售等季节性明显的业务。（3）定基比：以某一个固定周期为基准，后续所有周期均与基准值对比，反映数据长期累计变化趋势。适用场景：年度业务复盘、长期业务增长趋势分析、目标达成跟踪。3.报表数据与业务前台数据不一致的排查思路（1）核对统计口径：优先确认两端指标定义是否一致，比如成交额是否包含退款、取消订单，统计时间是否为自然日/业务日，用户是否去重。大部分数据差异均为口径不一致导致。（2）排查数据源：确认BI数据源和业务前台数据源是否为同一库、同一表，是否存在分库分表、数据同步遗漏问题。（3）核查数据加工逻辑：检查SQL计算逻辑、数据清洗规则、聚合方式是否存在错误，是否有漏算、重算、筛选条件缺失的情况。（4）校验数据时效性：排查数据同步、任务调度是否延迟，前台为实时数据，BI为离线定时统计，时间差会导致临时数据差异。（5）抽样溯源核对：选取单个日期、单个用户、单个订单抽样核对，定位具体差异数据，精准找到问题节点。四、SQL实操题详细答案说明：基于MySQL语法，适配主流数仓Hive、SparkSQL，逻辑通用1.2025年10月每日核心数据统计sql

SELECT

DATE(order_time)ASday_date,

COUNT(DISTINCTuser_id)ASdaily_order_user,--每日下单用户数

COUNT(CASEWHENorder_status=2THENorder_idEND)ASdaily_pay_order,--每日支付订单数

SUM(CASEWHENorder_status=2THENamountELSE0END)ASdaily_total_amount--每日总成交额（仅支付订单）

FROMorder_info

WHEREDATE(order_time)BETWEEN'2025-10-01'AND'2025-10-31'

GROUPBYday_date

ORDERBYday_date;2.各区域用户累计数据及付费率统计sql

SELECT

ui.area,

COUNT(DISTINCTui.user_id)AStotal_register_user,--累计注册用户数

COUNT(DISTINCTCASEWHENoi.order_status=2THENui.user_idEND)ASpay_user,--累计付费用户数

ROUND(

COUNT(DISTINCTCASEWHENoi.order_status=2THENui.user_idEND)/COUNT(DISTINCTui.user_id)*100,

2

)ASpay_rate--用户付费率

FROMuser_infoui

LEFTJOINorder_infooiONui.user_id=oi.user_idANDDATE(oi.order_time)BETWEEN'2025-01-01'AND'2025-12-31'

WHEREDATE(ui.register_time)BETWEEN'2025-01-01'AND'2025-12-31'

GROUPBYui.area

ORDERBYpay_rateDESC;五、业务分析题详细答案1.核心问题拆解平台UV、下单用户数持平，说明用户流量、下单转化规模无明显下滑，销售额下降的核心原因不在流量和下单人数，而是单用户贡献的营收数据下滑。2.核心潜在原因分析（1）客单价大幅下降：用户下单人数不变，但每单平均成交金额降低，比如高价商品销量下滑、低价促销商品占比提升、用户下单件数减少。（2）订单退款率上升：9月下单订单的退款、取消比例高于8月，有效成交订单减少，最终统计销售额下降。（3）商品结构变化：高毛利、高客单的核心品类销量下滑，引流低价品类销量持平，整体营收被拉低。（4）优惠活动力度变化：9月平台满减、折扣、优惠券力度更大，同等下单量下，实际成交金额更低。3.落地数据分析思路（1）核心指标拆解：对比8、9月客单价、单用户下单件数、退款率、取消率，定位核心下滑指标。（2）维度下钻分析：按商品品类、价格区间、用户层级、区域拆分数据，查看哪类商品、哪类用户导致销售额下滑。（3）活动数据对比：对比两月营销活动场次、优惠力度、补贴金额，分析