# Write your MySQL query statement below
1158. 市场分析 I
Table: Users
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ 此表主键是 user_id。 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ 此表主键是 order_id。 外键是 item_id 和（buyer_id，seller_id）。
Table: Items
+---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ 此表主键是 item_id。
请写出一条SQL语句以查询每个用户的注册日期和在 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 | +-----------+------------+----------------+
原站题解
pythondata 解法, 执行用时: 396 ms, 内存消耗: 61.5 MB, 提交时间: 2023-09-17 10:32:58
import pandas as pd
def market_analysis(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:
#筛选orders表2019年的订单，按buyer_id分组计数
tmp = orders[orders['order_date'].dt.year==2019].groupby('buyer_id').count()[['order_id']]
#users表左关联第1步的结果集
return users.join(tmp,on='user_id').fillna(0)[['user_id','join_date','order_id']].rename(columns={'user_id':'buyer_id','order_id':'orders_in_2019'})
def market_analysis2(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:
min_date, max_date = pd.to_datetime('2019-01-01'), pd.to_datetime('2019-12-30')
orders_df = orders[(orders['order_date']>=min_date) & (orders['order_date']<=max_date)]
orders_df = orders_df.groupby('buyer_id')['order_date'].count().reset_index()
df = pd.merge(users,orders_df,how='left',left_on='user_id',right_on='buyer_id').fillna(0)
df.columns = ['buyer_id','join_date','x','y','orders_in_2019']
return df[['buyer_id','join_date','orders_in_2019']]
def market_analysis3(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:
orders = orders[orders['order_date'].dt.year == 2019].groupby('buyer_id').size().reset_index()
orders = orders.rename(columns={0:'orders_in_2019'})
merged = pd.merge(users,orders,left_on='user_id',right_on='buyer_id',how='left').fillna(0)
merged = merged.drop(columns=['buyer_id']).rename(columns={'user_id':'buyer_id'})
return merged[['buyer_id','join_date','orders_in_2019']]
def market_analysis4(users: pd.DataFrame, orders: pd.DataFrame, items: pd.DataFrame) -> pd.DataFrame:
orders.loc[orders['order_date'].between('2019-01-01','2019-12-31','both'),'flag'] = '1'
orders = orders.loc[orders['flag']=='1']
orders = orders.groupby('buyer_id').agg({'order_date':'count'})
orders = orders.reset_index()
ans = users.merge(orders,how='left',left_on='user_id',right_on='buyer_id')
ans = ans.fillna(0)
ans = ans[['user_id','join_date','order_date']]
ans['order_date'] = ans['order_date'].astype(int)
ans = ans.rename(columns={'user_id':'buyer_id','order_date':'orders_in_2019'})
return ans
mysql 解法, 执行用时: 1325 ms, 内存消耗: 0 B, 提交时间: 2022-06-06 10:24:24
# Write your MySQL query statement below
select u.user_id as buyer_id, u.join_date, ifnull(b.cnt, 0) as orders_in_2019
from users u
left join (
select buyer_id, count(order_id) cnt
from orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
) b
on u.user_id = b.buyer_id
mysql 解法, 执行用时: 1354 ms, 内存消耗: 0 B, 提交时间: 2022-05-27 15:09:29
# Write your MySQL query statement below
select u.user_id as buyer_id, u.join_date, ifnull(b.cnt, 0) as orders_in_2019
from users u
left join (
select buyer_id, count(order_id) cnt
from orders
where order_date between '2019-01-01' and '2019-12-31'
group by buyer_id
) b
on u.user_id = b.buyer_id