滴滴SQL面试题之打车业务问题如何分析

一、题目

二、步骤

1、数据类型转化

2、提取2020年8月各城市每天的司机数，快车订单量和快车流水数据

①2020年8月各城市每天的司机数

②快车订单量和快车流水数据

③小结

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

4、分别提取司机数大于20，司机总在线时长大于2小时，订单量大于1，乘客数大于1的城市名称数据。

一、题目

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

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

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

3. 分别提取司机数大于20，司机总在线时长大于2小时，订单量大于1，乘客数大于1的城市名称数据。

二、步骤

1、数据类型转化

通过观察数据库表结构可知，关于时间的存储类型是varchar，但是需要提取到月份，所以需要将时间列的类型转换成date的特定的格式（数据中涉及多个时间）


update 司机数据 
set 日期=date_format(日期,'%Y-%m-%d');

2、提取2020年8月各城市每天的司机数，快车订单量和快车流水数据

①2020年8月各城市每天的司机数

根据题目意思以及表结构，需要将城市匹配数据与司机数据两张表进行连接，连接的条件是司机id相等

一个限制条件——2020年8月，也就是司机数据表的日期在2020-08-01到2020-08-31范围之内

一个聚合——对司机数据表中的司机id进行聚合count()

两个分组——8月各城市，也就是8月的每天以及每个城市的情况，所以需要对日期以及城市id进行分组


select b.`城市名称`,a.`日期`,COUNT(司机id) as '司机数'from 
`司机数据` as a left join `城市匹配数据` as b
on a.`城市id`=b.`城市id`
where a.`日期` between '2020-08-01' and '2020-08-31'
group by a.`城市id`,a.`日期`

②快车订单量和快车流水数据

在四个表中，只有订单数据中含有快车数据，但是表中没有城市id和司机id，所以需要跟其他表进行关联才能得到结果

两个限制条件——一个是时间上的限制，一个就是订单的类型

两个聚合——一个是订单量，一个是流水数据

订单量也就是对产品线id的求个数和

流水数据是对表中流水的求和

两个分组——8月各城市，也就是8月的每天以及每个城市的情况，所以需要对日期以及城市id进行分组


select c.`城市名称`,a.`日期`,COUNT(a.`产品线id`) as '快车订单量',SUM(a.`流水`) as '快车流水数据' from 
`订单数据` as a left join `司机数据` as b
on a.`司机id`=b.`司机id`
left join `城市匹配数据` as c
on b.`城市id` = c.`城市id`
where a.`日期` between '2020-08-01' and '2020-08-31' and a.`产品线id`=3
GROUP BY c.`城市id`,b.`日期`

③小结

前两步已经分别求出了想要的数据，但是因为判断条件的原因，不能将两张表给联合起来，所以只能将两个表作为子表，然后再去查子表得到数据

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

①2020年8月和9月的新司机的司机数

根据数据可知，所需要的数据全在一张表中，所以不需要连表

三个限制条件——时间、新司机（首单日期在当月），城市为北京

上一问的时间条件是8月，此问的是8月和9月也就是将时间的范围扩大到2020-09-31

新司机的条件是首单日期在当月，也就是用时间函数得到首单日期和日期的月份相等

通过城市匹配数据的城市id确定城市在北京

分组——只需要按照时间进行分组即可


SELECT
日期,
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' );

②在线时长

在线时长在在线时长数据的表中而订单完成时间等在司机数据表中，所以需要对两表进行关联，关联的条件就是司机id

也就是在第一问的条件基础上形成的子表与在线时长数据表进行关联

在线时长需要一个求和的计算


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

③订单量

订单量的计算与在线总时长的原理是一致的，只是关联的表不一样而已


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

④总结

有了前面查出来的数据之后可以将其作为子表，然后再通过查子表得到最后的数据


SELECT
	a.`新司机THP` AS '新司机THP',
	b.`老司机THP` AS '老司机THP' 
FROM
	(
	SELECT
		b.`订单量` / a.`在线时长` AS '新司机THP' 
	FROM
		(
		SELECT
			sum( b.`在线时长` ) AS '在线时长' 
		FROM
			(
			SELECT
				* 
			FROM
				`司机数据` 
			WHERE
				YEAR ( `首次完成订单时间` )= YEAR ( `日期` ) 
				AND MONTH ( `日期` ) = MONTH ( `首次完成订单时间` ) 
				AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
				AND `城市id` = 100000 
			) AS a
			LEFT JOIN `在线时长数据` AS b ON a.`司机id` = b.`司机id` 
		GROUP BY
		DATE_FORMAT( a.`日期`, '%Y-%m' )) AS a,
		(
		SELECT
			COUNT( b.`订单id` ) AS '订单量' 
		FROM
			(
			SELECT
				* 
			FROM
				`司机数据` 
			WHERE
				YEAR ( `首次完成订单时间` )= YEAR ( `日期` ) 
				AND MONTH ( `日期` ) = MONTH ( `首次完成订单时间` ) 
				AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
				AND `城市id` = 100000 
			) AS a
			LEFT JOIN `订单数据` AS b ON a.`司机id` = b.`司机id` 
		GROUP BY
		DATE_FORMAT( a.`日期`, '%Y-%m' )) AS b 
	) AS a,
	(
	SELECT
		b.`订单量` / a.`在线时长` AS '老司机THP' 
	FROM
		(
		SELECT
			sum( b.`在线时长` ) AS '在线时长' 
		FROM
			(
			SELECT
				* 
			FROM
				`司机数据` 
			WHERE
				YEAR ( `首次完成订单时间` )= YEAR ( `日期` ) 
				AND MONTH ( `日期` ) = MONTH ( `首次完成订单时间` ) 
				AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
				AND `城市id` = 100000 
			) AS a
			LEFT JOIN `在线时长数据` AS b ON a.`司机id` = b.`司机id` 
		GROUP BY
		DATE_FORMAT( a.`日期`, '%Y-%m' )) AS a,
		(
		SELECT
			COUNT( b.`订单id` ) AS '订单量' 
		FROM
			(
			SELECT
				* 
			FROM
				`司机数据` 
			WHERE
				YEAR ( `首次完成订单时间` ) <> YEAR ( `日期` ) 
				AND MONTH ( `日期` ) <> MONTH ( `首次完成订单时间` ) 
				AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
				AND `城市id` = 100000 
			) AS a
			LEFT JOIN `订单数据` AS b ON a.`司机id` = b.`司机id` 
		GROUP BY
		DATE_FORMAT( a.`日期`, '%Y-%m' )) AS b 
	) AS b

4、分别提取司机数大于20，司机总在线时长大于2小时，订单量大于1，乘客数大于1的城市名称数据。

①司机数大于20的城市名称

司机的数据在司机数据表中，城市名称在城市匹配数据表中，所以需要进行连表，连表的条件就是城市id

还需要对城市id进行分组，然后统计司机个数，并且其数量还要大于20，因为是分组之后的统计，所以判断条件不能用where，而是要用having，且在分组后面


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

②司机在线总时长大于2小时

在线时长数据，城市id两表之间是没有可以连接的键的，所以需要通过司机数据表做为中间表进行关联

先对在线时长表中的数据进行对司机id的分组然后聚合得到其在线总时长，然后再筛选大于2小时的数据

将筛选过的表再与司机数据表以及城市信息表进行关联

③ 订单量大于1

其思路与求在线总时长是一致的


SELECT
		a.`司机id`,
		a.`订单量`,
		c.`城市名称` 
	FROM
		( SELECT `司机id`, COUNT( `司机id` ) AS `订单量` FROM `订单数据` GROUP BY `司机id` HAVING COUNT( `司机id` ) > 1 ) AS a
		LEFT JOIN ( SELECT `司机id`, `城市id` FROM `司机数据` ) AS b ON a.`司机id` = b.`司机id`
		JOIN `城市匹配数据` AS c ON b.`城市id` = c.`城市id`

④乘客数大于1

思路也是一样的


SELECT
		a.`司机id`,
		a.`乘客数`,
		c.`城市名称` 
	FROM
		( SELECT `司机id`, COUNT( `乘客id` ) AS `乘客数` FROM `订单数据` GROUP BY `司机id` HAVING COUNT( `乘客id` ) > 1 ) AS a
		LEFT JOIN ( SELECT `司机id`, `城市id` FROM `司机数据` ) AS b ON a.`司机id` = b.`司机id`
		JOIN `城市匹配数据` AS c ON b.`城市id` = c.`城市id`

⑤小结

当需要关联的表很多时，可以先根据一个表得到筛选的数据，然后再与其它数据关联，因为筛选的条件或者说范围只是小范围的，全部表关联之后在进行容易出错

更多面试题可以关注公众号猴子数据分析

相关阅读:
[python刷题模板] 子序列自动机
如何实现Spring的事务管理功能:@Transactional声明式事务
跨域的MPLS VXN Option C
cocos2dx:CCOrbitCamera 实现精灵的球面翻转或类似翻书操作，以及翻转轨迹优化问题
【动态规划】96. 不同的二叉搜索树
如何实现LRU缓存淘汰算法？
Jetson Nano TensorRT C++加速 YOLOV5，集成进qt项目中
CVL网卡的ADQ特性在SPDK的NVMF测试中的应用实例 - 上篇
自定义类加载器加载网络Class
Java 异常处理、继承、重写/重载

原文地址：https://blog.csdn.net/qq_41404557/article/details/125999303

滴滴SQL面试题之打车业务问题如何分析

一、题目

二、步骤

1、数据类型转化

2、提取2020年8月各城市每天的司机数，快车订单量和快车流水数据

①2020年8月各城市每天的司机数

②快车订单量和快车流水数据

③小结

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

①2020年8月和9月的新司机的司机数

②在线时长

③订单量

④总结

4、分别提取司机数大于20，司机总在线时长大于2小时，订单量大于1，乘客数大于1的城市名称数据。

①司机数大于20的城市名称

②司机在线总时长大于2小时

③ 订单量大于1

④乘客数大于1

⑤小结

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