LISTAGG ウィンドウ関数 - Amazon Redshift
構文引数戻り値例

LISTAGG ウィンドウ関数

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

構文

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 |
+---------+-------------------------+