最近有个朋友分享了他参加了某互联网（直播带货）公司数据分析师的面试，以下是相关 SQL 笔试题的解析，使用的数据库是 MySQL 8.0。
如果觉得文章有用，欢迎关注❤️、点赞👍、推荐🎁
第 1 题：直播间人气值
问题描述
直播开播记录表 t1 包含以下字段：
主播 id：author_id
直播间 id：live_id
开播时长：live_duration
直播观看记录表 t2 包含以下字段：
观众 id：user_id
直播间 id：live_id
观看时长：watching_duration
要求计算直播间的人气值，输出结果格式如下：
|主播 id| 直播间 id | acu|
其中，ACU 为平均同时在线人数（Average concurrent users），计算方式为：观众侧观看时长/某场直播的开播时长，没有人观看的时候显示为 0。
问题解析
首先，通过连接开播记录表 t1 和观看记录表 t2 可以得到计算 ACU 所需的信息，然后将所有观众的观看时长加起来，除以开播时长即可。例如：
-- 创建示例表CREATE TABLE t1 (author_id integer, live_id integer, live_duration integer);INSERT INTO t1 VALUES (1, 1, 120), (2, 2, 180), (3, 3, 60);CREATE TABLE t2 (user_id integer, live_id integer, watching_duration integer);INSERT INTO t2 VALUES (1, 1, 30), (2, 1, 40), (3, 1, 50);INSERT INTO t2 VALUES (3, 2, 30), (4, 2, 60);-- 计算 ACUSELECT t1.author_id, t1.live_id, sum(t2.watching_duration)/t1.live_duration AS acuFROM t1JOIN t2 ON (t2.live_id = t1.live_id)GROUP BY t1.author_id, t1.live_id;author_id|live_id|acu |---------+-------+------+1| 1|1.0000|2| 2|0.5000|
以上查询使用到了内连接、GROUP BY 分组以及 SUM 聚合函数进行分组统计。
但是，以上查询还存在一个问题：直播间 3 没有观众，导致内连接查询没有返回结果。所以，我们还需要处理一下没有人观看的情况，方法就是使用左连接查询：
SELECT t1.author_id, t1.live_id, COALESCE(sum(t2.watching_duration), 0)/t1.live_duration AS acuFROM t1LEFT JOIN t2 ON (t2.live_id = t1.live_id)GROUP BY t1.author_id, t1.live_id;author_id|live_id|acu |---------+-------+------+1| 1|1.0000|2| 2|0.5000|3| 3|0.0000|
除了左连接之外，我们还使用了 COALESCE 函数，将空值转换为 0。当然 MySQL 中也可以使用 IFNULL 函数。
第 2 题：累计销售金额
问题描述
销售记录表 t3 包含以下字段：
用户 id：user_id
销售日期：sell_day
销售金额：amount
要求计算每个用户首次销售日期后 30 天累计销售金额，输出结果格式如下：
| 用户 id | 首次销售日期 | 首次销售日期后 30 天累计销售金额 |
问题解析
这个问题可以拆分为两个步骤，首先查找每个用户首次销售日期。这个可以通过 GROUP BY 基于用户 id 分组，然后使用聚合函数 MIN 返回最早的销售日期。实现的代码如下：
-- 创建示例表CREATE TABLE t3 (user_id integer, sell_day date, amount numeric);INSERT INTO t3 VALUES (1, '2021-01-01', 100), (1, '2021-01-02', 100), (1, '2021-01-29', 100),(1, '2021-02-01', 100);INSERT INTO t3 VALUES (2, '2021-01-10', 200), (2, '2021-01-11', 200), (2, '2021-01-12', 200),(2, '2021-01-13', 200);-- 查找每个用户的首次销售日期SELECT user_id, min(sell_day) AS first_dayFROM t3GROUP BY user_id;user_id|first_day |-------+----------+1|2021-01-01|2|2021-01-10|
然后，我们可以基于这些首次销售日期统计 30 天内的累计销售金额：
WITH s AS (SELECT user_id, min(sell_day) AS first_dayFROM t3GROUP BY user_id)SELECT s.user_id, s.first_day, sum(t3.amount) total_amountFROM sJOIN t3ON (t3.user_id = s.user_id AND t3.sell_day BETWEEN s.first_day AND s.first_day + INTERVAL '29' DAY)GROUP BY s.user_id, s.first_day;user_id|first_day |total_amount|-------+----------+------------+1|2021-01-01| 300|2|2021-01-10| 800|
以上查询中的 WITH 子句定义了一个通用表表达式，包含了每个用户的首次销售日期，然后和 t3 进行连接查询，返回了所需的数据。
对于这个问题，我们也可以使用窗口函数解决。例如：
SELECT *FROM (SELECT user_id, sell_day, amount,RANK() OVER (PARTITION BY user_id ORDER BY sell_day) AS rk,first_value(amount) OVER (PARTITION BY user_id ORDER BY sell_day) AS fisrt_day,sum(amount) OVER (PARTITION BY user_id ORDER BY sell_day RANGE BETWEEN CURRENT ROW AND INTERVAL '30' DAY FOLLOWING)FROM t3) tWHERE rk = 1;
RANK 函数用于分组排名，PARTITION BY 表示按照用户分组，ORDER BY 表示按照销售日期排名，第 1 名就是最早销售日期。first_value 函数用于返回最早销售日期对应的销量。SUM 函数用于返回最早销售日期 30 天内的累计销量。
第 3 题：渠道销量占比
问题描述
商家卖货记录表 t4 包含以下字段：
卖家 id：seller_id
买家 id：buyer_id
物品 id：item_id
销量：order_cnt
单价：price
商品渠道：source_type（1 表示自建商品，0 表示其他）
要求计算每个商家的自建商品销量占总销量的比例，输出信息的结构如下：
|卖家 id| 自建商品销量占比 |
问题解析
这个问题比较简单，只需要按照商家统计自建商品的销量和总销量，然后两者相除就可以了。例如：
-- 创建示例表CREATE TABLE t4 (seller_id integer, buyer_id integer, item_id integer, order_cnt integer, price numeric, source_type tinyint);INSERT INTO t4 VALUES (1, 11, 1, 5, 9.9, 1), (1, 12, 2, 15, 16.0, 0);INSERT INTO t4 VALUES (2, 13, 3, 10, 89, 0), (2, 14, 4, 1, 2000, 0);-- 每个商家自建商品销量占总销量的比例SELECT seller_id,sum(CASE source_type WHEN 1 THEN order_cnt ELSE 0 END)/sum(order_cnt) AS ratioFROM t4GROUP BY seller_id;seller_id|ratio |---------+------+1|0.2500|2|0.0000|
以上查询使用了两个 SUM 函数，第一个函数中包含了一个 CASE 表达式，它的作用就是统计自建商品的销量。第二个 SUM 函数的作用是统计所有商品的总销量。
第 4 题：畅销商品
问题描述
商家卖货记录表 t5 包含以下字段：
卖家 id：seller_id
买家 id：buyer_id
物品 id：item_id
物品数量：num
要求计算每个卖家销量最高的商品，输出信息包含以下内容：
|卖家 id| 物品 id | 物品总销量 |
问题解析
这是一类分组排名问题，需要按照卖家分组，计算销量最高的商品。这类问题使用窗口函数解决最方便，例如：
-- 创建示例表CREATE TABLE t5 (seller_id int, buyer_id int, item_id int, num int);INSERT INTO t5 VALUES (1, 11, 1, 100), (1, 12, 1, 200), (1, 12, 2, 300);INSERT INTO t5 VALUES (2, 11, 1, 100), (2, 12, 3, 200);-- 计算每个卖家销量最高的商品WITH sales AS (SELECT seller_id, item_id, sum(num) AS ssFROM t5GROUP BY seller_id, item_id),sales_rank AS (SELECT seller_id, item_id, ss,rank() OVER (PARTITION BY seller_id ORDER BY ss DESC) AS rkFROM sales)SELECT *FROM sales_rankWHERE rk = 1;seller_id|item_id|ss |rk|---------+-------+---+--+1| 1|300| 1|1| 2|300| 1|2| 3|200| 1|
首先，WITH 子句定义了两个通用表表达式。其中 sales 包含了每个卖家、每种物品的总销量。sales_rank 基于这个 sales 计算了同一个卖家的不同物品的销量排名，PARTITION BY 表示按照卖家分组，ORDER BY 表示按照销量从高到低排名。最后的查询语句返回了排名最高的商品。
其中，卖家 1 的商品 1 和商品 2 的销量相同，因此返回了两条记录。
总结
对于数据分析岗而言，SQL 分组聚合、CASE 表达式、窗口函数的掌握是基本要求，随着 MySQL 8.0 的推出使得这一切变得更加简单。
