记得当年投搜狐畅游的数据仓库开发工程师岗位时我一边刷题一边心里犯嘀咕游戏公司的数仓笔试到底会考什么后来真正坐到笔试屏幕前才发现游戏厂的数据仓库开发和一般互联网公司套路不太一样它更看重你对业务的理解——充值、付费、留存、活跃这些指标怎么落到模型上再落到SQL上每一步都有讲究。这篇文章我把当时的笔试题型、复习重点、踩坑经历完整梳理了一遍如果你正在准备游戏公司或者互联网大厂的数据仓库开发岗校招照着我这套思路去复习能少走不少弯路。1. 笔试题型与考察重点复盘1.1 游戏公司数仓笔试的特点先说一个很多人容易忽略的点同样是数据仓库开发工程师不同公司的笔试题侧重点差别很大。搜狐畅游是做游戏的它的数仓笔试天然带着游戏业务的味道。常规电商、金融公司会重点考察订单、支付、风控相关的建模场景而游戏公司则更加关注用户活跃、留存、付费、道具消耗这些游戏特有的行为链路。我当时拿到试卷的第一感受是理论题占比不高但每个理论题都问得很细比如“缓慢变化维有哪几种处理方式”“事实表可以分为哪几类”这些问题如果只是背了概念没有真正处理过对应场景答起来很容易泛泛而谈。真正拉分的是后面的SQL题和建模题SQL题直接给一张游戏登录日志表让你算次日留存率建模题给一个充值订单业务场景要求设计核心维度和事实表。这些题目考的不是你会不会写SQL而是你能不能像一个真正在做数仓的人那样思考问题。我的建议是准备这类笔试前先把游戏业务的核心指标口径搞清楚。活跃用户数、新增用户数、留存率、付费率、ARPU、ARPPU这些指标分别怎么定义、怎么从原始日志里算出来必须做到能直接手写SQL的程度。这些指标理解透了后续做题会顺畅很多。1.2 题目类型分布与时间分配我回忆了一下当时的笔试结构整体是90分钟到120分钟分为选择题/判断题、SQL编程题、建模设计题、简答题几个部分。选择题大概10道左右主要考察基础理论比如Hive的架构、数据仓库分层思想、维度建模基本概念、MapReduce的基本流程等难度不算高但覆盖面比较广。SQL编程题通常是2到4道从易到难排列。最开始的是简单的过滤、聚合慢慢会过渡到窗口函数、多表关联、留存计算这类偏实战的题目。建模设计题一般是一道完整的业务场景题需要你设计维度表、事实表并说明分层架构。这种题对表达能力要求比较高不是你把表字段列出来就完了还要解释为什么这么设计、什么粒度、怎么更新。我当时的时间分配策略是选择题控制在15分钟以内SQL题每道15到20分钟建模题留足30分钟简答题10分钟收尾。这里有个很关键的提醒——建模题一定要留够时间因为它是整套卷子里面最能拉开差距的部分。很多同学前面SQL磨太久后面建模题草草写两行就交卷了非常可惜。宁可前面SQL少写一种优化解法也要保证建模题的结构完整。2. 数据仓库基础理论必须烂熟于心2.1 维度建模星型模型与雪花模型数据仓库开发工程师笔试里维度建模是绝对的核心考点。星型模型和雪花模型的区别几乎每场笔试都会出现。我当时的理解可以概括成一句话星型模型是维度表直接围绕事实表展开雪花模型是在星型基础上把维度表进一步规范化拆分。举个例子一个游戏充值订单事实表关联用户维度、商品维度、区服维度、时间维度星型模型里用户维度表就是一个单独的表里面可以直接放用户的注册时间、所在渠道、设备类型。雪花模型则会把用户维度表里的渠道信息再拆成一张独立的渠道表通过外键关联。这样做的好处是减少数据冗余坏处是查询时要多关联一层表性能会下降。实际游戏数仓项目中绝大多数场景用的是星型模型。原因是数仓查询面对的是海量数据多一次join查询时间和资源消耗都会明显上升而游戏业务的维度表本身量级不大冗余一点完全能接受。我笔试答题时会在写清楚两者区别后主动补一句“在游戏数仓场景下我更倾向于星型模型因为查询简单、性能可控”这种补充往往能体现你真的做过项目而不是单纯背概念。另外雪花模型也不是完全没用。比如公司级别的公共维度像渠道信息被多个业务线共用时为了统一维护单独拆成一张维表是合理的。答题时可以提这种场景显得思考更全面。2.2 事实表与维度表的设计原则事实表是数仓建模的核心我的理解是事实表记录的是业务过程产生的度量值通常会不断增长维度表记录的是业务的描述信息比如人、物、时间、地点这些上下文。区分一个表到底是事实表还是维度表最简单的办法是看它的核心字段是数值型度量还是描述型属性。笔试里经常考到事实表的三种类型事务事实表、周期快照事实表、累积快照事实表。我做题时用的是这种记忆方式事务事实表每一行代表一个业务事件比如一笔充值订单一旦发生就不会变周期快照事实表每行代表一定周期内的状态总结比如每天玩家的账户余额快照累积快照事实表则记录一个业务流程从开始到结束的多个阶段比如一个订单从下单、支付、发货到完成的时间节点。维度表设计上代理键和自然键是高频考点。代理键是数仓内部自增生成的ID与业务无关自然键是业务系统里本来就有ID。游戏业务里用户ID在业务系统里是自然键但数仓建模时通常会用自增代理键这样可以用SCD方式处理维度变化避免因为业务ID变动影响历史数据。事实表粒度是另一个必须说清楚的点。我笔试时遇到的一个经典问题是“如果一张事实表同时记录了充值订单和退款订单粒度应该怎么定”。正确思路是每行一个订单级别流水充值为正数、退款为负数再用订单状态字段区分。如果不加区分地退款的金额和充值金额放在一起求和GMV统计就会出错。这些都是题里隐藏的坑答题时主动把这些边界条件列出来能拿到不少印象分。2.3 缓慢变化维SCD的处理策略缓慢变化维是维度建模里绕不开的知识点。游戏业务里最直观的例子是玩家等级和区服玩家从1级升到80级他所在的大区也可能因为合服发生变化那么用户维度表里这些属性怎么处理是直接覆盖还是保留历史SCD有三种经典策略。第一种是直接覆盖不保留任何历史适用于错误修正这类场景第二种是新增一行旧记录失效、新记录生效这就是拉链表第三种是新增一列用“当前值”和“历史值”字段分别存储。笔试里如果让你针对“玩家转移大区”这个需求设计维度表最稳妥的答法是SCD2因为游戏运营需要分析玩家转区前后的行为变化必须能查历史。我当时注意到一个容易出错的地方SCD2虽然能查历史状态但它会让维度表变大而且事实表关联时要特别注意关联的是哪个时间段的版本快照。很多同学在笔试里把SCD2的建表语句写出来了但没说明关联逻辑这样答题是不完整的。应该补充说明事实表关联维度表时要拿事实表的发生日期去匹配维度表的生效时间和失效时间才能准确还原当时的维度属性。3. SQL与Hive实战题从思路到答案3.1 经典SQL题留存率计算留存率计算是游戏数仓笔试里最常出现的SQL题没有之一。题目一般是这样有一张玩家登录日志表字段包括玩家ID、登录日期问次日留存率、7日留存率、30日留存率怎么算。看到这道题第一反应不是马上写SQL而是先明确口径。留存率的分母是某日新增用户数还是某日活跃用户数这直接决定结果。游戏行业里最常用的次日留存率定义是某日新增用户中第二天仍然有登录行为的用户占比。如果题目没有明确说明就按这个口径来并在答题时写清楚假设。Hive SQL的写法思路是先按玩家ID和登录日期去重避免同一天多次登录导致数据膨胀然后算出每个用户的首登日期最后用首登日期和每个登录日期做差。我用的是left join的方式把用户表和登录表关联起来再用datediff判断间隔天数。-- 先对原始日志去重得到用户每日活跃表 with user_active as ( select user_id, dt from game_login_log where dt between start_date and end_date group by user_id, dt ), -- 计算每个用户的首登日期 user_first as ( select user_id, min(dt) as first_dt from user_active group by user_id ) -- 以首登用户为分母统计次日仍有活跃的用户 select a.first_dt as dt, count(distinct a.user_id) as new_users, count(distinct case when datediff(b.dt, a.first_dt) 1 then a.user_id end) as day1_retained_users, round(count(distinct case when datediff(b.dt, a.first_dt) 1 then a.user_id end) / count(distinct a.user_id) * 100, 2) as day1_retention_rate from user_first a left join user_active b on a.user_id b.user_id and datediff(b.dt, a.first_dt) in (1, 7, 30) group by a.first_dt order by a.first_dt;这里有个关键细节left join之后如果直接用count(b.user_id)去统计次日留存用户可能会出现重复计数因为一个用户第二天可能登录多次。虽然前面user_active已经按用户ID和日期去重了但为了保险起见我还是用了count(distinct case when ...)的写法宁可多写一点也要保证结果准确。笔试阅卷时这种细节恰恰是加分项。3.2 窗口函数在处理游戏日志中的应用窗口函数是现在数仓笔试SQL题的高频考点。游戏场景下最常见的三类窗口函数需求一是连续登录天数判断二是付费金额排名和Top N统计三是漏斗转化分析。连续登录N天这个需求我用过一种很实用的解法叫做“日期减去连续序号”。思路是先把用户每天的登录记录去重然后用row_number()按用户分组、按日期排序生成序号再把登录日期减去这个序号得到一个新的日期值。如果用户是连续登录这个新日期是一样的按用户ID和新日期分组组内记录数就是连续登录天数。with user_active as ( select user_id, dt from game_login_log group by user_id, dt ), user_rank as ( select user_id, dt, date_sub(dt, row_number() over (partition by user_id order by dt)) as grp from user_active ) select user_id, count(*) as consecutive_days, min(dt) as start_dt, max(dt) as end_dt from user_rank group by user_id, grp having count(*) 3;这个写法的精妙之处在于它不需要自关联也不涉及复杂的递归逻辑用一条SQL就完成了。笔试时如果遇到类似题目我建议先把“日期减序号”这个思路写出来再套上SQL阅卷人一看就知道你是真懂不是死记答案。付费排名场景也很常见比如“求每个区服充值金额最高的前10个玩家”。这种题目基本上就是row_number()或者rank()窗口函数加上过滤条件没有再复杂的了。3.3 笔试中容易丢分的细节SQL题丢分往往不是因为不会写而是因为细节处理不到位。我总结了自己和其他同学在笔试中经常踩的坑有几个特别典型。第一个坑是不去重。游戏日志表里同一用户一天内可能有多条记录如果没做group by user_id, dt就去计算留存率结果会虚高。答题时一定要把去重这一步写在最前面并且注释说明原因。第二个坑是日期处理不规范。Hive里日期字段可能是date类型也可能是string类型直接用datediff时两个参数的格式必须一致。有些题目给的日期是2020-08-01这种字符串有些给的是20200801一定要先格式化统一否则计算结果会是负数或者空值。第三个坑是忽略空值。left join之后右侧表的字段会有null如果直接用count(b.user_id)统计会自动跳过null但如果用的是count(1)或者count(*)就会把null也数进去导致结果错误。建议统一用count(distinct case when ...)这种写法把统计逻辑写清楚。第四个坑是保留小数。留存率、付费率这类指标一般要求百分比保留两位小数用round(..., 2)处理一下就好。我见过不少同学SQL逻辑完全正确就是忘记加round白白丢分。4. 用户订单分析数据仓库设计题拆解4.1 需求解读游戏充值订单分析要解决什么问题建模设计题通常是整套笔试题里最重头的内容。搜狐畅游这类游戏公司的建模题十有八九会落在游戏充值订单分析这个业务场景上因为这是游戏公司最核心的收入来源。题目一般会给你一段业务描述玩家在游戏内通过渠道下载游戏、注册账号、创建角色、在游戏内购买道具后台记录了每一笔充值订单现在需要建设数据仓库来分析收入情况。接到这种题第一件事不是急着设计表而是拆解业务需求。我会先把指标列出来总的充值金额GMV、充值用户数、付费率、ARPU每用户平均收入、ARPPU每付费用户平均收入、每一笔订单的支付状态、不同渠道的充值贡献、不同道具类型的销售情况。这些指标再往下拆就需要明确粒度和维度。还有一类需求很容易被忽略运营侧要分析活动效果。比如某个时间段内上线了一个充值返利活动想对比活动前后付费用户数的变化。要做到这一点订单事实表里最好预留一个活动ID字段用来标记每笔订单是否来自活动。虽然题目不一定明确要求这个字段但主动加上能体现你对业务的理解深度。我当时做题的答题框架是先写清楚需求分析和指标口径再给出维度表设计再给出事实表设计最后补充分层架构和ETL同步策略。这四步走下来整个设计题就是一篇完整的小方案阅卷人一眼就能看出你的思路是清晰的。4.2 核心维度表设计维度表描述的是业务过程发生的上下文在订单分析场景里核心维度有用户、商品、区服、渠道、时间。用户维度表是游戏数仓里最重要的维度表字段包括用户ID、用户昵称、性别、设备类型、注册时间、注册渠道、注册区服、当前等级、当前活跃状态等。因为是SCD2设计还要加上生效时间、失效时间、是否当前版本这三个字段。这样既可以查用户当前的属性也能回溯用户在某一天的历史状态。商品维度表描述的是玩家购买的道具字段包括道具ID、道具名称、道具类型比如角色、皮肤、体力、礼包、品质、原价、现价、是否限时等。设计商品维度表时要注意道具的价格可能会调整所以价格应该放到事实表里而不是维度表里否则SCD维护起来非常痛苦。区服维度表在游戏公司特别常见包含区服ID、区服名称、大区、开服时间、合服时间等字段。由于游戏业务经常合服区服维度的变化也比较频繁同样要考虑SCD策略。渠道维度表相对简单包含渠道ID、渠道名称、渠道商、推广方式等字段。时间维度表属于通用维度几乎每个数仓都会有通常直接生成一张日期表包含日期、年、月、周、季度、是否工作日、是否节假日等字段。这些维度表设计完之后需要用外键关联到事实表。4.3 核心事实表设计订单事实表是整个订单分析数仓的核心。我的设计思路是每笔订单一行粒度是订单级别这样既能满足GMV汇总需求也能做订单级的明细分析。关键字段包括订单号、用户ID、商品ID、区服ID、渠道ID、活动ID、订单金额、实付金额、订单状态、下单时间、支付时间、退款时间等。这里特别注意一下金额字段的处理我建议订单金额和实付金额分开存。订单金额是原价实付金额是折扣后玩家实际付出的钱两者比例可以用于分析折扣力度对付费的影响。如果活动是“充值满100返20”返利金额应该单独建字段而不是改变实付金额否则后续统计口径会乱。订单状态字段也很重要一般包括待支付、支付成功、已退款、已关闭等。算GMV时只统计支付成功的订单算退款率时统计退款订单状态字段是过滤的关键。很多同学设计订单表时只放一个总金额字段不区分订单状态这在笔试里会被扣分因为实际业务中退款是常态不考虑退款的设计是没法上线的。除了明细级的订单事实表我还会再加一张日汇总事实表也就是DWS层按天、区服、渠道、商品类型分组统计充值金额、充值人数、订单数、退款金额等指标。这张表的作用是让上层报表和应用直接查询不用每次跑明细表性能会好很多。笔试时我会把这两张表都写出来并说明它们之间的层级关系。4.4 分层架构与ETL流程数仓分层是所有笔试的必考内容。标准的四层架构是ODS、DWD、DWS、ADS。我答题时的顺序是先讲每一层做什么再讲数据怎么流动最后讲每一层在订单分析里具体存什么数据。ODS层是源系统数据的原始落地把游戏DB里的订单表、用户表、道具表通过离线同步工具按天增量同步到数仓保持原始字段不变相当于备份层。DWD层进行数据清洗和规范化把订单表里的状态字段转成可读的枚举值统一时间格式补齐用户维度、商品维度、时间维度这些外键。DWS层做轻度汇总把DWD层的订单明细按天、按维度分组计算出GMV、付费用户数、订单数这些核心指标生成前面说的日汇总事实表。ADS层面向具体业务应用比如运营看板、付费分析报表、渠道分析平台等每张表服务于一个具体主题。ETL流程方面我重点说明了增量同步策略订单表每天都有新增和状态变更所以用增量方式按订单修改时间同步用户维度是SCD2表每天同步全部变更记录用唯一键去重商品维度表变化频率很低可以每周全量同步一次。笔试答题时把这些调度频率和同步方式写清楚说明你考虑过实际运维成本这是区分新手和老手的关键。5. 笔试中的其他高频考点与常见问题5.1 数据倾斜与优化数据倾斜是数据仓库开发笔试里一个很常见的“进阶题”它通常不会单独出题而是隐藏在SQL题或者简答题里让你分析某个SQL跑得特别慢的原因。游戏业务里最常见的场景是按用户ID分组统计时个别大R玩家高付费玩家的数据量特别大导致某个reduce任务一直跑不完。答题的标准思路是先说清楚数据倾斜的本质数据分布不均导致某些节点处理的数据量远超其他节点。然后给出优化方案。最简单的方案是MapJoin通过map端完成join避免shuffle阶段的数据倾斜适用于小表join大表的场景。如果确实是大表join大表可以给join key加随机前缀把数据打散到多个reduce里去处理再加一层去前缀的聚合。还有一个场景是group by倾斜比如统计每个渠道的充值金额时某个大渠道的数据量占比过高。优化方式是先加随机前缀打散做局部聚合再去掉前缀做全局聚合。市面上很多教程管这个叫“两阶段聚合”笔试里如果能把这个方案的完整SQL写出来印象分很高。但需要提醒一句不是所有SQL都需要考虑数据倾斜答题时先判断数据量级和分布再决定是否需要优化这样的回答更显成熟。5.2 面试官追问与应答思路笔试通过了之后紧接着就是面试。面试官会拿着你的笔试答案追问问得最多的是指标口径和边界场景。我当时被追问的一个典型问题是“你算的留存率里分母到底是什么”——这个问题就是考察你是否真的理解业务指标而不是背了一个公式。应答留存率相关问题时标准思路是把口径拆清楚分母是当日新增用户数分子是这些新增用户中第N天还有登录行为的用户数。要补充说明的是如果一个用户当天新增且当天就有充值行为这个用户该算新增还是付费需要有一个清晰的归属逻辑。游戏行业的通用做法是新增用户按首登日期归属付费用户按首充日期归属两个维度互不冲突。另一个高频追问是“如果业务方发现报表数据和线上数据对不上你会怎么排查”。这是个典型的数据质量排查场景我建议从四个方向回答先检查ODS同步是否完整看有没有丢分区再检查DWD层清洗逻辑看过滤条件是否有误然后检查事实表关联是否出现数据膨胀最后检查指标口径是否被改动比如线上把GMV定义为含退款金额而报表这边用的是不含退款金额。这四步排查下来基本能覆盖80%的数据对不上的问题。面试官还喜欢问“什么时候用宽表什么时候用明细表”。我的回答是面向业务报表和即席查询的场景优先用宽表因为查询快、理解成本低面向深度分析和数据挖掘的场景保留明细表更灵活因为宽表一旦做好维度组合就固定了临时想加一个维度会很痛苦。游戏数仓的常规做法是DWD保留明细DWS和ADS按主题建设宽表各司其职。5.3 我踩过的坑和实战心得笔试复盘到这里我想把自己踩过的坑再多说几句。第一个坑是建模题里忘了写粒度。我当时做设计题上来就画表结构字段写了一堆但没写“每行记录代表一笔订单”这句话导致后面很多字段解释不清楚。后来面试官提醒我建模第一步永远是确定粒度粒度不写清楚事实表设计就是空中楼阁。这个习惯工作后也一直保持到现在。第二个坑是SQL题里过度追求“最简写法”。有一次我为了少写几行把留存计算里count(distinct case when ...)简化成count(case when ...)结果因为用户一天登录多次最终的留存用户数被重复统计。这种坑在笔试里太容易踩了因为你自己构造的测试数据永远是完美的但生产环境的数据从来不会。第三个坑是不看题目场景照搬模板。有些同学看到留存率就套“新增用户留存”的模板但题目可能问的是“活跃用户留存”或者“付费用户留存”分母完全不同。拿到题目先花30秒理解业务场景再决定用哪个口径远比急着敲代码重要。说到底数据仓库开发工程师的笔试核心考察的是三个方面基础理论扎不扎实、SQL会不会处理真实数据的脏活、建模有没有业务sense。搜狐畅游2020年校招这批题回头再看其实和现在绝大多数游戏公司数仓岗位的考查方向没有本质区别。如果能把上面这些知识点消化到位再配合足够的刷题量顺利通过笔试是完全可期的。