ts.median() | Teradata R Package - 17.00 - ts.median() - Teradata R Package

Teradata® R Package User Guide

prodname
Teradata R Package
vrm_release
17.00
created_date
November 2020
category
User Guide
featnum
B700-4005-090K

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