last_value ↗

Use the last_*value() function within the windowcomp stage to retrieve the value of a specified field from the last row within a defined window or partition. This function is useful when you need to access the most recent or final value of a sequence relative to the current row being processed.

Syntax

windowcomp last_value(\<field\>) [by \<field\> [,\<field\>,...]] sort [asc|desc] \<field1\> [, [asc|desc] \<field2\>,...] [between 0|null|\<number\>|-\<number\> [and 0|null|\<number\>|-\<number\>] [frame_type=range]] [as \<alias\>]

Parameters

Name Type Required Description
field Any supported type Yes The field from which to retrieve the value.
by field string No Partitions the data into distinct groups. The last_value() function operates independently within each of these partitions.
sort field string Yes Defines the order of rows within each partition. This order is crucial for correctly identifying the "last" row for the last_value() calculation.
between clause string No Defines a "window frame" (a specific range of rows) relative to the current row. If omitted, the default window (typically the entire partition) is used.
alias string No Assigns an alias name to the new column displaying the result.

Returns

The last_value() function returns a single value that corresponds to the field's value in the last row within the defined window frame. This value is then returned for every row in that window, and its data type matches the data type of the input field.

Usage Notes

  • last_value() is a navigation function and must be used with the windowcomp stage.
  • The sort clause is mandatory because it defines the logical order of rows, determining which row is considered the "last" one.
  • If you use the by clause to partition the data, the function restarts its calculation for each new partition.
  • If the between clause is omitted, the default window frame typically covers the entire partition, meaning the "last" row is the last row of the partition.

Examples

Example 1: last_value() over an entire partition (default window)

Goal: Retrieve the duration_seconds from the last event within each is_successful group, ordered by _time, and replicate this value for every row in that group.

XQL Code:


config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp last_value(duration_seconds) by is_successful sort asc _time as last_duration_in_partition
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: You partition the data by is_successful and sort it by _time ascending. For the false partition, the chronologically last event is 109 with duration_seconds = 0.05. For the true partition, the last event is 110 with duration_seconds = 60.0. These last values are assigned to all rows within their respective partitions.

Output:

_time event_id is_successful duration_seconds last_duration_in_partition
2023-10-26 10:05:30 UTC 102 false 0.8 0.05
2023-10-26 10:40:10 UTC 106 false 2.1 0.05
2023-10-26 10:55:55 UTC 109 false 0.05 0.05
2023-10-26 10:00:00 UTC 101 true 1.5 60.0
2023-10-26 10:15:15 UTC 103 true 10.2 60.0
2023-10-26 10:20:00 UTC 104 true 0.1 60.0
2023-10-26 10:30:45 UTC 105 true 5.0 60.0
2023-10-26 10:45:00 UTC 107 true 7.8 60.0
2023-10-26 10:50:20 UTC 108 true 15.3 60.0
2023-10-26 11:00:10 UTC 110 true 60.0 60.0

Example 2: last_value() with a rolling window

Goal: Retrieve the duration_seconds from the last event within a sliding window consisting of the current row and the two preceding rows.

XQL Code:


config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp last_value(duration_seconds) by is_successful sort asc _time between -2 and 0 as rolling_last_value
| sort asc is_successful, asc _time, asc event_id
| limit 10

** Explanation** : You define a window of between -2 and 0 (the two preceding rows up to the current row). Because the current row is always the "last" element of this specific sliding window, the function simply returns the duration_seconds of the current row itself.

Output:

_time event_id is_successful duration_seconds rolling_last_value
2023-10-26 10:05:30 UTC 102 false 0.8 0.8
2023-10-26 10:40:10 UTC 106 false 2.1 2.1
2023-10-26 10:55:55 UTC 109 false 0.05 0.05
2023-10-26 10:00:00 UTC 101 true 1.5 1.5
2023-10-26 10:15:15 UTC 103 true 10.2 10.2
2023-10-26 10:20:00 UTC 104 true 0.1 0.1
2023-10-26 10:30:45 UTC 105 true 5.0 5.0
2023-10-26 10:45:00 UTC 107 true 7.8 7.8
2023-10-26 10:50:20 UTC 108 true 15.3 15.3
2023-10-26 11:00:10 UTC 110 true 60.0 60.0

Example 3: last_value() with a fixed window (current row to end)

Goal: Retrieve the duration_seconds from the last event in a window starting from the current row and extending to the end of the partition.

XQL Code:


config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp last_value(duration_seconds) by is_successful sort asc _time between 0 and null as last_duration_from_current
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: You define a window of between 0 and null (current row to the end of the partition). For any given row, the "last" element within this window is always the last element of the entire partition. This produces the same result as Example 1.

Output:

_time event_id is_successful duration_seconds last_duration_from_current
2023-10-26 10:05:30 UTC 102 false 0.8 0.05
2023-10-26 10:40:10 UTC 106 false 2.1 0.05
2023-10-26 10:55:55 UTC 109 false 0.05 0.05
2023-10-26 10:00:00 UTC 101 true 1.5 60.0
2023-10-26 10:15:15 UTC 103 true 10.2 60.0
2023-10-26 10:20:00 UTC 104 true 0.1 60.0
2023-10-26 10:30:45 UTC 105 true 5.0 60.0
2023-10-26 10:45:00 UTC 107 true 7.8 60.0
2023-10-26 10:50:20 UTC 108 true 15.3 60.0
2023-10-26 11:00:10 UTC 110 true 60.0 60.0