一、力扣链接
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'）。
编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
以 任意顺序 返回结果表。
三、目标拆解
四、建表语句
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')五、过程分析
1、手机端、桌面端、手机和桌面端用户情况汇总
2、列出所有平台以及所有用户每天的使用情况
六、代码实现
with t1 as(
select user_id, spend_date, sum(amount) amount,case when count(platform) = 1 and max(platform) = 'desktop' then 'desktop' when count(platform) = 1 and max(platform) = 'mobile' then 'mobile'when count(platform) = 2 then 'both' end as platform
from Spending group by user_id, spend_date
)
,t2 as(
select distinct spend_date, 'mobile' platform from Spending
union all
select distinct spend_date, 'desktop' from Spending
union all
select distinct spend_date, 'both' from Spending
)
select t2.spend_date, t2.platform, ifnull(sum(amount), 0) total_amount, ifnull(count(user_id), 0) total_users
from t2
left join t1 on t2.platform = t1.platform and t2.spend_date = t1.spend_date
group by t2.spend_date, t2.platform;
七、结果验证
八、小结
1、CTE 表达式 + 聚合函数 + group by + ifnull()
2、注意题目要求列出所有用户每天在三种平台的使用量
3、使用union all 把每天每种平台都列出来进行left jion
