LISTAGG ウィンドウ関数 - Amazon Redshift

LISTAGG ウィンドウ関数

クエリの各グループについて、LISTAGG ウィンドウ関数は、ORDER BY 式に従ってそのグループの行をソートしてから、それらの値を 1 つの文字列に連結します。

LISTAGG はコンピューティングノード専用の関数です。クエリがユーザー定義のテーブルまたは Amazon Redshift システムテーブルを参照しない場合、関数がエラーを返します。詳細については、「カタログテーブルへのクエリの実行」を参照してください。

構文

LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ] OVER ( [PARTITION BY partition_expression] )

引数

DISTINCT

(オプション) 連結する前に、指定された式から重複した値を削除する句。末尾のスペースは無視されるので、'a''a ' という文字列は重複として扱われます。LISTAGG は、発生した最初の値を使用します。詳細については、「末尾の空白の重要性」を参照してください。

aggregate_expression

集計する値を返す任意の有効な式 (列名など)。NULL 値および空の文字列は無視されます。

delimiter

(オプション) 連結された値を区切る文字列定数。デフォルトは NULL です。

WITHIN GROUP (ORDER BY order_list)

(オプション) 集計値のソート順を指定する句。ORDER BY により一意の順序になる場合にのみ確定的です。デフォルトでは、すべての行を集計し、1 つの値を返します。

OVER

ウィンドウのパーティションを指定する句。OVER 句にウィンドウの並び順またはウィンドウフレーム仕様を含めることはできません。

PARTITION BY partition_expression

(オプション) OVER 句のグループごとにレコードの範囲を設定します。

戻り値

VARCHAR(MAX)。結果セットが最大 VARCHAR サイズ (64K - 1、または 65535) より大きい場合、LISTAGG は以下のエラーを返します。

Invalid operation: Result size exceeds LISTAGG limit

次の例は、WINSALES テーブルを使用します。WINSALES テーブルの説明については、「ウィンドウ関数例のサンプルテーブル」を参照してください。

以下の例は、販売者 ID のリストを販売者 ID 順で返します。

select listagg(sellerid) within group (order by sellerid) over() from winsales; listagg ------------ 11122333344 ... ... 11122333344 11122333344   (11 rows)

以下の例は、購入者 B の販売者 ID のリストを日付順で返します。

select listagg(sellerid) within group (order by dateid) over () as seller from winsales where buyerid = 'b' ; seller --------- 3233 3233 3233 3233

以下の例は、購入者 B について販売日付のカンマ区切りリストを返します。

select listagg(dateid,',') within group (order by sellerid desc,salesid asc) over () as dates from winsales where buyerid = 'b'; dates ------------------------------------------- 2003-08-02,2004-04-18,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-04-18,2004-02-12

次の例は、DISTINCT を使用して、購入者 B の一意の販売日のリストを返します。

select listagg(distinct dateid,',') within group (order by sellerid desc,salesid asc) over () as dates from winsales where buyerid = 'b'; dates -------------------------------- 2003-08-02,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-02-12 2003-08-02,2004-04-18,2004-02-12

以下の例は、各購入者 ID について販売 ID のカンマ区切りリストを返します。

select buyerid, listagg(salesid,',') within group (order by salesid) over (partition by buyerid) as sales_id from winsales order by buyerid; +---------+-------------------------+ | buyerid | sales_id | +---------+-------------------------+ | a | 10005,40001,40005 | | a | 10005,40001,40005 | | a | 10005,40001,40005 | | b | 20001,30001,30003,30004 | | b | 20001,30001,30003,30004 | | b | 20001,30001,30003,30004 | | b | 20001,30001,30003,30004 | | c | 10001,10006,20002,30007 | | c | 10001,10006,20002,30007 | | c | 10001,10006,20002,30007 | | c | 10001,10006,20002,30007 | +---------+-------------------------+