GROUP BY-sats

Artikel
04/18/2024

Gäller för: markerad ja Databricks SQL Databricks Runtime

GROUP BY Satsen används för att gruppera raderna baserat på en uppsättning angivna grupperingsuttryck och beräkningsaggregeringar i gruppen med rader baserat på en eller flera angivna mängdfunktioner. Databricks SQL stöder även avancerade aggregeringar för att utföra flera aggregeringar för samma indatapostuppsättning via GROUPING SETS, CUBE, ROLLUP -satser. Grupperingsuttrycken och avancerade aggregeringar kan blandas i GROUP BY -satsen och kapslas i en GROUPING SETS -sats.

Mer information finns i avsnittet Mixed/Nested Grouping Analytics .

När en FILTER sats är kopplad till en aggregeringsfunktion skickas endast matchande rader till den funktionen.

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 [, ...] ] ) }

Aggregeringsfunktioner definieras som

aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]

Parametrar

ALL

Gäller för: Databricks SQL Databricks Runtime 12.2 LTS och senare

En kortfattad notation för att lägga till alla SELECT-list-uttryck som inte innehåller aggregerade funktioner som group_expressions. Om det inte finns GROUP BY ALL något sådant uttryck motsvarar utelämnande av GROUP BY satsen som resulterar i en global aggregering.

GROUP BY ALL är inte garanterat att skapa en uppsättning grupputtryck som kan matchas. Azure Databricks genererar UNRESOLVED_ALL_IN_GROUP_BY eller MISSING_AGGREGATION om den producerade satsen inte är väl utformad.
group_expression

Anger villkoren för att gruppera rader tillsammans. Gruppering av rader utförs baserat på resultatvärden för grupperingsuttrycken. Ett grupperingsuttryck kan vara ett kolumnnamn som GROUP BY a, kolumnposition som GROUP BY 0eller ett uttryck som GROUP BY a + b. Om group_expression innehåller en aggregeringsfunktion genererar Azure Databricks ett GROUP_BY_AGGREGATE fel.
grouping_set

En grupperingsuppsättning anges med noll eller fler kommaavgränsade uttryck inom parenteser. När grupperingsuppsättningen bara har ett element kan parenteser utelämnas. Är till exempel GROUPING SETS ((a), (b)) samma som GROUPING SETS (a, b).
GRUPPERINGSUPPSÄTTNINGAR

Grupperar raderna för varje grupperingsuppsättning som anges efter GROUPING SETS. Till exempel:

GROUP BY GROUPING SETS ((warehouse), (product)) är semantiskt likvärdigt med en union av resultat av GROUP BY warehouse och GROUP BY product.

Den här satsen är en förkortning för en UNION ALL där varje del av operatorn UNION ALL utför aggregering av varje grupperingsuppsättning som anges i GROUPING SETS -satsen.

GROUP BY GROUPING SETS ((warehouse, product), (product), ()) På samma sätt är det semantiskt likvärdigt med union av resultat av GROUP BY warehouse, product, GROUP BY product och en global aggregering.

Kommentar

För Hive-kompatibilitet tillåter GROUP BY ... GROUPING SETS (...)Databricks SQL . Uttrycken GROUP BY ignoreras vanligtvis, men om de innehåller extra uttryck utöver uttrycken GROUPING SETS inkluderas de extra uttrycken i grupperingsuttrycken och värdet är alltid null. Till exempel SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)är utdata för kolumn c alltid null.

SAMLAD

Anger flera nivåer av aggregeringar i en enda instruktion. Den här satsen används för att beräkna sammansättningar baserat på flera grupperingsuppsättningar. ROLLUP är en förkortning för GROUPING SETS. Till exempel:

GROUP BY warehouse, product WITH ROLLUP eller GROUP BY ROLLUP(warehouse, product) motsvarar

GROUP BY GROUPING SETS((warehouse, product), (warehouse), ()).

Medan GROUP BY ROLLUP(warehouse, product, (warehouse, location))

motsvarar GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

N-elementen i en ROLLUP specifikation resulterar i N+1 GROUPING SETS.
KUB

Satsen CUBE används för att utföra aggregeringar baserat på en kombination av grupperingskolumner som anges i GROUP BY -satsen. CUBE är en förkortning för GROUPING SETS. Till exempel:

GROUP BY warehouse, product WITH CUBE eller GROUP BY CUBE(warehouse, product) motsvarar

GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ()).

GROUP BY CUBE(warehouse, product, (warehouse, location)) motsvarar följande:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
N-elementen i en CUBE specifikation resulterar i 2^N GROUPING SETS.
aggregate_name

Ett samlingsfunktionsnamn (MIN, MAX, COUNT, SUM, AVG osv.).
DISTINKTA

Tar bort dubbletter i indatarader innan de skickas till aggregerade funktioner.
FILTER

Filtrerar de indatarader som boolean_expression i WHERE -satsen utvärderar till true skickas till den aggregerade funktionen. Andra rader ignoreras.

Analys av blandad/kapslad gruppering

En GROUP BY sats kan innehålla flera group_expressions och flera CUBE, ROLLUPoch GROUPING SETSs.

GROUPING SETS kan också ha kapslade CUBE, ROLLUPeller GROUPING SETS -satser. Till exempel:

GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))

CUBE och ROLLUP är bara syntaxsocker för GROUPING SETS. Se avsnitten ovan för hur du översätter CUBE och ROLLUP till GROUPING SETS. group_expression kan behandlas som en enskild grupp GROUPING SETS i den här kontexten.

För flera GROUPING SETS i GROUP BY -satsen genererar Databricks SQL en enda GROUPING SETS genom att göra en korsprodukt av den ursprungliga GROUPING SETS.

För kapslade GROUPING SETS i GROUPING SETS -satsen tar Databricks SQL sina grupperingsuppsättningar och tar bort dem. Till exempel följande frågor:

GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ());

GROUP BY warehouse, ROLLUP(product), CUBE(location, size);

motsvarar följande:

GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))

Medan GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))

motsvarar GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Exempel

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

GROUP BY-sats

Syntax

Parametrar

Analys av blandad/kapslad gruppering

Exempel

Relaterade artiklar

Ytterligare resurser