leecode 数据库：1158. 市场分析 I
数据导入：
SQL Schema：
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');
Pandas Schema:
data = [[1, '2018-01-01', 'Lenovo'], [2, '2018-02-09', 'Samsung'], [3, '2018-01-19', 'LG'], [4, '2018-05-21', 'HP']] Users = pd.DataFrame(data, columns=['user_id', 'join_date', 'favorite_brand']).astype({'user_id':'Int64', 'join_date':'datetime64[ns]', 'favorite_brand':'object'}) data = [[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]] Orders = pd.DataFrame(data, columns=['order_id', 'order_date', 'item_id', 'buyer_id', 'seller_id']).astype({'order_id':'Int64', 'order_date':'datetime64[ns]', 'item_id':'Int64', 'buyer_id':'Int64', 'seller_id':'Int64'}) data = [[1, 'Samsung'], [2, 'Lenovo'], [3, 'LG'], [4, 'HP']] Items = pd.DataFrame(data, columns=['item_id', 'item_brand']).astype({'item_id':'Int64', 'item_brand':'object'})
表结构：
表： 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）是 User 表的外键。
表：Items
+---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+item_id 是此表的主键（具有唯一值的列）。
编写解决方案找出每个用户的注册日期和在 2019 年作为买家的订单总数。
以 任意顺序 返回结果表。
查询结果格式如下。
示例 1:
输入： Users 表: +---------+------------+----------------+ | 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 表: +----------+------------+---------+----------+-----------+ | 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 表: +---------+------------+ | item_id | item_brand | +---------+------------+ | 1 | Samsung | | 2 | Lenovo | | 3 | LG | | 4 | HP | +---------+------------+ 输出： +-----------+------------+----------------+ | 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 | +-----------+------------+----------------+
预备知识
本题使用到的 MySQL 函数的说明：
ifnull(x1, x2) ：如果 x1 为 NULL， 返回 x2，否则返回 x1。
方法一：left join 和 group by
思路
首先根据 Orders 表计算每个用户的订单数，通过 buyer_id 和 Users 表的 user_id 将两份数据结合，找到所有用户的注册时间和订单的数量。
注意：用户订单的数量可能会 0，需要使用 ifnull 函数特殊处理这种数据。
算法
使用 Orders 表计算每个用户的产品数。使用 group by 聚合每个用户的购买记录。使用 between 筛选出时间为 2019 年的数据。使用 count(order_id) 计算出每个用户的订单数。
Mysql
select buyer_id, count(order_id) cnt
from Orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
使用 Users 表得到所有用户及其注册时间。并使用 left join，通过 user_id 和第一步的数据连接，求每个用户的订单数。 如果一个用户没有任何订单，那么第一步的数据中不会有这个用户的数据，最后的 orders_in_2019 会显示为 null，所以我们还需要使用 ifnull，如果数据为 null，将其改为 0。
Mysql代码
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 Orderswhere order_date between '2019-01-01' and '2019-12-31'group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id