# 日志系列--行车轨迹日志的统计分析

+关注继续查看

## 简介

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

从结果中可以看出，上午上班时间，以及晚上下班后，是乘车需求最旺盛的时候，出租车公司可以相应的调度更多的车辆。

1. 分时段平均乘车里程

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

1. 分时段平均乘车分钟数,单位里程需要的秒数，看看哪些时段比较堵

*| 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 time order by time limit 24

一些时刻特别堵，需要准备更多车辆来应对需求。

2. 分时段平均乘车费用，看看哪些时间赚的多

*| select  avg(total_amount)  as dollars,
(tpep_pickup_time %(24*3600)/3600+8)%24 as time
group by time order by time limit 24

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

3. 看看账单范围分布情况

*| select case when total_amount < 1 then 'bill_0_1'
when total_amount < 10 then 'bill_1_10' 
4. total_amount < 30 then 'bill_20_30'
5. total_amount < 40 then 'bill_30_40'
6. total_amount < 50 then 'bill_10_50'
7. total_amount < 100 then 'bill_50_100'
8. total_amount < 1000 then 'bill_100_1000'
9. 'bill_1000_' end
as bill_level , count(1) as count group by
bill_level
order by count desc


![bill_range.png](http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/272f5a5a89f580e587355a12175a8a88.png)
从成交金额的成交区间，可以看出大部分的成交金额在1到20(美元)之间。

dashboard链接

## 试用日志服务

dashboard链接

4406 0

<div id="content" class="bigfont mycontent" style="zoom:1; line-height:23.8px; margin:18px 28px; font-family:'lucida Grande',Verdana,'Microsoft YaHei'; clear:both; min-height:200px; height:auto!im
1771 0

1612 0

306 0

1188 0
PostgreSQL · 特性分析 · 统计信息计算方法

1764 0
+关注

104

2

+ 订阅

JS零基础入门教程（上册）