# Write your MySQL query statement below
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'）。
编写解决方案找出每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
以 任意顺序 返回结果表。
返回结果格式如下例所示：
示例 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 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。
原站题解
pythondata 解法, 执行用时: 436 ms, 内存消耗: 61.3 MB, 提交时间: 2023-10-16 16:55:35
import pandas as pd
def user_purchase(spending: pd.DataFrame) -> pd.DataFrame:
platform_cnt = spending.groupby(['user_id', 'spend_date'], as_index = False)['platform'].count()
platform_cnt.columns = ['user_id', 'spend_date', 'cnt']
df = spending.merge(platform_cnt, how = 'left', on = ['user_id', 'spend_date'])
df['platform'] = np.where(df['cnt'] > 1, 'both', df['platform'])
df_sum = df.groupby(['spend_date', 'platform'], as_index = False).agg({'amount' : 'sum', 'user_id' : 'nunique'})
df_sum.columns = ['spend_date', 'platform', 'total_amount', 'total_users']
df_spend_date = spending[['spend_date']].drop_duplicates()
df_platform = pd.DataFrame({'platform' : ['mobile', 'desktop', 'both']})
df_comb = df_spend_date.merge(df_platform, how = 'cross')
output = df_comb.merge(df_sum, how = 'left', on = ['spend_date', 'platform'])
output.fillna(0, inplace = True)
return output[['spend_date', 'platform', 'total_amount', 'total_users']]
mysql 解法, 执行用时: 416 ms, 内存消耗: 0 B, 提交时间: 2023-10-16 16:53:41
# Write your MySQL query statement below
select spend_date,b.platform,
sum(if(a.platform=b.platform,amount,0)) as total_amount,
count(if(a.platform=b.platform,1,null)) as total_users
from(
select spend_date,user_id,
if(count(distinct platform)=2,'both',platform) as platform,
sum(amount) as amount
from spending
group by user_id,spend_date
) a,(
select 'desktop' as platform union
select 'mobile' as platform union
select 'both' as platform
) b
group by spend_date,platform