Aggregatfunktionen

Eine Aggregatfunktion ist eine Funktion, die die Zeilen einer Gruppe in einem einzelnen Wert zusammenfasst. Beispiele für Aggregatfunktionen sind COUNT, MIN und MAX.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Bei Verwendung in Verbindung mit einer GROUP BY-Klausel haben die zusammengefassten Gruppen normalerweise mindestens eine Zeile. Wenn der zugehörige Wert SELECT keine GROUP BY-Klausel hat oder wenn bestimmte Modifikatoren für Aggregatfunktionen Zeilen aus der zusammenzufassenden Gruppe filtern, ist es möglich, dass die Aggregatfunktion eine leere Gruppe zusammenfassen muss. In diesem Fall geben die Funktionen COUNT und COUNTIF 0 zurück, während alle anderen Aggregatfunktionen NULL zurückgeben.

In den folgenden Abschnitten werden die von Cloud Spanner SQL unterstützten Aggregatfunktionen erläutert.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt für eine aus der Gruppe ausgewählte Zeile expression zurück. Welche Zeile ausgewählt wird, ist unbestimmt und nicht zufällig. Gibt NULL zurück, wenn die Eingabe keine Zeilen erzeugt. Gibt NULL zurück, wenn expression für alle Zeilen in der Gruppe NULL ist.

ANY_VALUE verhält sich so, als wäre IGNORE NULLS angegeben. Zeilen, für die expression den Wert NULL hat, werden nicht berücksichtigt und nicht ausgewählt.

Unterstützte Argumenttypen

Beliebig

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Zurückgegebene Datentypen

Entspricht dem Eingabedatentyp.

Beispiele

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt ein ARRAY von expression-Werten zurück.

Unterstützte Argumenttypen

Alle Datentypen außer ARRAY.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. IGNORE NULLS oder RESPECT NULLS: Wenn IGNORE NULLS angegeben ist, werden die NULL-Werte aus dem Ergebnis ausgeschlossen. Wenn RESPECT NULLS oder keiner der beiden Werte angegeben ist, werden die NULL-Werte in das Ergebnis eingeschlossen.
  3. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

Zurückgegebene Datentypen

ARRAY

Wenn keine Eingabezeilen vorhanden sind, gibt diese Funktion NULL zurück.

Beispiele

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Verkettet Elemente aus expression vom Typ ARRAY und gibt ein einziges ARRAY als Ergebnis zurück. Diese Funktion ignoriert NULL-Eingabe-Arrays, berücksichtigt aber die NULL-Elemente in Nicht-NULL-Eingabe-Arrays.

Unterstützte Argumenttypen

ARRAY

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

Zurückgegebene Datentypen

ARRAY

Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird.

Beispiele

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt den Durchschnitt der Nicht-NULL-Eingabewerte zurück oder NaN, wenn die Eingabe einen NaN-Wert enthält.

Unterstützte Argumenttypen

Jeder numerische Eingabetyp, z. B. INT64. Bitte beachten Sie, dass das zurückgegebene Ergebnis für Gleitkomma-Eingabetypen nicht deterministisch ist, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Zurückgegebene Datentypen

  • NUMERIC, wenn der Eingabetyp NUMERIC ist
  • FLOAT64

Beispiele

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Führt eine bitweise AND-Operation für expression aus und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Führt eine bitweise OR-Operation für expression aus und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Führt eine bitweise XOR-Operation für expression aus und gibt das Ergebnis zurück.

Unterstützte Argumenttypen

  • INT64

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Zurückgegebene Datentypen

INT64

Beispiele

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

  1. Gibt die Anzahl der Zeilen in der Eingabe zurück.
  2. Gibt die Anzahl der Zeilen zurück, in denen expression als beliebiger Wert außer NULL ausgewertet wurde.

Unterstützte Argumenttypen

expression kann ein beliebiger Datentyp sein.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

INT64

Beispiele

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt die Anzahl der TRUE-Werte für expression zurück. Gibt 0 zurück, wenn keine Eingabezeilen vorhanden sind oder wenn expression für alle Zeilen als FALSE oder NULL ausgewertet wird.

Unterstützte Argumenttypen

BOOL

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

INT64

Beispiele

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt das logische AND aller Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird.

Unterstützte Argumenttypen

BOOL

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

BOOL

Beispiele

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt das logische OR aller Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird.

Unterstützte Argumenttypen

BOOL

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

BOOL

Beispiele

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt den höchsten Wert der Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird. Gibt NaN zurück, wenn die Eingabe einen NaN-Wert enthält.

Unterstützte Argumenttypen

Alle Datentypen außer: ARRAY STRUCT

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt den Mindestwert der Nicht-NULL-Ausdrücke zurück. Gibt NULL zurück, wenn keine Eingabezeilen vorhanden sind oder expression für alle Zeilen als NULL ausgewertet wird. Gibt NaN zurück, wenn die Eingabe einen NaN-Wert enthält.

Unterstützte Argumenttypen

Alle Datentypen außer: ARRAY STRUCT

Optionale Klausel

HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX und HAVING MIN-Klausel.

Rückgabedatentypen

Derselbe Datentyp, der als Eingabewert verwendet wird.

Beispiele

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt einen Wert (entweder STRING oder BYTES) zurück, der durch die Verkettung von Nicht-Null-Werten erlangt wird.

Wenn ein delimiter angegeben ist, werden verkettete Werte durch dieses Trennzeichen voneinander getrennt. Andernfalls wird ein Komma als Trennzeichen verwendet.

Unterstützte Argumenttypen

STRING BYTES

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Reihenfolge der Elemente in der Ausgabe

Die Reihenfolge der Elemente in der Ausgabe ist nicht deterministisch. Dies bedeutet, dass Sie bei jeder Nutzung dieser Funktion möglicherweise ein anderes Ergebnis erhalten.

Rückgabedatentypen

STRING BYTES

Beispiele

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Beschreibung

Gibt die Summe der Nicht-Null-Werte zurück.

Ist der Ausdruck ein Gleitkommawert, ist die Summe nicht-deterministisch, was bedeutet, dass Sie jedes Mal, wenn Sie diese Funktion nutzen, ein anderes Ergebnis erhalten.

Unterstützte Argumenttypen

Alle unterstützten numerischen Datentypen.

Optionale Klauseln

Die Klauseln werden in folgender Reihenfolge angewendet:

  1. DISTINCT: Jeder unterschiedliche Wert von expression fließt nur einmal in das Ergebnis ein.
  2. HAVING MAX oder HAVING MIN: Schränkt die Anzahl der Zeilen ein, die die Funktion durch einen Maximal- oder Minimalwert aggregiert. Weitere Informationen finden Sie unter HAVING MAX- und HAVING MIN-Klausel.

Rückgabedatentypen

  • Gibt INT64 zurück, wenn die Eingabe eine ganze Zahl ist.
  • Gibt NUMERIC zurück, wenn der Eingabetyp NUMERIC ist.
  • Gibt FLOAT64 zurück, wenn die Eingabe ein Gleitkommawert ist.

Gibt NULL zurück, wenn die Eingabe nur NULL-Werte enthält.

Gibt NULL zurück, wenn die Eingabe keine Zeilen enthält.

Gibt Inf zurück, wenn die Eingabe Inf enthält.

Gibt -Inf zurück, wenn die Eingabe -Inf enthält.

Gibt NaN zurück, wenn die Eingabe einen NaN-Wert enthält.

Gibt NaN zurück, wenn die Eingabe eine Kombination aus Inf und -Inf enthält.

Beispiele

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Allgemeine Klauseln

HAVING MAX und HAVING MIN-Klausel

Die meisten Aggregatfunktionen unterstützen zwei optionale Klauseln mit dem Namen HAVING MAX und HAVING MIN, die den Satz von Zeilen, die von einer Funktion zusammengefasst werden, auf Zeilen mit einem Höchst- oder Mindestwert in einer bestimmten Spalte einschränken. Die Syntax sieht im Allgemeinen so aus:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: Beschränkt den Satz von Zeilen, die von der Funktion zusammengefasst werden, auf einen Wert für expression2, der dem Höchstwert für expression2 innerhalb der Gruppe entspricht. Der Höchstwert entspricht dem Ergebnis von MAX(expression2).
  • HAVING MIN: Beschränkt den Satz von Zeilen, die von der Funktion zusammengefasst werden, auf einen Wert für expression2, der dem Mindestwert für expression2 innerhalb der Gruppe entspricht. Der Mindestwert entspricht dem Ergebnis von MIN(expression2).

Diese Klauseln ignorieren NULL-Werte, wenn der Höchst- oder Mindestwert berechnet wird, es sei denn, expression2 ergibt NULL für alle Zeilen.

Diese Klauseln unterstützen die folgenden Datentypen nicht: ARRAY STRUCT

Beispiel

In diesem Beispiel wird der durchschnittliche Niederschlag für das letzte Jahr 2001 zurückgegeben.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Zuerst ruft die Abfrage die Zeilen mit dem Höchstwert in der Spalte year ab. Es gibt zwei:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Schließlich ermittelt die Abfrage die Werte in der Spalte inches (9 und 1) mit diesem Ergebnis:

+---------+
| average |
+---------+
| 5       |
+---------+