ts.n() | Teradata Package for R - ts.n() - Teradata Package for R

Teradata® Package for R User Guide

Product
Teradata Package for R
Release Number
17.00
Published
July 2021
Language
English (United States)
Last Update
2023-08-08
dita:mapPath
yih1585763700215.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
B700-4005
Product Category
Teradata Vantage

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