某互聯網公司資料分析崗 SQL 筆試題

大家好，我是只談技術不剪發的 Tony 老師，

最近有個朋友分享了他參加了某互聯網（直播帶貨）公司資料分析師的實習崗面試，以下是我對相關 SQL 筆試題的決議，使用的資料庫是 MySQL 8.0，

如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

第 1 題：直播間人氣值

問題描述

直播開播記錄表 t1 包含以下欄位：

• 主播 id：author_id
• 直播間 id：live_id
• 開播時長：live_duration

直播觀看記錄表 t2 包含以下欄位：

• 觀眾 id：user_id
• 直播間 id：live_id
• 觀看時長：watching_duration

要求計算直播間的人氣值，輸出結果格式如下：

其中，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);

-- 計算 ACU
SELECT t1.author_id, t1.live_id, sum(t2.watching_duration)/t1.live_duration AS acu
FROM t1
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|

以上查詢使用到了內連接、GROUP BY 分組以及 SUM 聚合函式進行分組統計，

但是，以上查詢還存在一個問題：直播間 3 沒有觀眾，導致內連接查詢沒有回傳結果，所以，我們還需要處理一下沒有人觀看的情況，方法就是使用左連接查詢：

SELECT t1.author_id, t1.live_id, COALESCE(sum(t2.watching_duration), 0)/t1.live_duration AS acu
FROM t1
LEFT 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，當然也可以使用 IFNULL 函式，

第 2 題：累計銷售金額

問題描述

銷售記錄表 t3 包含以下欄位：

• 用戶 id：user_id
• 銷售日期：sell_day
• 銷售金額：amount

要求計算每個用戶首次銷售日期后 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_day
FROM t3
GROUP 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_day
  FROM t3
  GROUP BY user_id
)
SELECT s.user_id, s.first_day, sum(t3.amount) total_amount
FROM s
JOIN t3
ON (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) t
WHERE rk = 1;

第 3 題：渠道銷量占比

問題描述

商家賣貨記錄表 t4 包含以下欄位：

• 賣家 id：seller_id
• 買家 id：buyer_id
• 物品 id：item_id
• 銷量：order_cnt
• 單價：price
• 商品渠道：source_type（1 表示自建商品，0 表示其他）

要求計算每個商家的自建商品銷量占總銷量的比例，輸出資訊的結構如下：

問題決議

這個問題比較簡單，只需要按照商家統計自建商品的銷量和總銷量，然后兩者相除就可以了，例如：

-- 創建示例表
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 ratio
FROM t4
GROUP 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

要求計算每個賣家銷量最高的商品，輸出資訊包含以下內容：

問題決議

這是一類分組排名問題，需要按照賣家分組，計算銷量最高的商品，這類問題使用視窗函式解決最方便，例如：

-- 創建示例表
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 ss
FROM t5 
GROUP 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 rk
FROM sales)
SELECT *
FROM sales_rank
WHERE 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 計算了同一個賣家的不同物品的銷量排名，最后的查詢陳述句回傳了排名最高的商品，

賣家 1 的商品 1 和商品 2 的銷量相同，因此回傳了兩條記錄，

總結

對于資料分析崗而言，SQL 分組聚合、CASE 運算式、視窗函式的掌握是基本要求，隨著 MySQL 8.0 的推出使得這一切變得更加簡單，