# Write your MySQL query statement below
262. 行程和用户
表：Trips
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | date | +-------------+----------+ id 是这张表的主键。 这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。 status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表：Users
+-------------+----------+ | Column Name | Type | +-------------+----------+ | users_id | int | | banned | enum | | role | enum | +-------------+----------+ users_id 是这张表的主键。 这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。 banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
查询结果格式如下例所示。
示例：
输入： Trips 表： +----+-----------+-----------+---------+---------------------+------------+ | id | client_id | driver_id | city_id | status | request_at | +----+-----------+-----------+---------+---------------------+------------+ | 1 | 1 | 10 | 1 | completed | 2013-10-01 | | 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 3 | 12 | 6 | completed | 2013-10-01 | | 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | | 5 | 1 | 10 | 1 | completed | 2013-10-02 | | 6 | 2 | 11 | 6 | completed | 2013-10-02 | | 7 | 3 | 12 | 6 | completed | 2013-10-02 | | 8 | 2 | 12 | 12 | completed | 2013-10-03 | | 9 | 3 | 10 | 12 | completed | 2013-10-03 | | 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | +----+-----------+-----------+---------+---------------------+------------+ Users 表： +----------+--------+--------+ | users_id | banned | role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+ 输出： +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+ 解释： 2013-10-01： - 共有 4 条请求，其中 2 条取消。 - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。 - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 3) = 0.33 2013-10-02： - 共有 3 条请求，其中 0 条取消。 - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。 - 取消率为 (0 / 2) = 0.00 2013-10-03： - 共有 3 条请求，其中 1 条取消。 - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 2) = 0.50
原站题解
pythondata 解法, 执行用时: 404 ms, 内存消耗: 61 MB, 提交时间: 2023-09-17 10:56:23
import pandas as pd
def trips_and_users(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
# 筛选非禁止用户和司机，以及日期
df = trips.merge(users, left_on='client_id', right_on='users_id', how='left')
df = df.merge(users, left_on='driver_id', right_on='users_id', how='left')
df = df[((df['banned_x'] == 'No') & (df['banned_y'] == 'No')) & ((df['request_at'] == '2013-10-01') | (df['request_at'] == '2013-10-03') | (df['request_at'] == '2013-10-02'))]
'''
# 筛选非禁止用户和司机，以及日期
banned_users = users[users['banned'] == 'Yes']['users_id']
df = trips[(~trips['client_id'].isin(banned_users)) & (~trips['driver_id'].isin(banned_users)) & (trips['request_at'] >= '2013-10-01') & (trips['request_at'] <= '2013-10-03')]
'''
# 非禁止用户订单总数
df_total = df.groupby('request_at')['status'].size().reset_index(name='count_total')
# 被司机或乘客取消的订单数量
df_cancel = df.groupby(['request_at', 'status']).size().reset_index(name='count_cancel')
df_cancel = df_cancel[df_cancel['status'] != 'completed']
df_cancel = df_cancel.groupby('request_at')['count_cancel'].sum()
# 计算取消率
df_rate = df_total.merge(df_cancel, on='request_at', how='left')
df_rate['Cancellation Rate'] = round(df_rate['count_cancel'] / df_rate['count_total'], 2)
# 修改列名
df_rate = df_rate.rename(columns={'request_at': 'Day'})
# 修改null
df_rate = df_rate.fillna(value=0)
return df_rate[['Day', 'Cancellation Rate']]
def trips_and_users2(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
# 筛选出被禁止的用户id作为列表
banned_user = users.query('banned == "Yes"')['users_id']
# 按要求进行筛选：1、乘客和司机都不能被禁止（用刚才筛出的id列表，反向"~"isin()剔除）；2、时间范围是1号~3号
df = trips[~(trips['client_id'].isin(banned_user)) & ~(trips['driver_id'].isin(banned_user)) & (trips["request_at"]>="2013-10-01") & (trips["request_at"]<="2013-10-03")]
# 用分组+agg，统计分子和分母，注意分子lambda的写法，之所以用sum(),是因为前面判断条件true的相当于=1，求和就等于统计了
out = df.groupby('request_at').agg(
分母=('status', 'count'),
分子=('status', lambda x: (x != 'completed').sum())
).reset_index()
# 新增一列要求的'Cancellation Rate'，用分子除以分母，最后用round方法控制小数点保留2位
out['Cancellation Rate'] = (out['分子'] / out['分母']).round(2)
# 最后输出，把日期那列按要求改一下名，挑选需要的列
return out.rename(columns={'request_at': 'Day'})[['Day', 'Cancellation Rate']]
def trips_and_users3(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
df = trips[(trips["request_at"]>="2013-10-01") & (trips["request_at"]<="2013-10-03")]
client_ban = users[(users["banned"]=='Yes')&(users["role"]=='client')]["users_id"].tolist()
driver_ban = users[(users["banned"]=='Yes')&(users["role"]=='driver')]["users_id"].tolist()
df = df[(~df["client_id"].isin(client_ban))&(~df["driver_id"].isin(driver_ban))]
cancel = df[df["status"]!="completed"].groupby("request_at").size().reset_index(name="cancel_cnt")
df = df.groupby("request_at").size().reset_index(name="total")
df = df.merge(cancel,on="request_at",how='left').fillna(0)
df["Cancellation Rate"] = round(df["cancel_cnt"]/df["total"],2)
df.rename(columns=({"request_at":"Day"}),inplace=True)
return df[["Day","Cancellation Rate"]]
mysql 解法, 执行用时: 404 ms, 内存消耗: N/A, 提交时间: 2018-08-22 16:28:17
# Write your MySQL query statement below select t.Request_at as Day, round(sum(case when t.Status like 'cancelled%' then 1 else 0 end)/count(*), 2) as 'Cancellation Rate' from Trips t inner join Users u on u.Users_Id = t.Client_Id and u.Banned = 'No' where t.Request_at between '2013-10-01'and'2013-10-03' group by t.Request_at;