题解|IFNULL判断打车状态#各城市最大同时等车人数#

# 乘客开始打车时间：event_time
# 乘客结束打车时间：end_time(无司机接单、超时或乘客取消打车的情况)

# 司机接单时间：order_time
# 行程开始时间：start_time  （若接单后取消，取消时间为finish_time,没有start_time）
# 行程结束时间：finish_time

## 等车是指：（开始打车 - 司机接单） + （司机接单 - 行程开始/行程取消）

### 重点就在于理清楚离开时间如何确定，也即离开状态如何确定
## 进入时间：event_time的时间
## 离开时间：1.无司机接单、超时或乘客取消打车。等车离开时间为打车结束时间：end_time
##          2.司机接单，打车时间结束，等待上车。等车离开时间为上车时间：start_time
##          3.司机接单，打车时间结束，但乘客未上车，接单后取消了。等车离开时间为结束时间：finish_time

## 所以以用户打车记录表为基准，进行左连接。以order_id是否为空来判断是否有司机接单。

# SELECT city,event_time AS uv_time,1 AS uv FROM tb_get_car_record
# ## 1.无司机接单、超时或乘客取消打车
# SELECT city,end_time AS uv_time,-1 AS uv FROM tb_get_car_record WHERE order_id IS NULL
# ## 2.司机接单，乘客上车，若司机接单后又取消了，没有start_time,finish_time为离开时间，使用IFNULL判断
# SELECT city,IFNULL(start_time,finish_time) AS uv_time,-1 AS uv 
# FROM tb_get_car_record
# LEFT JOIN tb_get_car_order
# USING (order_id)


SELECT city, MAX(uv_cnt) AS max_wait_uv
FROM(
    SELECT city,(SUM(uv) OVER(PARTITION BY city ORDER BY uv_time,uv DESC)) AS uv_cnt
    FROM(
        SELECT city,event_time AS uv_time,1 AS uv FROM tb_get_car_record
        UNION ALL
        SELECT city,end_time AS uv_time,-1 AS uv FROM tb_get_car_record WHERE order_id IS NULL
        UNION ALL
        SELECT city,IFNULL(start_time,finish_time) AS uv_time,-1 AS uv 
        FROM tb_get_car_record
        LEFT JOIN tb_get_car_order
        USING (order_id)
    )t1
    WHERE DATE_FORMAT(uv_time,'%Y-%m') = '2021-10'
)t2
GROUP BY city
ORDER BY max_wait_uv,city