WINDOW FUNCTION FIRST_VALUE

description

FIRST_VALUE() 返回窗口范围内的第一个值， ignore_null 决定是否忽略 null 值，参数 ignore_null 默认值为 false 。

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

example

我们有如下数据

  1.  select id, myday, time_col, state from t;
  3.  | id   | myday | time_col    | state |
  4.  |------|-------|-------------|-------|
  5.  |    3 |    21 | 04-21-13    |     3 |
  6.  |    7 |    22 | 04-22-10-24 |  NULL |
  7.  |    8 |    22 | 04-22-10-25 |     9 |
  8.  |   10 |    23 | 04-23-12    |    10 |
  9.  |    4 |    22 | 04-22-10-21 |  NULL |
  10.  |    9 |    23 | 04-23-11    |  NULL |
  11.  |    1 |    21 | 04-21-11    |  NULL |
  12.  |    5 |    22 | 04-22-10-22 |  NULL |
  13.  |   12 |    24 | 02-24-10-21 |  NULL |
  14.  |    2 |    21 | 04-21-12    |     2 |
  15.  |    6 |    22 | 04-22-10-23 |     5 |
  16.  |   11 |    23 | 04-23-13    |  NULL |

使用 FIRST_VALUE()，根据 myday 分组，返回每个分组中第一个 state 的值：

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

keywords

  1. WINDOW,FUNCTION,FIRST_VALUE