Для группирования результатов запроса в соответствии с различными критериями в 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>