【SQLPlanet】打车业务数据分析

背景数据

公司的app（类似滴滴、uber）为用户提供打车服务。现有四张表，分别是“司机数据”表，“订单数据”表，“在线时长数据”表，“城市匹配数据”表。

以下是四张表的部分数据和部分字段含义：

1.司机数据

2.订单数据

上表中的“产品线id”：1表示专车，2表示企业专车，3表示快车，4表示企业快车

3.在线时长数据

4.城市匹配数据

业务问题

1. 分析出2020年8月各城市每天的司机数、快车订单量和快车流水合计。
2. 分析出2020年8月和9月，每个月的北京市新老司机（首单日期在当月为新司机）的司机数、在线时长和TPH（订单量/在线时长）数据。
3. 分别提取司机数大于20的城市名称数据、总在线时长大于2小时的司机及其所在城市名称。

解决思路

1.提取2020年8月各城市每天的司机数、快车订单量和快车流水合计。

（1）2020年8月各城市每天的司机数

使用逻辑树分析方法，拆解业务需求的每个部分。

• “每天的司机数”，司机数的计算用到的表是“司机数据”表。当出现“每天”的时候，要想到用“日期”来分组（group by），然后用 count(司机id)来汇总得出司机数。
• “2020年8月”，可以用 between and 语句来对时间进行条件限制。
• “各城市”，也就是“每个城市”所以用“城市”来分组（group by）。
• 要显示城市名称，所以需要同时查询“司机数据”表和“城市匹配数据”表，通过“城市id”字段联结。因为要查询的是司机数，所以要保留“司机数据”表中的全部数据，因此使用左联结。

SQL语句如下：

SELECT city.城市名称,driver.日期,COUNT(driver.司机id) AS 司机数
FROM 司机数据 AS driver
LEFT JOIN 城市匹配数据 AS city
ON driver.城市id = city.城市id
WHERE driver.日期 BETWEEN '2020-08-01' AND '2020-08-31'
GROUP BY driver.城市id,driver.日期;

查询结果如下图(部分展示)：

（2）2020年8月各城市每天的快车订单量

• “每天的快车订单量”，对于计算快车订单量，用到的表是 “订单数据” 表。根据题目的字段解释（“产品线id”: 1是表示专车，2表示企业，3表示快车，4表示企业快车），可以用where子句把快车数据先筛选出来：产品线id=3。当出现“每天”的时候，要想到用“日期”来分组（group by），然后用 count((订单id)来汇总得出订单量。
• “2020年8月”，可以用 between and 语句来对时间进行条件限制。
• “各城市”，城市名称在“城市匹配数据“表中，“各城市”所以用“城市id“来分组（group by）。
• 在 “订单数据” 表、“司机数据”表中都没有城市数据，所以需要三表联结。因为要查询的是快车订单量，所以要保留“订单数据”表中的全部数据，因此使用左联结来与“司机数据”进行联结（联结依据为“司机id”）。然后，因为要对第一次联结后的表的“城市id”与“城市名称”进行匹配，所以再次用左联结来进行匹配（联结依据为“城市id”）。

SQL语句如下：

SELECT city.城市名称,orders.日期,COUNT(orders.订单id) AS 快车订单量
FROM 订单数据 AS orders
LEFT JOIN 司机数据 AS driver
ON orders.司机id = driver.司机id
LEFT JOIN 城市匹配数据 AS city
ON driver.城市id = city.城市id
WHERE orders.日期 BETWEEN '2020-08-01'</