ts.bottom() | Teradata R Package - 17.00 - ts.bottom() - 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.bottom() returns the smallest 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 bottom 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 bottom function with ties or not.

    Default value is FALSE.

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

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

  • Retrieve the smallest values.
    > df_seq_bottom <- df_seq_grp %>% summarise(bottom_temp = ts.bottom(temperature, 2))
    
  • Print the results.
    > df_seq_bottom %>% arrange(TIMECODE_RANGE, buoyid, bottom_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, bottom_temp
       TIMECODE_RANGE                                  `GROUP BY TIME(MINUTES~ buoyid bottom_temp
       <chr>                                           <int64>                  <int>       <int>
     1 2014-01-06 08:00:00.000000+00:00,2014-01-06 08~ 35345                        0          10
     2 2014-01-06 08:00:00.000000+00:00,2014-01-06 08~ 35345                        0          10
     3 2014-01-06 09:00:00.000000+00:00,2014-01-06 09~ 35347                        1          70
     4 2014-01-06 09:00:00.000000+00:00,2014-01-06 09~ 35347                        1          71
     5 2014-01-06 10:00:00.000000+00:00,2014-01-06 10~ 35349                       44          43
     6 2014-01-06 10:00:00.000000+00:00,2014-01-06 10~ 35349                       44          43
     7 2014-01-06 10:30:00.000000+00:00,2014-01-06 11~ 35350                       22          23
     8 2014-01-06 10:30:00.000000+00:00,2014-01-06 11~ 35350                       44          43
     9 2014-01-06 10:30:00.000000+00:00,2014-01-06 11~ 35350                       44          43
    10 2014-01-06 21:00:00.000000+00:00,2014-01-06 21~ 35371                        2          80
    # ... with more rows

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

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

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