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

tech2022-09-29 59

来源：力扣（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

最新回复(0)