44 用户购买平台

[astak16]

题目

题目链接：用户购买平台

写一段 SQL，查找每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

输出：spend_date | platform | total_amount | total_users

CREATE TABLE spending (
	user_id INT,
	spend_date date,
	platform ENUM ( 'desktop', 'mobile' ),
	amount INT 
);

INSERT INTO spending ( user_id, spend_date, platform, amount ) 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 );

SQL：方法一

select
	t1.spend_date,
	t1.platform,
	coalesce(sum(t2.amount), 0) total_amount,
	coalesce(sum(t2.users), 0) total_users
from (
	select spend_date, 'mobile' as platform from spending
	union
	select spend_date, 'desktop' as platform from spending
	union
	select spend_date, 'both' as platform from spending
) t1 left join (
	select 
		user_id,
		spend_date,
		any_value(
			case when count(platform) = 2 then 'both' else platform end
		) as platform,
		sum(amount) amount,
		count(distinct user_id) users
	from spending group by user_id, spend_date
) t2 on t1.spend_date = t2.spend_date and t1.platform = t2.platform
group by t1.spend_date, t1.platform order by t1.platform;

分析

分两步，分别构建一张临时，然后两张表进行联结

• 第一张表字段是日期 spend_date 和平台 platform
• 第二张表是用户 user_id、 spend_date、 platform、 amount、 users
  • 一个用户在一天当中，可以使用 mobile 或者 desktop，也可以同时使用。
  • 可以按照用户和时间进行分组，算出一个用户一天当中使用的平台以及总支出金额
• 将两张表通过 spend_date 和 platform 进行联结

解析

1. 按照日期和平台构建临时表 t1 ，作用是每天都有三个平台： mobile、desktop、both

   • 使用 union 关键字联结三次子查询

   select spend_date, 'mobile' as platform from spending
   union
   select spend_date, 'desktop' as platform from spending
   union
   select spend_date, 'both' as platform from spending

2. 基于 spending 表，按照 user_id、 spend_date 进行分组查询，作为临时表 t2

   • 使用 case 语句，计算出 platform ，如果一个用户在一天当中同时使用了 mobile 和 desktop ，则 platform 的值是 both
   • 如果一个用户的 platform = 'both'，这个用户的当天总支出是 mobile 和 desktop 的 支出相加
   • 一个用户一天只能是一个平台，所以要去重 count(distinct user_id) users

   select 
   	user_id,
   	spend_date,
   	any_value(
   		case when count(platform) = 2 then 'both' else platform end
   	) as platform,
   	sum(amount) amount,
   	count(distinct user_id) users
   from spending group by user_id, spend_date

3. 临时表 t1 联结临时表 t2 ，联结条件是 spend_date 和 platform

4. 查询联结后的表，计算出每天每个平台的总人数和总金额，按照 t1.spend_date 和 t1.platform 进行分组

   • coalesce(expr, expr, ...) ，返回第一个非空值

   select
   	t1.spend_date,
   	t1.platform,
   	coalesce(sum(t2.amount), 0) total_amount,
   	coalesce(sum(t2.users), 0) total_users
   from ...

SQL：方法二

select 
	t2.spend_date,
	t1.platform,
	sum(if(t1.platform = t2.platform, amount, 0)) as total_amount,
	count(if(t1.platform = t2.platform, 1, null)) as total_users
from (
	select 'mobile' as platform union
	select 'desktop' as platform union
	select 'both' as platform 
) t1, (
	select 
		user_id,
		spend_date, 
		any_value(if(count(platform) = 2, 'both', platform)) platform,
		sum(amount) amount
	from spending
	group by user_id, spend_date
) t2 group by t2.spend_date, t1.platform

解析

和方法一思路一样，区别是方法二用的是内连接。

1. 构建 t1 表只需要一个 platform ，它的作用是 spending 表中没有 both 字段，所以这里是固定输出三个值 mobile 、desktop、both

2. 构建 t2 表的方法和方法一一样

3. 对 t1 和 t2 使用自联结，没有条件的自联结是一个笛卡尔积

   select * from t1, t2
   --- 等价于
   select * from t1 join t2

4. 最终结果是每个平台每天的 总用户数和 支出总金额 ，所以使用日期和平台作为分组

   # 一个用户只能是一个平台（mobile/desktop/both), 这里的 amount 在 t2 表中被计算过了
   sum(if(t1.platform = t2.platform, amount, 0))
   
   # 一个用户只能是一个平台（mobile/desktop/both），所以输出 1
   count(if(t1.platform = t2.platform, 1, null))