The aggregate function ts.mean() returns the average value in the column grouped by time.
Nulls are not included in the result computation.
Arguments:
- value.expression: Specify the column for which average value is to be computed.
Use ts.mean(distinct(column_name)) to exclude duplicate rows while calculating average.
Example 1: Calculate the average value in the 'temperature' column of sequenced PTI table
- Calculate the average value.
> df_seq_avg <- df_seq_grp %>% summarise(avg_temp = ts.mean(temperature))
- Print the results.
> df_seq_avg %>% arrange(TIMECODE_RANGE, buoyid, avg_temp) # Source: lazy query [?? x 4] # Database: [Teradata 16.20.50.01] [Teradata Native Driver 17.0.0.2] # [TDAPUSER@<hostname>/TDAPUSERDB] # Ordered by: TIMECODE_RANGE, buoyid, avg_temp TIMECODE_RANGE `GROUP BY TIME(MINUTES(~ buoyid avg_temp <chr> <int64> <int> <dbl> 1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:30~ 35345 0 54.8 2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:30~ 35347 1 74.5 3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:30~ 35349 44 49 4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:00~ 35350 22 23 5 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:00~ 35350 44 43 6 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:30~ 35371 2 81
Example 2: Calculate the average value in the 'temperature' column of non-PTI table
- Calculate the average value.
> df_nonpti_avg <- df_nonpti %>% group_by_time(timebucket.duration = "10m", timecode.column = "TIMECODE") %>% summarise(avg_temp = ts.mean(temperature))
- Print the results.
> df_nonpti_avg %>% arrange(TIMECODE_RANGE, avg_temp) # Source: lazy query [?? x 3] # Database: [Teradata 16.20.50.01] [Teradata Native Driver 17.0.0.2] # [TDAPUSER@<hostname>/TDAPUSERDB] # Ordered by: TIMECODE_RANGE, avg_temp TIMECODE_RANGE `GROUP BY TIME(MINUTES(1~ avg_temp <chr> <int64> <dbl> 1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:10:00.00~ 2314993 54.5 2 2014-01-06 08:10:00.000000+00:00,2014-01-06 08:20:00.00~ 2314994 55 3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:10:00.00~ 2314999 74.5 4 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:10:00.00~ 2315005 49.6 5 2014-01-06 10:10:00.000000+00:00,2014-01-06 10:20:00.00~ 2315006 43 6 2014-01-06 10:30:00.000000+00:00,2014-01-06 10:40:00.00~ 2315008 43 7 2014-01-06 10:50:00.000000+00:00,2014-01-06 11:00:00.00~ 2315010 43 8 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:10:00.00~ 2315071 81