一道初级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)
);

❞

我特意把题目描述中的“「一个用户在某一天之内可以在不同的平台出现多次购买记录。」”这句话加粗了。这句话传递了两层意思：

1. 一个用户在同一天可以在多个平台产生购买记录；
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 需要说明的有几个地方：

1. 计算人数的时候使用了 COUNT(DISTINCT userid)
   去重，因为题目要求的是计算人数，而非次数；
2. 判断所用平台是否属于"pc+android+ios"组合时使用了 COALESCE()
   ，因为我们已经从需求中获知 platform
   字段不可能为空。如果 platform
   允许为空，继续使用 COALESCE()
   函数就得多加一些判断。MySQL 8.0+ 的版本可以直接使用 GROUPING()
   函数。
3. 在外层使用 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