125，SQL训练之，力扣，1159. 市场分析 II

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

. - 力扣（LeetCode）

表: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
表中包含一位在线购物网站用户的个人信息，用户可以在该网站出售和购买商品。

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id 是该表的主键(具有唯一值的列)。
item_id 是 Items 表的外键(reference 列)。
buyer_id 和 seller_id 是 Users 表的外键。

表: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id 是该表的主键(具有唯一值的列)。

编写一个解决方案，为每个用户找出他们出售的第二件商品(按日期)的品牌是否是他们最喜欢的品牌。如果用户售出的商品少于两件，则该用户的结果为否。保证卖家不会在一天内卖出一件以上的商品。

以 任意顺序 返回结果表。

返回结果格式如下例所示：

示例 1：

输入：
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2019-01-01 | Lenovo         |
| 2       | 2019-02-09 | Samsung        |
| 3       | 2019-01-19 | LG             |
| 4       | 2019-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2019-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2019-08-04 | 1       | 4        | 2         |
| 5        | 2019-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
输出：
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+
解释：
id 为 1 的用户的查询结果是 no，因为他什么也没有卖出
id为 2 和 3 的用户的查询结果是 yes，因为他们卖出的第二件商品的品牌是他们最喜爱的品牌
id为 4 的用户的查询结果是 no，因为他卖出的第二件商品的品牌不是他最喜爱的品牌

三，建表语句

Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int);
Create table If Not Exists Items (item_id int, item_brand varchar(10));
Truncate table Users;
insert into Users (user_id, join_date, favorite_brand) values ('1', '2019-01-01', 'Lenovo');
insert into Users (user_id, join_date, favorite_brand) values ('2', '2019-02-09', 'Samsung');
insert into Users (user_id, join_date, favorite_brand) values ('3', '2019-01-19', 'LG');
insert into Users (user_id, join_date, favorite_brand) values ('4', '2019-05-21', 'HP');
Truncate table Orders;
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', '2019-08-01', '4', '1', '2');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', '2019-08-02', '2', '1', '3');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', '2019-08-03', '3', '2', '3');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', '2019-08-04', '1', '4', '2');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2019-08-04', '1', '3', '4');
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', '2019-08-05', '2', '2', '4');
Truncate table Items;
insert into Items (item_id, item_brand) values ('1', 'Samsung');
insert into Items (item_id, item_brand) values ('2', 'Lenovo');
insert into Items (item_id, item_brand) values ('3', 'LG');
insert into Items (item_id, item_brand) values ('4', 'HP');


select * from users;
select * from orders;
select * from items;

四，分析

思路

表格大法：

第一步：左连接用户表和订单表 左连接的原因：有用户但是用户每下单也需要统计； 连接条件：用户id和卖家id

第二步：以用户分组，以下单日期排序 pandas中需要转为时间戳 msql不需要；

第三步：左连接连接第三张表 品牌表。左连接的原因是：item_id有null值的原因

第四步:过滤掉大于排序列2的行数 并且扩一个新的列，如果rn=2且用户出售的品牌是他们喜欢的品牌 就给yes，反之都都给no

第五步：再次排序 以rn降序，分组求top1

第六步：映射指定的列，改名，并输出

解题过程

分别用mysql和pandas实现以上表格的代码逻辑

第一步：左连接用户表和订单表 左连接的原因：有用户但是用户每下单也需要统计； 连接条件：用户id和卖家id

在mysql

第二步：以用户分组，以下单日期排序 pandas中需要转为时间戳 msql不需要；

在mysql

第三步：左连接连接第三张表 品牌表。左连接的原因是：item_id有null值的原因

在mysql

第四步:过滤掉大于排序列2的行数 并且扩一个新的列，如果rn=2且用户出售的品牌是他们喜欢的品牌 就给yes，反之都都给no

在mysql

第五步：再次排序 以rn降序，分组求top1

在mysql

第六步：映射指定的列，改名，并输出

在mysql

五，SQL解答

with t1 as (
    select
        user_id,favorite_brand, order_id, ifnull(order_date,0) as order_date, item_id,seller_id,
        row_number() over (partition by user_id order by ifnull(order_date,0)) rn
    from users u
        left join orders o
            on u.user_id =o.seller_id
)
#    select  * from t1;
,t2 as (
    select
        user_id, favorite_brand, order_id, order_date, rn, t1.item_id, item_brand
        from t1 left join items i on t1.item_id=i.item_id where rn=1 or rn=2
)
#    select * from t2;
,t3 as (
    select
    user_id, favorite_brand, rn, item_brand,
    case when rn=2 and item_brand=favorite_brand then 'yes'
        else 'no'
        end as 2nd_item_fav_brand
from t2
)
# select * from t3;
,t4 as (
    select user_id, favorite_brand, rn, item_brand, `2nd_item_fav_brand`,
       row_number() over (partition by user_id order by rn desc ) rn1
    from t3
)
# select * from t4;
select user_id as seller_id , `2nd_item_fav_brand` from t4 where rn1=1;

六，验证

七，知识点总结

• 左连接的运用
• row_nunber排序的运用
• if或者case when语法的运用
• 分组求top1的运用

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用