ts.top() | Teradata Package for R - ts.top() - 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
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
Product Category
Teradata Vantage

The aggregate function ts.top() returns the largest number of values in the argument "value.expression" for each group, with or without ties.

  • This function is valid only on columns with numeric types.
  • Nulls are not included in the result computation.
Arguments:
  • value.expression: Specifies the column from which top values are to be returned.
  • number.of.values: Specifies the integer value representing the number of values to return.
  • with.ties: Specifies a flag to decide whether to run top function with ties or not.

    Default value is FALSE.

Example 1: Get the largest 2 values without ties of a sequenced PTI table

This example gets the largest 2 values of the 'temperature' column for each group without ties of sequenced PTI table.

  • Retrieve the largest values.
    > df_seq_top <- df_seq_grp %>% summarise(top_temp = ts.top(temperature, 2))
  • Print the results.
    > df_seq_top %>% arrange(TIMECODE_RANGE, buoyid, top_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, top_temp
       TIMECODE_RANGE                                    `GROUP BY TIME(MINUTES(~ buoyid top_temp
       <chr>                                             <int64>                   <int>    <int>
     1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:3~ 35345                         0       99
     2 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:3~ 35345                         0      100
     3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:3~ 35347                         1       78
     4 2014-01-06 09:00:00.000000+00:00,2014-01-06 09:3~ 35347                         1       79
     5 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:3~ 35349                        44       55
     6 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:3~ 35349                        44       56
     7 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:0~ 35350                        22       23
     8 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:0~ 35350                        44       43
     9 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:0~ 35350                        44       43
    10 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:3~ 35371                         2       81
    # ... with more rows

Example 2: Get the largest 2 values with ties of a non-PTI table

This example gets the largest 2 values of the 'temperature' column for each group with ties of non-PTI table.

  • Retrieve the largest values.
    > df_nonpti_top <- df_nonpti_grp %>% summarise(top_temp = ts.top(temperature, 2, TRUE))
  • Print the results.
    > df_nonpti_top %>% arrange(TIMECODE_RANGE, top_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, top_temp
       TIMECODE_RANGE                                           `GROUP BY TIME(MINUTES(~ top_temp
       <chr>                                                    <int64>                     <int>
     1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08:01:00.00~ 23149921                       10
     2 2014-01-06 08:01:00.000000+00:00,2014-01-06 08:02:00.00~ 23149922                       10
     3 2014-01-06 08:02:00.000000+00:00,2014-01-06 08:03:00.00~ 23149923                       10
     4 2014-01-06 08:03:00.000000+00:00,2014-01-06 08:04:00.00~ 23149924                       10
     5 2014-01-06 08:04:00.000000+00:00,2014-01-06 08:05:00.00~ 23149925                       10
     6 2014-01-06 08:05:00.000000+00:00,2014-01-06 08:06:00.00~ 23149926                       10
     7 2014-01-06 08:06:00.000000+00:00,2014-01-06 08:07:00.00~ 23149927                       10
     8 2014-01-06 08:07:00.000000+00:00,2014-01-06 08:08:00.00~ 23149928                       10
     9 2014-01-06 08:08:00.000000+00:00,2014-01-06 08:09:00.00~ 23149929                       NA
    10 2014-01-06 08:09:00.000000+00:00,2014-01-06 08:10:00.00~ 23149930                       99
    # ... with more rows