Операции группировки GROUP BY в запросах SQL к базе данных Oracle

Светлана Комарова

Светлана Комарова

Автор статьи. Системный администратор, Oracle DBA. Информационные технологии, интернет, телеком. Подробнее.

GROUP BY в SQL запросах в базе данных Oracle на примерахДля группирования результатов запроса в соответствии с различными критериями в Oracle предлагается конструкция GROUP BY. Эта конструкция позволяет считать значение столбца во всех строках таблицы удовлетворяющим условию SELECT.

Вместе с конструкцией GROUP BY часто используются агрегатные функции для складывания данных каждой определяемой ею группы, т.е. для выполнения сначала сортировки данных по указанным в GROUP BY столбцам, а потом вычисления их суммарных показателей. Ниже показан пример применения конструкции GROUP BY

SQL> SELECT department_id, MAX(salary)
2 FROM employees
3* GROUP BY department_id;
DEPARTMENT_ID  MAX(SALARY)
-------------  -----------
     10            4400
     20           13000
     30           11000
     40            6500
     50            8200
5 rows selected.
SQL>


Oracle также позволяет использовать вложенные функции для групп. Приведенный ниже запрос, например, подразумевает вычисление минимального среднего бюджета для всех отделов (функция AVG здесь является вложенной в функцию MIN): 

SQL> SELECT MIN(AVG(budget))
FROM dept_budgets
GROUP BY dept_no;

 

Использование конструкции GROUP BY с операцией ROLLUP

Как с помощью конструкции GROUP BY получать промежуточные итоговые значения (subtotals), уже было показано. За счет использования конструкции GROUP BY с операцией ROLLUP, однако, можно получать как промежуточные итоговые, так и общие суммарные (totals) значения и, следовательно, генерировать промежуточные агрегатные значения на любом уровне. Другими словами, операция ROLLUP позволяет получать агрегатные значения для каждой группы на отдельных уровнях. Промежуточные итоговые строки и конечные суммарные строки называются суперагрегатными строками (superaggregate rows).

В листинге ниже приведен пример применения конструкции GROUP BY с операцией ROLLUP.


 

SQL> SELECT Year,Country,SUM(Sales) AS Sales
FROM Company_Sales
GROUP BY ROLLUP (Year,Country);
  YEAR    COUNTRY     SALES
--------  --------  -------
  1997    France       3990
  1997    USA         13090
  1997                17080
  1998    France       4310
  1998    USA         13900
  1998                18210
  1999    France       4570
  1999    USA         14670
  1999                19240
                      54530 /* Так выглядит конечное суммарное значение */
SQL>

 

Использование конструкции GROUP BY с операцией CUBE

Операцию CUBE можно считать расширением операции ROLLUP, поскольку она помогает расширять стандартные возможности конструкции GROUP BY в Oracle. Она вычисляет все возможные варианты промежуточных итоговых значений в операции GROUP BY. В предыдущем примере операция ROLLUP вернула промежуточные итоговые значения по годам. За счет использования операции CUBE можно получить итоговые значения не только по годам, но и по всей стране. Ниже приведен простой пример: 

SQL> SELECT department_id, job_id, SUM(salary)
2 FROM employees
3 GROUP BY CUBE (department_id, job_id);
DEPARTMENT_ID  JOB_ID     SUM(SALARY)
-------------  ---------  -----------
  10           AD_ASST          44000
  20           MK_MAN          130000
  20           MK_REP           60000
  30           PU_MAN          110000
  30           PU_CLERK        139000
. . .
SQL>

 

Использование конструкции GROUP BY с операцией GROUPING

Как было показано ранее, операция ROLLUP позволяет получать суперагрегатные промежуточные и суммарные итоговые данные. Операция GROUPING в конструкции GROUP BY помогает проводить различие между столбцами с суперагрегатными промежуточными и суммарными итоговыми данными и прочими данными в строках.

 

Использование конструкции GROUP BY с операцией GROUPING SETS

Операция GROUPING SETS позволяет распределять множество наборов столбцов по группам при вычислении агрегатных показателей вроде сумм. Ниже приведен пример, демонстрирующий применение этой операции для вычисления агрегатных показателей с их последующим распределением по трем таким группам: (year, region, item), (year, item) и (region, item). Операция GROUPING SETS устраняет необходимость в использовании неэффективных операций UNION ALL

SQL> SELECT year, region, item, sum(sales)
FROM regional_salesitem GROUP BY
GROUPING SETS (( year, region, item),
(year, item), (region, item));

 

Использование конструкции GROUP BY с операцией HAVING

Операция HAVING позволяет ограничивать или исключать результаты операции GROUP BY, т.е., по сути, накладывать на результирующий набор GROUP BY условие WHERE. В следующем примере операция HAVING ограничивает результаты запроса только теми отделами, в которых максимальная зарплата превышает 20 000:

SQL> SELECT department_id, max(salary)
2 FROM employees
3 GROUP BY department_id
4* HAVING MAX(salary)>20000;
DEPARTMENT_ID  MAX(SALARY)
-------------  -----------
    90         24000
SQL>

Вас заинтересует / Intresting for you:

Oracle и Java: использование P...
Oracle и Java: использование P... 5750 просмотров sepia Tue, 08 May 2018, 08:52:34
Значения NULL в PL/SQL Oracle
Значения NULL в PL/SQL Oracle 4807 просмотров Дэн Wed, 01 Jul 2020, 08:10:56
Язык SQL и PL/SQL Oracle
Язык SQL и PL/SQL Oracle 3358 просмотров Андрей Волков Tue, 21 Nov 2017, 13:17:28
Oracle IDE: JDeveloper, SQL De...
Oracle IDE: JDeveloper, SQL De... 4522 просмотров Ольга Потемкина Tue, 21 Nov 2017, 13:18:46
Войдите чтобы комментировать