Mysql 解法, 执行用时: 36ms, 内存消耗: 6500KB, 提交时间: 2022-01-25

SELECT "北京" as city, ROUND(AVG(order_num), 3) as avg_order_num,
    ROUND(AVG(income), 3) as avg_income
FROM (
    SELECT driver_id, COUNT(order_id) as order_num, SUM(fare) as income
    FROM tb_get_car_order
    JOIN tb_get_car_record USING(order_id)
    WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007'
    GROUP BY driver_id
    HAVING COUNT(order_id) >= 3
) as t_driver_info;


       

Mysql 解法, 执行用时: 37ms, 内存消耗: 6320KB, 提交时间: 2022-01-24

# select a.city as city,b.driver_id as driver_id,count(1) as order_num,sum(b.fare) as income from 
# tb_get_car_record as a inner join tb_get_car_order as b 
# on a.order_id = b.order_id 
# where date(b.order_time) between '2021-10-01' and '2021-10-07' and city = '北京'
# group by b.driver_id 
# having order_num>=3;

select a.city,round(sum(a.order_num)/count(a.order_num),3) as avg_order_num,
round(sum(a.income)/count(a.income),3) as avg_income from 
(select a.city as city,b.driver_id as driver_id,count(1) as order_num,sum(b.fare) as income from 
tb_get_car_record as a inner join tb_get_car_order as b 
on a.order_id = b.order_id 
where date(b.order_time) between '2021-10-01' and '2021-10-07' and city = '北京'
group by b.driver_id 
having order_num>=3) as a 
group by a.city;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6388KB, 提交时间: 2022-02-09

SELECT city, round(avg(cnt), 3) as avg_order_num,
    round(avg(sum_fare), 3) as avg_income
FROM (
    SELECT city, driver_id, count(t1.order_id) as cnt,
        sum(fare) as sum_fare
    FROM tb_get_car_order as t1 JOIN tb_get_car_record as t2
    ON t1.order_id = t2.order_id
    WHERE date(order_time) between '2021-10-01' and '2021-10-07'
    AND city = '北京'
    GROUP BY city, driver_id
    HAVING cnt >= 3
) as t
GROUP BY city

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-01-25

SELECT
    city,
    ROUND(AVG(order_cnt), 3) AS avg_order_num,
    ROUND(SUM(income_amount) / COUNT(driver_id), 3) AS avg_income
FROM
(SELECT
    city,
    driver_id,
    COUNT(t1.order_id) AS order_cnt,
    SUM(fare) AS income_amount
FROM tb_get_car_order AS t1
JOIN tb_get_car_record AS t2
ON t1.order_id = t2.order_id
WHERE
    DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07'
    AND
    city = '北京'
GROUP BY city, driver_id
HAVING order_cnt >= 3) AS t3
GROUP BY city;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2021-12-18

select max(city), 
       round(avg(order_num), 3) as avg_order_num,
       round(avg(total_fare), 3) as avg_income
from (select city, driver_id, count(tb_get_car_order.order_id) as order_num, sum(fare) as total_fare
      from tb_get_car_record inner join tb_get_car_order
      on tb_get_car_record.order_id = tb_get_car_order.order_id
      where city = "北京" and date(event_time) between date("2021-10-01") and date("2021-10-07")
      group by city, driver_id
      having count(tb_get_car_order.order_id) >= 3) as t1