题解 | #有取消订单记录的司机平均评分#

思路：

-- 1.先拿到2021年10月有过取消订单记录的司机：date_format(order_time, '%Y-%m') = '2021-10' and fare is null

-- 2.分组统计每人全部已完成的有评分订单的总评分以及平均评分

-- 3.每人全部已完成的有评分订单的平均评分 union all 总体平均分即可

-- 思路：
--      1.先拿到2021年10月有过取消订单记录的司机：date_format(order_time, '%Y-%m') = '2021-10' and fare is null
--      2.分组统计每人全部已完成的有评分订单的总评分以及平均评分
--      3.每人全部已完成的有评分订单的平均评分 union all 总体平均分即可
with tmp as(
select 
    distinct driver_id
from tb_get_car_order 
where date_format(order_time, '%Y-%m') = '2021-10' and fare is null
),
t1 as (
select 
    driver_id,
    sum(grade) total_grade,
    round(avg(grade), 1) as avg_grade,
    count(order_id) as cnt
from tb_get_car_order
where driver_id in (
    select 
        driver_id
    from tmp 
) and fare is not null 
group by driver_id
)

(select 
driver_id,
avg_grade
from t1
order by driver_id)
union all 
select 
    '总体' as driver_id, round(sum(total_grade) / sum(cnt), 1) as avg_grade
from t1