Лекция 10. Агрегация
Функция агрегации принимает на вход множество значений, выдавая на выходе одно. Наиболее часто используемая функция агрегации - это``count()``. Она получает набор строк и возвращает их количество.
В качестве параметра может использоваться любое SQL выражение. Наиболее часто используется шаблон *, означающий «все столбцы». В отличии от большинства функций агрегации count() не вычисляет значение своего аргумента, а просто считает сколько раз он был вызван:
SELECT count(*)
FROMemployee
-
Другая функция агрегации может вернуть нам среднее количество сотрудников в офисах. Для этого нам также потребуется использовать конструкцию GROUP BY в подзапросе:
SELECT avg(emp_count)
FROM (SELECTcount(*) AS emp_count
FROM employee
GROUP BY dep_id) AS emp_counts
-
Примечание
Запрос в этом примере производит подсчёт только по отделам, в которых есть сотрудники. Для включения в расчёты отделы без сотрудников нужно использовать более сложный подзапрос.
Лекция 11. Группировка данных
Конструкция GROUP BY, применяемая в выражении SELECT, служит для группировки результатов по какому-либо полю. Она зачастую используется совместно с агрегацией для применения агрегирующей функции к каждой из групп.
На изображении выше строки разделены на 3 подгруппы по некоему полю «a». Затем применена функция SUM() к полю «b» в каждой из этих групп.
В качестве примера совместного использования агрегации и конструкции GROUP BY рассчитаем количество сотрудников в каждом отделе:
SELECT count (*)
FROM employee GROUP BY dep_id
-
count
|
dep_id
|
2
|
1
|
10
|
2
|
6
|
3
|
9
|
4
|
Having
Для фильтрации сгруппированных агрегированных значений применяется конструкция HAVING. Например, можно изменить вывод примера выше: отфильтровать отделы, в которых количество сотрудников больше семи:
SELECT count(*) as emp_count
FROM employee
GROUP BY dep_id
HAVING emp_count>7
-
Выборка.
Рассмотрим, как команда SELECT ведёт себя при комбинации всех вышеописанных конструкций.
Для примера возьмём следующий набор строк:
-
Employee
|
emp_id
|
emp_name
|
dep_id
|
1
|
wally
|
1
|
2
|
dilbert
|
1
|
3
|
jack
|
2
|
4
|
ed
|
3
|
5
|
wendy
|
1
|
6
|
dogbert
|
4
|
7
|
boss
|
3
|
К которому будет применён вот этот код:
SELECT count(emp_id) as emp_count, dep_id
FROM employee
WHERE dep_id=1 OR dep_id=3 OR dep_id=4
GROUPBY dep_id
HAVING emp_count>1
ORDER BY emp_count, dep_id
Конструкция FROM определяет таблицы, из которых будут выбраны строки. В нашем примере это таблица employee:
...FROM employee...
-
emp_id
|
emp_name
|
dep_id
|
1
|
wally
|
1
|
2
|
dilbert
|
1
|
3
|
jack
|
2
|
4
|
ed
|
3
|
5
|
wendy
|
1
|
6
|
dogbert
|
4
|
7
|
boss
|
3
|
Каждая строка проверяется на соответствие условию, описанному в блоке WHERE. Только строки, прошедшие эту проверку, подвергаются дальнейшей обработке:
...WHEREdep_id=1 OR dep_id=3 OR dep_id=4...
-
emp_id
|
emp_name
|
dep_id
|
1
|
wally
|
1
|
2
|
dilbert
|
1
|
4
|
ed
|
3
|
5
|
wendy
|
1
|
6
|
dogbert
|
4
|
7
|
boss
|
3
|
Затем производится фоновая группировка по какому-либо критерия с помощью конструкции GROUP BY. Дальнейшая обработка производится уже над этими группами. Для текущего примера это выглядит следующим образом:
...GROUPBY dep_id...
-
«group»
|
emp_id
|
emp_name
|
dep_id
|
dep_id=1
|
1
|
wally
|
1
|
|
2
|
dilbert
|
1
|
|
5
|
wendy
|
1
|
dep_id=3
|
4
|
ed
|
3
|
|
7
|
boss
|
3
|
dep_id=4
|
6
|
dogbert
|
4
|
Затем функции агрегации применяются к каждой группе строк. В примере функция count() применяется к полю emp_id. Это значит, что для группы «1» она получит «1», «2» и «5», в качестве значений. Функции count() нет дела до того, какие значения в неё были переданы. И можно для упрощения передать *, что означает «все столбцы». Тем не менее, большинству функций агрегации важно, что именно передаётся в них в качестве параметров. Потому хорошей практикой считается определение тех столбцов, которые передаются в качестве параметров. Результат нашего примера выглядит примерно так:
...count(emp_id) AS emp_count...
-
emp_count
|
dep_id
|
3
|
1
|
2
|
3
|
1
|
4
|
Конструкция HAVING работает как WHERE, но только для агрегированных значений. В текущем примере она осуществляет фильтрацию групп, которые имеют более одного сотрудника:
...HAVING emp_count>1...
-
В конце применяется конструкция ORDER BY. Важно помнить, что реляционная математика, заложенная в основу SQL, базируется на понятии множеств. Которые являются по определению неупорядоченными. В обычном случае выборка, агрегация и фильтрация применяются к неупорядоченным строкам. И только в конце, перед отдачей результата пользователю, производится упорядочивание по какому-либо признаку:
...ORDER BY emp_count,dep_id
-
Лекция 12. Язык управления данными (DCL – Data Control Language)
Команды управления данными позволяют управлять доступом к информации, находящейся внутри базы данных. Как правило, они используются для создания объектов, связанных с доступом к данным, а также служат для контроля над распределением привилегий между пользователями. Команды управления данными следующие: GRANT, REVOKE.
Команды администрирования данных
С помощью команд администрирования данных пользователь осуществляет контроль за выполняемыми действиями и анализирует операции базы данных ; они также могут оказаться полезными при анализе производительности системы. Не следует путать администрирование данных с администрированием базы данных, которое представляет собой общее управление базой данных и подразумевает использование команд всех уровней.
Команды управления транзакциями
Существуют следующие команды, позволяющие управлять транзакциями базы данных: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.
Достарыңызбен бөлісу: |
