WINDOW FUNCTION FIRST_VALUE

description

FIRST_VALUE() 返回窗口范围内的第一个值。

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

example

我们有如下数据

1. select name, country, greeting from mail_merge;

3. | name    | country | greeting     |
4. |---------|---------|--------------|
5. | Pete    | USA     | Hello        |
6. | John    | USA     | Hi           |
7. | Boris   | Germany | Guten tag    |
8. | Michael | Germany | Guten morgen |
9. | Bjorn   | Sweden  | Hej          |
10. | Mats    | Sweden  | Tja          |

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

1. select country, name,
2. first_value(greeting)
3. over (partition by country order by name, greeting) as greeting from mail_merge;

5. | country | name    | greeting  |
6. |---------|---------|-----------|
7. | Germany | Boris   | Guten tag |
8. | Germany | Michael | Guten tag |
9. | Sweden  | Bjorn   | Hej       |
10. | Sweden  | Mats    | Hej       |
11. | USA     | John    | Hi        |
12. | USA     | Pete    | Hi        |

keywords

1. WINDOW,FUNCTION,FIRST_VALUE