WINDOW FUNCTION LAST_VALUE

description

LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。ignore_null 决定是否忽略 null 值,参数 ignore_null 默认值为 false 。

  1. LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause [window_clause])

example

使用FIRST_VALUE()举例中的数据:

  1. select * ,
  2. last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows between 1 preceding and 1 following) as ignore_null,
  3. last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows between 1 preceding and 1 following) as not_ignore_null,
  4. last_value(`state`) over(partition by `myday` order by `time_col` DESC rows between 1 preceding and 1 following) as ignore_null_default
  5. from t order by `id`, `myday`, `time_col`;
  6. | id | myday | time_col | state | ignore_null | not_ignore_null | ignore_null_default |
  7. |------|-------|-------------|-------|-------------|-----------------|---------------------|
  8. | 1 | 21 | 04-21-11 | NULL | 2 | NULL | NULL |
  9. | 2 | 21 | 04-21-12 | 2 | 2 | NULL | NULL |
  10. | 3 | 21 | 04-21-13 | 3 | 2 | 2 | 2 |
  11. | 4 | 22 | 04-22-10-21 | NULL | NULL | NULL | NULL |
  12. | 5 | 22 | 04-22-10-22 | NULL | 5 | NULL | NULL |
  13. | 6 | 22 | 04-22-10-23 | 5 | 5 | NULL | NULL |
  14. | 7 | 22 | 04-22-10-24 | NULL | 5 | 5 | 5 |
  15. | 8 | 22 | 04-22-10-25 | 9 | 9 | NULL | NULL |
  16. | 9 | 23 | 04-23-11 | NULL | 10 | NULL | NULL |
  17. | 10 | 23 | 04-23-12 | 10 | 10 | NULL | NULL |
  18. | 11 | 23 | 04-23-13 | NULL | 10 | 10 | 10 |
  19. | 12 | 24 | 02-24-10-21 | NULL | NULL | NULL | NULL |

keywords

  1. WINDOW,FUNCTION,LAST_VALUE