LeetCode MySQL 1127. 用户购买平台 *
发布日期:2021-07-01 03:30:47
浏览次数:3
分类:技术文章
本文共 4269 字,大约阅读时间需要 14 分钟。
文章目录
1. 题目
支出表: 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 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
2. 解题
- 先造出表的各种组合
select distinct spend_date, "desktop" platform from Spendingunionselect distinct spend_date, "mobile" platform from Spendingunionselect distinct spend_date, "both" platform from Spending
{ "headers": ["spend_date", "platform"], "values": [["2019-07-01", "desktop"], ["2019-07-02", "desktop"], ["2019-07-01", "mobile"], ["2019-07-02", "mobile"], ["2019-07-01", "both"], ["2019-07-02", "both"]]}
- 计算每天,某类属下的总金额、人数
select spend_date, if(count(distinct platform)=1, platform, 'both') plat, sum(amount) total_am, count(distinct user_id) total_u # 1 total_u 这么写也对,就1个人from Spendinggroup by spend_date, user_id
{ "headers": ["spend_date", "plat", "total_am", "total_u"], "values": [["2019-07-01", "both", 200, 1], ["2019-07-01", "mobile", 100, 1], ["2019-07-01", "desktop", 100, 1], ["2019-07-02", "mobile", 100, 1], ["2019-07-02", "desktop", 100, 1]]}
- 上面2表连接
select p.spend_date, p.platform, t.total_am, t.total_ufrom( select distinct spend_date, "desktop" platform from Spending union select distinct spend_date, "mobile" platform from Spending union select distinct spend_date, "both" platform from Spending) pleft join( select spend_date, if(count(distinct platform)=1, platform, 'both') plat, sum(amount) total_am, count(distinct user_id) total_u from Spending group by spend_date, user_id) ton p.platform = t.plat and p.spend_date = t.spend_date
{ "headers": ["spend_date", "platform", "total_am", "total_u"], 。"values": [["2019-07-01", "desktop", 100, 1], ["2019-07-02", "desktop", 100, 1], ["2019-07-01", "mobile", 100, 1], ["2019-07-02", "mobile", 100, 1], ["2019-07-01", "both", 200, 1], ["2019-07-02", "both", null, null]]}
- 对连接后的表,求和
# Write your MySQL query statement belowselect spend_date, platform, ifnull(sum(total_am),0) total_amount, ifnull(sum(total_u),0) total_usersfrom( select p.spend_date, p.platform, t.total_am, t.total_u from ( select distinct spend_date, "desktop" platform from Spending union select distinct spend_date, "mobile" platform from Spending union select distinct spend_date, "both" platform from Spending ) p left join ( select spend_date, if(count(distinct platform)=1, platform, 'both') plat, sum(amount) total_am, count(distinct user_id) total_u from Spending group by spend_date, user_id ) t on p.platform = t.plat and p.spend_date = t.spend_date) tempgroup by spend_date, platform
我的CSDN
长按或扫码关注我的公众号(Michael阿明),一起加油、一起学习进步!
转载地址:https://michael.blog.csdn.net/article/details/107731633 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
第一次来,支持一个
[***.219.124.196]2024年05月05日 02时47分17秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
锁的释放流程-ReentrantLock.unlock
2019-05-01
Java判断字符串是否为数字(浮点类型也包括)
2019-05-01
ubuntu opencv-python 安装很慢问题
2019-05-01
MySQL5.7版本修改了my.ini配置文件后mysql服务无法启动问题
2019-05-01
【大数据开发】Java基础 -总结21-Hashmap和HashTable的区别
2019-05-01
Azkaban体系结构
2019-05-01
机器学习之重头戏-特征预处理
2019-05-01
synchronized底层实现及锁的升级、降级
2019-05-01
PermGen space-永久区内存溢出
2019-05-01
Maven继承和聚合
2019-05-01
Apache Kafka:优化部署的 10 种最佳实践
2019-05-01
Leetcode 35. 搜索插入位置 c#
2019-05-01
[9] JMeter-常用函数的使用
2019-05-01
[12] JMeter-结果分析之图形图表
2019-05-01
使用aspose.words 18.6实现pdf文档转换
2019-05-01
Java数组详解
2019-05-01