Aggregatfunktionen¶
Aggregatfunktionen arbeiten mit Werten über Zeilen hinweg, um mathematische Berechnungen wie Summe, Durchschnitt, Zählung, Minimum-/Maxim-/Durchschnittswert, Standardabweichung und Schätzung sowie andere nicht mathematische Operationen durchzuführen.
Eine Aggregatfunktion nimmt mehrere Zeilen (tatsächlich keine, eine oder mehrere Zeilen) als Eingabe auf und erzeugt eine einzelne Ausgabe. Im Gegensatz dazu benötigen Skalarfunktionen eine Zeile als Eingabe und erzeugen eine Zeile (einen Wert) als Ausgabe.
Eine Aggregatfunktion gibt immer genau eine Zeile zurück, auch wenn die Eingabe null Zeilen enthält. Enthält die Eingabe null Zeilen, ist die Ausgabe NULL. Eine Aggregatfunktion könnte jedoch 0, eine leere Zeichenfolge oder einen anderen Wert zurückgeben, wenn Nullzeilen übergeben werden.
Unter diesem Thema:
Liste der Funktionen (nach Unterkategorie)¶
Funktionsname |
Anmerkungen |
|---|---|
Allgemeine Aggregation |
|
Verwendet eine andere Syntax als die anderen Aggregatfunktionen. |
|
Verwendet eine andere Syntax als die anderen Aggregatfunktionen. |
|
STDDEV und STDDEV_SAMP sind Aliasse. |
|
Alias für VAR_POP. |
|
Alias für VAR_SAMP. |
|
Bitweise Aggregation |
|
Boolesche Aggregation |
|
Hash |
|
Aggregation semistrukturierter Daten |
|
Lineare Regression |
|
Statistik und Wahrscheinlichkeit |
|
Zählen diskreter Werte |
|
Kardinalitätsschätzung . (mit HyperLogLog) |
|
Alias für HLL. |
|
Keine Aggregatfunktion; verwendet skalare Eingabe von HLL_ACCUMULATE oder HLL_COMBINE. |
|
Ähnlichkeitsschätzung . (mit MinHash) |
|
Alias für APPROXIMATE_SIMILARITY. |
|
Häufigkeitsschätzung . (mit Space-Saving) |
|
Keine Aggregatfunktion; verwendet skalare Eingabe von APPROX_TOP_K_ACCUMULATE oder APPROX_TOP_K_COMBINE. |
|
Perzentilschätzung . (mit t-Digest) |
|
Keine Aggregatfunktion; verwendet skalare Eingabe von APPROX_PERCENTILE_ACCUMULATE oder APPROX_PERCENTILE_COMBINE. |
|
Aggregations-Dienstprogramme |
|
Keine Aggregatfunktion, kann jedoch in Verbindung mit Aggregatfunktionen verwendet werden, um die Aggregationsebene für eine Zeile zu bestimmen, die von einer GROUP BY-Abfrage erstellt wird. |
|
Alias für GROUPING. |
Einführungsbeispiel¶
Das folgende Beispiel veranschaulicht den Unterschied zwischen einer Aggregatfunktion (AVG) und einer Skalarfunktion (COS) Die Skalarfunktion gibt eine Ausgabezeile für jede Eingabezeile zurück, während die Aggregatfunktion eine Ausgabezeile für mehrere Eingabezeilen zurückgibt:
Erstellen Sie eine Tabelle, und füllen Sie sie mit Werten auf:
CREATE TABLE simple (x INTEGER, y INTEGER);
INSERT INTO simple (x, y) VALUES
(10, 20),
(20, 44),
(30, 70);
Abfragen der Tabelle:
SELECT x, y
FROM simple
ORDER BY x,y;
+----+----+
| X | Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+
Die Skalarfunktion gibt für jede Eingabezeile eine Ausgabezeile zurück.
SELECT COS(x)
FROM simple
ORDER BY x;
+---------------+
| COS(X) |
|---------------|
| -0.8390715291 |
| 0.4080820618 |
| 0.1542514499 |
+---------------+
Die Aggregatfunktion gibt eine Ausgabezeile für mehrere Eingabezeilen zurück:
SELECT SUM(x)
FROM simple;
+--------+
| SUM(X) |
|--------|
| 60 |
+--------+
Aggregatfunktionen und NULL-Werte¶
Einige Aggregatfunktionen ignorieren NULL-Werte. Beispielsweise berechnet AVG den Durchschnitt der Werte 1, 5 und NULL mit dem Ergebnis 3, basierend auf der folgenden Formel:
(1 + 5) / 2 = 3
Sowohl im Zähler als auch im Nenner werden nur die beiden Nicht-NULL-Werte verwendet.
Wenn alle an die Aggregatfunktion übergebenen Werte NULL sind, gibt die Aggregatfunktion NULL zurück.
Einige Aggregatfunktionen können mehr als einer Spalte übergeben werden. Beispiel:
SELECT COUNT(col1, col2) FROM table1;
In diesen Fällen ignoriert die Aggregatfunktion eine Zeile, wenn eine einzelne Spalte NULL ist.
In der folgenden Abfrage gibt COUNT beispielsweise 1 zurück, nicht 4, da drei der vier Zeilen mindestens einen NULL-Wert in den ausgewählten Spalten enthalten:
Erstellen Sie eine Tabelle, und füllen Sie sie mit Werten auf:
CREATE OR REPLACE TABLE test_null_aggregate_functions (x INT, y INT);
INSERT INTO test_null_aggregate_functions (x, y) VALUES
(1, 2), -- No NULLs.
(3, NULL), -- One but not all columns are NULL.
(NULL, 6), -- One but not all columns are NULL.
(NULL, NULL); -- All columns are NULL.
Abfragen der Tabelle:
SELECT COUNT(x, y) FROM test_null_aggregate_functions;
+-------------+
| COUNT(X, Y) |
|-------------|
| 1 |
+-------------+
Wenn SUM mit einem Ausdruck aufgerufen wird, der auf zwei oder mehr Spalten verweist, und wenn eine oder mehrere dieser Spalten NULL sind, wird der Ausdruck zu NULL ausgewertet, und die Zeile wird ignoriert:
SELECT SUM(x + y) FROM test_null_aggregate_functions;
+------------+
| SUM(X + Y) |
|------------|
| 3 |
+------------+
Dieses Verhalten unterscheidet sich vom Verhalten von GROUP BY, bei dem Zeilen nicht gelöscht werden, wenn einige Spalten NULL sind:
SELECT x AS X_COL, y AS Y_COL
FROM test_null_aggregate_functions
GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
| 1 | 2 |
| 3 | NULL |
| NULL | 6 |
| NULL | NULL |
+-------+-------+