120，[经典面试题，ETL困难题]SQL训练之，力扣，1127. 用户购买平台

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

支出表: 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'）。

编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

以 任意顺序 返回结果表。

返回结果格式如下例所示：

示例 1：

输入：
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    |
+---------+------------+----------+--------+
输出：
+------------+----------+--------------+-------------+
| 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 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。

三，建表语句

Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);
Truncate table Spending;
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-02', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-02', 'desktop', '100');

四，分析

表格大法 创建维度表 和主表进行左连接

第一步：创建一个维度表；

第二步：转platform 为权重；

第三步：以日期，用户id分组 sum 聚合platform与金额列

第四步：把作为权重的platform转换回来

第五步：把维度表和我们第四步转换回来的表 左连接 连接条件双条件 日期与platfrom列

第六步：以日期和platfrom 分组 聚合 sum金额 与count用户id 统计人数

第七步：映射指定的列，改名，输出

解题过程

代码实现

第一步：创建一个维度表；

在mysql

第二步：转platform 为权重；

在mysql

第三步：以日期，用户id分组 sum 聚合platform与金额列

在mysql

第四步：把作为权重的platform转换回来

在mysql

第五步：把维度表和我们第四步转换回来的表 左连接 连接条件双条件 日期与platfrom列

在mysql

第六步：以日期和platfrom 分组 聚合 sum金额 与count用户id 统计人数

在mysql

第七步：映射指定的列，改名，输出

五，SQL解答

with
#创建一个 维度表
t1 as (
   select distinct spend_date,'desktop' as platform from Spending
    union
    select distinct spend_date,'mobile' as platform from Spending
    union
    select distinct spend_date,'both' as platform from Spending
)
#    select * from t1;
,t2 as (
    #给platfrom 平台 打上一个权重
    select
        user_id, spend_date, if(platform='mobile',1,2) as flag, amount
    from spending
)
# select * from t2;
   #以事件 和用户分组  求和权重以及求和消费金额
,t3 as (
    select
        spend_date,user_id,sum(flag) as sf,sum(amount) as sa
    from t2 group by spend_date,user_id
)
#    select * from t3;
,t4 as (
   # 更改权重为正常的值
    select spend_date as sd,user_id, case when sf='1' then 'mobile'
                when sf ='2' then 'desktop'
                else 'both' end as pl,
        sa from t3

)
#     select * from t4;
# 和我们第一个表 简历连 用第一个表左连接t4
,t5 as (
    select * from t1 left join t4 on t1.spend_date = t4.sd and t1.platform=t4.pl
)
# select * from t5;
select
    spend_date,platform,ifnull(sum(sa),0) as total_amount,count(user_id) as total_users
from t5 group by spend_date,platform;

六，验证

七，知识点总结

• 去重的运用
• union的运用
• if转换的运用
• 多列分组+多列聚合的运用
• 左连接的运用 多列作为连接条件的
• case when'语法的运用

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用