mysql第6章6.9练习题_SQL练习题六

61.用户购买平台(☆)

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

支出表: 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 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。

-- 有点难度,难点一:按时间和id分组以及打标签,难点二:构建一张表来left join

思路分析

1.获取每一天的id的类型和acount

select

spend_date,user_id,

if(count(distinct platform) =1,platform,'both') as platform,

sum(amount) as amount

from

Spending

group by spend_date, user_id

2.如果直接group by spend_date, platform 就无法出现0,0的情况,考虑使用join来实现

构建表:

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

综合:

select

t1.spend_date,t1.platform,

ifnull(sum(amount),0) as total_amount,

ifnull(count(distinct user_id),0) as total_users

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

) t1

left join

(

select

spend_date,user_id,

if(count(distinct platform) =1,platform,"both") as platform,

sum(amount) as amount

from

Spending

group by spend_date, user_id

) t2

on t1.spend_date = t2.spend_date and t1.platform = t2.platform

group by t1.spend_date,t1.platform

--摘评论区写法

select

spend_date, platform,

ifnull(sum(total_am),0) total_amount,

ifnull(sum(total_u),0) total_users

from

(

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

) temp

group by spend_date, platform

是否要对最外面的select 中的统计人数去重,不需要但是怎么和leedcode的执行答案有出入

62.报告的记录2(avg的应用)

编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位

动作表： Actions

+---------------+---------+

| Column Name | Type |

+---------------+---------+

| user_id | int |

| post_id | int |

| action_date | date |

| action | enum |

| extra | varchar |

+---------------+---------+

这张表没有主键，并有可能存在重复的行。

action 列的类型是 ENUM，可能的值为 ('view', 'like', 'reaction', 'comment', 'report', 'share')。

extra 列拥有一些可选信息，例如：报告理由(a reason for report)或反应类型(a type of reac