Now that we are familiar with the data set, we can use Teradata capabilities to quickly analyse the data set. First, let's identify how many passengers are being picked up by hour in the month of November.
SELECT TOP 10
$TD_TIMECODE_RANGE
,begin($TD_TIMECODE_RANGE) time_bucket_start
,sum(passenger_count) passenger_count
FROM trip
WHERE extract(month from pickup_datetime)=11
GROUP BY TIME(HOURS(1))
USING TIMECODE(pickup_datetime)
ORDER BY 1;
Result:
TIMECODE_RANGE time_bucket_start passenger_count
--------------------------------------------------------- --------------------------------- ---------------
(2014-11-25 00:00:00.000000, 2014-11-25 01:00:00.000000) 2014-11-25 00:00:00+00:00 1105
(2014-11-25 01:00:00.000000, 2014-11-25 02:00:00.000000) 2014-11-25 01:00:00+00:00 727
(2014-11-25 02:00:00.000000, 2014-11-25 03:00:00.000000) 2014-11-25 02:00:00+00:00 486
(2014-11-25 03:00:00.000000, 2014-11-25 04:00:00.000000) 2014-11-25 03:00:00+00:00 333
(2014-11-25 04:00:00.000000, 2014-11-25 05:00:00.000000) 2014-11-25 04:00:00+00:00 355
(2014-11-25 05:00:00.000000, 2014-11-25 06:00:00.000000) 2014-11-25 05:00:00+00:00 455
(2014-11-25 06:00:00.000000, 2014-11-25 07:00:00.000000) 2014-11-25 06:00:00+00:00 1055
(2014-11-25 07:00:00.000000, 2014-11-25 08:00:00.000000) 2014-11-25 07:00:00+00:00 2361
(2014-11-25 08:00:00.000000, 2014-11-25 09:00:00.000000) 2014-11-25 08:00:00+00:00 2477
(2014-11-25 09:00:00.000000, 2014-11-25 10:00:00.000000) 2014-11-25 09:00:00+00:00 2668
Yes, this can also be achieved by extracting the hour from time and then aggregating - it's additional code/work, but can be done without timeseries specific functionality.
But, now let's go a step further to identify how many passengers are being picked up and what is the average trip duration by vendor every 15 minutes in November.
SELECT TOP 10
$TD_TIMECODE_RANGE,
vendor_id,
SUM(passenger_count),
AVG((dropoff_datetime - pickup_datetime ) MINUTE (4)) AS avg_trip_time_in_mins
FROM trip
GROUP BY TIME (MINUTES(15) AND vendor_id)
USING TIMECODE(pickup_datetime)
WHERE EXTRACT(MONTH FROM pickup_datetime)=11
ORDER BY 1,2;
Result:
TIMECODE_RANGE vendor_id SUM(passenger_count) avg_trip_time_in_mins
--------------------------------------------------------- ---------- -------------------- ---------------------
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) CityLift 54 14
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) CruiseOn 45 11
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) DriveEasy 21 12
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) GoMoto 29 12
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) RideWise 28 11
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) StarRider 14 11
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) SwiftGo 44 9
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) VeloCab 38 14
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) ZippyRide 18 11
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) ZoomTransit 30 15
This is the power of Teradata time series functionality. Without needing complicated, cumbersome logic we are able to find average trip duration by vendor every 15 minutes just by modifying the group by time clause. Let's now look at how simple it is to build moving averages based on this. First, let's start by creating a view as below.
REPLACE VIEW NYC_taxi_trip_ts as
SELECT
$TD_TIMECODE_RANGE time_bucket_per
,vendor_id
,sum(passenger_count) passenger_cnt
,avg(CAST((dropoff_datetime - pickup_datetime MINUTE(4) ) AS INTEGER)) avg_trip_time_in_mins
FROM trip
GROUP BY TIME (MINUTES(15) and vendor_id)
USING TIMECODE(pickup_datetime)
WHERE extract(month from pickup_datetime)=11
Let's calculate a 2 hours moving average on our 15-minutes time series. 2 hour is 8 * 15 minutes periods.
SELECT * FROM MovingAverage (
ON NYC_taxi_trip_ts PARTITION BY vendor_id ORDER BY time_bucket_per
USING
MAvgType ('S')
WindowSize (8)
TargetColumns ('passenger_cnt')
) AS dt
WHERE begin(time_bucket_per)(date) = '2014-11-25'
ORDER BY vendor_id, time_bucket_per;
Result:
time_bucket_per vendor_id passenger_cnt avg_trip_time_in_mins passenger_cnt_smavg
--------------------------------------------------------- ---------- ------------- --------------------- -------------------
(2014-11-25 00:00:00.000000, 2014-11-25 00:15:00.000000) CityLift 116 15.47826086956522 116.0
(2014-11-25 00:15:00.000000, 2014-11-25 00:30:00.000000) CityLift 118 13.93877551020408 117.0
(2014-11-25 00:30:00.000000, 2014-11-25 00:45:00.000000) CityLift 107 13.19607843137255 113.66666666666667
(2014-11-25 00:45:00.000000, 2014-11-25 01:00:00.000000) CityLift 97 15.384615384615385 109.5
(2014-11-25 01:00:00.000000, 2014-11-25 01:15:00.000000) CityLift 80 12.0 103.6
(2014-11-25 01:15:00.000000, 2014-11-25 01:30:00.000000) CityLift 64 14.36170212765957 97.0
(2014-11-25 01:30:00.000000, 2014-11-25 01:45:00.000000) CityLift 46 14.02857142857143 89.71428571428571
(2014-11-25 01:45:00.000000, 2014-11-25 02:00:00.000000) CityLift 85 15.421052631578948 89.625
(2014-11-25 02:00:00.000000, 2014-11-25 02:15:00.000000) CityLift 48 13.208333333333334 80.625
(2014-11-25 02:15:00.000000, 2014-11-25 02:30:00.000000) CityLift 40 14.047619047619047 70.875
(2014-11-25 02:30:00.000000, 2014-11-25 02:45:00.000000) CityLift 33 11.969696969696969 61.625
(2014-11-25 02:45:00.000000, 2014-11-25 03:00:00.000000) CityLift 43 10.25 54.875
(2014-11-25 03:00:00.000000, 2014-11-25 03:15:00.000000) CityLift 41 14.0 47.5
(2014-11-25 03:15:00.000000, 2014-11-25 03:30:00.000000) CityLift 29 11.137931034482758 45.625
(2014-11-25 03:30:00.000000, 2014-11-25 03:45:00.000000) CityLift 24 12.333333333333334 42.875
(2014-11-25 03:45:00.000000, 2014-11-25 04:00:00.000000) CityLift 16 12.5625 39.25
(2014-11-25 04:00:00.000000, 2014-11-25 04:15:00.000000) CityLift 24 15.5 37.0
(2014-11-25 04:15:00.000000, 2014-11-25 04:30:00.000000) CityLift 33 13.818181818181818 35.25
(2014-11-25 04:30:00.000000, 2014-11-25 04:45:00.000000) CityLift 24 15.0 33.0
(2014-11-25 04:45:00.000000, 2014-11-25 05:00:00.000000) CityLift 17 13.0 26.0
Note
In addition to above time series operations, Teradata also provides special time series tables with Primary Time Index (PTI). These are regular Teradata tables with PTI defined rather than a Primary Index (PI). Though tables with PTI are not mandatory for time series functionality/operations, PTI optimizes how the time series data is stored physically and hence improves performance considerably compared to regular tables.