FIRST_VALUE 和 LAST_VALUE 視窗函數 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

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 ] )

引數

運算式

函數運算的目標欄或表達式。

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 的類型相同。

範例

下列範例傳回 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 ...