- Kategorien:
Aggregatfunktionen (Semistrukturierte Daten), Fensterfunktionen (Allgemein), Semistrukturierte Datenfunktionen (Array/Objekt)
ARRAY_AGG¶
Gibt die Eingabewerte in ein ARRAY pivotiert zurück. Wenn die Eingabe leer ist, wird ein leeres ARRAY zurückgegeben.
- 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> ] )
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. (EineORDER 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
undWITHIN 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
undWITHIN 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
undWITHIN GROUP
angeben oderDISTINCT
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);
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 | +------------+
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" | | | ] | +---------------+-------------------------------------------------------------+