FIRST_VALUE 和 LAST_VALUE 窗口函数 - Amazon Redshift
语法Arguments数据类型示例
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅中国的 Amazon Web Services 服务入门。

FIRST_VALUE 和 LAST_VALUE 窗口函数

在提供一组已排序行的情况下，FIRST_VALUE 返回有关窗口框架中的第一行的指定表达式的值。LAST_VALUE 函数返回有关框架中最后一行的表达式的值。

Syntax

FIRST_VALUE | LAST_VALUE
( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list frame_clause ]
)

Arguments

expression

对其执行函数的目标列或表达式。

IGNORE NULLS

将此选项与 FIRST_VALUE 结合使用时，该函数返回不为 NULL 的框架中的第一个值（如果所有值为 NULL，则返回 NULL）。将此选项与 LAST_VALUE 结合使用时，该函数返回不为 NULL 的框架中的最后一个值（如果所有值为 NULL，则返回 NULL）。

RESPECT NULLS

指示 Amazon Redshift 应包含 null 值以确定要使用的行。如果您未指定 IGNORE NULLS，则默认情况下不支持 RESPECT NULLS。

OVER

引入函数的窗口子句。

PARTITION BY expr_list

依据一个或多个表达式定义函数的窗口。

ORDER BY order_list

对每个分区中的行进行排序。如果未指定 PARTITION BY 子句，则 ORDER BY 对整个表进行排序。如果指定 ORDER BY 子句，则还必须指定 frame_clause。

FIRST_VALUE 和 LAST_VALUE 函数的结果取决于数据的排序。在以下情况下，结果是不确定的：

  • 当未指定 ORDER BY 子句且一个分区包含一个表达式的两个不同的值时

  • 当表达式的计算结果为对应于 ORDER BY 列表中同一值的不同值时。

frame_clause

如果 ORDER BY 子句用于聚合函数，则需要显式框架子句。框架子句优化函数窗口中的行集，包含或排除已排序结果中的行集。框架子句包括 ROWS 关键字和关联的说明符。请参阅 窗口函数语法摘要。

数据类型

这些函数支持使用任何 Amazon Redshift 数据类型的表达式。返回类型与 expression 的类型相同。

Examples

以下示例返回 VENUE 表中每个场地的座位数，同时按容量对结果进行排序（从高到低）。FIRST_VALUE 函数用于选择与框架中的第一行对应的场地的名称：在这种情况下，为座位数最多的行。按州对结果进行分区，以便当 VENUESTATE 值发生更改时，会选择一个新的第一个值。窗口框架是无界的，因此为每个分区中的每个行选择相同的第一个值。

对于加利福利亚，Qualcomm Stadium 具有最大座位数 (70561)，此名称是 CA 分区中所有行的第一个值。 

select venuestate, venueseats, venuename,
first_value(venuename)
over(partition by venuestate
order by venueseats desc
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

venuestate | venueseats |           venuename            |         first_value
-----------+------------+--------------------------------+------------------------------
CA         |      70561 | Qualcomm Stadium               | Qualcomm Stadium
CA         |      69843 | Monster Park                   | Qualcomm Stadium
CA         |      63026 | McAfee Coliseum                | Qualcomm Stadium
CA         |      56000 | Dodger Stadium                 | Qualcomm Stadium
CA         |      45050 | Angel Stadium of Anaheim       | Qualcomm Stadium
CA         |      42445 | PETCO Park                     | Qualcomm Stadium
CA         |      41503 | AT&T Park                      | Qualcomm Stadium
CA         |      22000 | Shoreline Amphitheatre         | Qualcomm Stadium
CO         |      76125 | INVESCO Field                  | INVESCO Field
CO         |      50445 | Coors Field                    | INVESCO Field
DC         |      41888 | Nationals Park                 | Nationals Park
FL         |      74916 | Dolphin Stadium                | Dolphin Stadium
FL         |      73800 | Jacksonville Municipal Stadium | Dolphin Stadium
FL         |      65647 | Raymond James Stadium          | Dolphin Stadium
FL         |      36048 | Tropicana Field                | Dolphin Stadium
...

下一个示例使用 LAST_VALUE 函数而不是 FIRST_VALUE；否则，查询与上一示例相同。对于加利福利亚，为该分区中的每个行返回 Shoreline Amphitheatre，因为它具有最小座位数 (22000)。 

select venuestate, venueseats, venuename,
last_value(venuename)
over(partition by venuestate
order by venueseats desc
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

venuestate | venueseats |           venuename            |          last_value
-----------+------------+--------------------------------+------------------------------
CA         |      70561 | Qualcomm Stadium               | Shoreline Amphitheatre
CA         |      69843 | Monster Park                   | Shoreline Amphitheatre
CA         |      63026 | McAfee Coliseum                | Shoreline Amphitheatre
CA         |      56000 | Dodger Stadium                 | Shoreline Amphitheatre
CA         |      45050 | Angel Stadium of Anaheim       | Shoreline Amphitheatre
CA         |      42445 | PETCO Park                     | Shoreline Amphitheatre
CA         |      41503 | AT&T Park                      | Shoreline Amphitheatre
CA         |      22000 | Shoreline Amphitheatre         | Shoreline Amphitheatre
CO         |      76125 | INVESCO Field                  | Coors Field
CO         |      50445 | Coors Field                    | Coors Field
DC         |      41888 | Nationals Park                 | Nationals Park
FL         |      74916 | Dolphin Stadium                | Tropicana Field
FL         |      73800 | Jacksonville Municipal Stadium | Tropicana Field
FL         |      65647 | Raymond James Stadium          | Tropicana Field
FL         |      36048 | Tropicana Field                | Tropicana Field
...

下面的示例介绍如何使用 IGNORE NULLS 选项，并且事先向 VENUE 表添加一个新行：

insert into venue values(2000,null,'Stanford','CA',90000);

此新行为 VENUENAME 列包含一个 NULL 值。现在，重复本部分中前面介绍的 FIRST_VALUE 查询：

select venuestate, venueseats, venuename,
first_value(venuename)
over(partition by venuestate
order by venueseats desc
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

venuestate | venueseats |         venuename          | first_value
-----------+------------+----------------------------+-------------
CA         |      90000 |                            |
CA         |      70561 | Qualcomm Stadium           |
CA         |      69843 | Monster Park               |
...

因为新行包含最高的 VENUESEATS 值 (90000) 且其 VENUENAME 为 NULL，所以 FIRST_VALUE 函数为 CA 分区返回 NULL。要在函数计算中忽略诸如此类的行，请向函数参数添加 IGNORE NULLS 选项： 

select venuestate, venueseats, venuename,
first_value(venuename ignore nulls)
over(partition by venuestate
order by venueseats desc
rows between unbounded preceding and unbounded following)
from (select * from venue where venuestate='CA')
order by venuestate;

venuestate | venueseats |         venuename          |   first_value
------------+------------+----------------------------+------------------
CA         |      90000 |                            | Qualcomm Stadium
CA         |      70561 | Qualcomm Stadium           | Qualcomm Stadium
CA         |      69843 | Monster Park               | Qualcomm Stadium
...