GROUP BY záradék
A következőkre vonatkozik: Databricks SQL Databricks Runtime
A GROUP BY
záradék a sorok csoportosítására szolgál megadott csoportosítási kifejezések és számítási összesítések alapján a sorok csoportjában egy vagy több megadott összesítő függvény alapján.
A Databricks SQL speciális aggregációkat is támogat, amelyek több aggregációt is végeznek ugyanahhoz a bemeneti rekordhoz GROUPING SETS
, CUBE
ROLLUP
záradékokkal.
A csoportosítási kifejezések és a speciális aggregációk keverhetők a GROUP BY
záradékba, és beágyazhatók egy GROUPING SETS
záradékba.
További részletekért tekintse meg a Vegyes/beágyazott csoportosítási elemzés szakaszt.
Ha egy záradékot FILTER
aggregátumfüggvényhez csatol, a függvény csak az egyező sorokat továbbítja a függvénynek.
Syntax
GROUP BY ALL
GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]
GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]
grouping_set
{ expression |
( [ expression [, ...] ] ) }
Míg az összesítő függvények a következőként vannak definiálva:
aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]
Paraméterek
ALL (összes)
A következőkre vonatkozik: Databricks SQL Databricks Runtime 12.2 LTS és újabb
Rövidített jelölés az összes
SELECT
olyan -list kifejezés hozzáadásához, amely nem tartalmazza az összesítő függvényeket s-kéntgroup_expression
. Ha ilyen kifejezés nem létezikGROUP BY ALL
, az egyenértékű aGROUP BY
globális összesítést eredményező záradék kihagyásával.GROUP BY ALL
nem garantált, hogy feloldható csoportkifejezések készletét hozza létre. Az Azure Databricks UNRESOLVED_ALL_IN_GROUP_BY vagy MISSING_AGGREGATION , ha a létrehozott záradék nem megfelelően formázott.group_expression
Megadja a sorok csoportosításának feltételeit. A sorok csoportosítása a csoportosítási kifejezések eredményértékei alapján történik. A csoportosítási kifejezés lehet oszlopnév, például
GROUP BY a
oszloppozícióGROUP BY 0
vagy hasonlóGROUP BY a + b
kifejezés. Hagroup_expression
aggregátumfüggvényt tartalmaz, az Azure Databricks GROUP_BY_AGGREGATE hibát okoz.grouping_set
A csoportosítási csoportokat zárójelben nulla vagy több vesszővel tagolt kifejezés határozza meg. Ha a csoportosítási csoportnak csak egy eleme van, a zárójelek kihagyhatók. Például ugyanaz,
GROUPING SETS ((a), (b))
mintGROUPING SETS (a, b)
a .CSOPORTOSÍTÁSI Standard kiadás TS
Csoportokba rendezi az egyes csoportosítási csoportok
GROUPING SETS
sorait. Példa:GROUP BY GROUPING SETS ((warehouse), (product))
szemantikailag egyenértékű az eredményekGROUP BY warehouse
ésGROUP BY product
a .Ez a záradék egy olyan rövidítés,
UNION ALL
amelyben azUNION ALL
operátor minden egyes lába a záradékbanGROUPING SETS
megadott csoportosítási csoportok összesítését végzi.Hasonlóképpen
GROUP BY GROUPING SETS ((warehouse, product), (product), ())
szemantikailag egyenértékű az eredmények és a globális aggregátumok egyesítésévelGROUP BY warehouse, product
GROUP BY product
.
Feljegyzés
A Hive kompatibilitása érdekében a Databricks SQL lehetővé teszi GROUP BY ... GROUPING SETS (...)
. A GROUP BY
kifejezéseket általában figyelmen kívül hagyják, de ha a GROUPING SETS
kifejezéseken kívül további kifejezéseket is tartalmaznak, a további kifejezéseket a csoportosítási kifejezések tartalmazzák, és az érték mindig null. A c oszlop kimenete például SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)
mindig null.
ÖSSZESÍTŐ
Az összesítések több szintjét adja meg egyetlen utasításban. Ez a záradék több csoportosítási csoporton alapuló összesítések kiszámítására szolgál.
ROLLUP
rövidítés a következőhözGROUPING SETS
: . Példa:GROUP BY warehouse, product WITH ROLLUP
vagyGROUP BY ROLLUP(warehouse, product)
egyenértékű aGROUP BY GROUPING SETS((warehouse, product), (warehouse), ())
.Míg
GROUP BY ROLLUP(warehouse, product, (warehouse, location))
egyenértékű a .-nak.
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ())
A specifikáció N elemei
ROLLUP
N+1GROUPING SETS
eredményt adnak.KOCKA
A
CUBE
záradék a záradékbanGROUP BY
megadott csoportosítási oszlopok kombinációján alapuló összesítések végrehajtására szolgál.CUBE
rövidítés a következőhözGROUPING SETS
: . Példa:GROUP BY warehouse, product WITH CUBE
vagyGROUP BY CUBE(warehouse, product)
egyenértékű aGROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ())
.GROUP BY CUBE(warehouse, product, (warehouse, location))
egyenértékű a következő értékével:GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
A specifikáció N elemei
CUBE
2^NGROUPING SETS
eredményt adnak.aggregate_name
Összesítő függvény neve (MIN, MAX, DARAB, SZUM, AVG stb.).
KÜLÖNBÖZŐ
Eltávolítja az ismétlődő elemeket a bemeneti sorokból, mielőtt azokat aggregátumfüggvényekbe továbbítanák.
SZŰRŐ
Szűri azokat a bemeneti sorokat, amelyek esetében a
boolean_expression
WHERE
záradék igaz értéket ad át az összesítő függvénynek, a többi sor elvetve.
Vegyes/beágyazott csoportosítási elemzés
A GROUP BY
záradék tartalmazhat több group_expressions és több CUBE
, ROLLUP
és GROUPING SETS
s.
GROUPING SETS
lehetnek beágyazott CUBE
, ROLLUP
vagy GROUPING SETS
záradékok is. Példa:
GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))
CUBE
és ROLLUP
csak szintaxis cukor .GROUPING SETS
A fordításhoz és a fordításhoz CUBE
tekintse meg a fenti szakaszokatGROUPING SETS
.ROLLUP
group_expression
ebben a kontextusban egyetlen csoportként GROUPING SETS
kezelhető.
A záradék több GROUPING SETS
esetében a GROUP BY
Databricks SQL egyetlen GROUPING SETS
példányt hoz létre az eredeti GROUPING SETS
kereszttermékkel.
A záradékba GROUPING SETS
ágyazott GROUPING SETS
Databricks SQL felveszi a csoportosítási csoportokat, és csíkozza őket. Például a következő lekérdezések:
GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ());
GROUP BY warehouse, ROLLUP(product), CUBE(location, size);
egyenértékűek az alábbiakval:
GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))
Míg GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))
egyenértékű a .-nak.GROUP BY GROUPING SETS((warehouse), (warehouse, product))
Példák
CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
VALUES (100, 'Fremont', 'Honda Civic', 10),
(100, 'Fremont', 'Honda Accord', 15),
(100, 'Fremont', 'Honda CRV', 7),
(200, 'Dublin', 'Honda Civic', 20),
(200, 'Dublin', 'Honda Accord', 10),
(200, 'Dublin', 'Honda CRV', 3),
(300, 'San Jose', 'Honda Civic', 5),
(300, 'San Jose', 'Honda Accord', 8);
-- Sum of quantity per dealership. Group by `id`.
> SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
id sum(quantity)
--- -------------
100 32
200 33
300 13
-- Use column position in GROUP by clause.
> SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
id sum(quantity)
--- -------------
100 32
200 33
300 13
-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
> SELECT id, sum(quantity) AS sum, max(quantity) AS max
FROM dealer GROUP BY id ORDER BY id;
id sum max
--- --- ---
100 32 15
200 33 20
300 13 8
-- Count the number of distinct dealers in cities per car_model.
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
car_model count
------------ -----
Honda Civic 3
Honda CRV 2
Honda Accord 3
-- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;
car_model count
------------ -----
Honda Civic 3
Honda CRV 2
Honda Accord 3
-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
> SELECT id,
sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
FROM dealer
GROUP BY id ORDER BY id;
id sum(quantity)
--- -------------
100 17
200 23
300 5
-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
city car_model sum
--------- ------------ ---
null null 78
null HondaAccord 33
null HondaCRV 10
null HondaCivic 35
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY city, car_model WITH ROLLUP
ORDER BY city, car_model;
city car_model sum
--------- ------------ ---
null null 78
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY city, car_model WITH CUBE
ORDER BY city, car_model;
city car_model sum
--------- ------------ ---
null null 78
null HondaAccord 33
null HondaCRV 10
null HondaCivic 35
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
--Prepare data for ignore nulls example
> CREATE TEMP VIEW person (id, name, age) AS
VALUES (100, 'Mary', NULL),
(200, 'John', 30),
(300, 'Mike', 80),
(400, 'Dan' , 50);
--Select the first row in column age
> SELECT FIRST(age) FROM person;
first(age, false)
--------------------
NULL
--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.
> SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
first(age, true) last(id, false) sum(id)
------------------- ------------------ ----------
30 400 1000