klausa GROUP BY

Artikel
04/18/2024

Berlaku untuk: centang ditandai ya Databricks SQL Databricks Runtime

Klausul GROUP BY digunakan untuk mengelompokkan baris berdasarkan sekumpulan ekspresi pengelompokan tertentu dan agregasi komputasi pada grup baris berdasarkan satu atau beberapa fungsi agregat yang ditentukan. Databricks SQL juga mendukung agregasi lanjutan untuk melakukan beberapa agregasi untuk kumpulan rekaman masukan yang sama melalui klausul GROUPING SETS, CUBE, ROLLUP. Ekspresi pengelompokan dan agregasi lanjutan dapat dicampur dalam klausul GROUP BY dan bersarang dalam klausul GROUPING SETS.

Lihat detail selengkapnya di bagian Analisis Pengelompokan Campuran/Berlapis.

Jika klausul FILTER dilampirkan ke fungsi agregat, hanya baris yang cocok yang diteruskan ke fungsi tersebut.

Sintaks

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

Sementara fungsi agregat didefinisikan sebagai

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

Parameter

ALL

Berlaku untuk: Databricks SQL Databricks Runtime 12.2 LTS ke atas

Notasi singkat untuk menambahkan semua SELECTekspresi -list yang tidak berisi fungsi agregat sebagai group_expression. Jika tidak ada GROUP BY ALL ekspresi seperti itu yang setara dengan menghilangkan GROUP BY klausa yang menghasilkan agregasi global.

GROUP BY ALL tidak dijamin untuk menghasilkan sekumpulan ekspresi grup yang dapat diselesaikan. Azure Databricks menaikkan UNRESOLVED_ALL_IN_GROUP_BY atau MISSING_AGGREGATION jika klausul yang dihasilkan tidak terbentuk dengan baik.
group_expression

Menentukan kriteria untuk mengelompokkan baris bersama-sama. Pengelompokan baris dilakukan berdasarkan nilai hasil ekspresi pengelompokan. Ekspresi pengelompokan mungkin nama kolom seperti GROUP BY a, posisi kolom seperti GROUP BY 0, atau ekspresi seperti GROUP BY a + b. Jika group_expression berisi fungsi agregat Azure Databricks menimbulkan kesalahan GROUP_BY_AGGREGATE .
grouping_set

Kumpulan pengelompokan ditentukan oleh null atau lebih ekspresi yang dipisahkan koma dalam tanda kurung. Ketika set pengelompokan hanya memiliki satu elemen, tanda kurung dapat dihilangkan. Misalnya, GROUPING SETS ((a), (b)) sama dengan GROUPING SETS (a, b).
KUMPULAN PENGELOMPOKAN

Mengelompokkan baris untuk setiap set pengelompokan yang ditentukan setelah GROUPING SETS. Contohnya:

GROUP BY GROUPING SETS ((warehouse), (product)) secara semantik setara dengan penyatuan hasil GROUP BY warehouse dan GROUP BY product.

Klausul ini adalah singkatan untuk UNION ALL tempat setiap kaki operator UNION ALL melakukan agregasi dari setiap set pengelompokan yang ditentukan dalam klausul GROUPING SETS.

Demikian pula, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) secara semantik setara dengan penyatuan hasil GROUP BY warehouse, product, GROUP BY product dan agregat global.

Catatan

Untuk kompatibilitas Apache Hive, Databricks SQL memungkinkan GROUP BY ... GROUPING SETS (...). Ekspresi GROUP BY biasanya diabaikan, tetapi jika mengandung ekspresi tambahan selain ekspresi GROUPING SETS, ekspresi tambahan akan dimasukkan dalam ekspresi pengelompokan dan nilainya selalu nol. Misalnya, SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b), output kolom c selalu null.

ROLLUP

Menentukan beberapa tingkat agregasi dalam satu pernyataan. Klausa ini digunakan untuk menghitung agregasi berdasarkan beberapa set pengelompokan. ROLLUP adalah singkatan dari GROUPING SETS. Contohnya:

GROUP BY warehouse, product WITH ROLLUP setara dengan GROUP BY ROLLUP(warehouse, product)

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

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

setara dengan GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

Elemen N dari spesifikasi ROLLUP menghasilkan N + 1 GROUPING SETS.
KUBUS

Klausul CUBE digunakan untuk melakukan agregasi berdasarkan kombinasi kolom pengelompokan yang ditentukan dalam klausul GROUP BY. CUBE adalah singkatan dari GROUPING SETS. Contohnya:

GROUP BY warehouse, product WITH CUBE setara dengan GROUP BY CUBE(warehouse, product)

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

GROUP BY CUBE(warehouse, product, (warehouse, location)) setara dengan yang berikut ini:
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Elemen N dari spesifikasi CUBE menghasilkan 2^N GROUPING SETS.
aggregate_name

Nama fungsi agregat (MIN, MAX, COUNT, SUM, AVG, dll.).
DISTINCT

Menghapus duplikat dalam baris input sebelum diteruskan ke fungsi agregat.
FILTER

Memfilter baris input yang boolean_expression dievaluasi dalam klausul WHERE menjadi benar diteruskan ke fungsi agregat; baris lain dibuang.

Analisis Pengelompokan Campuran/Bersarang

Klausul GROUP BY dapat mencakup beberapa group_expressions dan beberapa CUBE, ROLLUP, dan GROUPING SETS.

GROUPING SETS juga dapat berlapis klausul CUBE, ROLLUP, atau GROUPING SETS. Contohnya:

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

CUBE dan ROLLUP hanya sintaks gula untuk GROUPING SETS. Silakan merujuk ke bagian di atas untuk cara menerjemahkan CUBE dan ROLLUP ke GROUPING SETS. group_expression dapat diperlakukan sebagai kelompok tunggal GROUPING SETS dalam konteks ini.

Untuk beberapa GROUPING SETS dalam klausul GROUP BY, Databricks SQL menghasilkan satu GROUPING SETS dengan melakukan cross-product dari aslinya GROUPING SETS.

Untuk berlapis GROUPING SETS dalam GROUPING SETS klausul, Databricks SQL mengambil set pengelompokannya dan menghapusnya. Misalnya, kueri berikut:

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

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

setara dengan yang berikut ini:

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

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

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

Contoh

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

Bagikan melalui

klausa GROUP BY

Sintaks

Parameter

Analisis Pengelompokan Campuran/Bersarang

Contoh

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

klausa GROUP BY

Sintaks

Parameter

Analisis Pengelompokan Campuran/Bersarang

Contoh

Artikel terkait

Saran dan Komentar

Sumber Daya Tambahan: