klausa GROUP BY

Berlaku untuk: Databricks SQL Databricks Runtime

Klausa GROUP BY digunakan untuk mengelompokkan baris berdasarkan sekumpulan ekspresi pengelompokan dan agregasi komputasi yang ditentukan pada grup baris berdasarkan satu atau beberapa fungsi agregat yang ditentukan. Databricks SQL juga mendukung agregasi tingkat lanjut untuk melakukan beberapa agregasi untuk kumpulan catatan input yang sama melalui klausa GROUPING SETS, CUBE, ROLLUP. Ekspresi pengelompokan dan agregasi tingkat lanjut dapat dicampur di dalam klausul GROUP BY dan disarangkan di 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-parameternya

• ALL

  Berlaku untuk: Databricks SQL Databricks Runtime 12.2 LTS ke atas

  Notasi singkat untuk menambahkan semua ekspresi dalam daftar SELECTyang tidak berisi fungsi agregat sebagai group_expression. Jika tidak ada ekspresi semacam itu, GROUP BY ALL adalah setara dengan menghilangkan klausa GROUP BY, yang menyebabkan terjadinya agregasi global.

  GROUP BY ALL tidak dijamin untuk menghasilkan sekumpulan ekspresi grup yang dapat diselesaikan. Azure Databricks meningkatkan 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 posisi berada di luar rentang, Azure Databricks menaikkan GROUP_BY_POS_OUT_OF_RANGE. Jika group_expression berisi fungsi aggregate Azure Databricks menimbulkan kesalahan GROUP_BY_AGGREGATE. Jika referensi posisi diselesaikan ke ekspresi agregat, Azure Databricks menaikkan GROUP_BY_POS_AGGREGATE.

• grouping_set

  Kumpulan pengelompokan ditentukan oleh nol atau lebih ekspresi yang dipisahkan koma dalam tanda kurung. Ketika kumpulan 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 kumpulan 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.

  Klausa ini adalah singkatan dari UNION ALL di mana setiap komponen operator UNION ALL melakukan agregasi dari setiap set pengelompokan yang ditentukan dalam klausa 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, dalam 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
  

  atau

  GROUP BY ROLLUP(warehouse, product)
  

  setara dengan

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

  Sedangkan

  GROUP BY ROLLUP(warehouse, product, location))
  

  setara dengan

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

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

• KUBUS

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

  GROUP BY warehouse, product WITH CUBE
  

  atau

  GROUP BY CUBE(warehouse, product)
  

  setara dengan:

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

  Sedangkan

  GROUP BY CUBE(warehouse, product, location)
  

  setara dengan yang berikut ini:

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

  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

  Baris input yang menghasilkan nilai benar untuk boolean_expression dalam klausul WHERE diteruskan ke fungsi agregat; baris lainnya dibuang.

Analisis Pengelompokan Campuran dan Bersarang

Klausa 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 hanyalah sintaks pemanis 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 perkalian silang dari yang asli GROUPING SETS.

Untuk GROUPING SETS yang bersarang dalam klausa GROUPING SETS, Databricks SQL mengambil set pengelompokan 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))`.

Kondisi kesalahan umum

• GROUP_BY_AGGREGATE
• GROUP_BY_POS_AGGREGATE
• GROUP_BY_POS_OUT_OF_RANGE
• GROUPING_COLUMN_MISMATCH
• GROUPING_ID_COLUMN_MISMATCH
• GROUPING_SIZE_LIMIT_EXCEEDED
• MISSING_AGGREGATION
• MISSING_GROUP_BY
• STAR_GROUP_BY_POS
• UNRESOLVED_ALL_IN_GROUP_BY
• UNSUPPORTED_GROUPING_EXPRESSION

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

-- Aggregate function in `GROUP BY` is not allowed.
> SELECT id FROM dealer GROUP BY sum(quantity);
  Error: GROUP_BY_AGGREGATE

-- Column not in `GROUP BY` and not aggregated.
> SELECT city, car_model FROM dealer GROUP BY city;
  Error: MISSING_AGGREGATION

-- Positional reference out of range.
> SELECT city FROM dealer GROUP BY 2;
  Error: GROUP_BY_POS_OUT_OF_RANGE

Artikel terkait

• HAVING
• QUALIFY
• SELECT