clausola GROUP BY

Articolo
01/22/2025

Si applica a: segno di spunta sì Databricks SQL Databricks Runtime

La clausola GROUP BY viene usata per raggruppare le righe in base a un set di espressioni di raggruppamento e per calcolare aggregazioni sul gruppo di righe in base a una o più funzioni di aggregazione specificate. Databricks SQL supporta anche aggregazioni avanzate per eseguire più aggregazioni per lo stesso record di input set tramite GROUPING SETS, CUBE, ROLLUP clausole. Le espressioni di raggruppamento e le aggregazioni avanzate possono essere miste nella GROUP BY clausola e annidate in una GROUPING SETS clausola .

Per altri dettagli, vedere la sezione Analisi raggruppamenti misti/annidati.

Quando una FILTER clausola è associata a una funzione di aggregazione, solo le righe corrispondenti vengono passate a tale funzione.

Sintassi

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

Mentre le funzioni di aggregazione sono definite come

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

Parameters

ALL

Si applica a: Databricks SQL Databricks Runtime 12.2 LTS e versioni successive

Notazione abbreviata per aggiungere tutte le SELECT:list espressioni che non contengono funzioni di aggregazione come group_expressions. Se non esiste GROUP BY ALL alcuna espressione di questo tipo equivale a omettere la GROUP BY clausola che genera un'aggregazione globale.

GROUP BY ALL non è garantito produrre un set di espressioni di gruppo che possano essere risolte. Azure Databricks genera UNRESOLVED_ALL_IN_GROUP_BY o MISSING_AGGREGATION se la clausola prodotta non è ben formata.
group_expression

Specifica i criteri per raggruppare le righe. Il raggruppamento delle righe viene eseguito in base al risultato values delle espressioni di raggruppamento. Un'espressione di raggruppamento può essere un nome column come GROUP BY a, una posizione column come GROUP BY 0o un'espressione come GROUP BY a + b. Se group_expression contiene una funzione di aggregazione Azure Databricks genera un errore di GROUP_BY_AGGREGATE .
grouping_set

Un raggruppamento set viene specificato da zero o più espressioni separate da virgole tra parentesi. Quando il raggruppamento set ha un solo elemento, è possibile omettere le parentesi. Ad esempio, GROUPING SETS ((a), (b)) è identico a GROUPING SETS (a, b).
SET DI RAGGRUPPAMENTO

Raggruppa le righe per ogni raggruppamento di set specificato dopo GROUPING SETS. Ad esempio:

GROUP BY GROUPING SETS ((warehouse), (product)) è semanticamente equivalente a un'unione di risultati di GROUP BY warehouse e GROUP BY product.

Questa clausola è una sintassi abbreviata per un UNION ALLwhere ogni segmento dell'operatore UNION ALL esegue l'aggregazione di ogni set di raggruppamento specificato nella clausola GROUPING SETS.

Analogamente, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) è semanticamente equivalente all'unione dei risultati di GROUP BY warehouse, productGROUP BY producte a un'aggregazione globale.

Nota

Per la compatibilità di Hive, Databricks SQL consente GROUP BY ... GROUPING SETS (...). Le GROUP BY espressioni vengono in genere ignorate, ma se contengono espressioni aggiuntive oltre alle GROUPING SETS espressioni, le espressioni aggiuntive verranno incluse nelle espressioni di raggruppamento e il valore è sempre Null. Ad esempio, in SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b), l'output di column c è sempre null.

ROLLUP

Specifica più livelli di aggregazioni in una singola istruzione. Questa clausola viene usata per calcolare le aggregazioni in base a più set di raggruppamento. ROLLUP è una sintassi abbreviata per GROUPING SETS. Ad esempio:

GROUP BY warehouse, product WITH ROLLUP

GROUP BY ROLLUP(warehouse, product)

equivale a

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

Mentre

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

equivale a

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

Gli N elementi di una ROLLUP specifica generano N+1 GROUPING SETS.

CUBO

La clausola CUBE viene usata per eseguire aggregazioni in base a una combinazione di raggruppamento columns specificata nella clausola GROUP BY. CUBE è una sintassi abbreviata per GROUPING SETS. Ad esempio:

GROUP BY warehouse, product WITH CUBE

GROUP BY CUBE(warehouse, product)

equivale a:

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

Mentre

GROUP BY CUBE(warehouse, product, location)

equivale a quanto segue:

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

Gli N elementi di una CUBE specifica generano 2^N GROUPING SETS.

aggregate_name

Nome di funzione di aggregazione (MIN, MAX, COUNT, SUM, AVG e così via).
DISTINCT

Rimuove i duplicati nelle righe di input prima che vengano passati alle funzioni di aggregazione.
FILTER

Filtra le righe di input per le quali nella boolean_expressionWHERE clausola restituisce true vengono passate alla funzione di aggregazione. Le altre righe vengono eliminate.

Analisi raggruppamento misto/annidato

Una clausola GROUP BY può includere più group_expressions e più CUBE, ROLLUPe GROUPING SETS.

GROUPING SETS può anche avere clausole annidate CUBE, ROLLUP, o GROUPING SETS . Ad esempio:

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

CUBE ed ROLLUP è solo lo zucchero sintassi per GROUPING SETS.

Per informazioni su come tradurre CUBE e ROLLUP in GROUPING SETS, vedere le sezioni precedenti.

group_expression può essere considerato come un singolo gruppo GROUPING SETS in questo contesto.

Per più GROUPING SETS nella GROUP BY clausola , Databricks SQL genera un singolo GROUPING SETS oggetto eseguendo un prodotto incrociato dell'originale GROUPING SETS.

Per annidato GROUPING SETS nella GROUPING SETS clausola , Databricks SQL accetta i set di raggruppamenti e li rimuove. Ad esempio, le query seguenti:

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

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

sono equivalenti ai seguenti:

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

Mentre

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

equivale a

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

Esempi

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

Condividi tramite

clausola GROUP BY

Sintassi

Parameters

Analisi raggruppamento misto/annidato

Esempi

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

clausola GROUP BY

Sintassi

Parameters

Analisi raggruppamento misto/annidato

Esempi

Articoli correlati

Commenti e suggerimenti

Risorse aggiuntive