题目
公司A产品是一个为用户提供打车服务的app(类似滴滴、uber)。现有四张表,分别是“司机数据”表,“订单数据”表,“在线时长数据”表,“城市匹配数据”表。
业务问题:
- 提取2020年8月各城市每天的司机数、快车订单量和快车流水数据。
- 提取2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
- 分别提取司机数大于20,司机总在线时长大于2小时,订单量大于1,乘客数大于1的城市名称数据。
数据概述
产品线id | 1表示专车,2表示企业,3表示快车,4表示企业快车 |
司机id | 司机的编号标识 |
城市id | 城市的编号标识 |
订单id | 订单号 |
乘客id | 司机登入app进行接单的持续时间 |
在线时长 | 城市的地理名称 |
流水 | 此次订单的收入 |
数据预览
解题思路
先将表中所有的日期数据转化为日期格式“年-月-日”的形式,使用date_format函数
sql语句为:
update 司机数据
set 日期=date_format(日期,'%Y-%m-%d');
update 司机数据
set 首次完成订单时间=date_format(首次完成订单时间,'%Y-%m-%d');
update 订单数据
set 日期=date_format(日期,'%Y-%m-%d');
update 在线时长数据
set 日期=date_format(日期,'%Y-%m-%d')
更新后数据如下:
接下来看需要分析的业务问题。
1. 提取2020年8月各城市每天的司机数、快车订单量和快车流水数据。
(1) 2020年8月各城市每天的司机数
使用逻辑树分析方法,拆解业务需求的每个部分。
“2020年8月”,可以用 between and 函数来对时间进行条件限制。
“每天的司机数”,司机数的计算用到的表是 “司机数据” 表。当出现“每天”要想到分组汇总,来解决“每天”这样的问题。用“日期“来分组(group by),用 count(司机id) 来汇总司机数。
“各城市”,城市在“城市匹配数据“表中。也就是“每个城市”所以用“城市“来分组(group by)。
这里涉及到两个表“司机数据” 表和“城市匹配数据“表,所以遇到多表查询的情况,两表联结的条件为城市id,
因为要查询的是司机数,所以要保留“司机数据”表中的全部数据,因此使用左联结。
sql语句如下:
SELECT b.`城市名称`, a.`日期`, COU