|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
题目描述:
- 支出表: 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
- 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
复制代码
- select t2.spend_date, t2.platform,
- ifnull(sum(amount),0) total_amount, ifnull(count(user_id),0) total_users
- from
- (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
- ) t2
- left join
- (select spend_date, sum(amount) amount, user_id,
- case when count(*) = 1 then platform else "both" end as platform
- from Spending
- group by spend_date, user_id) t1
- on t1.spend_date = t2.spend_date
- and t1.platform = t2. platform
- group by t2.spend_date, t2.platform
复制代码
注意事项:
1.注意union的使用 |
|