ts.n() | Teradata R Package - 17.00 - ts.n() - 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.n() returns the total number of qualified rows in the column grouped by time. ts.n uses the argument "value.expression" to specify the column for which count is to be computed. The function returns the number of rows per group when the argument takes *, which is the default value of the function. Use ts.n(distinct(column_name)) to exclude duplicate rows while calculating the number of rows.

  • Nulls are not included in the result computation.
  • ts.n() is valid on all columns with any data type.
Arguments:
  • value.expression: Specify the column for which count is to be computed.

The function returns the number of rows per group when the argument takes *, which is the default value of the function.

Use ts.n(distinct(column_name)) to exclude duplicate rows while calculating the number of rows.

Example 1: Calculate the number of row in the 'temperature' column of sequenced PTI table

  • Calculate the number of rows.
    > df_seq_count <- df_seq_grp %>% summarise(count_temp = ts.n(temperature))
  • Print the results.
    > df_seq_count %>% arrange(TIMECODE_RANGE, buoyid, count_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, count_temp
      TIMECODE_RANGE                                   `GROUP BY TIME(MINUTES(~ buoyid count_temp
      <chr>                                            <int64>                   <int> <int64>  
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:~ 35345                         0  4       
    2 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:~ 35347                         1  6       
    3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:~ 35349                        44 11       
    4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350                        22  1       
    5 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350                        44  2       
    6 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:~ 35371                         2  3       

Example 2: Calculate the number of rows in the 'temperature' column of non-PTI table

  • Calculate the number of rows.
    > df_nonpti_count <- df_nonpti %>% group_by_time(timebucket.duration = "10m", timecode.column = "TIMECODE") %>% summarise(count_temp = ts.n(temperature))
  • Print the results.
    > df_nonpti_count %>% arrange(TIMECODE_RANGE, count_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, count_temp
      TIMECODE_RANGE                                         `GROUP BY TIME(MINUTES(1~ count_temp
      <chr>                                                  <int64>                   <int64>  
    1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:10:00.~ 2314993                    2       
    2 2014-01-06 08:10:00.000000+00:00,2014-01-06 08:20:00.~ 2314994                    2       
    3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:10:00.~ 2314999                    6       
    4 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:10:00.~ 2315005                   10       
    5 2014-01-06 10:10:00.000000+00:00,2014-01-06 10:20:00.~ 2315006                    1       
    6 2014-01-06 10:30:00.000000+00:00,2014-01-06 10:40:00.~ 2315008                    1       
    7 2014-01-06 10:50:00.000000+00:00,2014-01-06 11:00:00.~ 2315010                    1       
    8 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:10:00.~ 2315071                    3