3118. 发生在周五的交易 III_leetcode 发生在周五的交易 i 题解-优快云博客

本文链接：https://blog.youkuaiyun.com/CYJ1844/article/details/144076546

力扣题目跳转（3118. 发生在周五的交易 III - 力扣（LeetCode））

表：Purchases

+---------------+------+
| Column Name   | Type |
+---------------+------+
| user_id       | int  |
| purchase_date | date |
| amount_spend  | int  |
+---------------+------+
(user_id, purchase_date, amount_spend) 是该表的主键(具有唯一值的列)。
purchase_date 的范围从 2023 年 11 月 1 日到 2023 年 11 月 30 日，并包括这两个日期。
每一行包含 user_id, purchase_date，和 amount_spend。

表：Users

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| membership  | enum |
+-------------+------+
user_id 是这张表的主键。
membership 是 ('Standard', 'Premium', 'VIP') 的枚举类型。
这张表的每一行表示 user_id 和会员类型。

题目要求：

编写一个解决方案来计算 Premium 和 VIP 会员在 2023 年 11 月 每周的周五 的 总花费。如果某个周五没有 Premium 或 VIP 会员购买，把它当作 0。

按照每月的周次序升序排列结果表，然后以 membership 升序排序。

结果格式如下所示。

示例：

输入：

Purchases 表：
+---------+---------------+--------------+
| user_id | purchase_date | amount_spend |
+---------+---------------+--------------+
| 11      | 2023-11-03    | 1126         |
| 15      | 2023-11-10    | 7473         |
| 17      | 2023-11-17    | 2414         |
| 12      | 2023-11-24    | 9692         |
| 8       | 2023-11-24    | 5117         |
| 1       | 2023-11-24    | 5241         |
| 10      | 2023-11-22    | 8266         |
| 13      | 2023-11-21    | 12000        |
+---------+---------------+--------------+
Users 表：
+---------+------------+
| user_id | membership |
+---------+------------+
| 11      | Premium    |
| 15      | VIP        |
| 17      | Standard   |
| 12      | VIP        |
| 8       | Premium    |
| 1       | VIP        |
| 10      | Standard   |
| 13      | Premium    |
+---------+------------+
输出：
+---------------+-------------+--------------+
| week_of_month | membership  | total_amount |
+---------------+-------------+--------------+
| 1             | Premium     | 1126         |
| 1             | VIP         | 0            |
| 2             | Premium     | 0            |
| 2             | VIP         | 7473         |
| 3             | Premium     | 0            |
| 3             | VIP         | 0            |
| 4             | Premium     | 5117         |
| 4             | VIP         | 14933        |
+---------------+-------------+--------------+
        
解释：

在 2023 年 11 月的第一周，周五有一笔交易，2023-11-03，由一个 Premium 会员花费了 $1,126。这天没有 VIP 会员交易，所以值为 0。
在 2023 年 11 月的第二周，周五有一笔交易，2023-11-10，由一个 VIP 会员花费了 $7,473。因为这条没有 Premium 会员交易，Premium 会员的输出为 0。
相似地，在 2023 年 11 月的第三周，周五没有 Premium 或 VIP 会员交易，2023-11-17，所以这周两种分类都输出 0。
在 2023 年 11 月的第四周，周五存在交易，2023-11-24，有一名 Premium 会员购买了 $5,117 以及 VIP 会员购买了总共 $14,933（一个花费 $9,692，另一个花费 $5,241）。

注意：输出表以 week_of_month 和 membership 升序排序。

case 1 的建表语句。

Create Table if Not Exists Purchases( user_id int, purchase_date date, amount_spend int)
Create Table if Not Exists Users (user_id int, membership enum('Standard', 'Premium', 'VIP'))
Truncate table Purchases
insert into Purchases (user_id, purchase_date, amount_spend) values ('11', '2023-11-03', '1126')
insert into Purchases (user_id, purchase_date, amount_spend) values ('15', '2023-11-10', '7473')
insert into Purchases (user_id, purchase_date, amount_spend) values ('17', '2023-11-17', '2414')
insert into Purchases (user_id, purchase_date, amount_spend) values ('12', '2023-11-24', '9692')
insert into Purchases (user_id, purchase_date, amount_spend) values ('8', '2023-11-24', '5117')
insert into Purchases (user_id, purchase_date, amount_spend) values ('1', '2023-11-24', '5241')
insert into Purchases (user_id, purchase_date, amount_spend) values ('10', '2023-11-22', '8266')
insert into Purchases (user_id, purchase_date, amount_spend) values ('13', '2023-11-21', '12000')
Truncate table Users
insert into Users (user_id, membership) values ('11', 'Premium')
insert into Users (user_id, membership) values ('15', 'VIP')
insert into Users (user_id, membership) values ('17', 'Standard')
insert into Users (user_id, membership) values ('12', 'VIP')
insert into Users (user_id, membership) values ('8', 'Premium')
insert into Users (user_id, membership) values ('1', 'VIP')
insert into Users (user_id, membership) values ('10', 'Standard')
insert into Users (user_id, membership) values ('13', 'Premium')

一首先我们使用左连接，将两表连接，Purchases 为主表，然后选出需要的字段。使用 dayofweek 函数来判断日期是周几，再使用 where 条件筛选日期为题目需要的。

select
    p.user_id,
    ceil(day(purchase_date)/7) as week_of_month,
    dayofweek(purchase_date) as weekday,
    amount_spend,
    membership
from purchases p
left join users u on p.user_id = u.user_id
where purchase_date like '2023-11%' and dayofweek(purchase_date) = 6

输出如下

二使用 cte 后进行分组聚合得出最终需要的数据部分。

with tmp as
(select
    p.user_id,
    ceil(day(purchase_date)/7) as week_of_month,
    dayofweek(purchase_date) as weekday,
    amount_spend,
    membership
from purchases p
left join users u on p.user_id = u.user_id
where purchase_date like '2023-11%' and dayofweek(purchase_date) = 6)
select week_of_month,
        membership,
        sum(amount_spend) as total_amount
from tmp group by week_of_month, membership

输出如下

然后我对代码进行修改，省略了 cte 过程。返回的结果是一样的。

select
    ceil(day(purchase_date)/7) as week_of_month,
    membership,
    sum(amount_spend) as total_amount
from purchases p
left join users u on p.user_id = u.user_id
where purchase_date like '2023-11%' and dayofweek(purchase_date) = 6
group by week_of_month, membership

然后我们使用 union 自己创造需要连接的表的模型。

with tmp as
(select
    ceil(day(purchase_date)/7) as week_of_month,
    membership,
    sum(amount_spend) as total_amount
from purchases p
left join users u on p.user_id = u.user_id
where purchase_date like '2023-11%' and dayofweek(purchase_date) = 6
group by week_of_month, membership),
tmp1 as
(select 1 as week_of_month
union
select 2 as week_of_month
union
select 3 as week_of_month
union
select 4 as week_of_month),
tmp2 as
(select 'Premium' as membership
union
select 'VIP' as membership
)
select * from tmp1 join tmp2

然后再用我之前最早的结果和这张表进行左连接，主表为上面这张，对空值进行处理即可，然后排序。

with tmp as
(select
    ceil(day(purchase_date)/7) as week_of_month,
    membership,
    sum(amount_spend) as total_amount
from purchases p
left join users u on p.user_id = u.user_id
where purchase_date like '2023-11%' and dayofweek(purchase_date) = 6
group by week_of_month, membership),
tmp1 as
(select 1 as week_of_month
union
select 2 as week_of_month
union
select 3 as week_of_month
union
select 4 as week_of_month),
tmp2 as
(select 'Premium' as membership
union
select 'VIP' as membership
)
select
    t1.week_of_month,
    t1.membership,
    ifnull(total_amount,0) as total_amount
from (select * from tmp1 join tmp2) t1
left join tmp t2 on t1.week_of_month = t2.week_of_month
and t1.membership = t2.membership
order by week_of_month,t1.membership;