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

Teradata® Package for R User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
ft:locale
en-US
ft:lastEdition
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
lifecycle
latest
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