LeetCode-1158、1159 市场分析 I、II

灰太狼 2022-12-03 09:42 141阅读 0赞

> 来源：力扣（LeetCode）  
> 链接：https://leetcode-cn.com/problemset/database/

### 文章目录 ###

*  SQL结构：
 *   *  1158. 市场分析 I（简单）
     *  1159. 市场分析 II（困难）

# SQL结构： #

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', '2018-01-01', 'Lenovo')
    insert into Users (user_id, join_date, favorite_brand) values ('2', '2018-02-09', 'Samsung')
    insert into Users (user_id, join_date, favorite_brand) values ('3', '2018-01-19', 'LG')
    insert into Users (user_id, join_date, favorite_brand) values ('4', '2018-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', '2018-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', '2018-08-04', '1', '4', '2')
    insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2018-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')

Table: `Users`

Table: `Orders`

Table: `Item`

## 1158. 市场分析 I（简单） ##

需求：请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

查询结果格式如下：

Users table:
    +---------+------------+----------------+
    | user_id | join_date  | favorite_brand |
    +---------+------------+----------------+
    | 1       | 2018-01-01 | Lenovo         |
    | 2       | 2018-02-09 | Samsung        |
    | 3       | 2018-01-19 | LG             |
    | 4       | 2018-05-21 | HP             |
    +---------+------------+----------------+
    
    Orders table:
    +----------+------------+---------+----------+-----------+
    | order_id | order_date | item_id | buyer_id | seller_id |
    +----------+------------+---------+----------+-----------+
    | 1        | 2019-08-01 | 4       | 1        | 2         |
    | 2        | 2018-08-02 | 2       | 1        | 3         |
    | 3        | 2019-08-03 | 3       | 2        | 3         |
    | 4        | 2018-08-04 | 1       | 4        | 2         |
    | 5        | 2018-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         |
    +---------+------------+
    
    Result table:
    +-----------+------------+----------------+
    | buyer_id  | join_date  | orders_in_2019 |
    +-----------+------------+----------------+
    | 1         | 2018-01-01 | 1              |
    | 2         | 2018-02-09 | 2              |
    | 3         | 2018-01-19 | 0              |
    | 4         | 2018-05-21 | 0              |
    +-----------+------------+----------------+

-- 解法一： 先过滤出2019年作为买家的订单总数（根据买家分组，求出订单总数）temp表，
    -- 		   因为是每个用户，所以 以users表为主表进行left join temp表即可
    SELECT
    	Users.user_id AS buyer_id,
    	join_date,
    	IFNULL(UserBuy.cnt, 0) AS orders_in_2019
    FROM
    	Users
    LEFT JOIN 
    		(
    			SELECT
    				buyer_id,
    				COUNT(order_id) cnt
    			FROM Orders
    			WHERE YEAR (order_date) = 2019
    			GROUP BY buyer_id
    		) AS UserBuy
     ON Users.user_id = UserBuy.buyer_id

-- 解法二： 可以先直接用users表left join订单表，用case when把所有非2019的订单都变成0
    -- 		  因为join_date和user_id是一对一的，所以可以根据id分组后，直接在select后面跟join_date字段， 
    SELECT
    	t1.user_id AS buyer_id,
    	t1.join_date,
    	IFNULL(
    		SUM(
    			CASE
    			WHEN extract(YEAR FROM order_date) = '2019' THEN 1
    			ELSE 0
    			END),
    		0
    	) AS orders_in_2019
    FROM 
    	Users t1
    LEFT JOIN 
    	Orders o 
    ON t1.user_id = o.buyer_id
    GROUP BY t1.user_id

## 1159. 市场分析 II（困难） ##

需求：写一个 SQL 查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是 no 。

题目保证没有一个用户在一天中卖出超过一件商品

下面是查询结果格式的例子：

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         |
    +---------+------------+
    
    Result table:
    +-----------+--------------------+
    | seller_id | 2nd_item_fav_brand |
    +-----------+--------------------+
    | 1         | no                 |
    | 2         | yes                |
    | 3         | yes                |
    | 4         | no                 |
    +-----------+--------------------+
    
    id为 1 的用户的查询结果是 no，因为他什么也没有卖出
    id为 2 和 3 的用户的查询结果是 yes，因为他们卖出的第二件商品的品牌是他们自己最喜爱的品牌
    id为 4 的用户的查询结果是 no，因为他卖出的第二件商品的品牌不是他最喜爱的品牌

-- 解法一： 先用窗口函数，得到每个卖家按照订单时间升序的排行rk（为了找到每个用户按日期顺序卖出的第二件商品的品牌）
    -- 		  再根Items表进行join，得到对应的item_brand，并过滤出rk=2（按日期顺序卖出的第二件商品的item_brand）
    -- 		  再以users表为主表，进行join，判断商品的item_brand是否是用户的favorite_brand
    SELECT
    	u.user_id AS seller_id,
    	IF (item_brand = favorite_brand,'yes','no') AS 2nd_item_fav_brand
    FROM
    	Users u
    LEFT JOIN (
    		SELECT
    			i.item_id,
    			seller_id,
    			item_brand
    		FROM
    			Items i
    		LEFT JOIN (
    				SELECT
    					seller_id,
    					item_id,
    					rank() over(PARTITION BY seller_id ORDER BY order_date) rk
    				FROM Orders
    				) AS t1
    		ON i.item_id = t1.item_id
    		WHERE rk = 2
    ) AS t2 
    ON u.user_id = t2.seller_id