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

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

【场景】：在某个数据的基础上再计算

【分类】：in子查询、where (a,b) in、group by having

分析思路

注意：

1.外键是order_id，不能用uid做连接
2.取消了订单也算接单，不要把问题想的太复杂

关键是理解业务

首先，搞清楚5个时间的业务关系

event_time-打车时间, end_time-打车结束时间, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间

（1）统计2021年国庆7天期间在北京市接单至少3次的司机

• [条件]：date(order_time) between '20211001' and '20211007' and city = '北京'
• [使用]：group by driver_id having count(order_time) >= 3

（2）统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入，结果保留3位小数

• [使用]：sum() ;count()

求解代码

方法一

with子句

with
    main as(
        #统计2021年国庆7天期间在北京市接单至少3次的司机
        select
            driver_id,
            sum(fare) as fare_sum,
            count(order_time) as order_num
        from tb_get_car_order
        join tb_get_car_record using(order_id )
        where date(order_time) between '20211001' and '20211007'
        and city = '北京'
        group by driver_id having count(order_time) >= 3
    )
#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。
select
    '北京' as city,
    round(sum(order_num)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare_sum)/count(distinct driver_id),3) as avg_income
from main

方法二

多表连接

#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。
select
    '北京' as city,
    round(sum(order_num)/count(distinct driver_id),3) as avg_order_num,
    round(sum(fare_sum)/count(distinct driver_id),3) as avg_income
from(
    #统计2021年国庆7天期间在北京市接单至少3次的司机
    select
        driver_id,
        sum(fare) as fare_sum,
        count(order_time) as order_num
    from tb_get_car_order
    join tb_get_car_record using(order_id )
    where date(order_time) between '20211001' and '20211007'
    and city = '北京'
    group by driver_id having count(order_time) >= 3
) main

方法三

in子查询

#统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留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 tb_get_car_order
join tb_get_car_record using(order_id )
where driver_id in(
    #统计2021年国庆7天期间在北京市接单至少3次的司机
    select
        driver_id
    from tb_get_car_order
    join tb_get_car_record using(order_id )
    where date(order_time) between '20211001' and '20211007'
    and city = '北京'
    group by driver_id having count(order_time) >= 3
)
and date(order_time) between '20211001' and '20211007'
and city = '北京'

select 
    t.city
    ,round(count(order_id)/count(distinct driver_id),3) as avg_order
    ,round(sum(fare)/count(distinct driver_id),3) as avg_fare
from 
    (select 
         a.city,b.order_id,b.driver_id,b.order_time,b.fare
         ,count(b.order_id)over(partition by driver_id) as cnt
    from tb_get_car_record a,tb_get_car_order b
        where a.order_id=b.order_id
        and substring(order_time,1,10) between '2021-10-01' and '2021-10-07'
        and a.city='北京'
) t 
where cnt>=3 group by t.city

/*用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；
当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。
若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。
当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。
当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。
问题：请统计2021年国庆7天期间在北京市接单至少3次的司机 用WHERE EXISTS 表示
的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。*/
SELECT   tgcr1.city
	   , ROUND(COUNT(tgco1.order_time)/COUNT(DISTINCT tgco1.driver_id),3) AS avg_order_num
       , ROUND(SUM(tgco1.fare)/COUNT(DISTINCT tgco1.driver_id),3) AS avg_income
  FROM tb_get_car_order AS tgco1 LEFT JOIN tb_get_car_record AS tgcr1
	ON tgco1.order_id=tgcr1.order_id
 WHERE tgcr1.city='北京'
   AND tgco1.order_time BETWEEN '2021-10-01' AND '2021-10-07'
   AND EXISTS(SELECT 1
			    FROM tb_get_car_order AS tgco2 LEFT JOIN tb_get_car_record AS tgcr2
                  ON tgco2.order_id=tgcr2.order_id
			   WHERE tgco1.driver_id=tgco2.driver_id
                 AND tgcr2.city='北京'
				 AND tgco2.order_time BETWEEN '2021-10-01' AND '2021-10-07'
			   GROUP BY tgco2.driver_id
			  HAVING COUNT(tgco2.order_time)>=3)
/*子查询写法更简单
SELECT tmp.city, SUM(tmp.order_cnt)/COUNT(tmp.driver_id), SUM(tmp.fare_tmp)/COUNT(tmp.driver_id)
  FROM (SELECT   tgco1.driver_id, tgcr1.city
	           , COUNT(tgco1.order_time) AS order_cnt
			   , SUM(tgco1.fare) AS fare_tmp
		  FROM tb_get_car_order AS tgco1 LEFT JOIN tb_get_car_record AS tgcr1
			ON tgco1.order_id = tgcr1.order_id
		 WHERE tgco1.order_time BETWEEN '2021-10-01' AND '2021-10-07'
		   AND tgcr1.city='北京'
		 GROUP BY tgco1.driver_id
	    HAVING COUNT(tgco1.order_time)>=3) AS tmp*/

select city,round(avg(num),3) as avg_order_num , round(avg(sum1),3) as avg_income from (
select driver_id,city,count(*) as num ,sum(fare) as sum1
from tb_get_car_record join tb_get_car_order using(order_id)
     where  city='北京' and date(order_time) between '2021-10-1'and '2021-10-7'
group by driver_id ,city

having count(*)>=3  )t
group by city

接单就算

注意条件，北京、国庆 ，接单量>=3

SELECT 
city,
ROUND(avg(order_cnt), 3) AS avg_order_num,
ROUND(SUM(income_cnt) / COUNT(driver_id), 3) AS avg_income
FROM
    (SELECT 
          city,
          driver_id,
          COUNT(order_time) AS order_cnt,
          SUM(fare) AS income_cnt
     FROM tb_get_car_order 
     JOIN tb_get_car_record
     USING(uid)
     WHERE DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07'
     AND city = '北京'
    GROUP BY city, driver_id
    HAVING order_cnt >= 3) AS t
GROUP BY city 

select city,round(avg(total_order),3) avg_order_num,
round(avg(total_fare),3) avg_income
from
(select city,driver_id,count(*) over(partition by driver_id) total_order,
sum(fare) over(partition by driver_id) total_fare
from tb_get_car_record a,tb_get_car_order b
where a.order_id=b.order_id and date(order_time) between '2021-10-01' and '2021-10-07' and city='北京'
)t
where total_order>=3 
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'

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(avg(count_order_id),3) avg_order_num
,round(avg(sum_fare),3) avg_income
from (
    select
    city
    ,count(o.order_id) count_order_id
    ,sum(fare) sum_fare
    from
    tb_get_car_record r join tb_get_car_order o
    on r.order_id=o.order_id
    where
    city="北京"
    and date(order_time) between '2021-10-01' and '2021-10-07'
    group by o.driver_id
    having count(o.order_id)>=3
) t
group by city

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

SELECT # 计算目标值
	city
	,ROUND(SUM(order_cnt) / COUNT(DISTINCT driver_id),3) AS avg_order_num
	,ROUND(SUM(total_fare) / COUNT(DISTINCT driver_id),3) AS avg_income
FROM
	(
	SELECT # 两表拼接 加入city字段
		driver_id   # 分组计算各个司机的order_time数 + 总fare
		,city
		#,order_time
		,count(order_time) as order_cnt
		,SUM(fare) as total_fare
		#,ROUND(count(order_time)/COUNT(DISTINCT driver_id) ,3 ) AS avg_order_num
		#,ROUND(SUM(fare)/COUNT(DISTINCT driver_id) ,3 )AS avg_income
	FROM
		tb_get_car_record as a
	JOIN
		tb_get_car_order as b
	USING (order_id)
	WHERE city = '北京'
		AND order_time BETWEEN '2021-10-01 00:00:00' AND '2021-10-07 23:59:59'
	GROUP BY driver_id
	HAVING
		count(order_time) >=3
	) AS t
GROUP BY city

select k.city city,
round(avg(k.counts),3) avg_order_num,
sum(k.sum_fare)/count(1) avg_income from 
(select r.city city,sum(o.fare) sum_fare,count(o.driver_id) counts
from tb_get_car_record r join tb_get_car_order o
on r.order_id = o.order_id
where date(order_time) between '2021-10-01' and '2021-10-07'
and r.city = '北京'
group by o.driver_id
having count(o.driver_id)>=3) k
group by k.city

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

with t as
(
select 
city
,driver_id
,count(a.order_id) order_num
,sum(fare) income
from tb_get_car_record
join tb_get_car_order a using(order_id)
where date(order_time) between '2021-10-1' and '2021-10-7'
and city='北京'
group by 1,2
having order_num>=3
)
select 
city
,round(avg(order_num),3) avg_order_num
,round(avg(income),3) avg_income
from t
group by 1

select 
city
,round(sum(order_num)/count(driver_id),3) avg_order_num
,round(sum(income)/count(driver_id),3) avg_income
from 
(
select 
city
,driver_id
,count(a.order_id) order_num
,sum(fare) income
from tb_get_car_record
join tb_get_car_order a using(order_id)
where date(order_time) between '2021-10-1' and '2021-10-7'
and city='北京'
group by 1,2
having order_num>=3
) t
group by 1

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

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