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

The standard deviation is the second moment of a distribution.

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

Use ts.sd(distinct(column_name)) to exclude duplicate rows while calculating sample standard deviation.

Example 1: Calculate the sample standard deviation of the 'temperature' column of sequenced PTI table

  • Calculate the sample standard deviation.
    > df_seq_sd <- df_seq_grp %>% summarise(sd_temp = ts.sd(temperature))
  • Print the results.
    > df_seq_sd %>% arrange(TIMECODE_RANGE, buoyid, sd_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, sd_temp
      TIMECODE_RANGE                                      `GROUP BY TIME(MINUTES(~ buoyid sd_temp
      <chr>                                               <int64>                   <int>   <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:30:~ 35345                         0   51.7
    2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:30:~ 35347                         1    3.94
    3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:30:~ 35349                        44    5.81
    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 standard deviation of the 'temperature' column of non-PTI table

  • Calculate the sample standard deviation.
    > df_nonpti_sd <- df_nonpti %>% group_by_time(timebucket.duration = "10m", timecode.column = "TIMECODE") %>% summarise(sd_temp = ts.sd(temperature))
  • Print the results.
    > df_nonpti_sd %>% arrange(TIMECODE_RANGE, sd_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, sd_temp
      TIMECODE_RANGE                                            `GROUP BY TIME(MINUTES(1~ sd_temp
      <chr>                                                     <int64>                     <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:10:00.000~ 2314993                     62.9
    2 2014-01-06 08:10:00.000000+00:00,2014-01-06 08:20:00.000~ 2314994                     63.6
    3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:10:00.000~ 2314999                      3.94
    4 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:10:00.000~ 2315005                      5.76
    5 2014-01-06 10:10:00.000000+00:00,2014-01-06 10:20:00.000~ 2315006                     NA  
    6 2014-01-06 10:30:00.000000+00:00,2014-01-06 10:40:00.000~ 2315008                     NA  
    7 2014-01-06 10:50:00.000000+00:00,2014-01-06 11:00:00.000~ 2315010                     NA  
    8 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:10:00.000~ 2315071                      1