“B站校招数据仓库方向的笔试卷”是最近我后台收到的高频提问。不少准备投递数据研发、数据仓库、大数据开发岗位的同学都会拿这套题来练手。我得说B站这套笔试卷在圈内口碑挺有意思——它不像一般互联网公司那样纯考SQL语法也不是单纯背概念而是把数据仓库建模、明细表设计、指标口径、SQL实战全揉在一起题量不大但每道题都踩在数仓日常工作的核心节点上。今天就把我对这套试卷的理解、拆解和一套完整的高分答题思路整理出来希望能帮正在准备校招的同学少走弯路。这篇文章适合三类读者正在准备数据仓库/数据研发校招笔面试的应届生准备跳槽到数仓方向的初级开发以及想借一套真实题目检验自己数仓基本功的从业者。内容会覆盖数仓建模方法论、核心维度表和事实表设计、SQL笔试题套路以及大量实际项目中才遇得到的避坑经验。1. 笔试卷的整体定位与考点分布1.1 试卷结构这不是一份“刷题数据库”先说结论B站这套笔试卷更像一份“数仓工程师上岗能力自测表”而不是传统意义的考试题。从我收集到的信息看整套卷子大致分三块数据仓库理论基础、维度建模实战设计、SQL与数据开发能力。基础理论部分会考察数仓分层架构ODS、DWD、DWS、ADS、维度建模理论星型模型、雪花模型、事实表类型、数据质量保障手段等。这部分不会让你背定义而是给一个业务场景让你说明如何分层、如何建模、如何保证口径统一。建模实战部分通常会给一个具体的业务场景比如“用户订单分析”要求设计核心维度表和事实表说明粒度、主键、字段含义。这是整套卷子的重头戏后面我用一整章来拆。SQL与数据开发部分则是典型的笔试编程题覆盖窗口函数、留存计算、连续登录、行列转换、SQL调优偶尔还会考察Hive/Spark的底层原理。1.2 为什么校招笔试卷要这样出题站在招聘方的角度看这个考察逻辑非常清晰数仓岗位的校招生入职后前半年大概率是在做三类事——写ETL、建表建模、口径对齐。这就决定了笔试卷必须能筛出三类能力第一类是“能不能写对SQL”。校招生可以没写过生产级数仓但只要SQL能力强上手ETL的速度就很快。第二类是“懂不懂建模逻辑”。事实表和维度表怎么设计、粒度怎么定、缓慢变化维怎么处理这些是数仓工程师和普通后端开发的核心区别。第三类是“有没有业务 sense”。同样的订单数据让不同人设计表结构得到的方案差异极大这背后反映的是对指标口径、查询模式的理解。所以这份卷子不考偏题怪题考的全是数仓工程师日常天天打交道的活。1.3 校招笔试和社招面试的差异我经常和准备校招的同学说不要用社招的题库来准备笔试。社招面试更侧重项目深挖比如你做过什么数仓项目、遇到过什么数据倾斜、怎么优化的。而校招笔试卷是标准化的答案相对客观评卷也是按点给分。这意味着校招笔试准备的核心是“基础扎实、术语准确、思路完整”。比如让你设计订单事实表你不需要写出B站真实的埋点字段也不可能知道但你只要把维度建模的规范写清楚粒度声明、维度外键、度量字段、退化维度、分区策略就能拿到大部分分数。这套卷子的核心价值也在这里——它把数仓工程师的“基础功”量化了。2. 从用户订单分析看数仓建模方法论2.1 为什么笔试总爱考“订单分析”几乎每一家互联网公司的数仓笔试题都会出现“订单分析”或类似的交易类场景。原因很简单订单分析链路长从用户下单、支付、发货、收货到退款是一条完整的业务链路能够全面考察建模能力。更关键的是订单分析的核心“用户订单分析数据仓库设计核心维度表和事实表”正是数仓建模的经典场景——它涉及多个维度用户、商品、商家、时间、地区有多种事实订单、支付、退款还有业务口径的复杂度订单金额是下单口径还是支付口径、退款是否冲减GMV等。一道订单设计题基本能把一个人对数据仓库的理解摸透。2.2 维度建模的核心概念用买菜理解很多同学对维度建模的恐惧来自术语。但本质上维度建模就是回答两个问题我要分析什么事实我从哪些角度分析维度。用买菜来类比你每天记录“花了多少钱买什么东西”这是事实“今天”“在哪个菜市场”“买了蔬菜还是肉类”这是维度。事实表存的是可加的数值度量金额、数量、次数维度表存的是描述性属性名字、分类、地区、时间特征。在订单场景里订单事实表的核心度量就是订单金额、商品数量、优惠金额、运费维度则是哪一个用户用户维度、哪一件商品商品维度、哪一家店铺商家维度、什么时候下单时间维度、从哪个渠道来渠道维度。2.3 事实表的三种类型必须在笔试中写出来这是我在批改模拟答案时最常看到的扣分点。很多同学设计订单事实表时只写了一张“大宽表”把所有度量都塞进去。但专业的建模会区分三种事实表事务事实表每一笔业务事件产生一行比如订单明细表一行就是一个订单的一个商品子项。它最常用于统计订单数、GMV、销量这类可累计的指标。周期快照事实表按固定周期比如每天记录某个对象的累计状态比如“用户每日累计订单金额表”。常见于分析DAU、留存、复购等需要切片状态的场景。累积快照事实表记录一个流程从开始到结束的关键节点时间比如订单从创建、支付、发货到完成一行代表一个订单的完整生命周期。常用于分析流程耗时、转化漏斗。笔试时如果你能根据“用户订单分析”场景同时给出事务事实表和累积快照事实表的方案并说明各自适用的指标评分会明显上一个档次。2.4 缓慢变化维订单维度的“历史还原”能力订单分析里经常遇到一个坑——商品价格变了、分类调整了、用户等级升级了。如果直接关联最新的维度表所有历史订单都会按“现在的分类”统计历史数据就失真了。这就是缓慢变化维要解决的问题。完整写法是三种策略SCD1直接覆盖、SCD2保留历史版本增加生效时间和失效时间、SCD3保留上一次值。在订单事实表设计中通常采用“退化维度”策略把订单下单时刻的关键属性直接冗余到事实表比如商品的一级分类、二级分类、价格、商家名称这样即使后续维度变化历史订单分析依然能还原当时的业务状态。3. 订单分析主题的维度表与事实表设计实操3.1 第一步把分析需求整理成指标清单先做需求梳理再动手建表这是笔试答题的核心逻辑。拿到“用户订单分析”这个题目时先不要着急写字段而是先明确这个数仓主题要支撑哪些分析我一般会按三条线拆解第一条线是交易核心指标订单数、下单用户数、支付订单数、支付金额GMV、客单价、件单价。第二条线是商品分析商品销量TopN、类目销售占比、品牌销售趋势、退货率。第三条线是用户分析新老用户订单贡献、复购率、人均订单金额、用户生命周期价值。有了这个指标清单后边的表结构设计就有了明确的目标——每一张表都是为了支撑这些指标的计算。3.2 维度表设计四个必须覆盖的核心维度在笔试中维度表设计至少要覆盖四个核心维度用户维度、商品维度、商家维度和日期维度。我给出一个经过实际项目验证的字段设计方案可以直接作为答题模板。用户维度表字段名类型说明user_keybigint代理主键自增user_idstring业务主键用户IDregister_timetimestamp注册时间user_levelstring用户等级is_new_userint是否新客当天注册channelstring注册渠道region_idstring地区IDage_groupstring年龄分段商品维度表字段名类型说明goods_keybigint代理主键goods_idstring业务主键goods_namestring商品名称category_idstring类目IDcategory_namestring类目名称brand_idstring品牌IDbrand_namestring品牌名称goods_pricedecimal商品当前价格商家维度表字段名类型说明seller_keybigint代理主键seller_idstring业务主键seller_namestring商家名称seller_levelstring商家等级industrystring主营行业join_timetimestamp入驻时间日期维度表是数仓最容易被忽略但极其重要的一张维度表。核心字段包括date_keyint类型如20230724、year、month、day、week_of_year、quarter、is_weekend、is_holiday、season_name等。它的昵称叫“时间维”几乎每一张事实表都要和它关联可以一次性生成5到10年的数据然后反复使用。设计维度表时要特别注意字段类型的选择。日期不要用字符串存用date_key整数金额用decimal不要用double否则精度丢失状态字段用int或string都可以但要保证字典值统一维护。3.3 事实表设计订单事实表的完整字段方案订单事实表是整个“用户订单分析”的核心表它会直接支撑订单数、GMV、客单价等核心指标。我在实际项目中沉淀了一套字段规范笔试可以直接套用。订单事实表粒度一个订单一行字段名类型说明order_idstring订单ID业务主键order_snstring订单编号user_keybigint用户维度外键goods_keybigint商品维度外键单商品订单seller_keybigint商家维度外键date_keyint下单日期外键order_amountdecimal订单应付金额pay_amountdecimal实付金额discount_amountdecimal优惠金额freight_amountdecimal运费order_statusstring订单状态pay_statusstring支付状态source_channelstring下单渠道退化维度category_namestring下单时类目退化维度is_paidint是否支付is_refundint是否退款但实际业务中一个订单往往包含多个商品因此还需要订单明细事实表粒度一个订单的一个商品一行字段名类型说明detail_idstring明细IDorder_idstring订单IDuser_keybigint用户维度外键goods_keybigint商品维度外键seller_keybigint商家维度外键date_keyint下单日期外键goods_countint商品数量goods_pricedecimal下单时单价total_amountdecimal商品小计split_pay_amountdecimal分摊实付金额为什么要拆成订单事实表和订单明细事实表因为指标的口径不同。订单表统计订单数时是count(order_id)明细表统计销量时是sum(goods_count)。如果只做一张表要么订单数算错要么销量算错。这就是为什么笔试设计题一定要体现“粒度”意识——先声明粒度再谈字段。3.4 事实表和维度表的关联关系与数仓分层设计完表还需要把表放进数仓分层中说明各自的位置。这是笔试中体现工程素养的关键一步。贴源层ODS直接存放业务库同步过来的订单表、订单明细表、用户表、商品表、商家表保持原始结构不做过多的清洗主要作用是备份和追踪。明细层DWD完成清洗、规范化、维度退化。比如订单表在这个层会从用户表关联出user_key从商品表关联出goods_key把冗余的分类名称关联进来。这一层的核心原则是“维度退化到事实表构建明细宽表”。汇总层DWS基于DWD层按主题进行汇总。比如用户订单汇总表按用户日期维度汇总订单数、支付金额、退款金额、商品销售汇总表按商品日期维度汇总销量、销售额。应用层ADS面向具体报表和应用比如大屏GMV、运营看板、排行榜。分层设计的核心好处是避免业务方直接查询ODS原始表保证指标口径统一同时通过层层汇总减少重复计算。3.5 笔试评分点一份高分的建模答案长什么样结合我这些年看过的笔试答案一份能拿高分的建模设计题答案通常包含以下要素明确声明每张表的粒度。比如“订单事实表粒度为一个订单一行”“订单明细事实表粒度为一个订单的一个商品一行”这句话是建模的分水岭。区分代理主键和业务主键。user_key和user_id都写出来说明你理解维度表的SCD处理。包含退化维度字段。很多考生只写维度外键忽略了source_channel、category_name这类冗余字段这说明缺乏实际ETL经验。说明分区策略。比如订单表按date_key分区每天一个分区离线任务每天凌晨调度保证数据时效性。解释设计取舍。比如为什么把优惠金额单独拆出来而不是只写实付金额因为运营需要分析折扣力度对转化的影响。4. SQL实战与常见问题排查4.1 笔试SQL高频题型可以提前准备的六类题B站笔试卷的SQL部分题型基本固定。根据我收集到的信息和圈内讨论高频题型集中在以下六类窗口函数类TopN、排名、同环比、累计值计算。留存计算类次日留存、7日留存、漏斗转化。连续类问题连续登录N天用户、连续消费N周。行列转换类行转列、列转行、多行拼接。去重与空值处理类UV去重、null值替换、无效数据过滤。SQL优化类如何减少shuffle、如何避免数据倾斜。窗口函数是绝对重点。很多候选人能把join写得很熟但一遇到row_number()、lag()、lead()、sum() over(partition by ... order by ...)就发怵这是平时训练不足的表现。笔试卷里窗口函数通常不会单独出一道题而是嵌在业务场景里考。4.2 典型题目一连续3天登录用户这道题我在各个厂的笔试里见过至少五次是数仓SQL的经典入门题。需求给定用户登录记录表user_login(user_id, login_date)求连续登录3天及以上的用户。解题思路的关键在于连续日期的实质是“日期减去行号得到一个相同分组”。with t1 as ( select user_id, login_date, row_number() over(partition by user_id order by login_date) as rn from user_login group by user_id, login_date ) select user_id from ( select user_id, date_sub(login_date, rn) as grp from t1 ) t2 group by user_id, grp having count(1) 3;注意几个细节第一步先group by去重因为同一天可能有多条登录记录不去重会导致row_number计算错误date_sub(login_date, rn)得到的是分组标识同一个分组内日期是连续的最后having count(1) 3筛选连续3天。4.3 典型题目二计算用户次日留存率留存率是数据分析的高频需求笔试也爱考。需求计算某日新增用户在次日还活跃的比例。标准写法with new_user as ( select user_id, date(register_time) as reg_date from user_dim where date(register_time) 2024-01-01 ), active_next as ( select distinct user_id from user_active_log where dt date_add(2024-01-01, 1) ) select count(n.user_id) as new_user_cnt, count(a.user_id) as retained_cnt, round(count(a.user_id) / count(n.user_id), 4) as retention_rate from new_user n left join active_next a on n.user_id a.user_id;这道题的避坑要点在于活跃用户表通常是每天一个分区的登记录必须用distinct去重留存率的分子是次日活跃的新用户数分母是新用户总数join时用left join防止新用户因次日未活跃而丢失。4.4 典型题目三订单指标计算fact表和维表join的坑这道题和前面的建模题互为照应。表结构就是第3章设计的订单事实表和维度表。需求统计2024年1月每天的下单用户数、订单数、GMV并按商品类目分组展示。select date_key, a.category_name, count(distinct user_key) as buyer_cnt, count(order_id) as order_cnt, sum(pay_amount) as gmv from dwd_order_fact a where date_key between 20240101 and 20240131 and pay_status paid group by date_key, category_name order by date_key, category_name;这里有几个隐蔽的坑需要说明。第一count(order_id)统计的是订单数但如果事实表粒度是订单明细count(order_id)会重复计数此时要先count(distinct order_id)。第二GMV是支付金额必须过滤掉未支付订单不然订单数和GMV口径对不上。第三维度字段category_name已经冗余在事实表中所以不需要join维度表这也是第3章设计时做退化维度的意义所在。4.5 数据倾斜、空值和精度问题笔试中的隐藏分除了基础语法笔试卷还会在细节里埋坑这些地方往往是区分“会写SQL”和“能写生产SQL”的分水岭。数据倾斜是数仓面试的必问题。笔试里如果遇到join大表和小表可以选择mapjoin提示优化遇到group by热点key可以考虑加盐拆key再合并。这些不是要求你在笔试现场优化框架而是考察你有没有排查性能问题的意识。空值处理是另一个高频坑。事实表中user_key为空说明是匿名下单或数据缺失金额字段为空sum()会直接忽略但count()不会。更隐蔽的是join时维表缺失导致事实表数据丢失。生产环境通常会把维表缺失的key统一替换成-1并冗余一个“未知”维度记录而不是直接过滤掉。精度问题也值得提一句。金额字段在MySQL中可能是decimal(10, 2)但在Hive/Spark中如果建表时误用了double多次聚合运算后会出现精度漂移导致对账不平。这也是为什么第3章设计表结构时金额一律用decimal。4.6 常用排查SQL和问题速查表实际工作里数据对不上是常态笔试里偶尔也会出一个“数据异常排查”场景题。我整理了一份速查表建议准备笔试的同学可以反复揣摩。现象可能原因排查方法订单数比业务后台多明细表粒度重复检查是否有同订单多商品行被重复countGMV比业务后台少过滤了未支付或退款订单核对支付状态过滤条件留存率超过100%新增用户表用了全量数据确认是否限定注册时间维表join后数据变少维表缺失或on条件不严谨改left join并检查匹配率金额对不上精度丢失或时区影响检查decimal类型和日期分区5. 一些准备建议和踩坑记录前段时间我在帮一个学弟模拟这套笔试卷时发现一个很有意思的共性问题大家能把SQL题写得非常漂亮但一遇到“设计核心维度表和事实表”就露怯字段名随手写粒度不声明甚至会出现把用户手机号、地址等敏感信息直接塞进维度表的低级错误。这说明大多数人平时刷题只刷了SQL对建模训练太少。建议准备阶段至少手写三遍订单主题的建模设计从需求梳理、维度设计、事实表设计到分层方案形成肌肉记忆。另一个常见问题是字段命名太随意。笔试时书写的时间有限很多人会写出order_date、user_name之类的字段。但在实际数仓开发中字段命名有严格规范维度外键用xxx_key业务主键用xxx_id时间统一用date_key关联日期维表金额字段统一用decimal。好的命名规范能让代码可维护性大幅提升也是面试官评分的隐性加分项。还要提醒一点B站的业务场景和纯电商略有差异它有大量内容生态、视频UP主、直播带货相关的分析场景。但数仓建模的核心方法论是相通的掌握好用户、内容/商品、作者/商家、时间这几个核心维度以及订单/播放/互动这类核心事实无论考哪家公司的数仓笔试题都能很快迁移。这套试卷其实是一面很好的镜子把数仓工程师的日常能力要求照得清清楚楚——能写SQL更要懂建模能做表更要懂业务。沉下心把这套题吃透收获的远不止一张offer。