LAST_VALUE
Description
LAST_VALUE() is a window function that returns the last value within the window frame. The handling of null values can be controlled using the IGNORE NULLS options.
Syntax
LAST_VALUE(<expr>[,<ignore_null>])
Parameters
| Parameter | Description |
|---|---|
| expr | The expression from which to get the last value |
| ignore_null | Optional. When set, null values are ignored, returning the last non-null value |
Return Value
Returns the same data type as the input expression.
Examples
WITH example_data AS(
SELECT1as id,21as myday,'04-21-11'as time_col,NULLas state
UNIONALL
SELECT2,21,'04-21-12',2
UNIONALL
SELECT3,21,'04-21-13',3
UNIONALL
SELECT4,22,'04-22-10-21',NULL
UNIONALL
SELECT5,22,'04-22-10-22',NULL
UNIONALL
SELECT6,22,'04-22-10-23',5
UNIONALL
SELECT7,22,'04-22-10-24',NULL
UNIONALL
SELECT8,22,'04-22-10-25',9
UNIONALL
SELECT9,23,'04-23-11',NULL
UNIONALL
SELECT10,23,'04-23-12',10
UNIONALL
SELECT11,23,'04-23-13',NULL
UNIONALL
SELECT12,24,'02-24-10-21',NULL
)
SELECT
*,
last_value(`state`,1)OVER(
PARTITIONBY`myday`
ORDERBY`time_col`DESC
ROWSBETWEEN1PRECEDINGAND1FOLLOWING
)as ignore_null,
last_value(`state`,0)OVER(
PARTITIONBY`myday`
ORDERBY`time_col`DESC
ROWSBETWEEN1PRECEDINGAND1FOLLOWING
)as not_ignore_null,
last_value(`state`)OVER(
PARTITIONBY`myday`
ORDERBY`time_col`DESC
ROWSBETWEEN1PRECEDINGAND1FOLLOWING
)as ignore_null_default
FROM example_data
ORDERBY`id`,`myday`,`time_col`;
+------+-------+-------------+-------+-------------+-----------------+---------------------+
| id | myday | time_col | state | ignore_null | not_ignore_null | ignore_null_default |
+------+-------+-------------+-------+-------------+-----------------+---------------------+
| 1 | 21 | 04-21-11 | NULL | 2 | NULL | NULL |
| 2 | 21 | 04-21-12 | 2 | 2 | NULL | NULL |
| 3 | 21 | 04-21-13 | 3 | 2 | 2 | 2 |
| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL | NULL |
| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL | NULL |
| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL | NULL |
| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 | 5 |
| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL | NULL |
| 9 | 23 | 04-23-11 | NULL | 10 | NULL | NULL |
| 10 | 23 | 04-23-12 | 10 | 10 | NULL | NULL |
| 11 | 23 | 04-23-13 | NULL | 10 | 10 | 10 |
| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL | NULL |
+------+-------+-------------+-------+-------------+-----------------+---------------------+