LISTAGG 関数 - Amazon Redshift
構文引数戻り値使用に関する注意事項例

LISTAGG 関数

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

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

構文

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ]

引数

DISTINCT

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

aggregate_expression

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

delimiter

連結された値を区切る文字列定数。デフォルトは NULL です。

WITHIN GROUP (ORDER BY order_list)

集計値のソート順を指定する句。

戻り値

VARCHAR(MAX)。結果セットが VARCHAR の最大サイズより大きい場合、LISTAGG は次のエラーを返します。

Invalid operation: Result size exceeds LISTAGG limit

使用に関する注意事項

  • WITHIN GROUP 句を使用する複数の LISTAGG 関数が 1 つのステートメントに含まれる場合、各 WITHIN GROUP 句で ORDER BY 値を使用する必要があります。

    例えば、次のステートメントはエラーを返します。

    SELECT LISTAGG(sellerid) 
WITHIN GROUP (ORDER BY dateid) AS sellers,
LISTAGG(dateid) 
WITHIN GROUP (ORDER BY sellerid) AS dates
FROM sales;

    以下のステートメントは正常に実行されます。

    SELECT LISTAGG(sellerid) 
WITHIN GROUP (ORDER BY dateid) AS sellers,
LISTAGG(dateid) 
WITHIN GROUP (ORDER BY dateid) AS dates
FROM sales;

SELECT LISTAGG(sellerid) 
WITHIN GROUP (ORDER BY dateid) AS sellers,
LISTAGG(dateid) AS dates
FROM sales;

例

以下の例は、販売者 ID を集計し、販売者 ID 順で返します。

SELECT LISTAGG(sellerid, ', ') 
WITHIN GROUP (ORDER BY sellerid) 
FROM sales
WHERE eventid = 4337;

listagg                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
380, 380, 1178, 1178, 1178, 2731, 8117, 12905, 32043, 32043, 32043, 32432, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 47188, 48294

次の例では、DISTINCT を使用して一意の販売者 ID のリストを返します。

SELECT LISTAGG(DISTINCT sellerid, ', ') 
WITHIN GROUP (ORDER BY sellerid) 
FROM sales
WHERE eventid = 4337;

listagg                                                                                    
-------------------------------------------------------------------------------------------
380, 1178, 2731, 8117, 12905, 32043, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 48294

以下の例は、販売者 ID を集計し、日付順で返します。

SELECT LISTAGG(sellerid, ', ')  
WITHIN GROUP (ORDER BY dateid) 
FROM sales
WHERE eventid = 4337;

   listagg
-----------------------------------------------------------------------------------------------------------------------------------------
 41498, 47188, 47188, 1178, 1178, 1178, 380, 45676, 46324, 48294, 32043, 32043, 32432, 12905, 8117, 38750, 2731, 32432, 32043, 380, 38669

次の例は、ID が 660 の購入者についてパイプで区切った販売日のリストを返します。

SELECT LISTAGG(
    (SELECT caldate FROM date WHERE date.dateid=sales.dateid), ' | '    
)
WITHIN GROUP (ORDER BY sellerid DESC, salesid ASC)
FROM sales
WHERE buyerid = 660;

             listagg
-------------------------------------------------
2008-07-16 | 2008-07-09 | 2008-01-01 | 2008-10-26

次の例は、ID が 660、661、662 の購入者についてカンマで区切った販売 ID のリストを返します。

SELECT buyerid, 
LISTAGG(salesid,', ')
WITHIN GROUP (ORDER BY salesid) AS sales_id
FROM sales
WHERE buyerid BETWEEN 660 AND 662
GROUP BY buyerid
ORDER BY buyerid;
            
buyerid |                sales_id
--------+-----------------------------------------------------
660     | 32872, 33095, 33514, 34548
661     | 19951, 20517, 21695, 21931
662     | 3318, 3823, 4215, 51980, 53202, 55908, 57832, 171603