The aggregate function ts.median() returns the median of all values in each group. The function returns the average of the two middle values if the argument "value.expression" contains an even number of values.
- This function is valid only on columns with numeric types.
- Nulls are not included in the result computation.
Arguments:
- value.expression: Specifies the column for which median is to be computed.
- use.distinct: Specifies whether to exclude duplicates specified by "value.expression" from the computation.
Default value is FALSE. By default, all non-null values in the column specified in "value.expression", including duplicates, are considered in the computation.
If use.distinct = TRUE, then all duplicate values are excluded in the computation.
Example 1: Calculate the median of the 'temperature' column of sequenced PTI table
- Calculate the median.
> df_seq_median <- df_seq_grp %>% summarise(median_temp = ts.median(temperature))
- Print the results.
> df_seq_median %>% arrange(TIMECODE_RANGE, buoyid, median_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, median_temp TIMECODE_RANGE `GROUP BY TIME(MINUTES~ buoyid median_temp <chr> <int64> <int> <dbl> 1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:~ 35345 0 54.5 2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:~ 35347 1 74.5 3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:~ 35349 44 53 4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350 22 23 5 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350 44 43 6 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:~ 35371 2 81
Example 2: Calculate the median of the 'temperature' column of non-PTI table
- Calculate the median.
> df_nonpti_median <- df_nonpti_grp %>% summarise(median_temp = ts.median(temperature))
- Print the results.
> df_nonpti_median %>% arrange(TIMECODE_RANGE, median_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, median_temp TIMECODE_RANGE `GROUP BY TIME(MINUTES(~ median_temp <chr> <int64> <dbl> 1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:01:00~ 23149921 10 2 2014-01-06 08:01:00.000000+00:00,2014-01-06 08:02:00~ 23149922 10 3 2014-01-06 08:02:00.000000+00:00,2014-01-06 08:03:00~ 23149923 10 4 2014-01-06 08:03:00.000000+00:00,2014-01-06 08:04:00~ 23149924 10 5 2014-01-06 08:04:00.000000+00:00,2014-01-06 08:05:00~ 23149925 10 6 2014-01-06 08:05:00.000000+00:00,2014-01-06 08:06:00~ 23149926 10 7 2014-01-06 08:06:00.000000+00:00,2014-01-06 08:07:00~ 23149927 10 8 2014-01-06 08:07:00.000000+00:00,2014-01-06 08:08:00~ 23149928 10 9 2014-01-06 08:08:00.000000+00:00,2014-01-06 08:09:00~ 23149929 NA 10 2014-01-06 08:09:00.000000+00:00,2014-01-06 08:10:00~ 23149930 99 # ... with more rows