https://leetcode-cn.com/problems/hopper-company-queries-i/
我的代码
with recursive t(n) as (
select 1
union all
select n+1 from t where n<12
)
select a.m month,a.id active_drivers,case when b.cnt is not null then b.cnt else 0 end accepted_rides from (
select a.m,a.id from (
select a.m,convert(a.id,unsigned integer) id,row_number() over(partition by a.m order by a.id desc) id1 from(
select a.y m,case when b.id is not null then @id:=b.id else @id end id from(
select n y from t)a left join(
select year(join_date)y,month(join_date)m,sum(case when join_date<='2020-12-31' then 1 else 0 end) over(order by join_date) id
from Drivers
where year(join_date)<=2020
)b on a.y=b.m and b.y=2020
,(select @id:=0) as init
)a
)a where a.id1=1
)a left join (
select month(a.requested_at) m,count(*)cnt
from(
select * from Rides where year(requested_at)=2020
)a left join AcceptedRides b on a.ride_id=b.ride_id
where b.ride_id is not null
group by month(a.requested_at)
)b on a.m=b.m
大神的代码：
报告2020年每个月的以下统计信息：1) 截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）
2) 该月接受的乘车次数（accepted_rides）。
WITH RECURSIVE
mon_time AS (
-- 递归生成月份
SELECT 1 AS month
UNION ALL
SELECT month + 1
FROM mon_time
WHERE month <= 11),
active_driver AS (
-- 生成每个月激活的司机数，2020年以前的归到1月份
SELECT IF(YEAR(d.join_date) < '2020', 1, MONTH(d.join_date)) AS `month`,
COUNT(*) AS `driver`
FROM Drivers d
WHERE YEAR(d.join_date) < '2021'
GROUP BY IF(YEAR(d.join_date) < '2020', 1, MONTH(d.join_date)) ),
accepted_ride AS (
-- 计算每个月的订单
SELECT MONTH(r.requested_at) AS `month`, COUNT(*) AS `order`
FROM Rides r
JOIN AcceptedRides a ON r.ride_id = a.ride_id
WHERE YEAR(r.requested_at) = '2020'
GROUP BY MONTH(r.requested_at))
SELECT a.month,
IFNULL(SUM(b.driver) OVER (ORDER BY a.month), 0) AS `active_drivers`,
IFNULL(c.`order`, 0) AS `accepted_rides`
FROM mon_time a
LEFT JOIN active_driver b ON a.month = b.month
LEFT JOIN accepted_ride c ON a.month = c.month
