GROUPING SETS

GROUPING SETS, ROLLUP und CUBE können in der GROUP BY-Klausel verwendet werden, um in derselben Abfrage über mehrere Dimensionen zu gruppieren. Beachten Sie, dass diese Syntax nicht mit GROUP BY ALL kompatibel ist.

Beispiele

Berechnet das durchschnittliche Einkommen entlang der angegebenen vier Dimensionen:

-- the syntax () denotes the empty set (i.e., computing an ungrouped aggregate)
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ());

Berechnet das durchschnittliche Einkommen entlang derselben Dimensionen:

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY CUBE (city, street_name);

Berechnet das durchschnittliche Einkommen entlang der Dimensionen (city, street_name), (city) und ():

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY ROLLUP (city, street_name);

Beschreibung

GROUPING SETS führt dasselbe Aggregat über unterschiedliche GROUP BY-Klauseln in einer einzigen Abfrage aus.

CREATE TABLE students (course VARCHAR, type VARCHAR);
INSERT INTO students (course, type)
VALUES
    ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters'),
    ('CS', NULL), ('CS', NULL), ('Math', NULL);

SELECT course, type, count(*)
FROM students
GROUP BY GROUPING SETS ((course, type), course, type, ());

course	type	count_star()
Math	NULL	1
NULL	NULL	7
CS	PhD	1
CS	Bachelor	2
Math	Masters	1
CS	NULL	2
Math	NULL	2
CS	NULL	5
NULL	NULL	3
NULL	Masters	1
NULL	Bachelor	2
NULL	PhD	1

In der Abfrage oben gruppieren wir über vier unterschiedliche Mengen: course, type, course, type und () (die leere Gruppe). Das Ergebnis enthält NULL für eine Gruppe, die nicht in der Gruppierungsmenge des Ergebnisses liegt, d. h. die obige Abfrage entspricht der folgenden Anweisung aus UNION ALL-Klauseln:

-- Group by course, type:
SELECT course, type, count(*)
FROM students
GROUP BY course, type
UNION ALL
-- Group by type:
SELECT NULL AS course, type, count(*)
FROM students
GROUP BY type
UNION ALL
-- Group by course:
SELECT course, NULL AS type, count(*)
FROM students
GROUP BY course
UNION ALL
-- Group by nothing:
SELECT NULL AS course, NULL AS type, count(*)
FROM students;

CUBE und ROLLUP sind syntaktischer Zucker, um häufig verwendete Gruppierungsmengen einfach zu erzeugen.

Die ROLLUP-Klausel erzeugt alle „Untergruppen“ einer Gruppierungsmenge, z. B. erzeugt ROLLUP (country, city, zip) die Gruppierungsmengen (country, city, zip), (country, city), (country), (). Das kann nützlich sein, um unterschiedliche Detailebenen einer Group-by-Klausel zu erzeugen. Das erzeugt n+1 Gruppierungsmengen, wobei n die Anzahl der Terme in der ROLLUP-Klausel ist.

CUBE erzeugt Gruppierungsmengen für alle Kombinationen der Eingaben, z. B. erzeugt CUBE (country, city, zip) (country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), (). Das erzeugt 2^n Gruppierungsmengen.

Gruppierungsmengen mit GROUPING_ID() identifizieren

Die von GROUPING SETS, ROLLUP und CUBE erzeugten Super-Aggregatzeilen können oft an den für die jeweilige Spalte zurückgegebenen NULL-Werten erkannt werden. Können die in der Gruppierung verwendeten Spalten selbst echte NULL-Werte enthalten, ist es jedoch schwierig zu unterscheiden, ob der Wert im Ergebnis ein „echter“ NULL-Wert aus den Daten selbst ist oder ein von der Gruppierungskonstruktion erzeugter NULL-Wert. Die Funktion GROUPING_ID() bzw. GROUPING() dient dazu zu erkennen, welche Gruppen die Super-Aggregatzeilen im Ergebnis erzeugt haben.

GROUPING_ID() ist eine Aggregatfunktion, die die Spaltenausdrücke entgegennimmt, die die Gruppierung(en) bilden. Sie gibt einen BIGINT-Wert zurück. Der Rückgabewert ist 0 für Zeilen, die keine Super-Aggregatzeilen sind. Für Super-Aggregatzeilen gibt sie einen ganzzahligen Wert zurück, der die Kombination von Ausdrücken identifiziert, die die Gruppe bilden, für die das Super-Aggregat erzeugt wird. An dieser Stelle hilft ein Beispiel. Betrachten Sie die folgende Abfrage:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT y, q, m, GROUPING_ID(y, q, m) AS "grouping_id()"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

Das sind die Ergebnisse:

y	q	m	grouping_id()
2023	1	1	0
2023	1	2	0
2023	1	3	0
2023	1	NULL	1
2023	2	4	0
2023	2	5	0
2023	2	6	0
2023	2	NULL	1
2023	3	7	0
2023	3	8	0
2023	3	9	0
2023	3	NULL	1
2023	4	10	0
2023	4	11	0
2023	4	12	0
2023	4	NULL	1
2023	NULL	NULL	3
NULL	NULL	NULL	7

In diesem Beispiel ist die unterste Gruppierungsebene die Monatsebene, definiert durch die Gruppierungsmenge (y, q, m). Ergebniszeilen dieser Ebene sind einfach Aggregatzeilen, und die Funktion GROUPING_ID(y, q, m) gibt dafür 0 zurück. Die Gruppierungsmenge (y, q) erzeugt Super-Aggregatzeilen über der Monatsebene, lässt einen NULL-Wert für die Spalte m und GROUPING_ID(y, q, m) gibt 1 zurück. Die Gruppierungsmenge (y) erzeugt Super-Aggregatzeilen über der Quartalsebene, lässt NULL-Werte für die Spalten m und q, und GROUPING_ID(y, q, m) gibt 3 zurück. Schließlich erzeugt die Gruppierungsmenge () eine Super-Aggregatzeile für das gesamte Ergebnis, lässt NULL-Werte für y, q und m, und GROUPING_ID(y, q, m) gibt 7 zurück.

Um die Beziehung zwischen Rückgabewert und Gruppierungsmenge zu verstehen, können Sie sich vorstellen, dass GROUPING_ID(y, q, m) in ein Bitfeld schreibt, wobei das erste Bit dem letzten an GROUPING_ID() übergebenen Ausdruck entspricht, das zweite Bit dem vorletzten an GROUPING_ID() übergebenen Ausdruck usw. Das wird klarer, wenn Sie GROUPING_ID() nach BIT casten:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT
    y, q, m,
    GROUPING_ID(y, q, m) AS "grouping_id(y, q, m)",
    right(GROUPING_ID(y, q, m)::BIT::VARCHAR, 3) AS "y_q_m_bits"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

Das liefert diese Ergebnisse:

y	q	m	grouping_id(y, q, m)	y_q_m_bits
2023	1	1	0	000
2023	1	2	0	000
2023	1	3	0	000
2023	1	NULL	1	001
2023	2	4	0	000
2023	2	5	0	000
2023	2	6	0	000
2023	2	NULL	1	001
2023	3	7	0	000
2023	3	8	0	000
2023	3	9	0	000
2023	3	NULL	1	001
2023	4	10	0	000
2023	4	11	0	000
2023	4	12	0	000
2023	4	NULL	1	001
2023	NULL	NULL	3	011
NULL	NULL	NULL	7	111

Beachten Sie, dass die Anzahl der an GROUPING_ID() übergebenen Ausdrücke bzw. die Reihenfolge, in der sie übergeben werden, unabhängig von den tatsächlichen Gruppendefinitionen in der GROUPING SETS-Klausel ist (oder den durch ROLLUP und CUBE implizierten Gruppen). Solange die an GROUPING_ID() übergebenen Ausdrücke irgendwo in der GROUPING SETS-Klausel vorkommen, setzt GROUPING_ID() ein Bit an der Position des Ausdrucks, sobald dieser Ausdruck zu einem Super-Aggregat hochgerollt wird.

Syntax