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