LAST_VALUE
更新时间：2025-10-16
描述
LAST_VALUE() 是一个窗口函数，用于返回窗口范围内的最后一个值。可以通过 IGNORE NULLS 选项来控制是否忽略空值。
语法
SQL
1LAST_VALUE(<expr>[, <ignore_null>])参数
| 参数 | 说明 |
|---|---|
| expr | 需要获取最后一个值的表达式 |
| ignore_null | 可选。参数 ignore_null 默认值为 false, 设置后会忽略空值 |
返回值
返回与输入表达式相同的数据类型。
举例
SQL
1WITH example_data AS (
2 SELECT 1 as id, 21 as myday, '04-21-11' as time_col, NULL as state
3 UNION ALL
4 SELECT 2, 21, '04-21-12', 2
5 UNION ALL
6 SELECT 3, 21, '04-21-13', 3
7 UNION ALL
8 SELECT 4, 22, '04-22-10-21', NULL
9 UNION ALL
10 SELECT 5, 22, '04-22-10-22', NULL
11 UNION ALL
12 SELECT 6, 22, '04-22-10-23', 5
13 UNION ALL
14 SELECT 7, 22, '04-22-10-24', NULL
15 UNION ALL
16 SELECT 8, 22, '04-22-10-25', 9
17 UNION ALL
18 SELECT 9, 23, '04-23-11', NULL
19 UNION ALL
20 SELECT 10, 23, '04-23-12', 10
21 UNION ALL
22 SELECT 11, 23, '04-23-13', NULL
23 UNION ALL
24 SELECT 12, 24, '02-24-10-21', NULL
25)
26SELECT
27 *,
28 last_value(`state`, 1) OVER(
29 PARTITION BY `myday`
30 ORDER BY `time_col` DESC
31 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
32 ) as ignore_null,
33 last_value(`state`, 0) OVER(
34 PARTITION BY `myday`
35 ORDER BY `time_col` DESC
36 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
37 ) as not_ignore_null,
38 last_value(`state`) OVER(
39 PARTITION BY `myday`
40 ORDER BY `time_col` DESC
41 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
42 ) as ignore_null_default
43FROM example_data
44ORDER BY `id`, `myday`, `time_col`;
Text
1+------+-------+-------------+-------+-------------+-----------------+---------------------+
2| id | myday | time_col | state | ignore_null | not_ignore_null | ignore_null_default |
3+------+-------+-------------+-------+-------------+-----------------+---------------------+
4| 1 | 21 | 04-21-11 | NULL | 2 | NULL | NULL |
5| 2 | 21 | 04-21-12 | 2 | 2 | NULL | NULL |
6| 3 | 21 | 04-21-13 | 3 | 2 | 2 | 2 |
7| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL | NULL |
8| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL | NULL |
9| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL | NULL |
10| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 | 5 |
11| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL | NULL |
12| 9 | 23 | 04-23-11 | NULL | 10 | NULL | NULL |
13| 10 | 23 | 04-23-12 | 10 | 10 | NULL | NULL |
14| 11 | 23 | 04-23-13 | NULL | 10 | 10 | 10 |
15| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL | NULL |
16+------+-------+-------------+-------+-------------+-----------------+---------------------+