出租车公司记录了每一次载客交易发生的信息细节,包括上下客时间、经纬度、路程距离、支付方式、支付金额、缴税额等信息。详细的数据,为出租车公司的运营提供了极大的帮助,例如,了解哪些时间段比较热门,对应增加运行车次;哪些地区需求比较广泛,调度更多车辆前往。这些数据,使得乘客的需求得到了及时的响应,而驾驶员的收入也得到了提高,进而整个社会的效率得到了提高。

出租车公司把载客日志保存在阿里云日志服务上,利用日志服务可靠的存储,以及快速统计计算,挖掘日志中有用信息。本文将展示出租车公司如何使用阿里云日志服务来挖掘数据中的信息。

数据样例:

RatecodeID:  1VendorID:  2__source__:  11.164.232.105    __topic__:  dropoff_latitude:  40.743995666503906    dropoff_longitude:  -73.983505249023437extra:  0    fare_amount:  9    improvement_surcharge:  0.3    mta_tax:  0.5    passenger_count:  2    payment_type:  1    pickup_latitude:  40.761466979980469    pickup_longitude:  -73.96246337890625    store_and_fwd_flag:  N    tip_amount:  1.96    tolls_amount:  0    total_amount:  11.76    tpep_dropoff_datetime:  2016-02-14 11:03:13    tpep_dropoff_time:  1455418993    tpep_pickup_datetime:  2016-02-14 10:53:57    tpep_pickup_time:  1455418437    trip_distance:  2.02
		


常见的统计

要对数据进行查询和分析,请先开启并配置索引

  1. 分时段乘车人次,查看哪些时段比较热门
     *| select count(1) as deals, sum(passenger_count) as passengers,    
     (tpep_pickup_time %(24*3600)/3600+8)%24 as time        
     group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24
    					


    从结果中可以看出,上午上班时间,以及晚上下班后,是乘车需求最旺盛的时候,出租车公司可以相应的调度更多的车辆。
    					
  2. 分时段平均乘车里程
    *| select  avg(trip_distance)  as trip_distance, 
    (tpep_pickup_time %(24*3600)/3600+8)%24 as time         
    group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24
    					


    某些时刻,对乘车里程的需求也挺旺盛,出租车公司在对应的时候也需要准备更多的车辆。

  3. 分时段平均乘车分钟数,单位里程需要的秒数,看看哪些时段比较堵
    *| select  avg(tpep_dropoff_time-tpep_pickup_time)/60  as driving_minutes, 
    (tpep_pickup_time %(24*3600)/3600+8)%24 as time  
    group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24
    					


    *| select  sum(tpep_dropoff_time-tpep_pickup_time)/sum(trip_distance)  as driving_minutes, 
    (tpep_pickup_time %(24*3600)/3600+8)%24 as time        
    group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24
    					


    一些时刻特别堵,需要准备更多车辆来应对需求。
  4. 分时段平均乘车费用,看看哪些时间赚的多
    *| select  avg(total_amount)  as dollars, 
    (tpep_pickup_time %(24*3600)/3600+8)%24 as time 
    group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24
    					


    凌晨4点钟的客单价比较高,有经济压力的驾驶员可以选择在这个时候提供服务。

  5. 看看账单范围分布情况
    *| select case when total_amount < 1 then 'bill_0_1'  
    when total_amount < 10 then 'bill_1_10' 
    when total_amount < 20 then 'bill_10_20' 
    when total_amount < 30 then 'bill_20_30' 
    when total_amount < 40 then 'bill_30_40' 
    when total_amount < 50 then 'bill_10_50' 
    when total_amount < 100 then 'bill_50_100' 
    when total_amount < 1000 then 'bill_100_1000' 
    else 'bill_1000_'  end 
    as bill_level , count(1) as count group by 
    case when total_amount < 1 then 'bill_0_1'
    when total_amount < 10 then 'bill_1_10' 
    when total_amount < 20 then 'bill_10_20' 
    when total_amount < 30 then 'bill_20_30' 
    when total_amount < 40 then 'bill_30_40' 
    when total_amount < 50 then 'bill_10_50' 
    when total_amount < 100 then 'bill_50_100' 
    when total_amount < 1000 then 'bill_100_1000' 
    else 'bill_1000_'  end 
    order by count desc 
    
    					


    从成交金额的成交区间,可以看出大部分的成交金额在1到20美元之间。