那年在宿舍刷到蘑菇街2019届实习生招聘我盯着“数据仓库开发工程师”这个岗位心里一半是兴奋一半是没底。打开笔试链接之后看到“用户订单分析数据仓库请设计核心维度表和事实表”这道题我坐了一会儿敢说绝大多数第一次投数仓实习的同学面对这类题目都会卡壳——它不是让你背概念而是直接给你一个业务场景看你怎么搭模型。后来我读了研又做了几年数据开发回过头再看这套笔试题发现它其实把数仓开发的核心素养考得很全业务理解、建模基本功、SQL能力、逻辑思维一个都没落下。这篇文章就把这类笔试的考察重点拆开讲清楚尤其把订单分析里的维度建模和事实表设计掰开揉碎给正在准备数据仓库实习笔试、校招面试的同学一份可以直接参考的作答思路也适合刚入门的数据开发朋友对照着查漏补缺。1. 先看清这场笔试数据仓库开发岗到底在考什么1.1 数仓开发工程师的日常工作与能力模型很多人对数据仓库开发有个误解以为就是写写SQL、跑跑ETL其实不然。数仓开发的核心工作是“把业务数据变成可分析的数据资产”日常涉及需求沟通、数据建模、ETL调度、指标口径统一、数据质量保障、性能优化这一长串环节。落到能力上就三块第一是SQL功夫窗口函数、多表关联、聚合去重这些必须是肌肉记忆第二是建模功底拿到一个业务场景能不能快速拆分维度、设计事实、确定粒度第三是业务理解电商的订单、支付、退款、优惠、物流每一项背后都有复杂的业务逻辑理解不到位表建得再漂亮也是空中楼阁。蘑菇街作为电商平台交易是最核心的业务链路笔试自然会把订单分析当成重点场景来考。我在实际做电商数仓的时候也有明显感受订单相关表是整个数仓体系里复用率最高、最被人盯着的部分上到报表下到推荐都要从订单数据里取数。所以笔试出这个题看起来是考建模实际上是在看你有没有建立“面向业务分析的数据模型”的思维方式。1.2 笔试出题规律与考察范围整理历年的数仓实习生笔试题考察范围其实相当固定一般就四大块数据仓库基础理论、数据库知识、SQL编写、建模设计。蘑菇街2019届这套题也不例外印象里分布大概是理论题会问数仓分层、维度表和事实表的区别、范式与反范式SQL题会给订单表和用户表让你算每日GMV、用户留存率、复购率之类的指标压轴的建模题就是用户订单分析场景要求设计核心维度表和事实表考察从业务到模型的完整链路。这类笔试的特点是“看着不难拿高分难”。理论基础靠背诵能拿分但SQL题和建模题没有标准答案阅卷人看的是你的思考过程是否完整、是否考虑到了边界情况。比如设计订单事实表的时候有没有考虑到一个订单对应多个商品的情况用户维度表里用户地址变化了怎么处理优惠金额分摊到商品明细还是只能放在订单头这些细节才是区分普通答案和优秀答案的地方。所以准备这类考试刷题只是表面功夫真正要打磨的是“遇到业务问题怎么拆解”的思维方式。1.3 弄清建模题的题眼在哪一句话概括建模题的题眼从业务过程出发确定粒度再拆维度和事实。很多新手一上来就开写字段比如“用户表要有用户ID、姓名、手机号、地址、注册时间”这种答法暴露的问题是没有经过业务分析只是把“用户”这个词对应到了数据库表上。真正的建模思路是反过来的。先问自己用户订单分析要回答哪些业务问题无非是“买了什么”“买了多少”“什么时候买的”“从哪个渠道买的”“谁买的”“转化如何”“履约状态如何”。从这些问题倒推出需要记录的维度外键和度量字段再回头设计表结构这才是数仓建模的正确姿势。我阅过一些校招笔试的答卷那些从业务问题入手、先说明粒度再列表结构的答案一眼就能看出对数据仓库是有理解的。2. 笔试核心题拆解用户订单分析的核心维度表和事实表2.1 第一步永远是确定业务过程与粒度假设笔试题目是这样的请为电商平台用户订单分析场景设计核心维度表和事实表。首先你要做的不是写CREATE TABLE而是在脑子里明确三个基本问题分析什么业务过程、每一行数据代表什么粒度、需要记录哪些度量。先看业务过程。订单相关的业务过程远不止“下单”一个还包括支付、发货、确认收货、退款、取消。不同业务过程对应不同事实表比如下单时记录的事实表叫“订单事实表”支付时记录的叫“支付事实表”。笔试里最常见的场景是以下单为主兼顾支付和履约状态所以通常会设计一张交易事实表同时用状态字段或时间字段来表示后续流程的进展。再看粒度。这是个很容易出区分度的点。一张订单可能包含多个商品如果你把每行定义为“一条订单”那张订单的总金额可以直接存在这一行里但商品维度的外键就放不下因为一行里塞不了多个商品ID如果把每行定义为“一个订单中的一个商品明细”那商品维度能对上但订单级的信息比如收货地址、优惠券总额、运费就会被冗余到多行。两个方案没有绝对对错取决于分析需求。笔试作答时我建议把这一点明确写出来比如“本设计采用订单明细粒度即一行代表一个订单中的一个SKU订单级信息通过订单号冗余关联”。这样阅卷人一眼就知道你考虑过粒度问题分数差距就是这么拉开的。2.2 维度表设计为每个分析角度建一张表维度表是分析的角度回答的是“谁、什么、何时、何地、通过什么渠道”这类问题。用户订单分析场景下核心维度表至少包括以下几张用户维度表。用户是买家主要字段包括用户ID代理键、注册手机号自然键、昵称、性别、年龄、注册时间、会员等级、所在城市、渠道来源。注意“会员等级”这类属性会随时间变化笔试能主动提到用拉链表或者缓慢变化维策略处理会是明显的加分项。商品维度表。商品是交易对象电商里一定要区分布SPU和款SKU因为分析“什么品类卖得好”要用SPU分析“哪个颜色尺码卖得好”要用SKU。字段可以包含商品ID、SPU ID、商品名称、品类ID、品类名称、品牌、上架时间、状态等。店铺维度表。蘑菇街这类平台有商家维度字段包括店铺ID、店铺名称、店铺等级、开店时间、主营类目等。有些分析场景还会把平台自营和第三方商家分开这属于可选的业务扩展。时间维度表。很多人忽略时间维度直接拿订单时间戳去做分析但标准数仓建模会单独建一张时间维度表把日期拆成年、季度、月、周、日、是否为节假日、是否为工作日等。好处是查“今年双11比去年双11增长多少”这类需求时可以简单用维度字段过滤不用在SQL里写一堆日期函数。渠道维度表和地域维度表。渠道维度记录订单来源是App、小程序、H5、站外广告还是其他地域维度对应收货地址的省市区。这两张表字段不多但在分析投放效果、地区销售分布时必不可少。维度字段设计有个原则能冗余尽量冗余。比如商品维度里既放品类ID也放品类名称看起来违反范式但数仓面向分析减少查询时的关联就是提高性能。笔试里如果有人问“为什么不遵守三范式”直接回答“数仓维度表采用反范式设计用冗余换取查询效率”这句话就能体现出你懂数仓和业务库的本质区别。2.3 事实表设计度量、外键与退化维度事实表记录的是业务过程产生的事实核心是“度量”也就是可以加总或计算的值。用户订单分析的事实表度量字段通常包括商品数量、应付金额、实付金额、优惠金额、运费、成本金额如果有等。需要特别注意事实表里的外键要对应到维度表的代理键而不是直接放业务ID。比如事实表里放“用户维度ID”通过它关联用户维度表才能查到用户所属的城市、会员等级。不过订单号是个例外它既是业务主键也会冗余到事实表里这种冗余字段在数仓里叫退化维度。为什么这么做因为订单号经常被业务方直接拿来查询比如“这个订单为什么退款了”如果把订单号单独做成维度表查一次还要关联一次太笨了。订单事实表还涉及一个“可加性”问题。商品数量、实付金额是每个维度下都能直接SUM的属于可加度量订单金额这种半可加度量在用户维度下可以加总但在商品维度下就可能因为优惠分摊问题而不准确折扣率、单价这类属于不可加度量只能求平均。笔试答题时能点出“哪些指标在不同粒度下具备可加性”会让答案显得很有专业深度。订单场景通常还会涉及累积快照事实表。下单、支付、发货、签收、退款这一整个生命周期每个环节都有一个时间戳累积快照事实表会把它们放在一行里比如“下单时间”“支付时间”“发货时间”“完成时间”“退款时间”专门用来分析订单履约周期、各环节转化率。这个知识点在笔试里属于进阶加分点答出来就能跟“只会设计事务事实表”的考生拉开差距。2.4 星型模型为什么更适合订单分析设计完维度和事实最后还要说清楚模型的整体结构。电商订单分析场景最合适的方案是星型模型中间一张订单事实表周围一圈维度表事实表通过外键与各维度表连接维度表不做过多层级拆分。反过来雪花模型会把商品维度再拆成品类维度、品牌维度规范化程度更高但查询时需要多层关联性能受损开发效率也低。从分析需求看星型模型的优势很明显第一用户和BI工具看模型时很容易理解事实表就是记录维度表就是筛选条件第二查询性能好关联次数少在数据量大的时候差异非常明显第三维度表冗余程度高业务不太关心标准化更关心“查得快、好理解”。当然这不是说雪花模型一无是处。如果某个维度本身的层级关系特别重要比如组织架构“大区-省-市-门店”你也可以保留一个层级维度表但从总体模型设计上星型是主流。笔试答案里最好明确写一句“整体采用星型模型商品维度类属性采用冗余方式放到商品维度表内”这句话虽然短但能表达出建模选型是有意识的设计而不是随手的拼凑。2.5 一张能拿分的答卷完整建表示例笔试答题时如果时间允许画完逻辑模型后补上建表SQL会非常加分。我在这里给一个Hive风格的订单明细事实表示例笔试和实际开发里都够用CREATE TABLE dwd_order_detail_di ( order_detail_id STRING COMMENT 订单明细ID, order_id STRING COMMENT 订单号退化维度, user_dim_id BIGINT COMMENT 用户维度代理键, product_dim_id BIGINT COMMENT 商品维度代理键, shop_dim_id BIGINT COMMENT 店铺维度代理键, channel_dim_id BIGINT COMMENT 渠道维度代理键, time_dim_id BIGINT COMMENT 时间维度代理键, region_dim_id BIGINT COMMENT 地域维度代理键, product_quantity INT COMMENT 商品数量, order_amount DECIMAL(18,2) COMMENT 应付金额, pay_amount DECIMAL(18,2) COMMENT 实付金额, discount_amount DECIMAL(18,2) COMMENT 优惠金额, freight_amount DECIMAL(18,2) COMMENT 运费, order_status TINYINT COMMENT 订单状态, order_time STRING COMMENT 下单时间, pay_time STRING COMMENT 支付时间, ship_time STRING COMMENT 发货时间, finish_time STRING COMMENT 完成时间, etl_time STRING COMMENT ETL处理时间 ) COMMENT 订单明细事实表一行代表一个订单中的一个SKU PARTITIONED BY (dt STRING COMMENT 数据分区按下单日期) STORED AS ORC;这张表把粒度写进了注释里包含了订单级和明细级两类信息状态和时间字段保留了后续分析履约过程的可能。分区字段用下单日期dt这是最常用的取数方式。实际笔试时不一定要求写出这么完整的DDL但能写出来说明你真的理解这张表是怎么被使用的。3. 笔试题背后的隐藏考点数仓建模理论3.1 事实表的三种类型与应用场景笔试题目里经常直接出现“事实表分哪几种、各适合什么场景”这类理论题。结合订单场景理解起来就很简单事务事实表记录每个业务事件比如每一笔下单就是一行适合统计下单量、GMV这类增量指标缺点是历史状态不保留。周期快照事实表按固定周期记录实体状态比如每天记录一次每个订单的当前状态适合分析“每天有多少在途订单、多少待发货订单”相当于给业务状态拍了每日快照。累积快照事实表则把一个业务流程从开始到结束的所有关键时间点放在一行里上面提到的订单全生命周期表就是典型例子适合分析“从下单到支付平均耗时多久”“有多少订单付款后没发货”这类问题。笔试答题时最好以订单业务为例子把三种表分别对应出来而不是干巴巴地背定义。我当时就是这个答法后来在实习里做“订单履约时效看板”用的就是累积快照和周期快照两张表笔试时候的理论积累直接变成了实战工具。3.2 缓慢变化维维度属性变化怎么处理缓慢变化维也就是SCD是数仓建模里最经典的理论考点之一。用户改了收货地址、商品换了所属类目、店铺改了名称这些都是维度属性随时间变化的情况。处理策略通常有三种。SCD1直接覆盖原值只保留最新状态实现最简单但历史分析会失真比如你按用户当前城市分析历史订单结果会跟着变。SCD2是拉链方式保留多条记录用生效日期和失效日期标记版本查历史时按日期取对应版本。SCD3保留当前值和上一个值适合只需要对比当前和上一次变化的场景。笔试里如果让设计用户维度表你可以主动问一句用户地址变化频繁但历史订单分析需要按当时地址维度统计所以用户维度表需要采用SCD2策略增加start_date、end_date、is_valid三个字段。这个问题能在提前设计阶段提出来说明你不是只会建表而是真在考虑数据对业务的支持。后来我在实际项目里处理会员等级变化时也用到了拉链这个机制是数仓开发的基本功值得花时间吃透。3.3 拉链表笔试里的加分项拉链表在笔试和面试里出现概率很高因为它是处理“渐变维度”的标准化方案。以用户会员等级为例用户可能从普通会员升级到VIP1过两个月再升到VIP2如果用SCD1就只能看到当前等级无法分析“升级前后消费行为差异”。拉链表的核心字段就几个会员等级、生效日期start_date、失效日期end_date、是否当前有效is_valid。用户在某个时间区间内处于某个等级查询当前状态时过滤end_date为9999-12-31查询历史状态时按业务日期匹配区间。维护拉链表的方式通常是对比当前表和每日增量数据把变化的记录关闭旧版本、插入新版本跑在每日ETL里。笔试不一定要求手写拉链SQL但能解释清楚拉链表的原理和适用场景就已经相当加分了。如果还能写出来简单的更新语句比如用开窗函数ROW_NUMBER找出最新记录来维护拉链状态面试官基本会认定你是有真实开发经验的。3.4 数仓分层ODS、DWD、DWS、ADS各层做什么数仓分层是几乎所有数仓笔试都会问的理论题。常见的分法是ODS层存放从业务库同步过来的原始数据基本不做加工只做清洗、去重、格式规范化DWD层做明细整合把业务过程建模成事实表和维度表订单明细事实表就属于DWD层DWS层按主题做汇总比如按“用户日期”维度预先聚合出订单数和GMV供上层直接用ADS层面向具体应用比如报表、大屏、算法特征数据通常是高度汇总的结果。面试官问分层的目的其实是想听你解释“为什么需要分层”。答案包括第一公共逻辑下沉到DWD层避免每张报表从ODS重复处理第二层层递进可以控制成本和权限明细数据只对少数人开放汇总数据可以加大范围第三数据从原始到汇总有清晰的加工链路出了问题好回溯。结合订单场景说会更有说服力ODS层是订单原始表DWD层是关联了用户、商品、店铺维度之后的订单明细事实表DWS层是“用户维度每日订单汇总表”ADS层是“业务侧的订单增长看板”这样一层一层往上职责清晰每层都有明确的产出。3.5 指标口径订单金额怎么定义笔试里常有一类题让你算“订单金额”“GMV”之类的指标这时候最怕的不是不会写SQL而是没搞清口径。同一个“订单金额”在不同业务语境下有完全不同的含义下单金额是用户下单那一刻的应付金额支付金额是用户实际支付出去的钱成交金额GMV一般是支付成功的订单金额退款金额则是要单独剔除的。分析“业绩好不好”和“实际收入多少”用的口径完全不同。所以答题时第一步不是写SQL而是说明口径比如“本文GMV定义为支付成功且未取消的订单实付金额之和退款订单不计入”。指标体系是在建模之外的另一项数仓基本功笔试能体现出对口径的敏感比你多背几个函数有用得多。我在实际工作里经常要和运营对口径口径不一致导致的返工比想象中更常见这套意识越早建立越好。4. 从笔试到面试订单分析高频SQL与思维陷阱4.1 高频SQL考点与参考答案思路订单分析场景下笔试SQL题基本都是围绕聚合、关联、窗口函数转的。我挑三个最高频的写一下思路。第一个是每日GMV。先确定口径“支付成功且未取消”然后group by日期sum支付金额。需要注意时间字段用什么作为“日期”这是业务口径问题通常用支付时间而不是下单时间。第二个是用户留存率。比如“计算2024年1月1日注册用户在次日、3日、7日的留存率”思路是先取注册用户表再关联订单表或活跃表找到每个用户在这些时间点的活跃情况最后求比例。实现上一般用left join加datediff或者用窗口函数对每个用户标记首次活跃时间。留存率是互联网运营最基础也最高频的指标几乎是笔试和面试必考。第三个是各商品销售额TopN。假设要查最近30天每个商品的销售额前10名思路是先按商品维度聚合销售金额再用窗口函数ROW_NUMBER() OVER (PARTITION BY 日期 ORDER BY 销售额 DESC)生成排名最后过滤排名小于等于10。这个题考查的是窗口函数的熟练度建议提前练熟ROW_NUMBER、RANK、DENSE_RANK的区别ROW_NUMBER并列时随机分配RANK并列后跳号DENSE_RANK并列不跳号根据业务需要选择。4.2 答题时容易掉进去的坑笔试SQL题看起来简单但坑就藏在一些边界条件里。第一个坑是粒度搞不清表里一行是“订单明细”却按“订单数”去count结果数被放大好几倍。第二个坑是去重逻辑不严谨比如订单表里同一订单有多个商品算支付金额时如果sum明细金额可能因为优惠分摊而多算或少算通常应该采用订单级金额而不是明细级金额。第三个坑是时间字段的选择不统一下单时间、支付时间、发货时间混合使用导致指标波动。第四个坑是时区和货币单位跨境电商还得考虑不同币种换算国内业务一般不用管但时区问题在数据里确实存在。这类坑在笔试时不容易暴露因为阅卷不一定有标准答案但在面试聊SQL思路的时候会被追问。如果能在写答案前先声明“我先把订单状态过滤为已支付”或“这里用订单明细粒度所以统计订单数时要distinct order_id”面试官对你的好感度会明显提升。这些细节看似小其实是业务经验的一部分绕开它们需要的不是聪明而是对数据的敬畏和反复验证的习惯。4.3 如何在答案里露出业务 Sense数仓开发工程师的工作有一半是跟业务方沟通所以笔试答案里自然流露出业务理解能力是极大的加分项。我见过一份很漂亮的答卷他设计完订单事实表之后补了一句“考虑到订单级和明细级粒度差异本表采用明细粒度同时冗余订单应付金额等指标如果后续需要分析优惠券分摊效果可以在商品级拆分优惠否则建议保留订单级优惠金额。”这句话看起来轻描淡写但说明他既懂建模技术又懂电商业务场景里的优惠分摊问题。另一种体现业务理解的方式是主动建议拆分事实表。比如下单和支付存在明显的时间差和转化关系如果混在一张表里查询支付转化漏斗时就要不停过滤“支付时间不为空”的记录。面试时你可以顺势提一句“实际生产环境里下单事实表和支付事实表一般分开设计再通过订单号关联”这个思路一出来面试官就知道你是真的接触过真实数仓项目而不是只会背书。5. 如果让我重新做这套笔试题我会这样应对5.1 时间分配与答题顺序假设笔试总时长90分钟我的分配思路是前15分钟快速浏览全卷把题型和分值在脑子里排个序接下来20分钟解决基础理论和简单的SQL题这类题分数稳定先把保底分拿下中间40分钟主攻建模大题和复杂SQL因为这是拉开差距的地方要给足时间把思路写完整最后15分钟检查一遍重点看有没有漏掉边界条件、有没有字段冲突顺带补充一些主动的加分说明。这里有两点要提醒一是千万不要在做完一道题反复纠结改动上浪费时间笔试给分是按点给的一句准确的结论比一大段模糊的论述更有价值二是如果某道题一时卡住可以先跳过去做后面数仓笔试里建模题和SQL题往往是相互启发的回头再看可能突然就有思路了。我当年就是靠这个节奏把一道卡了十分钟的SQL题留到最后结果做建模题时突然想明白了几张表的关系回头秒解。5.2 建模题答题模板业务过程-粒度-度量-维度-模型经过这些年的实战和辅导经验我总结了一套建模题的通用答题模板按顺序走下来基本不会漏点。第一步写“业务过程”明确分析的是下单、支付还是退款如果是综合场景要说明核心事实表的选择。第二步写“粒度”一句话说清楚每一行代表什么比如“一行代表一个订单中的一个商品明细”。第三步写“度量”列出金额、数量、运费、优惠等字段并大致说明哪些是可加的。第四步写“维度”列出用户、商品、店铺、时间、渠道、地域等维度表以及每个维度里必要的属性字段特别是处理渐变维度的策略。第五步写“模型”整体采用星型模型还是雪花模型事实表的关联方式是什么每个维度表的主外键是怎么定义的。最后再补充一句表分区策略按日期分区增量更新。这个模板最大的价值是强迫自己按照标准流程思考而不是凭感觉写字段。笔试阅卷很多是看踩点你把这五大块写完整逻辑链条清晰即使个别字段设计得不够完美也不会丢大分。这套模板我在后来带团队时也让身边新人用过面试官反馈普遍不错。5.3 给准备实习笔试的几点实在建议最后一个部分算是过来人的体己话。第一SQL窗口函数一定要练熟尤其ROW_NUMBER、RANK、LAG、LEAD、SUM OVER这几种笔试和面试出镜率极高。第二把订单分析这个场景从建模到指标完整做过一遍因为绝大多数互联网公司的核心业务都是交易订单建模一通百通。第三多关注指标口径的差异遇到一个指标先问“它到底是怎么算的”这是数据开发最日常也最重要的思维习惯。还有一点容易被忽略笔试不是炫技场是基本功的检验。与其堆砌华丽的大数据组件名词不如把维度建模、粒度选择、口径定义这几件基础事讲透彻。面试官想招的实习生不是什么都会的天才而是基础扎实、思路清晰、沟通顺畅、能踏实干活的人。你在答卷里表现出的每一点细致和严谨都会在打分时变成实实在在的竞争力。