力扣1127(MySQL)-用户购买平台(困难)
题目:
支出表: Spending
这张表记录了用户在一个在线购物网站的支出历史,该在线购物平台同时拥有桌面端(‘desktop’)和手机端(‘mobile’)的应用程序。
这张表的主键是 (user_id, spend_date, platform)。
平台列 platform 是一种 ENUM ,类型为(‘desktop’, ‘mobile’)。
问题
写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
查询结果格式如下例所示:
Spending table:
Result table:
在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买,而用户3 仅 使用了桌面端购买。
在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买,且没有用户 同时 使用桌面端和手机端购买。
建表语句:
1 drop table if EXISTS spending_1127; 2 create table if not exists spending_1127( 3 user_id int, 4 spend_date date, 5 platform enum('desktop','mobile'), 6 amount int 7 ); 8 truncate table spending_1127; 9 insert into spending_1127 values(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');
解题思路:
这道题对于我来说有点困难,参考了一下其他博主的题解
①先查询出每个用户id的platform情况以及交易总额;
1 select user_id, spend_date,if(count(distinct platform) = 2, 'both', platform) as platform,sum(amount) as total_amount 2 from spending_1127 3 group by user_id, spend_date;
②再创建一个临时表,列出platform的情况;
1 select 'desktop' as platform union 2 select 'mobile' as platform union 3 select 'both' as platform
③再使上面两步查询出的临时表内连接,以spend_date,platform分组,使用case when进行数量统计;
1 select spend_date,b.platform, 2 sum(case when a.platform = b.platform then total_amount else 0 end) as total_amount, 3 count(if(a.platform = b.platform, 1, null)) as total_users 4 from ( 5 select user_id, spend_date,if(count(distinct platform) = 2, 'both', platform) as platform,sum(amount) as total_amount 6 from spending_1127 7 group by user_id, spend_date 8 ) as a, ( 9 select 'desktop' as platform union 10 select 'mobile' as platform union 11 select 'both' as platform 12 )as b 13 group by spend_date,platform 14 order by spend_date
小知识:
内连接分为显示的内连接和隐式的内连接,内连接的结果是笛卡尔积
显示的内连接:有inner join关键字,有on关键字表示的条件;
隐式的内连接:用逗号分隔两个数据库表,条件的表示只能用where。