ts.var() | Teradata R Package - 17.00 - ts.var() - 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.var() returns the sample variance of values of the column grouped by time.

The variance of a sample is a measure of dispersion from the mean of that sample. It is the square of the sample standard deviation.

  • When there are fewer than two non-NULL data points in the sample used for the computation, ts.var() returns NULL/NA.
  • Nulls are not included in the result computation.
  • Division by zero results in NULL/NA value rather than an error.
  • If data represents only a sample of the entire population for the column, Teradata recommends to use ts.var() to calculate sample variance instead of ts.varp() which calculates population variance. As the sample size increases, the values for ts.var() and ts.varp() approach the same number.
Arguments:
  • value.expression: Specify the column for which sample variance is to be computed.

Use ts.var(distinct(column_name)) to exclude duplicate rows while calculating sample variance.

Example 1: Calculate the sample variance of values in the 'temperature' column of sequenced PTI table

  • Calculate the sample variance.
    > df_seq_var <- df_seq_grp %>% summarise(var_temp = ts.var(temperature))
  • Print the results.
    > df_seq_var %>% arrange(TIMECODE_RANGE, buoyid, var_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, var_temp
      TIMECODE_RANGE                                     `GROUP BY TIME(MINUTES(~ buoyid var_temp
      <chr>                                              <int64>                   <int>    <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:30~ 35345                         0   2670.
    2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:30~ 35347                         1     15.5
    3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:30~ 35349                        44     33.8
    4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:00~ 35350                        22     NA 
    5 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:00~ 35350                        44      0 
    6 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:30~ 35371                         2      1 

Example 2: Calculate the sample variance of values in the 'temperature' column of non-PTI table

  • Calculate the sample variance.
    > df_nonpti_var <- df_nonpti %>% group_by_time(timebucket.duration = "10m", timecode.column = "TIMECODE") %>% summarise(var_temp = ts.var(temperature))
  • Print the results.
    > df_nonpti_var %>% arrange(TIMECODE_RANGE, var_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, var_temp
      TIMECODE_RANGE                                           `GROUP BY TIME(MINUTES(1~ var_temp
      <chr>                                                    <int64>                      <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:10:00.00~ 2314993                     3960.
    2 2014-01-06 08:10:00.000000+00:00,2014-01-06 08:20:00.00~ 2314994                     4050 
    3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:10:00.00~ 2314999                       15.5
    4 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:10:00.00~ 2315005                       33.2
    5 2014-01-06 10:10:00.000000+00:00,2014-01-06 10:20:00.00~ 2315006                       NA 
    6 2014-01-06 10:30:00.000000+00:00,2014-01-06 10:40:00.00~ 2315008                       NA 
    7 2014-01-06 10:50:00.000000+00:00,2014-01-06 11:00:00.00~ 2315010                       NA 
    8 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:10:00.00~ 2315071                        1