滴滴面试题---Mysql业务取数

业务需求：【时间、数据均为面试之用】

数据源： 链接:https://pan.baidu.com/s/1hcH5lw8uDccbpCg85Qorag 提取码:86k2

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

---

---

现有四张表，分别是“司机数据”表，“订单数据”表，“在线时长数据”表，“城市匹配数据”表。
1、“司机数据”表，记录了日期、司机id、城市id、首次完成订单时间

日期	司机id	城市id	首次完成订单时间
2020-08-01	1	100000	2016-01-01
2020-08-01	2	100000	2018-07-02
2020-08-01	3	100000	2020-07-08

2、“订单数据”表，记录了日期、订单id、司机id、乘客id、产品线id、流水

产品线id: 1是表示专车，2表示企业，3表示快车，4表示企业快车

日期	订单id	司机id	乘客id	产品线id	流水
2020-08-01	1001	1	301	1	200
2020-08-01	1002	1	302	1	100
2020-08-01	1003	2	302	1	120

3、“在线时长数据”表，记录了日期、司机id、在线时长

日期	司机id	在线时长
2020-08-01	1	2
2020-08-01	2	1.1
2020-08-01	2	5

4、“城市匹配数据”表，记录了城市id、城市名称

城市id	城市名称
100000	北京
200000	上海
300000	天津

---

---

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

解题思路：
涉及对多表的查询，首先明确所查询的字段都来源于什么表
a.对题目关键词作判断，各自匹配哪个字段

SELECT a.`日期`,c.`城市名称`,a.`司机id`,b.`订单id`,b.`流水`
FROM 司机数据 a
LEFT JOIN 订单数据 b 
	ON a.`司机id` = b.`司机id`
LEFT JOIN 城市匹配数据 c 
	ON a.`城市id` = c.`城市id`;

b.对日期，产品id作筛选、按城市作分组，对司机数、订单数、流水做聚合操作

#产品id='3表示快车
SELECT t.`日期`,t.`城市名称`,
		count(distinct t.`司机id`) AS 司机数,
		count(t.`订单id`) AS 快车订单量,
		SUM(t.`流水`) AS 快车流水数据
FROM (
        SELECT a.`日期`,c.`城市名称`,a.`司机id`,b.`产品线id`,b.`订单id`,b.`流水`
        FROM 司机数据 a
        LEFT JOIN 订单数据 b 
            ON a.`司机id` = b.`司机id`
        LEFT JOIN 城市匹配数据 c 
            ON a.`城市id` = c.`城市id`
	) t
WHERE (t.`日期` BETWEEN '2020-08-01' AND '2020-08-31') AND t.`产品线id`='3'   
GROUP BY t.`城市名称`,t.`日期`;

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

a.这题的关键在于怎么对新老司机做查询
新老司机：用if做判断,当日期=首次完成订单时间，那么为新司机，否则为老司机。

#首单日期在当月为新司机，用DATE_FORMAT(date, '%Y-%m')格式化处理
SELECT a.*
	, if(DATE_FORMAT(`日期`, '%Y-%m') = DATE_FORMAT(`首次完成订单时间`, '%Y-%m'), '新司机', '老司机') AS 新老司机
FROM `司机数据` a

再对其根据题目要求进行一个筛选、分组聚合操作

#城市id=100000表示北京市
SELECT t.`城市id`,
        MONTH(t.`日期`) AS 月份,
		SUM(IF(新老司机='新司机',1,0)) AS 新司机数,
		SUM(IF(新老司机='老司机',1,0)) AS 老司机数
FROM
(SELECT a.*,
			if(DATE_FORMAT(a.`日期`, '%Y-%m') = DATE_FORMAT(a.`首次完成订单时间`, '%Y-%m'), '新司机', '老司机') AS 新老司机
FROM `司机数据` a
WHERE (a.`日期` BETWEEN '2020-08-01' AND '2020-09-30') AND a.`城市id`='100000'
) t
GROUP BY MONTH(t.`日期`),t.`城市id`;

b.求在线时长和TPH（订单量/在线时长）数据

求订单量–来源于订单数据表；

求在线时长–来源于在线时长数据表

由于司机一天内可能会接多个订单，若直接关联这两张表，司机id将不唯一，会导致新老司机那一列出现计数时出现重复，算的新老司机的数量会不准确

SELECT 
    a.`日期`,
    a.`城市id`,
    b.`订单id`,
    c.`在线时长`,
    a.`司机id`
	,if(DATE_FORMAT(a.`日期`, '%Y-%m') = DATE_FORMAT(a.`首次完成订单时间`, '%Y-%m'), '新司机', '老司机') AS 新老司机
FROM `司机数据` a
LEFT JOIN `订单数据` b
	ON a.`司机id`=b.`司机id`
LEFT JOIN `在线时长数据` c
	on a.`司机id`=c.`司机id`;

解决的方法：新老司机和在线时长、TPH数据都分开来算，最后再进行拼接

#数据表中也没给出每个订单所花的时间，只给出当天所有订单共花的时间，所以在线时长和订单量也要分开算

#查询订单量
SELECT 
  MONTH(a.`日期`) AS 月份,
  count(a.`订单id`) AS 订单量
FROM `订单数据` a
LEFT JOIN `司机数据` b
	ON a.`司机id`=b.`司机id`
WHERE (a.`日期` BETWEEN '2020-08-01' AND '2020-09-30') AND b.`城市id`='100000'
GROUP BY MONTH(a.`日期`)

#查询在线时长
SELECT 
  MONTH(a.`日期`) AS 月份,
  SUM(a.`在线时长`) AS 在线时长
FROM `在线时长数据` a
LEFT JOIN `司机数据` b
	ON a.`司机id`=b.`司机id`
WHERE (a.`日期` BETWEEN '2020-08-01' AND '2020-09-30') AND b.`城市id`='100000'
GROUP BY MONTH(a.`日期`)

c.将所有表拼接在一起即可

select 
t1.*,t2.订单量,t3.在线时长,(t2.订单量/t3.在线时长) TPH
from
(select 
t.城市id,
month(t.日期) 月份,
sum(if(新老司机='新司机',1,0)) 新司机数,
sum(if(新老司机='老司机',1,0)) 老司机数
from 
(select
a.*,
if(date_format(日期,'%y-%m')=DATE_FORMAT(首次完成订单时间,'%y-%m'),'新司机','老司机') 新老司机
from 司机数据 a) t
where (t.日期 BETWEEN '2020-08-01' and '2020-09-30' ) and t.城市id=100000
group by month(t.日期)) t1
left join 
(select 
b.城市id,
month(b.日期) 月份,
count(a.订单id) 订单量
from 订单数据 a 
left join 司机数据 b
on a.司机id=b.司机id
where (b.日期 BETWEEN '2020-08-01' and '2020-09-30') and b.城市id=100000
group by month(a.日期)) t2
on t1.城市id=t2.城市id and t1.月份=t2.月份
left join 
(select 
b.城市id,
month(b.日期) 月份,
sum(a.在线时长) 在线时长
from 在线时长数据 a 
left join 司机数据 b
on a.司机id=b.司机id
where (b.日期 BETWEEN '2020-08-01' and '2020-09-30') and b.城市id=100000
group by month(a.日期)
) t3
on t1.城市id=t3.城市id and t1.月份=t3.月份

---

---

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

解题思路：

a.司机数大于20的城市名称

select 
b.*,
count(1) 司机数
from 司机数据 a
left join 城市匹配数据 b
on a.城市id=b.城市id
group by a.城市id
having count(1)>20

b.司机总在线时长大于2小时的司机id

select 
a.司机id
from
在线时长数据 a 
left join 司机数据 b
on a.司机id=b.司机id
where a.在线时长>2

c.司机订单量大于1的司机id

select 
a.司机id,count(订单id) 订单量
from
订单数据 a 
left join 司机数据 b
on a.司机id=b.司机id
group by a.司机id
having count(订单id)>1

d.合起来

select 
distinct t3.城市名称
from 
(select 
a.司机id,b.城市id
from
在线时长数据 a 
left join 司机数据 b
on a.司机id=b.司机id
where a.在线时长>2) t1
join 
(select 
a.司机id,b.城市id,count(订单id) 订单量
from
订单数据 a 
left join 司机数据 b
on a.司机id=b.司机id
group by a.司机id
having count(订单id)>1
) t2
on t1.司机id=t2.司机id
left join 
(select 
b.*,
count(1) 司机数
from 司机数据 a
left join 城市匹配数据 b
on a.城市id=b.城市id
group by a.城市id
having count(1)>20) t3
on t2.城市id=t3.城市id