ts.first() | Teradata R Package - 17.00 - ts.first() - 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.first() returns the oldest value, determined by timecode, for each group.

In the event of a tie, such as simultaneous timecode values for a particular group, all tied results are returned. If a sequence number is present with the data, it can break a tie, assuming it is unique across identical timecode values.

ts.first() uses the argument "value.expression" to specify the column for which oldest value of the group is to be returned.

  • This function is valid only on columns with numeric types.
  • Nulls are not included in the result computation.

Example 1: Get the oldest value of the 'temperature' column of sequenced PTI table

  • Get the oldest value.
    > df_seq_first <- df_seq_grp %>% summarise(first_temp = ts.first(temperature))
  • Print the results.
    > df_seq_first %>% arrange(TIMECODE_RANGE, buoyid, first_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, first_temp
      TIMECODE_RANGE                                   `GROUP BY TIME(MINUTES(~ buoyid first_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 09:00:00.000000+00:00,2014-01-06 09:~ 35347                         1         70
    3 2014-01-06 10:00:00.000000+00:00,2014-01-06 10:~ 35349                        44         43
    4 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350                        22         23
    5 2014-01-06 10:30:00.000000+00:00,2014-01-06 11:~ 35350                        44         43
    6 2014-01-06 21:00:00.000000+00:00,2014-01-06 21:~ 35371                         2         80

Example 2: Get the oldest value of the 'temperature' column of non-PTI table

  • Get the oldest value.
    > df_nonpti_first <- df_nonpti_grp %>% summarise(first_temp = ts.first(temperature))
  • Print the results.
    > df_nonpti_first %>% arrange(TIMECODE_RANGE, first_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, first_temp
       TIMECODE_RANGE                                         `GROUP BY TIME(MINUTES(~ first_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