【数据分析】派单排序策略优化验证（附sql查询代码）

SELECT cityname, avg(移动时光),avg(移动距离),avg(服务时光), count(t1.orderid)
FROM(
	SELECT orderid, taskstatus, cityname,TIMESTAMPDIFF(second,start_time,end_time) AS 移动时光,distance as 移动距离, TIMESTAMPDIFF(second,accept_time,order_end_time)as 服务时光
	FROM 表
	WHERE orderstatus =定单完成状况 and taskstatus = 服务完成状况 ) t1
JOIN(
        #目标是找到仅一个服务人员就完成的定单，消除异常状况
	SELECT *
	FROM(
		SELECT orderid, COUNT(taskstatus) as only_one_task
		FROM 表
		WHERE orderstatus =定单完成状况
		GROUP BY orderid) t
	WHERE only_one_task = 1) t2SELECT cityname, avg(移动时光),avg(移动距离),avg(服务时光), c