Teradata Package for R Function Reference | 17.00 - 17.00 - group_by_time - Teradata Package for R

Description

This function takes an existing tbl_teradata and converts into a grouped tbl_teradata object that allows a set of aggregate functions to be computed on data grouped in terms of time. Although the grouping is optimized for objects of class "tbl_teradata" created using PTI tables, it is also supported on objects of class "tbl_teradata" created using non-PTI tables when the argument "timecode.column" is specified.
Note: If consecutive groupings are performed, latest grouping operation is considered, i.e. if groupby is followed by groupby_time, groupby_time grouping is considered for aggregation. Similarly, if groupby_time is followed by groupby, groupby grouping is considered for aggregation. If consecutive groupby_time operations are performed, latest groupby_time is considered for aggregation.

Usage

group_by_time(
  df,
  timebucket.duration,
  value.expression = c(),
  timecode.column = NULL,
  sequence.column = NULL,
  fill = NULL,
  ...
)

Arguments

Value

A 'tbl_teradata' object.

Examples

# Get remote data source connection.
con <- td_get_context()$connection

# Load the required tables.
loadExampleData("time_series_example", "ocean_buoys_seq", "ocean_buoys_nonseq",
                "ocean_buoys_nonpti", "package_tracking_pti")

# Create objects of class "tbl_teradata".
df_seq <- tbl(con, "ocean_buoys_seq") # tbl of sequenced PTI table.
df_nonseq <- tbl(con, "ocean_buoys_nonseq") # tbl of non-sequenced PTI table.
df_nonpti <- tbl(con, "ocean_buoys_nonpti") # tbl of non-PTI table.

# Example 1: Grouping the sequenced PTI tables based on time with timebucket duration of
#            30 minutes and the column 'buoyid' and ignoring the aggregation when there is
#            no values in the group.

# Grouping by timebucket duration of 30 minutes and 'buoyid'.
# Note the use of shorthand notation for timebucket duration.
seq_group1 <- df_seq %>% group_by_time(timebucket.duration = "30m",
                                       value.expression = "buoyid")

# Applying min() aggregation on grouped tbl object.
seq_group1 %>% summarise(min_temp = min(temperature))

# Example 2: Grouping the non-PTI tables based on time with timebucket duration of 1 minute
#            and fills the missing timebuckets with previous values.

# Grouping by timebucket duration of 1 minute.
# Note the use of formal notation for timebucket duration.
# Note the use of timecode.column argument (mandatory for non-PTI table).
seq_group2 <- df_nonpti %>% group_by_time(timebucket.duration = "MINUTES(1)",
                                          timecode.column = "TIMECODE",
                                          fill = "PREV")

# Applying min() aggregation on grouped tbl object.
temp_grouped <- seq_group2 %>% summarise(min_temp = min(temperature))

# Displaying the rows in the increasing order to TIMECODE_RANGE or 'GROUP BY TIME(MINUTES(1))'
# column.
temp_grouped %>% arrange(`GROUP BY TIME(MINUTES(1))`)

# Example 3: Grouping the non-sequenced PTI tables based on time with timebucket duration of
#            2 Calendar years. Note the use of timecode.column and sequence.column arguments.

# The next three grouping give a quick demo of the use of fill argument keeping all other
# arguments same.
# With fill = "PREVIOUS"
grp1 <- df_nonseq %>% group_by_time(timebucket.duration = "CAL_YEARS(1)",
                                    timecode.column = "TD_TIMECODE",
                                    sequence.column = "buoyid",
                                    fill = "PREVIOUS")
min_t <- grp1 %>% summarise(min_temp = min(temperature))
min_t %>% arrange(`GROUP BY TIME(CAL_YEARS(1))`)

# With fill = "NEXT"
grp1 <- df_nonseq %>% group_by_time(timebucket.duration = "CAL_YEARS(1)",
                                    timecode.column = "TD_TIMECODE",
                                    sequence.column = "buoyid",
                                    fill = "NEXT")
min_t <- grp1 %>% summarise(min_temp = min(temperature))
min_t %>% arrange(`GROUP BY TIME(CAL_YEARS(1))`)

# With fill = 10000 (some numeric constant)
grp1 <- df_nonseq %>% group_by_time(timebucket.duration = "CAL_YEARS(1)",
                                    timecode.column = "TD_TIMECODE",
                                    sequence.column = "buoyid",
                                    fill = 10000)
min_t <- grp1 %>% summarise(min_temp = min(temperature))
min_t %>% arrange(`GROUP BY TIME(CAL_YEARS(1))`)

# Example 4: Finding Time Elapsed between Shipping and Receiving an Item. Input data used for
#            this example contains information about parcels sent by a delivery service. This
#            example also demonstrates the use of unbounded timebucket duration ('*') for
#            'ts.delta_t' time series aggregate function.
df_pack_pti <- tbl(con, "package_tracking_pti")
df_grp <- df_pack_pti %>% group_by_time(timebucket.duration = "*",
                                        value.expression = "parcel_number")
df_out <- df_grp %>% summarise(delta_t = ts.delta_t("Status LIKE 'picked%up%customer'",
                                                    "Status LIKE 'delivered%customer'"))
df_out %>% arrange(TIMECODE_RANGE, parcel_number)