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