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

with siji as(
    select driver_id 
    from tb_get_car_order 
    where fare is null 
    and date(order_time) between '2021-10-01'and '2021-10-31'
) , -- 找到有取消订单的司机
pf as(select driver_id, grade
from tb_get_car_order
where driver_id in(select driver_id from siji)
and grade is not null)-- 找到这些司机的评分

select driver_id,round(avg(grade),1)as avg_grade
from pf
group by driver_id -- 计算均分

union
select '总体'as driver_id,round(sum(grade)/count(driver_id),1)as avg_grade
from pf -- 计算总分
order by driver_id --排序，汉字默认在数字后