GROUP BY yan tümcesi

Şunlar için geçerlidir: Databricks SQL Databricks Runtime

GROUP BY yan tümcesi, belirli bir dizi gruplama ifadesine dayalı olarak satırları gruplandırır ve bir veya daha fazla belirtilen toplama işlevine göre bu gruplar üzerinde işlemler yaparak toplamaları hesaplar. Databricks SQL, GROUPING SETS, CUBE ve ROLLUP tümceleri aracılığıyla aynı giriş kaydı kümesi için birden çok toplama yapmak üzere gelişmiş toplamaları da destekler. Gruplandırma ifadeleri ve gelişmiş toplamalar GROUP BY tümcesinde karıştırılabilir ve bir GROUPING SETS yan tümcesi içinde iç içe yerleştirilebilir.

Karma/İç İçe Gruplandırma Analizi bölümünde diğer ayrıntılara bakın.

Bir FILTER yan tümce bir toplama işlevine eklendiğinde, bu işleve yalnızca eşleşen satırlar geçirilir.

Sözdizimi

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

Toplama işlevleri şu şekilde tanımlanır:

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

Parametreler

• TÜMÜ

  Şunlar için geçerlidir: Databricks SQL Databricks Runtime 12.2 LTS ve üzeri

  Toplama işlevlerini içermeyen tüm SELECTliste ifadelerini s olarak group_expressioneklemek için bir kısaltma gösterimi. Böyle bir ifade yoksa, GROUP BY ALL ifadesi GROUP BY cümlesinin atlanmasıyla eşdeğerdir ve bu, genel toplamayla sonuçlanır.

  GROUP BY ALL çözümlenebilen bir grup ifadeleri kümesi oluşturma garantisi verilmemektedir. Azure Databricks, üretilen yan tümce iyi biçimlendirilmemişse UNRESOLVED_ALL_IN_GROUP_BY veya MISSING_AGGREGATION oluşturur.

• group_expression

  Satırları birlikte gruplandırma ölçütlerini belirtir. Satır gruplandırması, gruplandırma ifadelerinin sonuç değerlerine göre gerçekleştirilir. Gruplandırma ifadesi, GROUP BY a gibi bir sütun adı, GROUP BY 0 gibi bir sütun konumu veya GROUP BY a + b gibi bir ifade olabilir. Bir konum aralık dışındaysa, Azure Databricks GROUP_BY_POS_OUT_OF_RANGE yükseltir. group_expression bir aggregate işlevi içeriyorsa Azure Databricks bir GROUP_BY_AGGREGATE hatası oluşturur. Konumsal başvuru bir toplama ifadesine çözümlenmişse, Azure Databricks GROUP_BY_POS_AGGREGATE oluşturur.

• gruplama_seti

  Gruplandırma kümesi, parantez içinde sıfır veya daha fazla virgülle ayrılmış ifadeyle belirtilir. Gruplandırma kümesinde yalnızca bir öğe olduğunda parantezler atlanabilir. Örneğin GROUPING SETS ((a), (b)) ile GROUPING SETS (a, b) aynıdır.

• Gruplandırma Kümeleri

  Belirtilen her gruplandırma kümesi için, GROUPING SETS sonrasında satırları gruplandırır. Örneğin:

  GROUP BY GROUPING SETS ((warehouse), (product)), GROUP BY warehouse ve GROUP BY product sonuçlarının bir birleşimine semantik olarak eşdeğerdir.

  Bu hüküm, UNION ALL operatörünün UNION ALL yan tümcesinde GROUPING SETS belirtilen her bir gruplandırma kümesini toplama işlemi gerçekleştirmesi için bir kısaltmadır.

  Benzer şekilde, GROUP BY GROUPING SETS ((warehouse, product), (product), ()), GROUP BY warehouse, product, GROUP BY product ve genel toplamın sonuçlarının birleşimine semantik olarak eşdeğerdir.

Not

Hive uyumluluğu için Databricks SQL izin verir GROUP BY ... GROUPING SETS (...). GROUP BY ifadeleri genellikle göz ardı edilir, ancak eğer GROUPING SETS ifadelerinin yanı sıra ek ifadeler içeriyorlarsa, bu ek ifadeler gruplandırma ifadelerine dahil edilir ve değer her zaman null olur. Örneğin, SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)içinde c sütununun çıkışı her zaman null'tır.

• TOPLAMA

  Tek bir deyimde birden çok toplama düzeyi belirtir. Bu yan tümce, birden çok gruplandırma kümesine göre toplamaları hesaplamak için kullanılır. ROLLUP , için GROUPING SETSbir kısaltmadır. Örneğin:

  GROUP BY warehouse, product WITH ROLLUP
  

  veya

  GROUP BY ROLLUP(warehouse, product)
  

  eşdeğerdir

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

  İken

  GROUP BY ROLLUP(warehouse, product, location))
  

  eşdeğerdir

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

  Bir belirtimin ROLLUP N öğeleri N+1 GROUPING SETSile sonuçlanır.

• KÜP

  CUBE cümlesi, GROUP BY cümlesinde belirtilen gruplandırma sütunlarının birleşimine göre toplama işlemleri gerçekleştirmek için kullanılır. CUBE , için GROUPING SETSbir kısaltmadır. Örneğin:

  GROUP BY warehouse, product WITH CUBE
  

  veya

  GROUP BY CUBE(warehouse, product)
  

  eşdeğerdir:

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

  İken

  GROUP BY CUBE(warehouse, product, location)
  

  aşağıdakilere eşdeğerdir:

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

  Belirtimin CUBE N öğeleri 2^N GROUPING SETSsonucunu döndürür.

• aggregate_name

  Birleştirme işlevi adı (MIN, MAX, COUNT, SUM, AVG vb.).

• FARKLI

  Toplama işlevlerine geçirilmeden önce giriş satırlarındaki yinelemeleri kaldırır.

• FİLTRE

  boolean_expression içindeki WHERE ifadesi true olarak değerlendirilen giriş satırları filtrelenir ve topluluk işlevine geçirilir; diğer satırlar ise atılır.

Karışık/İç İçe Gruplama Analizi

GROUP BY yan tümcesi birden çok group_expressions ve birden çok CUBE, ROLLUPve GROUPING SETSiçerebilir.

GROUPING SETS iç içe CUBE, ROLLUPveya GROUPING SETS yan tümceleri de olabilir. Örneğin:

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

CUBE ve ROLLUP, yalnızca GROUPING SETS için bir sözdizim şekeri gibidir.

Nasıl CUBE ve ROLLUP'i GROUPING SETS'ye çevireceğinizi öğrenmek için lütfen yukarıdaki bölümlere bakın.

group_expression bu bağlamda tek grup GROUPING SETS olarak ele alınabilir.

Yan tümcesindeki birden çok GROUPING SETS için, Databricks SQL özgün GROUP BYlerin çapraz çarpımını yaparak tek bir GROUPING SETS oluşturur.

Databricks SQL yan tümcesinde iç içe yerleştirilmiş gruplandırma kümelerini alır ve bunları kaldırır. Örneğin, aşağıdaki sorgular:

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

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

aşağıdakilere eşdeğerdir:

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

İken

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

eşdeğerdir

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

Yaygın hata koşulları

• 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

Örnekler

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

İlgili makaleler

• HAVING
• QUALIFY
• SELECT