1 業務描述
在打車業務中, 會存在乘客取消訂單, 或者司機取消訂單情況,
在這些取消訂單的情況中, 有些是因為乘客自身的一些原因,
比如有突發事件, 或者臨時不需用車的情況等,
也有些是因為乘坐過程的因素, 導致客戶取消訂單,
我們認為因為乘客自身原因取消訂單時, 這是正常的取消,
而如果在訂單取消時, 有司機或者乘客投訴時, 是異常取消
在業務當中需要關註乘客的取消率
2 表定義
在這裡, 我們模擬兩個表,
分別是訂單表和投訴表,
訂單表:
- create table trips (
- id int,
- client_id int, // 乘客id
- driver_id int, // 司機 id
- status varchar(40), // 訂單狀態
- date date
- );
status是訂單狀態, 包括:
cancelled_by_client 被乘客取消,
cancelled_by_driver 被司機取消,
completed 完成訂單
插入數據
id |
client_id |
driver_id |
status |
date |
1 |
1 |
5 |
cancelled_by_client |
2023-11-15 |
2 |
3 |
5 |
cancelled_by_driver |
2023-11-15 |
3 |
4 |
8 |
completed |
2023-11-15 |
4 |
1 |
8 |
completed |
2023-11-16 |
5 |
2 |
6 |
completed |
2023-11-16 |
6 |
3 |
8 |
completed |
2023-11-17 |
7 |
2 |
5 |
completed |
2023-11-17 |
8 |
4 |
7 |
cancelled_by_client |
2023-11-17 |
9 |
3 |
6 |
cancelled_by_client |
2023-11-17 |
投訴表:
- create table trip_user (
- users_id int,
- complaint varchar(20), // 是否投訴
- role varchar(20)
- );
users_id |
complaint |
role |
1 |
No |
client |
2 |
Yes |
client |
3 |
No |
client |
4 |
No |
client |
5 |
No |
driver |
6 |
No |
driver |
7 |
Yes |
driver |
8 |
No |
driver |
3 計算正常取消率
這裡我們計算訂單正常的取消率
- select date,
- round(
- 1.0 * sum(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else 0 end)
- / count(1)
- , 2
- ) as cancellation_rate
- from trips as t
- where client_id not in
- (select users_id from trip_user where complaint='Yes')
- and driver_id not in
- (select users_id from trip_user where complaint='Yes')
- GROUP BY date
- ORDER BY date;
結果是:
date |
cancellation_rate |
2023-11-15 |
0.67 |
2023-11-16 |
0.00 |
2023-11-17 |
0.50 |