题36:
根据下面三个表编写SQL查询以报告2020年每个月的以下统计信息:
(1)截至某月底,当前在Hopper公司工作的驾驶员数:active_drivers;
(2)该月接受的乘车次数:accepted_rides。
要求:返回按month 升序排列的结果表,其中month 是月份的数字(一月是1,二月是2,依此类推)。
其中:driver_id是该表的主键。该表的每一行均包含驾驶员的ID以及他们加入Hopper公司的日期。
其中:ride_id是该表的主键。该表的每一行均包含行程ID(ride_id),用户ID(user_id)以及该行程的日期(requested_at)。注意的是此表可能有一些不被接受的乘车请求。
其中:ride_id是该表的主键。该表的每一行都包含已接受的行程信息。
表中的行程信息都在“Rides”表中存在。
解题思路:
(1)生成一个月份表a
with recursive a as(
select 1 month
union all
select month + 1 from a where month <= 11
)
(2)求累计注册司机人数:将表a和Drivers表做左连接,连接的条件就是月份小于等于2020年每个月,以月份作为分组条件,注意需要加上IFNULL()来包含无注册人员的例外情况。
select month,ifnull(count(driver_id),0) active_drivers
from A
left join Drivers
on 202000+month >= date_format(join_date,'%Y%m')
group by month
(3)求每月接单数量
select month(requested_at) month ,count(*) accepted_rides
from Rides
join AcceptedRides
using(ride_id)
where year(requested_at) = 2020
group by month (requested_at)
最终代码:
with recursive a as(
select 1 month
union all
select month + 1 from a where month <= 11
)
select b.month, b.active_drivers,ifnull(c.accepted_rides,0) accepted_rides
from(
select month,ifnull(count(driver_id),0) active_drivers
from A
left join Drivers
on 202000+month >= date_format(join_date,'%Y%m')
group by month
) b
left join (
select month(requested_at) month ,count(*) accepted_rides
from Rides
join AcceptedRides
using(ride_id)
where year(requested_at) = 2020
group by month (requested_at)
) c
using(month)
order by month;
注:
(1)DATE_FORMAT(join_date,’%Y%m’)提取注册日期的年月份,“%Y%m”定义了输出格式是YYYYMM且为数值型。
(2)using()是简化连接,但是一定要满足两个条件:
- 查询必须是等值连接;
- 等值连接中的列必须具有相同的名称和数据类型。