【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' AND '2020-08-31'
AND orders.产品线id = 3
GROUP BY driver.城市id,orders.日期;

查询结果如下图：

（3）2020年8月各城市每天的快车流水合计

分析过程与上题类似，不再赘述。

SQL语句如下：

SELECT city.城市名称,orders.日期,sum(orders.流水) 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' AND '2020-08-31'
AND orders.产品线id = 3
GROUP BY driver.城市id,orders.日期;

查询结果如下图：

2.提取2020年8月和9月，每个月的北京市新老司机（首单日期在当月为新司机）的司机数、在线时长和TPH（订单量/在线时长）数据。

我们将新老司机分开来分析，先针对新司机进行提取，然后老司机同理可得。

（1）提取2020年8月和9月，每个月的北京市新司机的司机数。

我们可以使用多维度拆解分析方法来拆解题目为以下子问题：

1. 每个月的司机数
2. 条件：新司机
3. 时间条件：2020年8月和9月
4. 城市条件：北京市

先来看子问题1：每个月的司机数
对于司机数的计算，用到“司机数据”表。“每个月”就是按月份分组（group by），用count(司机id)来汇总司机数。

SELECT COUNT(司机id) AS 司机数
FROM 司机数据
GROUP BY 月份;

那么上面的月份怎么得到呢？
可以通过“日期”列获得：date_formate(日期,’%Y-%m’)
所以，上面的SQL修改为：

SELECT COUNT(司机id) AS 司机数
FROM 司机数据
GROUP BY DATE_FORMAT(日期,'%Y-%m') AS 月份;

子问题2：条件新司机
对于 ‘新司机’ 这个条件，由题目中的释义可知：首单日期在当月为新司机。因此我们通过比较 “日期” 与 “首次完成订单日” 两列的年月 相等的，即为新司机。
我们用函数 year(日期) 来提取日期中的年份。用函数 month(日期) 来提取日期中的月份。

利用如下“where”条件来表示新司机：

WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期) 

加入“新司机”条件后的sql如下：

SELECT COUNT(司机id) AS 新司机数
FROM 司机数据
WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期)
GROUP BY DATE_FORMAT(日期,'%Y-%m') AS 月份;

子问题3：时间条件2020年8月和9月
利用where加条件，between…and 函数限制时间范围。

SELECT DATE_FORMAT(日期,'%Y-%m') AS 月份, COUNT(司机id) AS 新司机数
FROM 司机数据
WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期)
AND 日期 BETWEEN '2020-08-01' AND '2020-09-31'
GROUP BY DATE_FORMAT(日期,'%Y-%m');

子问题4：城市条件北京市
利用where添加城市条件，城市id等于北京市的id。

最终SQL如下：

SELECT DATE_FORMAT(日期,'%Y-%m') AS 月份, COUNT(司机id) AS 新司机数
FROM 司机数据
WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期)
AND 日期 BETWEEN '2020-08-01' AND '2020-09-31'
AND 城市id = '100000'
GROUP BY DATE_FORMAT(日期,'%Y-%m');

查询结果如下：

（2）提取2020年8月和9月，每个月的北京市新司机的在线时长。

对于在线时长的计算，要用到“在线时长数据”表，在线时长的合计用sum(在线时长)来计算。
我们要现在“司机数据”表中通过查询得到新司机，然后再利用子查询，使用sun(在线时长)得到总时长。

SQL语句如下：

SELECT DATE_FORMAT(newd.日期,'%Y-%m') AS 月份,
sum(onlinet.在线时长) AS 新司机在线时长
FROM
(SELECT *
FROM 司机数据
WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期)
AND 日期 BETWEEN '2020-08-01' AND '2020-09-31'
AND 城市id = '100000') AS newd
LEFT JOIN 在线时长数据 AS onlinet
ON newd.司机id = onlinet.司机id
GROUP BY DATE_FORMAT(newd.日期,'%Y-%m');

查询结果如下：

（3）提取2020年8月和9月，每个月北京新司机的TPH（订单量/在线时长）。

由题可知，TPH=订单量/在线时长。其中在线时长我们在上一题中已经求得，因此只需求出订单量即可。

订单量的计算，会用到 “订单数据” 表，用count(订单id)来计算。然后思路与上题一样，新司机在 “订单数据” 表中并没有，而是在 “司机数据”表 中通过查询语句才能得到。

因此我们用两个表的联结，得到 8,9月北京新司机的在线时长，然后再利用子查询，使用count(订单id)得到订单量。

SQL语句如下：

SELECT DATE_FORMAT(newd.日期,'%Y-%m') AS 月份,
COUNT(orders.订单id) AS 新司机订单量
FROM
(SELECT *
FROM 司机数据
WHERE YEAR(首次完成订单时间)=YEAR(日期) 
AND MONTH(首次完成订单时间)=MONTH(日期)
AND 日期 BETWEEN '2020-08-01' AND '2020-09-31'
AND 城市id = '100000') AS newd
LEFT JOIN 订单数据 AS orders
ON newd.司机id = orders.司机id
GROUP BY DATE_FORMAT(newd.日期,'%Y-%m');

查询结果如下：

因此，2020年8月和9月，每个月的北京市新司机的TPH=订单量/在线时长=2/4=0.5。

对于老司机的司机数，在线时长以及TPH，同理可得，只需要将 “新司机” 的条件 换成 “老司机” 即可，其他语句都不变。

老司机查询条件语句：

WHERE YEAR(首次完成订单时间)<>YEAR(日期) 
AND MONTH(首次完成订单时间)<>MONTH(日期)

3.分别提取司机数大于20的城市名称数据、总在线时长大于2小时的司机及其所在城市名称。

（1）司机数大于20的城市名称。

司机数的计算用count(司机id)，用到的是“司机数据”表。司机数大于20需要在“司机数据”表中根据城市分组（group by）后，使用having语句加上count(司机id)>20的条件。”城市名称在 “城市匹配数据”中，用表的联结。

SQL语句如下：

SELECT dnum.城市id, citys.城市名称, dnum.司机数
FROM 
(SELECT 城市id, COUNT(司机id) AS 司机数
FROM 司机数据
GROUP BY 城市id
HAVING COUNT(司机id)>20) AS dnum
LEFT JOIN 城市匹配数据 AS citys
ON dnum.城市id = citys.城市id;

查询结果如下：

（2）在线时长大于2小时的司机及其所在城市名称

总在线时长用sum(在线时长)来计算，用的是 “在线时长数据”表，而这个表中没有城市id，因此我们需要先联结“司机数据”表，得到城市id，再通过联结“城市匹配数据”表，得到对应的城市名称。

SQL查询语句如下：

SELECT onlinet.司机id, onlinet.总时长, citys.城市名称
FROM
(SELECT 司机id, SUM(在线时长) AS 总时长
FROM 在线时长数据
GROUP BY 司机id
HAVING SUM(在线时长)>2)
AS onlinet
LEFT JOIN 司机数据 AS drivers
ON onlinet.司机id=drivers.司机id
LEFT JOIN 城市匹配数据 AS citys
ON drivers.城市id=citys.城市id;

查询结果如下：

最后

以上就是有魅力西牛最近收集整理的关于【SQLPlanet】打车业务数据分析的全部内容，更多相关【SQLPlanet】打车业务数据分析内容请搜索靠谱客的其他文章。