ARRAY_AGG¶

Syntax¶

Aggregatfunktion

ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]

Fensterfunktionen

ARRAY_AGG( [ DISTINCT ] <expr1> )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Argumente¶

• expr1: Der Ausdruck (normalerweise ein Spaltenname), der über die Werte bestimmt, die in die Liste aufgenommen werden.

• expr2: Der Ausdruck (normalerweise ein Spaltenname), der die Partitionen bestimmt, in denen die Werte gruppiert werden sollen.

• orderby_clause: Ein Ausdruck (normalerweise ein Spaltenname), der die Reihenfolge der Werte in der Liste bestimmt.

Rückgabewerte¶

Gibt einen Wert vom Typ ARRAY zurück.

Die maximale Datenmenge, die ARRAY_AGG für einen einzelnen Aufruf zurückgeben kann, beträgt 16 MB.

Nutzungshinweise¶

• DISTINCT wird von dieser Funktion unterstützt.

• Wenn Sie WITHIN GROUP (<Order-by-Klausel>) nicht angeben, ist die Reihenfolge der Elemente in jedem Array unvorhersehbar. (Eine ORDER BY-Klausel außerhalb der WITHIN GROUP-Klausel gilt für die Reihenfolge der Ausgabezeilen, jedoch nicht für die Reihenfolge der Arrayelemente innerhalb einer Zeile.)

• Wenn Sie DISTINCT und WITHIN GROUP angeben, müssen sich beide auf die gleiche Spalte beziehen. Beispiel:

  SELECT array_agg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
  

  Copy

  Wenn Sie unterschiedliche Spalten für DISTINCT und WITHIN GROUP angeben, tritt ein Fehler auf:

  SELECT array_agg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
  

  Copy

  SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
  

  Copy

  Sie müssen entweder die gleiche Spalte für DISTINCT und WITHIN GROUP angeben oder DISTINCT weglassen.

• NULL-Werte werden bei der Ausgabe weggelassen.

• Bei Verwendung als Fensterfunktion:

  • Diese Funktion unterstützt nicht:

    • ORDER BY-Unterklausel in der OVER()-Klausel

    • Fensterrahmen

Beispiele¶

In den Beispielabfragen unten werden die folgenden Tabellen und Daten verwendet:

CREATE TABLE orders (
    o_orderkey INTEGER,         -- unique ID for each order.
    o_clerk VARCHAR,            -- identifies which clerk is responsible.
    o_totalprice NUMBER(12, 2), -- total price.
    o_orderstatus CHAR(1)       -- 'F' = Fulfilled (sent); 
                                -- 'O' = 'Ordered but not yet Fulfilled'.
    );

INSERT INTO orders (o_orderkey, o_orderstatus, o_clerk, o_totalprice) 
  VALUES 
    ( 32123, 'O', 'Clerk#000000321',     321.23),
    ( 41445, 'F', 'Clerk#000000386', 1041445.00),
    ( 55937, 'O', 'Clerk#000000114', 1055937.00),
    ( 67781, 'F', 'Clerk#000000521', 1067781.00),
    ( 80550, 'O', 'Clerk#000000411', 1080550.00),
    ( 95808, 'F', 'Clerk#000000136', 1095808.00),
    (101700, 'O', 'Clerk#000000220', 1101700.00),
    (103136, 'F', 'Clerk#000000508', 1103136.00);

Copy

In diesem Beispiel wird eine nicht pivotierte Ausgabe von SELECT ohne Verwendung von ARRAY_AGG() veranschaulicht. Der Unterschied in der Ausgabe zwischen diesem Beispiel und dem folgenden Beispiel zeigt, dass ARRAY_AGG() die Daten pivotiert.

SELECT O_ORDERKEY AS order_keys
  FROM orders
  WHERE O_TOTALPRICE > 450000
  ORDER BY O_ORDERKEY;
+------------+
| ORDER_KEYS |
|------------|
|      41445 |
|      55937 |
|      67781 |
|      80550 |
|      95808 |
|     101700 |
|     103136 |
+------------+

Copy

In diesem Beispiel sehen Sie, wie Sie ARRAY_AGG() verwenden können, um eine Ausgabespalte in einem Array in einer einzelnen Zeile zu pivotieren:

SELECT ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000;
+--------------------------------------------------------------+
| ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC) |
|--------------------------------------------------------------|
| [                                                            |
|   41445,                                                     |
|   55937,                                                     |
|   67781,                                                     |
|   80550,                                                     |
|   95808,                                                     |
|   101700,                                                    |
|   103136                                                     |
| ]                                                            |
+--------------------------------------------------------------+

Copy

Dieses Beispiel veranschaulicht die Verwendung des Schlüsselworts DISTINCT mit ARRAY_AGG().

SELECT ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000
  ORDER BY O_ORDERSTATUS ASC;
+-----------------------------------------------------------------------------+
| ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC) |
|-----------------------------------------------------------------------------|
| [                                                                           |
|   "F",                                                                      |
|   "O"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

Copy

In diesem Beispiel werden zwei separate ORDER BY-Klauseln verwendet; eine steuert die Reihenfolge im Ausgabearray innerhalb der einzelnen Zeilen und die andere die Reihenfolge der Ausgabezeilen:

SELECT 
    O_ORDERSTATUS, 
    ARRAYAGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000
  GROUP BY O_ORDERSTATUS
  ORDER BY O_ORDERSTATUS DESC;
+---------------+-------------------------------------------------------------+
| O_ORDERSTATUS | ARRAYAGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC) |
|---------------+-------------------------------------------------------------|
| O             | [                                                           |
|               |   "Clerk#000000220",                                        |
|               |   "Clerk#000000411",                                        |
|               |   "Clerk#000000114"                                         |
|               | ]                                                           |
| F             | [                                                           |
|               |   "Clerk#000000508",                                        |
|               |   "Clerk#000000136",                                        |
|               |   "Clerk#000000521",                                        |
|               |   "Clerk#000000386"                                         |
|               | ]                                                           |
+---------------+-------------------------------------------------------------+

Copy