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

WITH t1 as( -- 找到所有的有取消订单的司机和所有评分
    SELECT
       driver_id,
       grade
FROM tb_get_car_order
WHERE driver_id
IN (select driver_id -- 找到所有的有取消订单的司机id
    FROM tb_get_car_order
    WHERE fare IS NULL) AND fare IS NOT NULL
    order by order_id
),t2 as( -- 找到所求司机对应的评分分别求和并分别统计数量
    SELECT
       driver_id,
       sum(grade) OVER(PARTITION BY driver_id) fk_s,
       sum(grade) OVER() z_s,
       count(driver_id) OVER(PARTITION BY driver_id) fk_cnt,
       count(driver_id) OVER() z_cnt
    FROM t1
)

SELECT -- 按题目要求计算用union合并输出
       DISTINCT driver_id,
       round(fk_s/fk_cnt,1) avg_grade
FROM t2
UNION
SELECT
       DISTINCT '总体',
       round(z_s/z_cnt,1) avg_grade
FROM t2