Лекция_6

1.

ВВЕДЕНИЕ В БАЗЫ ДАННЫХ
ЛЕКЦИЯ № 6
Преподаватель: Гладкова Екатерина Сергеевна
Должность: инженер-программист 1 категории
Департамент: Разработка аналитических систем
Подразделение: Группа разработки веб АЦР

2. Повторение. Соединение таблиц внутреннее соединение (INNER JOIN)

ПОВТОРЕНИЕ. СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
Нередко возникает ситуация, когда нам надо получить данные из
нескольких таблиц. Для соединения данных из разных таблиц можно
использовать оператор INNER JOIN или JOIN. Он представляет так
называемое внутреннее соединение. Его формальный синтаксис:
SELECT столбцы
FROM таблица1
JOIN таблица2 ON условие1
После оператора JOIN идет название второй таблицы, данные которой надо
добавить в выборку. Далее после ключевого слова ON указывается условие
соединения. Это условие устанавливает, как две таблицы будут сравниваться. Как
правило, для соединения применяется первичный ключ одной таблицы и внешний
ключ другой таблицы.
В результате остаются только те строки, для которых нашлось соответствие.
2

3. Повторение. Соединение таблиц внутреннее соединение (INNER JOIN)

ПОВТОРЕНИЕ. СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
Возьмем таблицы Сотрудники (employees) и Подразделения (divisions):
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
division_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
division_name
VARCHAR (80)
birthdate
DATE
division_head
INTEGER
post_id
INTEGER
department_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
divisions
Используя JOIN, выберем фамилии (last_name) всех сотрудников и добавим к
ним названия подразделений (division_name) в которых они работают:
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании
столбцов для выборки указывается их полное имя вместе с именем таблицы.
3

4. Повторение. Соединение таблиц внутреннее соединение (INNER JOIN)

ПОВТОРЕНИЕ. СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
Результат:
4

5. Повторение. Соединение таблиц Псевдонимы таблиц

ПОВТОРЕНИЕ. СОЕДИНЕНИЕ ТАБЛИЦ
ПСЕВДОНИМЫ ТАБЛИЦ
В условиях соединения ON имя таблицы обычно не пишут, т.к. читать длинные
строки кода весьма утомительно. Подобно псевдонимам столбцов в списке
выборки SELECT можно задать псевдонимы таблицам. Для этого после
названия таблицы через пробел нужно написать псевдоним таблицы. После
того, как таблице назначен псевдоним, обращаться к ней по названию уже
нельзя - только по псевдониму.
Перепишем предыдущий запрос с использованием псевдонимов для таблиц. Было:
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
стало:
SELECT e.last_name, div.division_name
FROM employees e
JOIN divisions div ON div.division_id = e.division_id
Часто таблицам дают псевдонимы по первым буквам слов из названия таблицы.
5

6. Повторение. Соединение таблиц

ПОВТОРЕНИЕ. СОЕДИНЕНИЕ ТАБЛИЦ
Принцип различных типов соединений наглядно отражен на схеме:
INNER JOIN
(внутреннее соединение)
LEFT JOIN
(левое внешнее соединение)
RIGHT JOIN
(правое внешнее соединение)
FULL JOIN
(полное внешнее соединение)
6

7. Запросы Агрегатные функции

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ
Агрегатные функции вычисляют одно значение над некоторым
набором строк. В PostgreSQL имеются следующие агрегатные
функции:
• AVG: находит среднее значение.
• COUNT: находит количество строк в запросе, для которых
выражение не содержит значение NULL.
• SUM: находит сумму значений.
• MIN: находит наименьшее значение.
• MAX: находит наибольшее значение.
7

8. Запросы Агрегатные функции. AVG

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ. AVG
Функция Avg возвращает среднее значение на диапазоне значений
столбца таблицы.
Найдем среднюю заработную плату (salary) для всех должностей из
таблицы Должности (posts):
posts
post_id
SERIAL PRIMARY KEY
post_name
VARCHAR (80)
salary
INTEGER
SELECT AVG(salary)
FROM posts
Результат:
8

9. Запросы Агрегатные функции. AVG

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ. AVG
Также мы можем применить фильтрацию. Например, найдем среднюю
зарплату (salary) для всех должностей «старших» сотрудников:
posts
post_id
SERIAL PRIMARY KEY
post_name
VARCHAR (80)
salary
INTEGER
SELECT AVG(salary)
FROM posts
WHERE post_name LIKE 'Старший%'
Результат:
9

10. Запросы Агрегатные функции. Count

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ. COUNT
Функция COUNT вычисляет количество строк во всей выборке или по
столбцу.
Например, подсчитаем, сколько всего сотрудников в нашей базе данных:
SELECT COUNT(*)
FROM employees
Результат:
Или, подсчитаем, сколько всего уникальных (DISTINCT) имен сотрудников
(first_name) в нашей базе данных:
SELECT COUNT(DISTINCT first_name)
FROM employees
Результат:
10

11. Запросы Агрегатные функции. Min и Max

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ. MIN И MAX
Функции Min и Max возвращают соответственно минимальное и
максимальное значение по столбцу. Например, найдем минимальную
заработную плату (salary) всех сотрудников из таблицы Должности (posts):
SELECT MIN(salary)
FROM posts
Результат:
А так же максимальную заработную плату (salary) всех сотрудников из
таблицы Должности (posts):
Результат:
SELECT MAX(salary)
FROM posts
11

12. Запросы Агрегатные функции. Sum

ЗАПРОСЫ
АГРЕГАТНЫЕ ФУНКЦИИ. SUM
Функция Sum вычисляет сумму значений столбца.
Например, подсчитаем общую зарплату (salary) всех сотрудников:
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
post_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
post_name
VARCHAR (80)
birthdate
DATE
salary
INTEGER
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
posts
Результат:
SELECT SUM(salary)
FROM posts p
JOIN employees e ON e.post_id = p.post_id
12

13. Лекция 6. Тренировка

ЛЕКЦИЯ 6. ТРЕНИРОВКА
5.1 Выведите в одном запросе (но в разных колонках)
среднюю (AVG), минимальную (MIN) и максимальную (MAX)
зарплату (salary) и количество человек (COUNT), которые ее
получают в 10 подразделении.
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
post_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
post_name
VARCHAR (80)
birthdate
DATE
salary
INTEGER
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
posts
SELECT
FROM
JOIN … ON
WHERE
13

14. Лекция 6. Тренировка. Решение

ЛЕКЦИЯ 6. ТРЕНИРОВКА. РЕШЕНИЕ
5.1 SELECT AVG(p.salary), MIN(p.salary), MAX(p.salary),
COUNT(*)
FROM employees e
JOIN posts p ON p.post_id = e.post_id
WHERE e.division_id = 10
14

15. Лекция 5. Тренировка

ЛЕКЦИЯ 5. ТРЕНИРОВКА
5.2 Выведите в одном запросе (но в разных колонках)
среднюю (AVG), минимальную (MIN) и максимальную (MAX)
зарплату (salary) и количество человек (COUNT), которые ее
получают в 6 департаменте.
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
division_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
division_name
VARCHAR (80)
birthdate
DATE
division_head
INTEGER
post_id
INTEGER
department_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
posts
post_id
SERIAL PRIMARY KEY
post_name
VARCHAR (80)
salary
INTEGER
divisions
SELECT
FROM
JOIN … ON
JOIN … ON
WHERE
15

16. Лекция 5. Тренировка. Решение

ЛЕКЦИЯ 5. ТРЕНИРОВКА. РЕШЕНИЕ
5.2 SELECT AVG(p.salary), MIN(p.salary), MAX(p.salary),
COUNT(*)
FROM employees e
JOIN posts p ON p.post_id = e.post_id
JOIN divisions d ON d.division_id = e.division_id
WHERE d.department_id = 6
16

17. Запросы Группировка

ЗАПРОСЫ
ГРУППИРОВКА
Для группировки данных в PostgreSQL применяются операторы
GROUP BY и HAVING, при их использовании формальный порядок
операторов выглядит следующим образом:
SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]
17

18. Запросы Группировка. GROUP BY

ЗАПРОСЫ
ГРУППИРОВКА. GROUP BY
Оператор GROUP BY определяет, как строки будут группироваться.
Например, сгруппируем сотрудников по именам (first_name) из таблицы
Сотрудники (employees):
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
ORDER BY first_name
Результат:
Первый столбец в выражении SELECT first_name представляет название группы, а
второй столбец - представляет результат
функции COUNT(*), которая вычисляет
количество строк в группе.
18

19. Запросы Группировка. GROUP BY

ЗАПРОСЫ
ГРУППИРОВКА. GROUP BY
Стоит учитывать, что любой столбец, который используется в выражении
SELECT (не считая столбцов, которые хранят результат агрегатных функций),
должны быть указаны после оператора GROUP BY. Так, в нашем примере,
столбец first_name указан и в выражении SELECT, и в выражении GROUP BY.
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
И если в выражении SELECT производится выборка по одному или
нескольким столбцам и также используются агрегатные функции, то
необходимо использовать выражение GROUP BY. Так, следующий пример
работать не будет, так как он не содержит выражение группировки:
SELECT first_name, COUNT(*)
FROM employees
19

20. Запросы Группировка. HAVING

ЗАПРОСЫ
ГРУППИРОВКА. HAVING
Оператор HAVING указывает, какие группы будут включены в выходной
результат, то есть выполняет фильтрацию групп. Его использование
аналогично применению оператора WHERE.
Например, сгруппируем сотрудников по именам (first_name) из таблицы
Сотрудники (employees) и найдем все группы, для которых нашлось больше
одного повторения:
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*) > 1
ORDER BY first_name
Результат:
20

21. Запросы Группировка. HAVING

ЗАПРОСЫ
ГРУППИРОВКА. HAVING
При этом в одной команде мы можем использовать выражения WHERE и
HAVING:
SELECT first_name, COUNT(*)
FROM employees
WHERE first_name LIKE 'Е%'
GROUP BY first_name
HAVING COUNT(*) > 1
ORDER BY first_name
То есть в данном случае сначала
фильтруются строки: выбираются
те имена (first_name), которые
начинаются на букву «Е». Затем
выбранные имена группируются
Результат:
непосредственно по самим
именам. И далее фильтруются
сами группы – выбираются те,
которые содержат больше одного
повторения.
21

22. Лекция 6. Тренировка

ЛЕКЦИЯ 6. ТРЕНИРОВКА
6.1 Выведите два столбца, которые будут отображать
группировку Сотрудников (employees) по месяцам даты
рождения. В первой колонке выведите номер месяца *, а
во второй - количество сотрудников, которые родились в
этот месяц. Отсортируйте по возрастанию месяца.
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
patronymic
VARCHAR (30)
birthdate
DATE
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
* Номер месяца:
extract(month from birthdate)
22

23. Лекция 6. Тренировка. Решение

ЛЕКЦИЯ 6. ТРЕНИРОВКА. РЕШЕНИЕ
6.1 SELECT
extract(month from birthdate) as month,
COUNT(*)
FROM employees
GROUP BY month
ORDER BY month
23

24. Лекция 6. Тренировка

ЛЕКЦИЯ 6. ТРЕНИРОВКА
6.2 Выведите название должностей в первой колонке, а
во второй колонке подсчитайте количество человек,
которые работают на каждой должности.
employees
posts
employee_id
SERIAL PRIMARY KEY
post_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
post_name
VARCHAR (80)
first_name
VARCHAR (30)
salary
INTEGER
patronymic
VARCHAR (30)
birthdate
DATE
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
24

25. Лекция 6. Тренировка. Решение

ЛЕКЦИЯ 6. ТРЕНИРОВКА. РЕШЕНИЕ
6.2 SELECT post_name, COUNT(*)
FROM posts p
JOIN employees e ON p.post_id = e.post_id
GROUP BY post_name
25

26. Лекция 6. Тренировка

ЛЕКЦИЯ 6. ТРЕНИРОВКА
6.3 Доработайте задание 6.2 добавив условие фильтрации
строк: должны выводиться только должности 2 категории. И
условие фильтрации групп: должны выводиться только
группы, которые содержат больше двух сотрудников.
employees
posts
employee_id
SERIAL PRIMARY KEY
post_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
post_name
VARCHAR (80)
first_name
VARCHAR (30)
salary
INTEGER
patronymic
VARCHAR (30)
birthdate
DATE
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
26

27. Лекция 6. Тренировка. Решение

ЛЕКЦИЯ 6. ТРЕНИРОВКА. РЕШЕНИЕ
6.3 SELECT post_name, COUNT(*)
FROM posts p
JOIN employees e ON p.post_id = e.post_id
+ WHERE p.post_name LIKE '%2%'
GROUP BY post_name
+ HAVING COUNT(*) > 2
27

28. Лекция 6. Тренировка

ЛЕКЦИЯ 6. ТРЕНИРОВКА
6.4 Доработайте задание 6.3 так, чтобы люди считались не
во всей Кристе, а только в департаменте с id = 6.
Отсортируйте выборку по второй колонке.
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
division_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
division_name
VARCHAR (80)
birthdate
DATE
division_head
INTEGER
post_id
INTEGER
department_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
divisions
posts
post_id
SERIAL PRIMARY KEY
post_name
VARCHAR (80)
salary
INTEGER
28

29. Лекция 6. Тренировка. Решение

ЛЕКЦИЯ 6. ТРЕНИРОВКА. РЕШЕНИЕ
6.4 SELECT post_name, COUNT(*)
FROM posts p
JOIN employees e ON p.post_id = e.post_id
+ JOIN divisions d ON d.division_id = e.division_id
WHERE p.post_name LIKE '%2%'
+
AND d.department_id = 6
GROUP BY post_name
HAVING COUNT(*) > 2
29