Kategorien:: Aggregatfunktionen (Semistrukturierte Daten), Fensterfunktionen (Allgemein), Funktionen für semistrukturierte und strukturierte Daten (Array/Objekt)

ARRAY_AGG¶

Gibt die Eingabewerte in ein Array pivotiert zurück. Wenn die Eingabe leer ist, gibt die Funktion ein leeres Array zurück.

Aliasse:: ARRAYAGG

Syntax¶

Aggregatfunktion

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

Fensterfunktionen

ARRAY_AGG( [ DISTINCT ] <expr1> )
  [ WITHIN GROUP ( <orderby_clause> ) ]
  OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] ] [ <window_frame> ] )

Argumente¶

Benötigt:

expr1: Ein Ausdruck (normalerweise ein Spaltenname), der über die Werte bestimmt, die in das Array aufgenommen werden.
OVER(): Die OVER-Klausel gibt an, dass die Funktion als Fensterfunktion verwendet wird. Weitere Details dazu finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Optional:

DISTINCT

Entfernt doppelte Werte aus dem Array.

WITHIN GROUP orderby_clause

Klausel, die einen oder mehrere Ausdrücke (typischerweise Spaltennamen) enthält, die die Reihenfolge der Werte in jedem Array bestimmen.

Die Syntax WITHIN GROUP(ORDER BY) unterstützt die gleichen Parameter wie die ORDER BY-Hauptklausel in einer SELECT-Anweisung. Siehe ORDER BY.

PARTITION BY expr2

Klausel der Fensterfunktion, die einen Ausdruck (normalerweise einen Spaltennamen) angibt. Dieser Ausdruck definiert Partitionen, die die Eingabezeilen gruppieren, bevor die Funktion angewendet wird. Weitere Details dazu finden Sie unter Syntax und Verwendung von Fensterfunktionen.

ORDER BY expr3 [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ {window_frame} ]

Optionaler Ausdruck, nach dem innerhalb jeder Partition sortiert werden soll, gefolgt von einem optionalen Fensterrahmen. Die detaillierte Syntax von window_frame finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Wenn diese Funktion mit einem rangbezogenen Rahmen verwendet wird, unterstützt die ORDER BY-Klausel nur eine einzige Spalte. Für zeilenbezogene Rahmen gilt diese Einschränkung nicht.

LIMIT wird nicht unterstützt.

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 128 MB.

Nutzungshinweise¶

Wenn Sie WITHIN GROUP(ORDER BY) 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 für einen Ausdruck in WITHIN GROUP(ORDER BY) eine Zahl angeben, wird diese Zahl als numerische Konstante geparst, nicht als Ordinalposition einer Spalte in der SELECT-Liste. Geben Sie daher keine Zahlen als WITHIN GROUP(ORDER BY)-Ausdrücke an.
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) ...;
```
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) ...;
```
```
SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
```
Sie müssen entweder die gleiche Spalte für DISTINCT und WITHIN GROUP angeben oder DISTINCT weglassen.
DISTINCT und WITHIN GROUP werden für Fensterfunktionsaufrufe nur unterstützt, wenn es keine ORDER BY-Klausel innerhalb der OVER-Klausel gibt. Wenn eine ORDER BY-Klausel in der OVER-Klausel verwendet wird, folgen die Werte im Ausgabearray der gleichen Standardreihenfolge (d. h. der Reihenfolge, die WITHIN GROUP (ORDER BY expr3) entspricht).
NULL-Werte werden bei der Ausgabe weggelassen.

Beispiele¶

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

CREATE TABLE orders (
  o_orderkey INTEGER,
  o_clerk VARCHAR,
  o_totalprice NUMBER(12, 2),
  o_orderstatus CHAR(1)
);

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

In diesem Beispiel wird eine nicht pivotierte Ausgabe einer Abfrage 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 |
+------------+

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

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

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

Im folgenden Beispiel wird ein anderes Datenset verwendet. Die Funktion ARRAY_AGG wird als Fensterfunktion mit einem ROWS BETWEEN-Fensterrahmen aufgerufen. Erstellen Sie zunächst die Tabelle, und laden Sie 14 Zeilen in die Tabelle:

CREATE OR REPLACE TABLE array_data AS (
WITH data AS (
  SELECT 1 a, [1,3,2,4,7,8,10] b
  UNION ALL
  SELECT 2, [1,3,2,4,7,8,10]
  )
SELECT 'Ord'||a o_orderkey, 'c'||value o_clerk, index
  FROM data, TABLE(FLATTEN(b))
);

Führen Sie nun die folgende Abfrage aus: Beachten Sie, dass hier nur ein Teil des Resultsets gezeigt wird.

SELECT o_orderkey,
    ARRAY_AGG(o_clerk) OVER(PARTITION BY o_orderkey ORDER BY o_orderkey
      ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS result
  FROM array_data;

+------------+---------+
| O_ORDERKEY | RESULT  |
|------------+---------|
| Ord1       | [       |
|            |   "c1"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3", |
|            |   "c2"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3", |
|            |   "c2", |
|            |   "c4"  |
|            | ]       |
| Ord1       | [       |
|            |   "c3", |
|            |   "c2", |
|            |   "c4", |
|            |   "c7"  |
|            | ]       |
| Ord1       | [       |
|            |   "c2", |
|            |   "c4", |
|            |   "c7", |
|            |   "c8"  |
|            | ]       |
| Ord1       | [       |
|            |   "c4", |
|            |   "c7", |
|            |   "c8", |
|            |   "c10" |
|            | ]       |
| Ord2       | [       |
|            |   "c1"  |
|            | ]       |
| Ord2       | [       |
|            |   "c1", |
|            |   "c3"  |
|            | ]       |
...