2021年国庆在北京接单3次及以上的司机统计信息

with order_detail as(
    select t1.order_id,t1.uid,t1.driver_id,t1.order_time,t1.fare,
    t2.city
    from tb_get_car_order t1 left join tb_get_car_record t2
    on t1.order_id = t2.order_id
)
,driver_list as(
select driver_id
from order_detail
where city = '北京'
and date(order_time) between '2021-10-01' and '2021-10-07'
group by driver_id
having count(order_id) >= 3)

select 
    city,
    round(count(order_id)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare)/count(distinct driver_id),3) as avg_income
from order_detail
where driver_id in (select driver_id from driver_list)
and date(order_time) between '2021-10-01' and '2021-10-07'
and city = '北京'
group by city

select city,
        round(count(order_id)/count(distinct(driver_id)) ,3) as avg_order_num,
        sum(fare)/count(distinct(driver_id)) as avg_income
from tb_get_car_order
left join tb_get_car_record using(order_id)
where city='北京'  and driver_id in (select driver_id
                                    from tb_get_car_order
                                    group by driver_id
                                    having count(order_id)>=3)
        and  DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07'

with
t as( #总表
select city, driver_id as did, order_time, fare
from tb_get_car_record as t1
inner join tb_get_car_order as t2 using(order_id) 
where city='北京' and date(order_time) between '2021-10-01' and '2021-10-07'
),
t1 as ( # 接单数大于2次的司机和收入
select city, did, count(order_time) as cnt,  sum(fare) as income
from t
group by city, did
having cnt>2
)

select city, 
    round(avg(cnt), 3), 
    round(sum(income)/count(distinct did), 3)
from t1
group by city

SELECT
city,
	ROUND(COUNT(tbo.order_id)/COUNT(DISTINCT driver_id),3) avg_order_num,
	ROUND(SUM(fare)/COUNT(DISTINCT driver_id),3) avg_income
	FROM tb_get_car_order tbo JOIN tb_get_car_record tbr ON tbo.`order_id`=tbr.`order_id`
WHERE city='北京'  AND DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07' AND driver_id IN (
SELECT driver_id FROM tb_get_car_order tbo JOIN tb_get_car_record tbr USING(order_id) WHERE DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07'
AND city='北京'
GROUP BY driver_id HAVING COUNT(order_id)>=3 
) GROUP BY city;

select city
    ,round(avg(order_num),3) avg_order_num
    ,avg(sum_fare) avg_income
from(
    select city
        ,driver_id
        ,sum(fare) sum_fare
        ,count(order_time) order_num
    from tb_get_car_record
    left join tb_get_car_order
    using(order_id)
    where city = '北京'
    and order_time between '2021-10-01' and '2021-10-07'
    group by city
        ,driver_id
    having count(order_time) >= 3
) a
group by city

select distinct
    city,
    round(avg(count(order_time)) over(),3) avg_order_num,
    round(avg(sum(fare)) over(),3) avg_income
from tb_get_car_record t1
join tb_get_car_order 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 count(order_time) >= 3;

select city,round(sum(order_cnt)/count(driver_id),3) as avg_order_num,round(sum(fare_sum)/count(driver_id),3) as avg_income
from(
    select city,driver_id,count(order_id) as order_cnt,sum(fare) as fare_sum
    from(
        select
            b.city, -- 城市
            a.order_id, -- 订单号
            a.driver_id, -- 司机
            date(a.order_time) as order_date,
            a.fare -- 费用
        from
            tb_get_car_order a
            left join tb_get_car_record b on a.order_id = b.order_id
        where b.city='北京'
    ) t
    where order_date between '2021-10-01' and '2021-10-07'
    group by 1,2
    having order_cnt>=3
) a
group by 1

with cte as(
select driver_id,count(order_id) as num,sum(fare) as income 
from tb_get_car_order a left join tb_get_car_record b using(order_id)
where city='北京' and date(order_time) between '2021-10-01' and  '2021-10-07'   # 如果中途取消的不算，则只需将 date(order_time) 改为date(start_time)
group by driver_id
having num>=3   )
select '北京' as city,round(avg(num),3) as avg_order_num,round(avg(income),3) as avg_income
from cte  

select city, round(sum(num)/count(*),3) as avg_order_num,round(avg(s),3) as avg_income
from(
    select city, driver_id,count(order_time) as num, sum(fare) as s
 from tb_get_car_record u inner join tb_get_car_order d on u.order_id=d.order_id where order_time between '2021-10-01' and '2021-10-07' and u.city='北京'
 group by city,driver_id
) c where num>=3 group by city;

select 
    city, 
    ROUND(AVG(order_num), 3) as avg_order_num,
    ROUND(AVG(income), 3) as avg_income
from (
select
city,
driver_id,
row_number() over(partition by driver_id order by  t.order_time desc) as rn,
count(t.order_id) over(partition by driver_id  ) as order_num,
sum(fare) over(partition by driver_id  ) as income
from tb_get_car_order t 
inner join tb_get_car_record p 
on t.order_id=p.order_id 
and p.city='北京'
where datediff('2021-10-07',date(t.order_time)) between 0 and 6
) tt 
where order_num>=3 and rn=1

select city,round(avg(order_count),3) as avg_order_num,
round(avg(sum_fare),3) as avg_income
from(select city,count(order_time) as order_count,sum(fare) as sum_fare
from tb_get_car_record
join tb_get_car_order using(order_id)
where date_format(order_time,'%Y-%m-%d') between '2021-10-01' and '2021-10-07' and city="北京"
group by driver_id)t0
where sum_fare is not null and order_count>=3
group by city

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