一道初级ETL BI的笔试题
今天收到一个朋友发来的初级ETL BI 的笔试题，我觉得题目蛮有意思的，于是拿出来和大家分享。
问题描述
❝
现在有用户购物记录表 t_user_pay(id, paydate, userid, platform ,amount)，分别对应：id流水编号，购买日期, 用户id，购买平台(pc，android, ios)，支付金额 。「一个用户在某一天之内可以在不同的平台出现多次购买记录。」要求求出: 「日期 平台组合类型 用户数 购买总金额」注意：平台组合类型要求查询出单独在pc，android，ios 购买的数据，以及各种组合 pc + android ， android + ios ，ios + pc ，ios + pc + android 。
t_user_pay 的表结构：
create table t_user_pay(
id int UNSIGNED auto_increment primary key,
paydate date,
userid int UNSIGNED,
platform varchar(20),
amount decimal(12,2)
);❞
我特意把题目描述中的“「一个用户在某一天之内可以在不同的平台出现多次购买记录。」”这句话加粗了。这句话传递了两层意思：
- 一个用户在同一天可以在多个平台产生购买记录；
- 一个用户在同一天同一个平台可以出现多条购买记录。当然，用户在这一天也可能不产生任何购买记录。
表数据
题目审完了，我们就来看数据。下表是我自己造的数据，大家如果要运行文中的 SQL，也得自己造数据。
id paydate userid platform amount
------ ---------- ------ -------- --------
1 2020-06-10 1029 ios 77.00
2 2020-06-10 1029 android 83.00
3 2020-06-10 1029 ios 24.00
4 2020-06-10 1029 pc 16.00
5 2020-06-10 1030 pc 49.00
6 2020-06-10 1030 ios 59.00
7 2020-06-10 1030 android 30.00
8 2020-06-10 1031 ios 25.00
9 2020-06-10 1031 android 68.00
10 2020-06-11 1032 pc 98.00
11 2020-06-11 1031 android 68.00
12 2020-06-11 1031 pc 54.00
13 2020-06-11 1030 ios 87.00
14 2020-06-11 1030 android 75.00
15 2020-06-11 1029 pc 102.00 表中只包含了两个日期（2020-06-10、2020-06-11）、四个用户（1029、1030、1031、1032）、三个平台（ios、android、pc）。
思路分析
这个需求复杂在哪呢？复杂的地方在于求出某个字段的所有组合，platform 总共有七种组合：ios、android、pc、android+ios、pc+ios、pc+android、pc+android+ios，目前在 MySQL 里没有找到可以生成某个字段的值的所有组合的函数。
SELECT platform,SUM(xxx) FROM t_user_pay GROUP BY platform 语句只能求出 ios、android、pc 这三种组合，而在 GROUP BY 子句中加入 WITH ROLLUP 就可以多求出一个组合——pc+android+ios 。
剩下的这三个组合：android+ios、pc+ios、pc+android 可以单独求出结果，再用 UNION ALL 合并数据。
实现
思路说开了，我们现在来看具体实现的 SQL 。
SELECT
*
FROM
(SELECT
paydate,
COALESCE(platform, 'pc+android+ios') AS platform,
COUNT(DISTINCT userid) AS user_cnt,
SUM(amount) AS amount_total
FROM
t_user_pay
GROUP BY paydate,
platform WITH ROLLUP
UNION
ALL
SELECT
paydate,
'pc+ios',
COUNT(DISTINCT userid),
SUM(amount)
FROM
t_user_pay
WHERE platform IN ('pc', 'ios')
GROUP BY paydate
UNION
ALL
SELECT
paydate,
'pc+android',
COUNT(DISTINCT userid),
SUM(amount)
FROM
t_user_pay
WHERE platform IN ('pc', 'android')
GROUP BY paydate
UNION
ALL
SELECT
paydate,
'android+ios',
COUNT(DISTINCT userid),
SUM(amount)
FROM
t_user_pay
WHERE platform IN ('android', 'ios')
GROUP BY paydate) t
WHERE paydate IS NOT NULL
ORDER BY paydate,
platform这段 SQL 需要说明的有几个地方：
- 计算人数的时候使用了
COUNT(DISTINCT userid)去重，因为题目要求的是计算人数，而非次数； - 判断所用平台是否属于"pc+android+ios"组合时使用了
COALESCE()，因为我们已经从需求中获知platform字段不可能为空。如果platform允许为空，继续使用COALESCE()函数就得多加一些判断。MySQL 8.0+ 的版本可以直接使用GROUPING()函数。 - 在外层使用
paydate IS NOT NULL条件把所有日期的"pc+android+ios"组合的总计的记录过滤掉了，因为需求里没要求统计这项数据。
下表是 SQL 执行的结果。
paydate platform user_cnt amount_total
---------- -------------- -------- --------------
2020-06-10 android 3 181.00
2020-06-10 android+ios 3 366.00
2020-06-10 ios 3 185.00
2020-06-10 pc 2 65.00
2020-06-10 pc+android 3 246.00
2020-06-10 pc+android+ios 3 431.00
2020-06-10 pc+ios 3 250.00
2020-06-11 android 2 143.00
2020-06-11 android+ios 2 230.00
2020-06-11 ios 1 87.00
2020-06-11 pc 3 254.00
2020-06-11 pc+android 4 397.00
2020-06-11 pc+android+ios 4 484.00
2020-06-11 pc+ios 4 341.00 正文到这里就结束了。
这是我写的第 25 篇原创文章。年初的时候给自己定了一个目标：争取每周写一篇文章。半年过去了，实际产出的量和计划的量差距不算很大，到年底完成目标问题不大。