GROUP BY-спецификация

Функция

Задает сгруппированную таблицу, которая получается путем применения < спецификации группировки > к результату предшествующей < FROM-спецификации >.

Спецификация

         
< GROUP BY-спецификация >::=
< группировка со сведением данных >::=
ROLLUP | CUBE ( элемент группировки [, …])
< группировка по заданным группам >::=
GROUPING SETS (( группа ) [, …])

Синтаксические правила

  1. Каждая < спецификация группировки > в < GROUP BY-спецификации > должна однозначно указывать на < имя столбца > или < значимое выражение > (их синонимы не допустимы), получаемое в результате выполнения предшествующей < FROM-спецификации > или < WHERE-спецификации >.

    select make from auto where color='BLACK' group by make;
    SELECT name, make, MAX(cylnders)
      FROM auto, person
     WHERE auto.personid=person.personid
     GROUP BY name, make;
    
    select year+1900, model
      from auto group by year,model;
  2. Опция PK задает группировку по первичному ключу таблицы. Это позволяет исключить повторение всех неагрегируемых столбцов записей выборки данных из конструкции GROUP BY.

    Например, если в конструкции GROUP BY col1, col2, col3… столбец col1 является первичным ключом, то столбцы col2, col3… при группировке уже не учитываются, поэтому их можно заменить конструкцией PK.

    Допускается синтаксис < имя таблицы >.PK, так и просто PK для случая одной таблицы.

    create or replace table test (id int primary key, salary int, dept int);
    
    insert into test values(1, 10, 1);
    insert into test values(2, 15, 1);
    insert into test values(3, 15, 2);
    insert into test values(4, 20, 2);
    insert into test values(5, 20, 3);

    Нижеследующие запросы эквивалентны:

    select id, salary*1000, 'department #' + to_char(dept)
      from  test
     group by id,salary,dept
    having dept <  3;
    
    select id, salary*1000, 'department #' + to_char(dept)
      from test group by   pk
    having   dept <  3;
  3. Если в таблице есть и первичный ключ, и столбец с именем "PK", то группировка GROUP BY PK будет производиться по первичному ключу.

  4. В < значимом выражении > < спецификации группировки >, соответствующем GROUP BY, не должен содержаться < подзапрос >. Можно использовать конструкции < спецификация значения по условию > (CASE), < спецификация типа > (CAST), знаки операций, скалярные функции и логические выражения.

    Примеры.
    а)
    create or replace table tst (i int, b boolean);
    insert into tst values (1,TRUE);
    insert into tst values (2,FALSE);
    insert into tst values (1,TRUE);
    insert into tst values (1,TRUE);
    
    select i, count(b) from tst group by i,2 >1;
     I
     -
    |          1|          3|
    |          2|          1|
    
    б) Определить название отдела в зависимости от его номера и сгруппировать полученные данные по названию отдела:
    select dept_id, case dept_id when 1 then 'testing'
      when 2 then 'development'
      when 3 then 'directing'
      when 4 then 'management'
      else 'common department' end as name_of_department
      from tab_aggr
     group by dept_id, case dept_id when 1 then 'testing'
                       when 2 then 'development'
                       when 3 then 'directing'
                       when 4 then 'management'
                       else 'common department' end;
    
    Результат выполнения примера:
     DEPT_ID     NAME_OF_DEPARTMENT
     -------     ------------------
    |          1|testing           |
    |          2|development       |
    |          3|directing         |
    |          4|management        |
    |          5|common department |
    |          6|common department |
    |          7|common department |
  5. В < списке выборки > можно указывать целиком < значимое выражение >, по которому делается группировка, или другие столбцы, используемые как аргументы агрегатных функций.

    select round(weight/1000) as "Вес в тоннах", model
      from auto group by round(weight/1000), model;
  6. Пользовательскую функцию, содержащую внутри себя < запросы выборки >, можно не указывать в < спецификации группировки >.

    create or replace table t1( i int, j int );
    insert into t1 values(1,4);
    insert into t1 values(2,5);
    insert into t1 values(2,6);
    insert into t1 values(3,6);
    insert into t1 values(3,7);
    
    create or replace procedure test(in i int) result int for debug
    declare var s cursor( i int );//
    code
      open s for direct "select i from t1 where i="+itoa(i)+";";//
      fetch s;//
      return s.i;//
    end;
    Эти конструкции эквивалентны:
    select i, test(i) from t1 group by i, test(i);
    select i, test(i) from t1 group by i;
    |          1|          1|
    |          2|          2|
    |          3|          3|
    Аналогичные конструкции с пользовательскими функциями, не содержащими < запрос выборки >, недопустимы.
    select i, max(5)  from t1 group by i, max(5); //не выполняется
    select i, (select 5)  from t1 group by i, (select 5); // выполняется
    select i, (select 5)  from t1 group by i; // выполняется
  7. Операция GROUP BY запрещена для столбцов типа BLOB и EXTFILE.

  8. Если < элементом группировки > является < SQL-параметр >, то должен явно указываться тип данных этого параметра.

    select year+1900, :model (char (20))
      from auto group by year,:model (char (20));
    FORD
    |       1970|FORD                |
    |       1971|FORD                |

  9. В операции GROUP BY запрещается использование более чем одной функции с атрибутом DISTINCT, кроме случая дублирования одного и того же аргумента функции (например, в SELECT и в HAVING).

    Правильная конструкция:

    select round(avg( cylnders),0), count( model) from auto group by color;

    Недопустимая конструкция (DISTINCT применяется для разных выражений):

    select round(avg( distinct cylnders),0), count( distinct model) from auto group by  color;

    Допустимая конструкция (DISTINCT применяется для одинаковых выражений):

    select round(avg( distinct (cylnders+length(make))),0), count(distinct (cylnders+length(make))) from auto group by color;
  10. На первом месте в списке < групп > должна быть непустая группа.

    Недопустимая конструкция:

    select make from auto group by grouping sets (),make;

Общие правила

  1. Результат < GROUP BY-спецификации > – разбивка предшествующей < FROM-спецификации > или < WHERE-спецификации > на набор групп. Набор групп – это минимальное число таких групп, в каждой из которых все значения группируемых столбцов равны.

    select model, count(*) from auto where year= 70 group by model;
    | 124 SPORT COUPE | 6 |
    | 1302S           | 3 |
    | 1600            | 7 |
      …
  2. Каждая строка полученной группы содержит одно и то же значение каждого группирующего столбца. Если к группе применяется < HAVING-спецификация > или < значимое выражение >, то ссылка на группирующий столбец является ссылкой на это значение.

  3. Конструкция < группировка со сведением данных > указывает на то, что помимо стандартных строк, предоставленных предложением GROUP BY, в результирующий набор добавляются сводные (итоговые) строки. Сводная строка возвращается для всех возможных сочетаний групп и подгрупп в результирующем наборе. Имя столбца в сводной строке результирующего набора выводится как NULL-значение. Чтобы определить, представляют ли NULL-значения в результирующем наборе сводные значения или фактические данные, используется функция GROUPING.

  4. Опция ROLLUP указывает на то, что помимо стандартных строк, предоставленных GROUP BY, в результирующий набор вводятся сводные строки.

    Подсчитать количество автомобилей разного цвета:

    select color, count(*)
      from auto
     group by color;
     COLOR
     -----
    | BLACK         |        262 |
    | BLUE          |        108 |
    | BROWN         |         46 |
    | GREEN         |         43 |
    | GREY          |        104 |
    | RED           |         52 |
    | WHITE         |        323 |
    | YELLOW        |         62 |

    Подсчитать количество автомобилей разного цвета с выдачей сводной (итоговой) суммы можно двумя способами:

    a) без конструкции ROLLUP:

    select color, count(*)
      from auto
     group by color
     union
    select 'Итого: ', count(*) from auto;
    COLOR
     -----
    | BLACK          |        262 |
    | BLUE           |        108 |
    | BROWN          |         46 |
    | GREEN          |         43 |
    | GREY           |        104 |
    | RED            |         52 |
    | WHITE          |        323 |
    | YELLOW         |         62 |
    | Итого:         |       1000 |

    б) с использованием конструкции ROLLUP:

    select color, count(*)
      from auto
     group by rollup(color);
    COLOR
     -----
    | BLACK          |        262 |
    | BLUE           |        108 |
    | BROWN          |         46 |
    | GREEN          |         43 |
    | GREY           |        104 |
    | RED            |         52 |
    | WHITE          |        323 |
    | YELLOW         |         62 |
    |                |       1000 |

    Чтобы итоговая сумма имела название (например, 'Итого:'), надо применить функцию GROUPING.

    Подсчитать количество автомобилей разного цвета с выдачей сводной (итоговой) суммы:

    select case GROUPING(color) when 0 then color else 'Итого: '  end as color,
     count(*) from auto group by rollup(color);
    
    COLOR                                  
    -----                                  
    |BLACK           |        262|
    |BLUE            |        108|
    |BROWN           |         46|
    |GREEN           |         43|
    |GREY            |        104|
    |RED             |         52|
    |WHITE           |        323|
    |YELLOW          |         62|
    |Итого:          |       1000| 
  5. При указании опции ROLLUP группы обобщаются в иерархическом порядке, начиная с самого нижнего уровня в группе и заканчивая самым верхним. Иерархия группы определяется порядком, в каком заданы столбцы, по которым производится группирование. Изменение порядка столбцов, по которым производится группирование, может повлиять на количество строк в результирующем наборе.

    Подсчитать количество автомобилей разного цвета, выпущенных разными производителями:

    select make, color, count(*)
      from auto
     group by rollup(make,color);
    MAKE             COLOR
     ----             -----
    | ALPINE           | BLACK  |  1 |
    | ALPINE           | GREY   |  3 |
    | ALPINE           | WHITE  |  3 |
    | AMERICAN MOTORS  | BLACK  | 23 |
    | AMERICAN MOTORS  | BLUE   | 11 |
    | AMERICAN MOTORS  | BROWN  |  6 |
    | AMERICAN MOTORS  | GREEN  |  2 |
    | AMERICAN MOTORS  | GREY   |  6 |
    …
  6. Опция CUBE означает, что помимо строк, перечисленных в GROUP BY, в результирующий набор должны включаться сводные (итоговые) строки для всех возможных сочетаний групп и подгрупп результирующего набора (в отличие от опции ROLLUP, которая возвращает итоговую сумму только по одной группе).

  7. Количество сводных строк в результирующем наборе определяется по количеству столбцов, включенных в предложение GROUP BY. Каждый операнд (столбец) в предложении GROUP BY привязывается к группирующему NULL-значению, и группирование применяется ко всем остальным операндам (столбцам). Поскольку оператор CUBE возвращает все возможные сочетания групп и подгрупп, количество строк остается тем же, независимо от заданного порядка группирования столбцов.

    Подсчитать количество автомобилей разного цвета, выпущенных разными производителями, с выдачей сводных данных по цветовой гамме:

    select make, color, count(*)
      from auto
     group by cube(make,color);
    ----              -----
    | ALPINE          | BLACK |  1 |
    | ALPINE          | GREY  |  3 |
    | ALPINE          | WHITE |  3 |
    | AMERICAN MOTORS | BLACK | 23 |
    | AMERICAN MOTORS | BLUE  | 11 |
      …
    …
    
    (сводные данные по первой группе)
    |ALPINE          |      | 7  |
    |AMERICAN MOTORS |      | 91 |
    |BMW             |      | 10 |
    |CHRYSLER        |      | 168|
    |CITROEN         |      | 7  |
    |DATSUN          |      | 10 |
    |DE TOMASO       |      | 12 |
    …
    
    (сводные данные по второй группе)
    |         |BLACK  |262 |
    |         |BLUE   |108 |
    |         |BROWN  |46  |
    |         |GREEN  |43  |
    |         |GREY   |104 |
    |         |RED    |52  |
    |         |WHITE  |323 |
    |         |YELLOW  62
    |         |        1000
  8. Различия между опциями CUBE и ROLLUP:

    • CUBE создает результирующий набор, содержащий статистические выражения для всех комбинаций значений заданных столбцов;

    • ROLLUP создает результирующий набор, содержащий статистические выражения иерархии значений в заданных столбцах.

  9. Конструкция < группировка по заданным группам > (GROUPING SETS) позволяет группировать данные по любым произвольным группам или их комбинациям. Если группы или критерии группировки включают более одного столбца или выражения, то группы заключаются в скобки. Пустые скобки используются для обозначения единственной группы, которая охватывает всю таблицу.

  10. Использование < GROUP BY-спецификация > с опцией GROUPING SETS эффективно в следующих случаях:

    • необходим только один проход базовой таблицы;

    • не нужно объединение (UNION) сложных операторов;

    • чем больше < групп >, тем больше выигрыш в производительности.

    Алгоритм группировки данных:

    • количество изолированных групп в результирующей выборке данных равно указанному количеству < групп >;

    • создаваемые группы располагаются в выборке данных в той очередности, в какой они перечислены в опции GROUPING SETS;

    • количество столбцов в результирующей выборке данных равно сумме не совпадающих столбцов в < элементах группировки >.

      Например:

      GROUPING SETS ((a, b), (b, c) – будут созданы две группы, в выборке данных будут присутствовать столбцы  a, b ,c.
      GROUPING SETS ((a, b), (c,d) – будут созданы две группы, в выборке данных будут присутствовать столбцы a, b ,c, d
    • для столбцов группы, у которых нет значений в формируемой результирующей выборке данных, подставляются NULL-значения.

  11. < GROUP BY-спецификация > с опцией GROUPING SETS эквивалента полному объединению данных, получаемых из отдельных запросов для каждой группы.

    Например:

    create or replace table "Города"("Название" char(15), "Статус" char(4), "Население, чел." bigint);
    
    insert into "Города" values ('Москва','рспб', 12000000), ('Воронеж', 'облс', 1000000), ('Борисоглебск', 'р-он', 400000), ('Семилуки', 'пгт', 120000), ('Курск', 'облс', 450000), ('Елец', 'р-он', 80000);
    
    Получить список городов с их численностью населения и суммарное количество населения в городах соответствующего статуса.
    Эти запросы эквивалентны:
    
    SELECT "Название", "Статус", SUM("Население, чел.")
      FROM "Города"
     GROUP BY GROUPING SETS (("Название"), ("Статус"));
    
    SELECT "Название", NULL as "Статус", SUM("Население, чел.")
      FROM "Города"
     GROUP BY "Название"
     UNION ALL
    SELECT NULL as "Название", "Статус", SUM("Население, чел.")
      FROM "Города" GROUP BY "Статус";
    
        Название        |   Статус   |                 |
    |   Борисоглебск    |            |      400000.0   |
    |   Воронеж         |            |     1000000.0   |
    |   Елец            |            |       80000.0   |
    |   Курск           |            |      450000.0   |
    |   Москва          |            |    12000000.0   |
    |   Семилуки        |            |      120000.0   |
    |                   |   облс     |     1450000.0   |
    |                   |   пгт      |      120000.0   |
    |                   |   р-он     |      480000.0   |
    |                   |   рспб     |    12000000.0   |

Примеры

  1. GROUP BY a эквивалентно GROUP BY GROUPING SETS((a))
  2. GROUP BY a,b,c  эквивалентно GROUP BY GROUPING SETS((a,b,c))
  3. GROUP BY ROLLUP(a,b) эквивалентно GROUP BY GROUPING SETS((a,b),(a),() )
  4. GROUP BY CUBE(a,b,c)эквивалентно
    GROUP BY GROUPING SETS((a,b,c),
                             (a,b),
                             (a,c),
                             (a),
                             (b,c),
                             (b),
                             (c),
                             () )
  5. GROUP BY a, ROLLUP(b,c) эквивалентно
    GROUP BY GROUPING SETS((a,b,c)
                             (a,b)
                             (a) )
  6. GROUP BY a, b, ROLLUP(c,d) эквивалентно
    GROUP BY GROUPING SETS((a,b,c,d),
                             (a,b,c),
                             (a,b) )
  7. GROUP BY ROLLUP(a), ROLLUP(b,c) эквивалентно
    GROUP BY GROUPING SETS((a,b,c),
                             (a,b),
                             (a),
                             (b,c),
                             (b),
                             () )
  8. GROUP BY ROLLUP(a), CUBE(b,c) эквивалентно
    GROUP BY GROUPING SETS((a,b,c),
                             (a,b),
                             (a,c),
                             (a),
                             (b,c),
                             (b),
                             (c),
                             () )
  9. GROUP BY CUBE(a,b), ROLLUP(c,d) эквивалентно
    GROUP BY GROUPING SETS((a,b,c,d),
                             (a,b,c),
                             (a,b),
                             (a,c,d),
                             (a,c),
                             (a),
                             (b,c,d),
                             (b,c),
                             (b),
                             (c,d),
                             (c),
                             () )
  10. GROUP BY a, ROLLUP(a,b) эквивалентно
    GROUP BY GROUPING SETS((a,b),
                             (a))
  11. Группировка по первичным ключам разных таблиц:

    create or replace table test1(i int primary key, j int, ch char(10));
    insert into test1 values(0, 1, 't0_1');
    insert into test1 values(1, 1, 't1_1');
    insert into test1 values(2, 1, 't1_2');
    insert into test1 values(3, 3, 't1_3');
    
    create or replace table test2(i int, j int, ch char(10), primary key(i,j));
    insert into test2 values(1, 1, 't2_1');
    insert into test2 values(1, 2, 't2_2');
    insert into test2 values(2, 1, 't2_3');
    insert into test2 values(3, 1, 't2_4');
    
    select t1.i as t1i, t1.j as t1j, t2.i as t2i, t2.j as t2j, min(t1.ch) as mint1ch, max(t2.ch) as maxt2ch
      from test1 t1, test2 t2
     where t1.i=t2.i
     group by t1.pk, t2.pk
    having min(t1.ch)  > 't1';
    
       T1I       T1J     T2I      T2J    MINT1CH  MAXT2CH
       ---       ---     ---      ---    -------  -------
    |       1|       1|       1|       1|t1_1    |t2_1    |
    |       1|       1|       1|       2|t1_1    |t2_2    |
    |       2|       1|       2|       1|t1_2    |t2_3    |
    |       3|       3|       3|       1|t1_3    |t2_4    |
    
  12. Группировка данных по двум группам:

    select model as "Модели автомобилей", color as "Окраска автомобилей"
      from auto
     group by grouping sets ((model),(color));
    
    Модели автомобилей   Окраска автомобилей
     ------------------   -------------------
    |124 SPORT COUPE     |                   |
    |1275 GT             |                   |
    |1302 S              |                   |
    |1600                |                   |
    …
    |SM                  |                   |
    |TR 6                |                   |
    |XJ 6 4.2            |                   |
    |                    |BLACK              |
    |                    |BLUE               |
    |                    |BROWN              |
    |                    |GREEN              |
    |                    |GREY               |
    |                    |RED                |
    |                    |WHITE              |
    |                    |YELLOW             |
  13. Использование функции GROIPING и конструкции GROUPING SETS:

    select "Название", "Статус",
    /* выдает битовую маску: первый бит – если значение агрегировано */
    /* по всем "Городам", второй – если по всем "Статусам" */
      grouping ("Название","Статус")
      from "Города"
     group by
    /* группировка идентична CUBE, но расписан список группирующих множеств */
       grouping sets (("Название","Статус"),("Название"),("Статус"),());
    
    Название        Статус
     --------        ------
    |Борисоглебск   |р-он  |    0|
    |Воронеж        |облс  |    0|
    |Елец           |р-он  |    0|
    |Курск          |облс  |    0|
    |Москва         |рспб  |    0|
    |Семилуки       |пгт   |    0|
    |Борисоглебск   |      |    1|
    |Воронеж        |      |    1|
    |Елец           |      |    1|
    |Курск          |      |    1|
    |Москва         |      |    1|
    |Семилуки       |      |    1|
    |               |облс  |    2|
    |               |пгт   |    2|
    |               |р-он  |    2|
    |               |рспб  |    2|
    |               |      |    3|