Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

FIRST_VALUE 和 LAST_VALUE 开窗函数示例

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

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

Copy
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)。

Copy
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 表添加一个新行:

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

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

Copy
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 选项:

Copy
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 ...