Материалы представленные на сайте исключительно с целью ознакомления пользователям Интернета и не преследуют коммерческих целей или нарушение авторских прав.
© 2018 - 2024
Соединение по именам столбцов похоже на естественное соединение. Отличие состоит в том, что можно указать, какие одноименные столбцы должны проверяться. В естественном соединении проверяются все одноименные столбцы.
Допустим, имеются две таблицы с одинаковыми структурами:
· Коробки (Размер, Количество, Цвет);
· Крышки (Размер, Количество, Цвет).
Предположим, нам нужны комплекты, в каждом из которых количества коробок и крышек одного размера совпадают, а их цвета могут быть различными. Естественное соединение в этом случае не подойдет, поскольку в нем проверяются все одноименные столбцы, а потому в таблицу результатов не попадут комплекты из разноцветных коробок и крышек. Поэтому следует использовать соединение по одинаковым именам только столбцов Размер и Количество:
SELECT *
FROM Коробки JOIN Крышки USING (Размер, Количество);
После ключевого слова USING в круглых скобках указывается список одноименных столбцов соединяемых таблиц, которые необходимо проверять.
Приведенный выше запрос можно сформулировать иначе:
SELECT *
FROM Коробки, Крышки
WHERE (Коробки.Размер = Крышки.Размер)
AND (Коробки.Количество = Крышки.Количество);
На следующих рисунках показаны таблицы Коробки и Крышки, а также результат рассмотренного запроса.
| Коробки | Крышки | |||||
| Размер | Количество | Цвет | Размер | Количество | Цвет | |
| 30´20 | 13 | Белый | 30´20 | 13 | Белый | |
| 30´30 | 7 | Белый | 30´30 | 3 | Белый | |
| 30´15 | 20 | Синий | 30´15 | 20 | Синий | |
| 20´20 | 26 | Красный | 20´20 | 26 | Желтый | |
| 20´20 | 29 | Белый | 20´20 | 28 | Красный | |
| Комплекты | |||||
| Размер | Количество | Цвет | Размер | Количество | Цвет |
| 30´20 | 13 | Белый | 30´20 | 13 | Белый |
| 30´15 | 20 | Синий | 30´15 | 20 | Синий |
| 20´20 | 26 | Красный | 20´20 | 26 | Желтый |
Условное соединение
Условное соединение позволяет соединить таблицы по произвольным столбцам, не обязательно имеющим одинаковые имена. Кроме того, в качестве условия соединения может выступать не только равенство, но вообще любое логическое выражение, которое записывается после ключевого слова ON. Если условие выполняется для текущей записи декартового произведения исходных таблиц, то она входит в результатную таблицу.
Пусть в базе данных имеются следующие две таблицы:
· Сотрудники (Номер, Фамилия, Имя, Номер_отдела);
· Отделы (Номер, Название).
Тогда эти таблицы можно соединить:
SELECT *
FROM Сотрудники JOIN Клиенты
ON (Номер_отдела = Отделы.Номер);
Допустимо также использовать для условного соединения инструкцию INNER JOIN ON.
Конструкция JOIN была включена в стандарт SQL для того, чтобы условия соединения источников данных (условия ON) можно было отличить от условий принадлежности записей к итоговому набору (условия WHERE).
Например:
SELECT *
FROM Сотрудники, Клиенты
WHERE (Номер_отдела = Отделы.Номер)
AND (Фамилия = 'Иванов');
и
SELECT *
FROM Сотрудники JOIN Клиенты
ON (Номер_отдела = Отделы.Номер)
WHERE Фамилия = 'Иванов';
Две синтаксические формы функционально идентичны и возвращают одинаковые результаты. Синтаксис JOIN позволяет отделить критерий связи источников от критерия выбора записей, поскольку связи определяются только в секции ON. Это существенно упрощает чтение и модификацию запросов, так как не приходится разбираться в смысле каждого условия в секции WHERE.
Сложные соединения
Хотя одна секция JOIN соединяет всего два набора данных, на практике соединения не ограничиваются двумя источниками. За набором, созданным посредством соединения, может следовать новая секция JOIN – по аналогии с тем, как перечисляются через запятую источники данных.
Пример соединения нескольких источников данных:
SELECT last_name, publisher, e.isbn, subject
FROM authors AS a
JOIN books AS b ON a.id = b.author_id
JOIN editions AS e ON b.id = e.book_id
JOIN publishers AS p ON e.publisher_id = p.id
JOIN subjects AS s ON b.subject_id = s.id;
Хотя таблица books участвует в соединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для соединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).
Лабораторная работа 4
Выполнить следующие запросы к базе данных booktown:
1. Код isbn, дата издания, цена продажи и количество экземпляров для всех изданий на складе.
2. Дата покупки, код покупателя, дата издания, тип обложки для всех покупок. Отсортировать по дате издания.
3. Дата покупки, фамилия и имя покупателя для всех покупок. Отсортировать по фамилии и имени покупателя.
4. Список авторов с названиями написанных ими книг, отсортированный по авторам.
5. Список названий книг и фамилий их авторов с указанием темы, отсортировать по названию книги.
6. Фамилия и имя покупателя, название купленной им книги для всех покупок. Отсортировать по покупателям.
7. Все пары: название издательства, фамилия автора (произведение которого было издано данным издательством). Упорядочить по названию издательства.
8. Фамилия и имя покупателя, название издательства купленной им книги для всех покупок. Упорядочить по покупателю.
9. Список номеров ISBN и тип обложки для книг в бумажном переплете с указанием издательств, выпустивших книгу; список отсортировать по издательствам.
10. Фамилия автора и название тем, которые он отразил в своих произведениях. Упорядочить по автору.
11. Код ISBN, название книги, фамилия автора, затраты магазина на закупку всех имеющихся в наличии экземпляров. Отсортировать так, чтобы в начале списка шли самые малозатратные издания.
12. Название и число экземпляров книг, количество которых на складе равно нулю. Упорядочить по названию.
13. Название и дата продажи книги, которая была куплена самой первой в магазине.
14. Фамилия и имя автора книги, купленной предпоследней. Указать также дату продажи.
15. Название книги, дата издания, первое или повторное издание для всех изданий (если издание 1, то «первое»; иначе – «повторное»). Отсортировать по названию книги.
16. Название книги, издательство и тип обложки для книг, изданных издательством Roc в твердом переплете. Упорядочить по названию книги.
17. Название и тема книг, относящихся к темам Science и Science Fiction. Отсортировать по названию книги.
18. Названия книг, которые приобрела Annie Jackson (с указанием покупателя).
19. Название и издатель книг, изданных не издательствами Ace Books, Roc, Penguin, Doubleday, Random House.
20. Название книги и адрес издательства для книг, изданных не в городе New York.
ИТОГОВЫЕ ЗАПРОСЫ
Агрегатные функции
Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значение. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Агрегатные функции – особый класс функций, применяемых сразу к нескольким записям набора данных, но возвращающим одно значение. Обычно агрегатные функции используются в запросах с группировкой, но также встречается их применение и в запросах без группирования. В этом случае агрегатная функция обрабатывает все записи итогового набора.
Далее перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.
avg(выражение) Среднее арифметическое значений выражения для всех записей в группе
count(выражение) Количество записей в группе, для которых значение выражения отлично от NULL
max(выражение) Максимальное значение выражения в группе
min(выражение) Минимальное значение выражения в группе
stddev(выражение) Среднеквадратичное отклонение значений выражения в группе
sum(выражение) Сумма значений выражения в группе
variance(выражение) Дисперсия значений выражения в группе
Термин выражение означает любой столбец в итоговом наборе или любое выражение, выполняющее операцию с этим столбцом.
При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако возможно задать заголовки для значений итоговых функций и других столбцов по своему усмотрению. Для этого достаточно после имени столбца в операторе SELECT указать выражение вида AS заголовок_столбца.
Count(параметр) – возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей итогового набора, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT.
Например:
SELECT count(location) AS set_locs,
count(ALL location) AS all_locs,
count(DISTINCT location) AS unique_locs,
count(*) AS all_rows
FROM subjects;
Результат запроса:
set_locs all_locs unique_locs all_rows
15 15 7 16
Примеры использования других агрегатных функций:
SELECT AVG(retail) AS средняя_цена
FROM stock;
SELECT MIN(reatail * 28.8) AS минимальная_цена_в_долларах
FROM stock;
SELECT SUM(retail) AS общая_стоимость_редких_книг
FROM stock
WHERE stock < 10;
Группировка записей
Предложение GROUP BY в инструкции SELECT задает столбцы, используемые для формирования групп из выбранных строк. Строки каждой группы содержат одно и то же значение заданного столбца (столбцов). Выражение за ключевым словом GROUP BY может быть простым полем таблицы, оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей производится по совпадению значений во всех перечисленных выражениях. Появление секции GROUP BY в запросе SQL приводит к тому, что все записи с одинаковым значением выражений, заданных в предложении GROUP BY, группируются в одну запись. Если предложение GROUP BY расположено после предложения WHERE, то создаются группы из строк, выбранных после применения WHERE.
Необходимо понимать, что все целевые поля, указанные в секции SELECT, участвующие в запросе с группировкой, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Другими словами, при включении предложения GROUP BY в инструкцию SELECT список выбора может состоять только из выражений, указанных в предложении GROUP BY или из агрегатных функций.
Выведем количество книг, хранящихся в базе данных booktown для каждого издательства:
SELECT name AS publisher,
count(isbn) AS number_of_books
FROM editions AS e INNER JOIN publishers AS p
ON (e.publisher_id = p.id)
GROUP BY name;
Секция GROUP BY указывает на то, что записи объединенного набора данных должны группироваться по имени издательства. Все записи с одинаковым названием издательства группируются, после чего функция count() подсчитывает в каждой группе количество непустых значений поля isbn и возвращает результат – количество записей, объединенных в каждую группу для одного издательства.
Получим количество книг, написанных авторами по каждой теме:
SELECT last_name, first_name, subject,
count(title) AS number_of_books
FROM books AS b INNER JOIN authors AS a
ON (b.author_id = a.id)
INNER JOIN subjects AS s
ON(b.subject_id = s.id)
GROUP BY last_name, first_name, subject;
Отбор групп записей
Предложение HAVING, за которым следует условие отбора, определяет группы строк, которые включаются в результатную таблицу. Условие отбора будет применяться к каждой из групп, сформированных с помощью секции GROUP BY. Если некоторая группа не удовлетворяет условию отбора, то она не включается в результатную таблицу.
Разница между предложениями HAVING и WHERE заключается в том, что условие отбора, заданное в предложении WHERE, применяется к отдельным записям перед объединением их в группы, а условие отбора предложения HAVING применяется к группам строк. Секция WHERE не может содержать агрегатных функций. Условия же секции HAVING, наоборот, основаны на агрегатных функциях, а не на условиях для отдельных записей.
Выведем количество книг, хранящихся в базе данных booktown, для тех издательств, которые представлены двумя и более книгами:
SELECT name AS publisher,
count(isbn) AS number_of_books
FROM editions AS e INNER JOIN publishers AS p
ON (e.publisher_id = p.id)
GROUP BY name
HAVING count(isbn)>1;
Лабораторная работа 5
Выполнить следующие запросы к базе данных booktown:
1. Общее количество книг на складе (stock).
2. Количество книг с розничной ценой выше 30.
3. Выручка магазина при реализации всех книг.
4. Средняя цена (cost) книги.
5. Средняя цена (cost) для имеющихся книг на складе (stock).
6. Даты наиболее ранней и наиболее поздней публикаций.
7. Количество совершенных покупок.
8. Количество покупателей, совершавших покупки.
9. Количество покупателей с различными фамилиями, совершавших покупки.
10. Количество авторов, написавших какие-либо книги.
11. Количество изданий, выпущенных в твердой и мягкой обложке.
12. Количество первых изданий, вторых и так далее. В таком же порядке.
13. Количество книг, которые приобрел каждый покупатель. Отсортировать по покупателю.
14. Список покупателей-однофамильцев с подсчетом их количества.
15. Количество кодов isbn, которые были изданы каждым издательством. Отсортировать по названию издательства.
16. Количество книг различных названий, которые выпустило каждое издательство. Отсортировать по названию издательства.
17. Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
18. Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Издательства, выпустившие менее двух изданий (кодов isbn), не выводить. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
19. Первая дата публикации для каждого автора. Упорядочить по датам.
20. Фамилии авторов, чьи произведения не имеют изданий выше второго.
21. Фамилии авторов, все издания которых имеются в наличии в количестве не менее 50 экземпляров.
22. Фамилии авторов, все книги которых выходили только в бумажной обложке. Отсортировать по фамилии автора.
23. Наибольшее количество книг, написанных одним автором.
24. Наименьшее количество изданий, выпущенных одним издательством.
СЛОЖНЫЕ ЗАПРОСЫ
Дата: 2019-02-19, просмотров: 476.