-- 链接 LINK: https://www.nowcoder.com/practice/f022c9ec81044d4bb7e0711ab794531a?tpId=268&tqId=2294893&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj
-- 标题 TITLE: SQL20 有取消订单记录的司机平均评分
-- 日期 DATE: 2022-02-02 06:09:46

/* NOTE : UNION 与 ORDER BY 的问题，以及 WITH ROLLUP
使用 WITH ROLLUP，此函数是对聚合函数进行求和，
注意 with rollup是对 group by 后的第一个字段，进行分组求和
*/

-- 题解 SQL CASE 1
SELECT driver_id, avg_grade
FROM (
    SELECT driver_id, ROUND(AVG(grade), 1) avg_grade, 0 tag
    FROM (
        SELECT driver_id, grade, 0 tag,
                SUM(CASE WHEN DATE_FORMAT(order_time, '%Y-%m') = '2021-10' AND start_time IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY driver_id) isCancel
        FROM tb_get_car_order
        WHERE finish_time IS NOT NULL
    ) T1
    WHERE isCancel > 0 AND grade IS NOT NULL
    GROUP BY driver_id
    UNION ALL
    SELECT '总体' driver_id, ROUND(AVG(grade), 1) avg_grade, 1 tag
    FROM (
        SELECT driver_id, grade, 
                SUM(CASE WHEN DATE_FORMAT(order_time, '%Y-%m') = '2021-10' AND start_time IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY driver_id) isCancel
        FROM tb_get_car_order
        WHERE finish_time IS NOT NULL
    ) T2
    WHERE isCancel > 0 AND grade IS NOT NULL
) TA
ORDER BY tag, avg_grade
;


-- 题解 SQL CASE 2
SELECT IFNULL(driver_id, '总体') driver_id, ROUND(AVG(grade), 1) avg_grade
FROM (
    SELECT driver_id, grade, 0 tag, 
            SUM(CASE WHEN DATE_FORMAT(order_time, '%Y-%m') = '2021-10' AND start_time IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY driver_id) isCancel
    FROM tb_get_car_order
    WHERE finish_time IS NOT NULL
) T1
WHERE isCancel > 0 AND grade IS NOT NULL
GROUP BY driver_id
WITH ROLLUP
ORDER BY (CASE WHEN driver_id IS NULL THEN 1 ELSE 0 END), driver_id
;


-- 数据 DATA ===================================================
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid INT NOT NULL COMMENT '用户ID',
city VARCHAR(10) NOT NULL COMMENT '城市',
event_time datetime COMMENT '打车时间',
end_time datetime COMMENT '打车结束时间',
order_id INT COMMENT '订单号'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_get_car_order (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
order_id INT NOT NULL COMMENT '订单号',
uid INT NOT NULL COMMENT '用户ID',
driver_id INT NOT NULL COMMENT '司机ID',
order_time datetime COMMENT '接单时间',
start_time datetime COMMENT '开始计费的上车时间',
finish_time datetime COMMENT '订单结束时间',
mileage FLOAT COMMENT '行驶里程数',
fare FLOAT COMMENT '费用',
grade TINYINT COMMENT '评分'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES
(101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null),
(102, '北京', '2021-11-01 09:00:30', '2021-11-01 09:01:00', 9001),
(101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002),
(103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003),
(104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004),
(105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005),
(106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006),
(107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007),
(108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008),
(109, '北京', '2021-10-08 18:00:00', '2021-10-08 18:01:00', 9009),
(110, '北京', '2021-09-30 17:59:30', '2021-09-30 18:01:00', 9010) ;

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
(9002, 101, 202, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null),
(9001, 102, 202, '2021-11-01 09:01:00', '2021-11-01 09:06:00', '2021-11-01 09:31:00', 10.0, 41.5, 3),
(9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4),
(9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4),
(9005, 105, 203, '2021-10-01 08:02:10', null, '2021-10-01 08:31:00', null, null, null),
(9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 5),
(9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5),
(9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4),
(9009, 109, 203, '2021-10-08 18:01:00', '2021-10-08 18:11:50', '2021-10-08 18:51:00', 13, 40, 5),
(9010, 110, 203, '2021-09-30 18:01:00', '2021-09-30 18:15:59', '2021-09-30 18:45:00', 10, 30, 5);