The last() function returns the newest value, determined by the 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. It is a single-threaded function.
- This function is valid only on columns of numeric types.
- Nulls are not included in the result computation.
Examples Prerequisite
See Example Setup to set up the environment for the following examples.
Example: Run last() on DataFrame created on non-sequenced PTI table
>>> ocean_buoys_grpby1 = ocean_buoys.groupby_time(timebucket_duration="2cy", value_expression="buoyid", fill="NULLS")
>>> ocean_buoys_grpby1.last().sort(["TIMECODE_RANGE", "buoyid"]) /mnt/c/Users/pp186043/GitHub_Repos/pyTeradata/teradataml/common/utils.py:398: VantageRuntimeWarning: [Teradata][teradataml](TDML_2086) Following warning raised from Vantage with warning code: 4001 [Teradata Database] [Warning 4001] Time Series Auxiliary Cache Warning: Multiple results found for one or more Time Series aggregate functions in this query, but only one result was returned. To get all results, resubmit this query with these aggregates isolated. VantageRuntimeWarning) TIMECODE_RANGE GROUP BY TIME(CAL_YEARS(2)) buoyid last_salinity last_temperature 0 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 0 55 100 1 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 1 55 72 2 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 2 55 82 3 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 44 55 43
"[Teradata Database] [Warning 4001] Time Series Auxiliary Cache Warning: Multiple results found for one or more Time Series aggregate functions in this query, but only one result was returned. To get all results, resubmit this query with these aggregates isolated."
This warning recommends to run last() independently on each column to get all the results.
Example: Run last() on one single column
When running the last() function on one single column, you can pass column name as input. In this example, run last() on 'temperature' column.
>>> ocean_buoys_grpby1.last("temperature") TIMECODE_RANGE GROUP BY TIME(CAL_YEARS(2)) buoyid last_temperature 0 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 2 82 1 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 0 100 2 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 0 10 3 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 44 43 4 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 1 79 5 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 1 72
Example: Run last() on DataFrame created on sequenced PTI table
In this example, the table has few columns incompatible for last() operation 'dates' and 'TD_TIMECODE'. When running this command, incompatible columns are ignored.
>>> ocean_buoys_seq_grpby1 = ocean_buoys_seq.groupby_time(timebucket_duration="2cy", value_expression="buoyid", fill="NULLS")
>>> ocean_buoys_seq_grpby1.last().sort(["TIMECODE_RANGE", "buoyid"]) TIMECODE_RANGE GROUP BY TIME(CAL_YEARS(2)) buoyid last_TD_SEQNO last_salinity last_temperature 0 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 0 27 55 100 1 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 1 25 55 79 2 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 2 16 55 82 3 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 22 1 25 23 4 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 44 2 55 43
Example: Run last() on DataFrame created on non-PTI table
>>> ocean_buoys_nonpti_grpby1 = ocean_buoys_nonpti.groupby_time(timebucket_duration="2cy", value_expression="buoyid", timecode_column="timecode", fill="NULLS")
>>> ocean_buoys_nonpti_grpby1.last().sort(["TIMECODE_RANGE", "buoyid"]) /mnt/c/Users/pp186043/GitHub_Repos/pyTeradata/teradataml/common/utils.py:398: VantageRuntimeWarning: [Teradata][teradataml](TDML_2086) Following warning raised from Vantage with warning code: 4001 [Teradata Database] [Warning 4001] Time Series Auxiliary Cache Warning: Multiple results found for one or more Time Series aggregate functions in this query, but only one result was returned. To get all results, resubmit this query with these aggregates isolated. VantageRuntimeWarning) TIMECODE_RANGE GROUP BY TIME(CAL_DAYS(2)) buoyid last_salinity last_temperature 0 ('2014-01-06 00:00:00.000000-00:00', '2014-01-... 8039 0 55 100 1 ('2014-01-06 00:00:00.000000-00:00', '2014-01-... 8039 1 55 79 2 ('2014-01-06 00:00:00.000000-00:00', '2014-01-... 8039 2 55 82 3 ('2014-01-06 00:00:00.000000-00:00', '2014-01-... 8039 44 55 43
Example: Run last() on selected columns
Run the last() function on selected columns 'temperature' and 'salinity'.
>>> ocean_buoys_seq_grpby1 = ocean_buoys_seq.groupby_time("2cy", 'buoyid')
>>> ocean_buoys_seq_grpby1.last(['temperature', 'salinity']) TIMECODE_RANGE GROUP BY TIME(CAL_YEARS(2)) buoyid last_temperature last_salinity 0 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 2 82 55 1 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 44 43 55 2 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 22 23 25 3 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 1 79 55 4 ('2014-01-01 00:00:00.000000-00:00', '2016-01-... 2 0 100 55