ts.sdp() | Teradata R Package - 17.00 - ts.sdp() - 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.sdp() returns the population 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.sdp returns NULL/NA.
  • Nulls are not included in the result computation.
  • ts.sdp() can only be used if data represents entire population. Otherwise, Teradata recommends to use ts.sd() to calculate sample standard deviation.
Arguments:
  • value.expression: Specify the column for which population standard deviation is to be computed.

Use ts.sdp(distinct(column_name)) to exclude duplicate rows while calculating population standard deviation.

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

  • Calculate the population standard deviation.
    > df_seq_sdp <- df_seq_grp %>% summarise(sdp_temp = ts.sdp(temperature))
  • Print the results.
    > df_seq_sdp %>% arrange(TIMECODE_RANGE, buoyid, sdp_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, sdp_temp
      TIMECODE_RANGE                                     `GROUP BY TIME(MINUTES(~ buoyid sdp_temp
      <chr>                                              <int64>                   <int>    <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:30~ 35345                         0   44.8 
    2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:30~ 35347                         1    3.59
    3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:30~ 35349                        44    5.54
    4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:00~ 35350                        22    0   
    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    0.816

Example 2: Calculate the population standard deviation of the 'temperature' column of non-PTI table

  • Calculate the population standard deviation.
    > df_nonpti_sdp <- df_nonpti %>% group_by_time(timebucket.duration = "10m", timecode.column = "TIMECODE") %>% summarise(sdp_temp = ts.sdp(temperature))
  • Print the results.
    > df_nonpti_sdp %>% arrange(TIMECODE_RANGE, sdp_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, sdp_temp
      TIMECODE_RANGE                                           `GROUP BY TIME(MINUTES(1~ sdp_temp
      <chr>                                                    <int64>                      <dbl>
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:10:00.00~ 2314993                     44.5 
    2 2014-01-06 08:10:00.000000+00:00,2014-01-06 08:20:00.00~ 2314994                     45   
    3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:10:00.00~ 2314999                      3.59
    4 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:10:00.00~ 2315005                      5.46
    5 2014-01-06 10:10:00.000000+00:00,2014-01-06 10:20:00.00~ 2315006                      0   
    6 2014-01-06 10:30:00.000000+00:00,2014-01-06 10:40:00.00~ 2315008                      0   
    7 2014-01-06 10:50:00.000000+00:00,2014-01-06 11:00:00.00~ 2315010                      0   
    8 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:10:00.00~ 2315071                      0.816