LeetCode-1127. 用户购买平台(困难)-待深入理解

支出表: Spending

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| spend_date  | date    |
| platform    | enum    | 
| amount      | int     |
+-------------+---------+
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。
这张表的主键是 (user_id, spend_date, platform)。
平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。
 

写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

查询结果格式如下例所示：

Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1       | 2019-07-01 | mobile   | 100    |
| 1       | 2019-07-01 | desktop  | 100    |
| 2       | 2019-07-01 | mobile   | 100    |
| 2       | 2019-07-02 | mobile   | 100    |
| 3       | 2019-07-01 | desktop  | 100    |
| 3       | 2019-07-02 | desktop  | 100    |
+---------+------------+----------+--------+

Result table:
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop  | 100          | 1           |
| 2019-07-01 | mobile   | 100          | 1           |
| 2019-07-01 | both     | 200          | 1           |
| 2019-07-02 | desktop  | 100          | 1           |
| 2019-07-02 | mobile   | 100          | 1           |
| 2019-07-02 | both     | 0            | 0           |
+------------+----------+--------------+-------------+ 
在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买，而用户3 仅 使用了桌面端购买。
在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/user-purchase-platform
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

思考：按照id和日期分组，如果有两个结果，就是都有，如果只有一个就是单一平台。

解题：

按照spend_date,user_id把只有desktop的,只有mobile的,以及both的全部查出来,

然后再把这个结果按照spend_date分组计算金额以及人数：

select spend_date,platform, sum(amount) as total_amount, count(user_id) total_users
from
(select spend_date, user_id, 
(case count(distinct platform)
    when 1 then platform
    when 2 then 'both'
    end
) as  platform, sum(amount) as amount
from Spending
group by spend_date, user_id
) as temp2
group by spend_date, platform

但是这样得出来的只有某些spend_date某些user_id
的，题目要求是不存在也要放在结果里面,只是结果置0而已。

：：

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

select distinct(spend_date), p.platform   
from Spending,
(select 'desktop' as platform union
 select 'mobile' as platform union
 select 'both' as platform
) as p 

这样枚举出来就把结果的前两列查出来了。
然后把这两步的结果合并就可以了。
完整的sql如下：

select temp1.spend_date, temp1.platform, 
       ifnull(temp3.total_amount, 0) total_amount, 
       ifnull(temp3.total_users,0) total_users
       from
(select distinct(spend_date), p.platform   
from Spending,
(select 'desktop' as platform union
 select 'mobile' as platform union
 select 'both' as platform
) as p 
) as temp1

left join 

(select spend_date,platform, sum(amount) as total_amount, count(user_id) total_users
from
(select spend_date, user_id, 
(case count(distinct platform)
    when 1 then platform
    when 2 then 'both'
    end
) as  platform, sum(amount) as amount
from Spending
group by spend_date, user_id
) as temp2
group by spend_date, platform
) as  temp3
on temp1.platform = temp3.platform and temp1.spend_date = temp3.spend_date

方法二：

第一步 观察样例
先观察样例，结果是按照时间和平台分组的，每个日期都对应了三个完整的平台，这种结构，是不是很像笛卡尔积呢（cross join）？
忘记的同学复习一下，假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
因此，我们要是在时间和平台笛卡尔积基础上有了对应的实际数据，就可以进行统计了。
第二步 问题拆解
那么，我们的问题就可以拆解并分成以下几个步骤：
①原始表没有both的分类，我们加工下把他加上；
②最好有个完整的时间和分类表（笛卡尔积）
③我们把①表和②表left join一下，得到null值以方便进行0值的统计
④得到上述结果后，输出结论就是简单的group by操作了
第三步 分步操作得出结果
①:原始表加工，即创建新列platform和amount。（记录数6条——>5条）

select 
    a.user_id,a.spend_date,
    if(count(*)=1,a.platform,'both') as platform,   -- 根据同一天同用户的记录数，确定是否为'both'
    if(count(*)=1,a.amount,sum(a.amount)) as amount -- 同理，按条件分别输出amount与sum(amount)
from Spending a        -- 加a以作区分
group by user_id,spend_date

②：获取完整的时间和分类表。即时间和数组（'desktop'，'mobile'，'both'）的笛卡尔积。
1）这里由于不知道如何自定义一组数据作为一列，因此采用了2次union的方法，即以select 'desktop'作为单列单行数据，进行2次拼接，最终得到一个三行一列包含'desktop'，'mobile'，'both'的临时表a；
2）获取源数据中的完整日期信息，作临时表b
3）a，b取笛卡尔积。

select spend_date,platform from 
(select * from (select 'desktop' as platform) a union (select 'mobile') union (select 'both')) a,(select distinct spend_date from Spending) b

 ③+④：结果为按时间和平台分组，为了应对有些时间某平台记录为0，应以完整记录表left join出空值

select c.spend_date,c.platform,ifnull(sum(d.amount),0) as total_amount,count(d.user_id) as total_users
from 
    (select spend_date,platform from                    -- -------------------------
        (select * from (select 'desktop' as platform) b   -- 表②
        union (select 'mobile') union (select 'both')) e, -- |||||||||||||
        (select distinct spend_date from Spending) f) c  -- ------------------------
    left join                                            -- 左连接
    (select a.user_id,a.spend_date,                      -- ------------------------- 
        if(count(*)=1,a.platform,'both') as platform,    -- |||||||||||||
        if(count(*)=1,a.amount,sum(a.amount)) as amount  -- 表①
    from Spending a                                      -- |||||||||||||
    group by user_id,spend_date) d                       -- -------------------------
    on c.spend_date=d.spend_date and c.platform=d.platform -- 连接条件
group by c.spend_date,c.platform
order by field(c.platform,'desktop','mobile','both'),c.spend_date

 补充一点，为了自定义平台的排列顺序，可以用field函数。语法如下：field(数据来源列,自己写的顺序逗号分割)

知识点：