Happy Games Studio 数据分析师测试题全解：亿级订单表 SQL 聚合查询实战指南

• 教程

本篇技术指南围绕仓库中 analytics/happy-games-studio-analitik-dannykh/README.md 记载的“Happy Games Studio 数据分析师”SQL 测试题展开，完整还原三张订单域数据表结构、百万级测试数据生成方案，并逐步给出四道聚合查询的解析与标准 SQL 答案。读完本文，你将掌握订单明细粒度建模、窗口函数与月同比对比、HAVING 过滤分组等实战技巧，可直接复用于真实数据分析面试与订单分析场景。

一、任务背景与数据模型

本测试题来自 Happy Games Studio（游戏工作室）的数据分析师岗位招聘，要求应聘者独立完成一套包含建表、造数、查询的完整 SQL 实操任务。考核重点并非单条语句的编写，而是对数据库建模、大数据量下的查询性能、时间维度聚合分析的综合理解。

题目给出的数据模型是典型的电商/游戏商城订单三表结构，从源码角度看，仓库 sql 目录 中收录了大量同类 SQL 测试（如 Sberbank、Alfabank、Samokat 的题库），本任务是其中覆盖面最完整的一份——既要求建模、造数，又要求四道不同难度的分析查询。

三张表结构如下：

任务要求**每张表至少 100 万行（1 百万条）**测试数据，这在数据建模上是一个明确的性能压力点：即便是不带任何索引的裸表，对百万行做JOIN与GROUP BY聚合也需要谨慎设计查询路径，否则全表扫描将拖慢整个练习流程。

二、数据库选型与 Schema 设计

题目要求“在答复中说明所使用的数据库名称与版本，并附上数据库结构 dump”。在选型上建议选择免费、跨平台、文档丰富的 PostgreSQL，例如 16.x 版本，理由如下：

• 对窗口函数、CTE、date_trunc等日期聚合函数的支持完善，恰好覆盖后四道查询的全部需求；
• 生成百万级序列化测试数据非常方便（generate_series）；
• 在真实订单分析场景中（如仓库 samokat-analitik-dannykh 下的orders.csv、warehouses.csv等数据集），PostgreSQL 同样是最常见的分析型 SQL 环境。

完整的建表 Schema（PostgreSQL 方言）如下：

-- 用户表 CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单表 CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total_price NUMERIC(12,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单明细表 CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), product_name VARCHAR(255) NOT NULL, price NUMERIC(12,2) NOT NULL, quantity INT NOT NULL CHECK (quantity > 0) ); -- 为高频过滤与聚合字段建立索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); CREATE INDEX idx_order_items_order_id ON order_items(order_id);

设计要点说明：

• id使用BIGSERIAL自增主键，避免百万级插入时手写 ID 冲突；
• total_price、price使用NUMERIC(12,2)精确十进制，避免浮点误差污染金额统计；
• created_at统一为TIMESTAMPTZ，保证“最近一个月”“当前年份”“去年同期”等时区敏感查询结果一致；
• 三个外键/过滤字段的索引是后文四道查询在百万行数据上保持可用性的关键，特别是orders(created_at)与orders(user_id)的组合，直接服务于“近一年/近一月”时间窗口过滤。

参照仓库中同类测试题的 dump 风格，例如 Sberbank 员工表测试 使用CREATE TABLE+INSERT INTO ... VALUES的方式给出完整可运行脚本，本任务同样要求交付建表 + 造数的完整 dump。

三、百万级测试数据生成方案

在 PostgreSQL 中最优雅的造数方式是利用generate_series与随机函数组合。以下脚本可在几十秒内为三张表各生成 100 万行数据：

-- 1) 生成 100 万用户 INSERT INTO users (name, email, created_at) SELECT 'User_' || gs, 'user_' || gs || '@example.com', timestamp '2020-01-01' + random() * interval '5 years' FROM generate_series(1, 1000000) AS gs; -- 2) 生成 100 万订单：随机挂到用户上，时间分布近两年 INSERT INTO orders (user_id, total_price, created_at) SELECT floor(random() * 1000000) + 1, -- 随机 user_id round((random() * 5000 + 10)::numeric, 2), -- 订单金额 10 ~ 5010 now() - random() * interval '730 days' -- 近两年内随机时间 FROM generate_series(1, 1000000) AS gs; -- 3) 生成 100 万订单明细：每个订单 1~5 个商品行 INSERT INTO order_items (order_id, product_name, price, quantity) SELECT floor(random() * 1000000) + 1, -- 随机 order_id 'Product_' || (floor(random() * 100) + 1), -- 100 种商品之一 round((random() * 500 + 1)::numeric, 2), -- 单价 1 ~ 501 floor(random() * 5) + 1 -- 数量 1~5 FROM generate_series(1, 1000000) AS gs;

造数要点：

• random()返回[0,1)区间的浮点数，配合floor与+1可得到指定范围的整数；
• timestamp '2020-01-01' + random() * interval '5 years'让用户注册时间均匀分布在五年内，保证“当前年份”与“上一年”都有足够数据；
• 订单时间使用now() - random() * interval '730 days'分布在最近两年，确保“最近一个月”“最近一年”“今年 vs 去年同月”四道题的窗口都有数据可查；
• 建议造数前先关闭自动提交、批量提交（或使用COPY导入），可显著缩短百万行插入耗时；
• 若使用 MySQL，可将BIGSERIAL换成BIGINT AUTO_INCREMENT、TIMESTAMPTZ换成DATETIME、interval换成DATE_SUB(NOW(), INTERVAL ...)，思路完全一致。

四、查询 1：统计下单超过 10 次的用户

题目：找出每个下单超过 10 次的用户的订单总数。

标准答案：

SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 10 ORDER BY order_count DESC;

解析：

• 按user_id分组后，COUNT(*)即每个用户的订单数；
• HAVING COUNT(*) > 10在分组后过滤，只保留下单超过 10 次的用户——这是WHERE无法替代的：WHERE作用于分组前的行，HAVING作用于分组后的聚合结果；
• ORDER BY order_count DESC按订单数降序输出，便于人工核验 top 用户。

性能提示：在 100 万行orders上，本查询依赖idx_orders_user_id索引完成分组预排序；如果仍需全表扫描，PostgreSQL 会退化为 Sort+GroupAggregate，在百万行规模下耗时仍在可接受范围，但建议始终保留该索引。

五、查询 2：每个用户最近一个月的平均订单额

题目：计算每个用户最近一个月的平均订单金额。

标准答案：

SELECT user_id, AVG(total_price) AS avg_order_amount FROM orders WHERE created_at >= date_trunc('month', now()) - interval '1 month' AND created_at < date_trunc('month', now()) GROUP BY user_id;

解析：

• date_trunc('month', now())返回当前自然月的 1 日 0 点；
• 时间窗口[当月月初 - 1 个月, 当月月初)精确覆盖“上一个完整自然月”，避免误用now() - interval '30 days'造成的滑动窗口偏差（后者跨月且长度不等）；
• 对每个用户用AVG(total_price)求月内订单均值，无订单的用户自然不出现。

若希望覆盖“最近 30 天”而非自然月，可替换为WHERE created_at >= now() - interval '30 days'，语义不同，按题面“最近一个月”一般取自然月更严谨。

六、查询 3：今年各月平均订单额与去年同期对比

题目：计算当前年份每个月的平均订单金额，并与上一年同月份对比。

标准答案（PostgreSQL）：

WITH monthly AS ( SELECT date_trunc('month', created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part('year', created_at) IN (date_part('year', now())::int, date_part('year', now())::int - 1) GROUP BY date_trunc('month', created_at) ) SELECT to_char(m.month, 'YYYY-MM') AS month, m.avg_amount AS current_year_avg, prev.avg_amount AS last_year_avg, round((m.avg_amount - prev.avg_amount) / NULLIF(prev.avg_amount, 0) * 100, 2) AS yoy_change_pct FROM monthly m JOIN monthly prev ON prev.month = m.month - interval '1 year' ORDER BY m.month;

解析：

• 先用 CTE 将订单按date_trunc('month', created_at)聚合出“每月平均订单额”，只需扫描一次orders，后续自连接直接在内存结果上完成；
• 自连接条件prev.month = m.month - interval '1 year'精确定位上一年同月，天然实现对“2026-03 对比 2025-03”式的月同比；
• NULLIF(prev.avg_amount, 0)防止上年该月无数据时除零报错，输出NULL表示无法计算；
• to_char(m.month, 'YYYY-MM')输出可读的月份字符串；若统计粒度为每个订单而非月份均值，也可在 CTE 中按date_part('month', created_at)直接分组，语义等价。

若数据库为 MySQL，可用DATE_FORMAT(created_at, '%Y-%m')替换to_char，用DATE_SUB或INTERVAL 1 YEAR实现同月偏移，整体逻辑不变。

七、查询 4：近一年订单最多的 10 个用户及近一月均值

题目：找出最近一年内下单数量最多的 10 个用户，并同时计算他们最近一个月的平均订单额。

标准答案（PostgreSQL）：

WITH top_users AS ( SELECT user_id, COUNT(*) AS yearly_orders FROM orders WHERE created_at >= now() - interval '1 year' GROUP BY user_id ORDER BY yearly_orders DESC LIMIT 10 ) SELECT tu.user_id, tu.yearly_orders, COALESCE(m.avg_monthly, 0) AS avg_monthly_amount FROM top_users tu LEFT JOIN ( SELECT user_id, AVG(total_price) AS avg_monthly FROM orders WHERE created_at >= date_trunc('month', now()) - interval '1 month' AND created_at < date_trunc('month', now()) GROUP BY user_id ) m ON m.user_id = tu.user_id ORDER BY tu.yearly_orders DESC;

解析：

• 第一步在“近一年”窗口内统计每个用户订单数并ORDER BY ... LIMIT 10，选出 Top-10 活跃用户；
• 第二步复用第五节的“上一个月”聚合逻辑，仅针对这 10 个用户二次计算月均订单额；
• 使用LEFT JOIN而非INNER JOIN：若某 top 用户上个月没有订单，avg_monthly为NULL，用COALESCE(..., 0)兜底为 0，保证 10 行结果不丢用户；
• 两段窗口条件写在两个子查询中，语义独立清晰，比一次性WHERE混合两个时间窗口更不易出错。

从执行计划角度：该查询是典型的两阶段聚合（先过滤近一年 → 分组排序取前 10 → 再小范围过滤近一月），orders(created_at)与orders(user_id)索引分别服务两个时间窗口的过滤，百万行下性能良好。

八、常见错误写法与改进示范

题目允许“补充一个错误写法并解释原因”，以下是一组典型反例及其问题分析，对面试官而言，这比单写正确答案更能体现候选人的 SQL 功底：

错误写法 1（对应查询 1）：把聚合条件放进 WHERE

-- 错误：WHERE 无法引用聚合结果 SELECT user_id, COUNT(*) AS order_count FROM orders WHERE COUNT(*) > 10 GROUP BY user_id;

问题：WHERE在GROUP BY之前求值，此时COUNT(*)尚未计算，语法上会直接报错；语义上“按行过滤”和“按分组过滤”是两个不同阶段，必须使用HAVING。

错误写法 2（对应查询 2）：滑动窗口当作自然月

-- 错误：30 天窗口跨月，口径不严谨 SELECT user_id, AVG(total_price) AS avg_amount FROM orders WHERE created_at >= now() - interval '30 days' GROUP BY user_id;

问题：now() - interval '30 days'得到的是滚动 30 天区间，会跨越自然月边界，不同日期运行结果口径不同，无法与“按自然月”的其他指标对齐；正确做法是用date_trunc('month', now())取整月边界。

错误写法 3（对应查询 3）：错误使用日期别名参与计算

-- 错误：SELECT 别名在 WHERE/GROUP BY 中不可直接引用（部分数据库行为不同） SELECT date_trunc('month', created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part('year', month) = date_part('year', now()) GROUP BY month;

问题：WHERE与GROUP BY中的别名依赖执行顺序，在多数数据库中不可用；应显式写date_trunc('month', created_at)，或者把聚合放进 CTE 中再引用列名。此外仅过滤“今年”而未引入“去年”数据，无法完成同比。

九、答案交付与自检清单

按题目要求，完整答复应包含以下五部分，缺一不可：

1. 数据库名称与版本：例如PostgreSQL 16，写清 major 版本即可；
2. 数据库结构 dump：三张表的完整CREATE TABLE脚本（含类型、约束、索引）；
3. 测试数据填充脚本：三张表各 100 万行的插入语句，能一键复现；
4. 四道查询的 SQL 与逐条解释：每个查询写明思路、关键函数与结果口径；
5. （可选）错误写法及解释：如上节所示，至少给出 1 个反例并说明问题。

建议在交付前用EXPLAIN ANALYZE检查四道查询在百万行数据上的执行计划，确认是否走索引、是否存在全表扫描，并验证四条语句的结果行数合理性：

EXPLAIN ANALYZE SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 10;

十、延伸思考：从测试题到真实订单分析

这道题覆盖的聚合能力在真实数据分析工作中几乎每天都会用到，仓库中就有多个可直接对照练习的数据集：例如 Samokat 订单数据（含orders.csv、products.csv、warehouses.csv），以及 Delimobil 测试数据、Wolt 分析师数据集 等。把本任务中的四类查询（分组计数 + HAVING、自然月聚合、月同比、Top-N 两阶段聚合）迁移到这些数据集上，即可形成一套完整的“订单域分析”练习闭环。

从更广视角看，仓库 sql 题库 中同类测试题还覆盖了自连接比较（如 Sberbank 员工薪资大于上级）、多表连接与时间过滤（如 Alfabank 按年份与商品名筛选）、日期区间判断（如 Samokat 仓库营业状态统计）等题型，可见“时间维度 + 分组聚合”正是俄罗斯各 IT 公司数据分析师面试的高频考点，本任务则是其中建模、造数、查询一次到位的综合型代表。

• 教程