2014-02-09
639
Группы внутри групп
Предложение GROUP BY
Если команда SELECT содержит предложение GROUP BY, то строки таблицы группируются по значению выражения, указанного в предложении GROUP BY. Каждая группа состоит из строк, имеющих одинаковые значения выражения, заданного в предложении GROUP BY. Значение групповой функции вычисляется для каждой такой группы строк.
В запросе, содержащем фразу GROUP BY, все элементы списка SELECT должны быть выражениями из фразы GROUP BY, выражениями, содержащими групповые функции, константами либо выражениями, составленными из выражений предыдущих трех типов. В предложении GROUP BY нельзя использовать позиционные обозначения или псевдонимы выражений из списка SELECT.
При использовании GROUP BY строки по умолчанию сортируются в порядке возрастания в соответствии со списком GROUP BY. Изменить порядок сортировки можно с помощью предложения ORDER BY.
Пример: Для каждого отдела найти количество служащих в нем и их суммарную зарплату.
SELECT dept_id, SUM(salary), COUNT(*)
FROM s_emp
GROUP BY dept_id;
Результат:
|
|
|
DEPT_ID SUM(SALARY) COUNT(*)
--------- ----------- ---------
10 1450 1
31 2800 2
32 1490 1
33 1515 1
34 2320 2
35 1450 1
41 4990 4
…
12 rows selected.
Столбец, заданный в предложении GROUP BY не обязательно должен присутствовать в предложении SELECT, но его присутствие делает результаты более осмысленными.
Пример: Узнать, какие буквы алфавита встречаются в качестве начальных в фамилиях служащих, и сколько фамилий начинается с каждой буквы.
SELECT SUBSTR(last_name, 1, 1), COUNT(*)
FROM s_emp
GROUP BY SUBSTR(last_name, 1, 1);
Результат:
S COUNT(*)
- ---------
B 1
C 2
D 2
G 1
H 1
M 4
…
13 rows selected.
Можно получить сводные результаты по подгруппам внутри групп, если указать более, чем один столбец в предложении GROUP BY.
Пример: Вывод количества служащих по должностям внутри отделов.
SELECT dept_id, title, COUNT(*)
FROM s_emp
GROUP BY dept_id, title;
Результат:
DEPT_ID TITLE COUNT(*)
--------- ----------------------- ---------
10 VP, Finance 1
31 Sales Representative 1
31 VP, Sales 1
32 Sales Representative 1
33 Sales Representative 1
34 Sales Representative 1
34 Stock Clerk 1
35 Sales Representative 1
41 Stock Clerk 2
41 VP, Operations 1
41 Warehouse Manager 1
42 Stock Clerk 2
42 Warehouse Manager 1
…
21 rows selected.
Обратите внимание, что все столбцы, входящие в предложение SELECT, но не являющиеся аргументами групповых функций обязательно должны быть перечислены в предложении GROUP BY, иначе возникает ошибка.
Пример:
SELECT dept_id, title, COUNT(*)
FROM s_emp
GROUP BY dept_id;
Результат:
SELECT dept_id, title, COUNT(*)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
В запросах с групповыми функциями Вы можете использовать предложение WHERE, чтобы исключить некоторые строки из групп.
Пример: Вывести максимальную зарплату по должностям для служащих, начавших работать не позднее 1991 года.
SELECT title, MAX(salary)
FROM s_emp
WHERE start_date>TO_DATE('31121990','ddmmyyyy')
GROUP BY title;
Результат:
TITLE MAX(SALARY)
|
|
|
----------------------- -----------
Sales Representative 1525
Stock Clerk 1400
VP, Sales 1400
Warehouse Manager 1307
Для того чтобы исключать группы из результата запроса, предложение WHERE не используется.
Пример: Вывести номера отделов, в которых суммарная заработная плата превышает 2500.
SELECT dept_id, SUM(salary)
FROM s_emp
WHERE SUM(salary)>2500
GROUP BY dept_id;
Результат:
WHERE SUM(salary)>2500
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Чтобы ограничить выбор групп используется предложение HAVING, которое задает условие, которому должна удовлетворять группа, чтобы попасть в выборку. Oracle обрабатывает предложения WHERE, GROUP BY и HAVING следующим образом:
1. Если предложение содержит фразу WHERE, то отбрасываются все строки, которые не удовлетворяют ей.
2. Вычисляются и формируются группы строк, как определено предложением GROUP BY.
3. Отбрасываются все группы, которые не удовлетворяют предложению HAVING.
Пример: Вывести номера отделов, в которых суммарная заработная плата превышает 2500.
SELECT dept_id, SUM(salary) FROM s_emp
GROUP BY dept_id
HAVING SUM(salary)>2500;
Результат:
DEPT_ID SUM(SALARY)
--------- -----------
31 2800
41 4990
42 3245
43 2700
45 3267
50 4050
6 rows selected.
Лекция 7. Язык SQL. Выборка данных из нескольких таблиц.
Если требуются данные из более, чем одной таблицы базы данных, в предложении FROM команды SELECT указывается список этих таблиц через запятую. При этом, как правило, используется условие соединения, которое приводится в предложении WHERE.
Есть два основных типа условий соединения:
- эквисоединения,
- не-эквисоединения.
Кроме того, существуют дополнительные методы соединения:
- внешние соединения,
- соединения таблицы с собой.
В случае если условие соединения опущено или недействительно, результатом запроса будет декартово произведение всех таблиц указанных в предложении FROM, то есть будут получены все возможные комбинации строк этих таблиц.
Пример:
SELECT name, last_name
FROM s_dept, s_emp;
Результат:
NAME LAST_NAME
--------------- --------------
Finance Velasquez
Sales Velasquez
Sales Velasquez
Sales Velasquez
Sales Velasquez
Sales Velasquez
Operations Velasquez
Operations Velasquez
Operations Velasquez
Operations Velasquez
Operations Velasquez
Administration Velasquez
Finance Ngao
Sales Ngao
Sales Ngao
Sales Ngao
Sales Ngao
Sales Ngao
Operations Ngao
Operations Ngao
Operations Ngao
Operations Ngao
Operations Ngao
Administration Ngao
Finance Nagayama
Sales Nagayama
Sales Nagayama
Sales Nagayama
Sales Nagayama
Sales Nagayama
Operations Nagayama
…
300 rows selected.
В приведенном выше примере отсутствует предложение WHERE с условием соединения. Поэтому мы получили декартово произведение таблиц s_dept и s_emp: каждое значение столбца name таблицы s_dept соединилось с каждым значением столбца last_name таблицы s_dept. В таблице s_emp – 25 строк, в таблице s_dept – 12 строк, поэтому результатом запроса стала выборка из 25х12, то есть 300 строк. В случае, если мы добавим выборку столбца из еще одной таблицы, например, столбца address из таблицы s_customer, количество выбранных строк возрастет. Так как в таблице s_customer 15 строк, мы получим 25х12х15, то есть 4500 строк.
Как правило, декартово произведение содержит большое количество строк, при этом результат получается не очень полезным. Поэтому если вывод именно всех комбинаций строк не требуется, следует использовать условие соединения, которое задается в предложении WHERE.
