Базы данных/Лабораторная работа 2 — различия между версиями

Версия 02:16, 16 апреля 2016

Задачи: освоить операции реляционной алгебры, оконные функции. Сохранение запросов в виде представлений. Фактически после этой работы вы сможете составить запрос любой сложности и создать удобное представление результатов.

Всё ещё в процессе написания, но задания можно начинать делать

Алиасы таблиц и выбираемых полей

Для удобства составления запросов можно присваивать таблицам или выборкам алиасы. Это удобно в случае, если в запросе участвует несколько таблиц и вы хотите обратиться к конкретной.

select (select title from title t where t.id=ci.movie_id) from cast_info ci;

Также можно присваивать алиасы значениям выбираемых полей. Это, например, удобно для последующей работы с этими полями:

select * from 
(select production_year, count(1) as cnt from title group by production_year) t
where t.cnt > 50000

Работа с реляционной алгеброй

В предыдущей лабораторной работе вы работали как правило с одной таблицей, выполняя операции селекции (добавляя условия в WHERE) и проекцией (перечисляя, что именно вы хотите выбрать между SELECT и FROM, в том числе, выполняя подзапросы к другим базам). Основные механизмы, которые дают большое преимущество реляционным СУБД относятся к взаимодействию нескольких отношений или результатов выборок. Эта лабораторная работа завершает введение и исчерпывает тему DML.

Объединения

Объединять множества в реляционных базах данных можно двумя способами: вертикально и горизонтально. Горизонтальные объединения используются намного чаще, так как реляционные базы нормализованы и нужные вам атрибуты могут быть раскиданы по разным сущностям. Посмотрите, как это выглядит в базе IMDb:

Используя связи между сущностями, вы можете горизонтально объединять таблицы и таким образом расширять результаты вышей выборки. Операции горизонтального соединения таблиц представлены в языках стандарта SQL ключевым словом JOIN. Рассмотрим варианты использования горизонтальных объединений.

Исчерпывающее руководство для визуалов:

Но если вы любите копировать команды из методички и смотреть, что получается, читайте дальше:)

Введение в JOIN

Присоединение обычно происходит по некоторым атрибутам, которые называются первичными и внешними ключами. Первичный ключ обычно называется id, он содержится во всех таблицах, которые претендуют на участие в верно построенной реляционной базе. Также таблицы могут содержать внешние ключи - это атрибуты, как правило типа unsigned integer, которые содержат те же значения, что и первичные ключи соответствующих сущностей.

Например, теперь, если вы хотите получить отчет, в котором будут следующие поля: имя актера, имя персонажа, фильм, год выпуска фильма, то вам нужно присоединить к таблице cast_info таблицы, где содержатся названия фильмов, имена актеров и имена персонажей, а именно: char_name, title и name, которые нужно присоединить по их первичным ключам.

Синтаксис команды join:

SELECT * FROM t1
JOIN t2 ON t1.id=t2.t1_id

В условии соединения ON можно указывать множество условий используя скобки и AND/OR.

Альтернативный способ выборки из нескольких таблиц:

SELECT * FROM t1, t2 
WHERE t1.id=t2.t1_id

В результате запросов вы получите все сопоставленные записи и атрибуты из обеих таблиц.

Внутреннее объединение INNER JOIN

Обычный JOIN сопоставляет кортежи соединяемых сущностей по указаному в ON правилу. Если какое-либо из значений по которому соединяется сущность не задано (NULL), то кортеж не попадет в результирующую выборку.

Пример человекопонятной выборки, кто где снимался:

select t.title, n.name, c.name from cast_info ca
join title t on t.id=ca.movie_id
join name n on n.id=ca.person_id
join char_name c on c.id=ca.person_role_id
where t.kind_id=1
limit 10;

Лимит указан для быстрой демонтсрации запроса.

Задания:

• Сделайте следующий отчет: название компании, выпускающей кинофильмы; название фильма; факты о фильме - для какой-либо выбранной вами кинокомпании (выберите более менее активную) за период в 5 последних лет. Диапазон дат укажите не конкретными датами, а относительно сегодняшнего дня, поищите соответствующие функции работы с временем.
• Соберите статистику по количеству фильмов выпускаемых разными странами (ищите страну в movie_info) и количество фильмов, для которых не указана страна.

Объединения слева и справа LEFT JOIN, RIGHT JOIN

В случае, если вы хотите обязательно выбрать все кортежи из первой таблицы, даже если нет соответствующих картежей во второй, используйте LEFT JOIN. Если соответствующих записей не будет, то атрибуты второй таблицы будут указаны как NULL.

Выбрать ключевые слова с названиями фильмов, но так чтобы все фильмы попали в выборку.

select t.title, k.keyword from title t left join movie_keyword mk on mk.movie_id=t.id left join keyword k on k.id=mk.keyword_id where mk.id is null limit 100;

Лимит указан для быстрой демонтсрации запроса.

RIGHT JOIN соответственно оставляет все значения в присоединяемой таблице, а для кортежей, для которых не удалось сопоставить кортежи из первой таблицы, прописывает NULL.

Задания:

1. Найдите фильмы, в которых нет персонажей.
2. Найдите актеров, которые никогда не снимались в фильмах. (двумя способами: с помощью JOIN и с помощью WHERE NOT EXIST)

Полное объединение FULL JOIN

Работает аналогично LEFT и RIGHT JOIN: в случае, если какому-то кортежу из первой или второй таблице не удалось найти пару, то вместо соответствующих значений прописывается NULL.

Пока никаких идей для полезного запроса, full join нужен обычно для того, чтобы обнаруживать нарушения целостности. Попробуйте узнать, есть ли в cast_info записи с невалидными идентификаторами актеров, фильмов или ролей и фильмы и роли, для которых нет записей в cast_info. Или проверьте, что в таблице фактов об актерах нет невалидных идентификаторов (ссылки на несуществующих актеров). Если вы корректно удалили актера в первой лабораторной работе или просто ничего не делали, то таких записей не должно быть.

Декартово произведение

Как выбло ранее показано, существует альтернативный способ выборки из нескольких таблиц:

SELECT * FROM t1, t2 WHERE t1.id=t2.t1_id

Если не указывать условий, то таблицы перемножатся: то есть в результате вы получите выборку, в которой каждая запись из одной таблицы сопоставляется с каждой записью из другой таблицы.

Посчитайте, сколько всего различных комбинаций актеров и персонажей может быть исходя из базы. В этой выборке следует также участь, что персонаж может появляться в разных фильмах, но называться одинаково, при этом просто умножив cast_info на что-то не получится. Выбирайте сразу количество, не выводя результаты выборки.

Операции над множествами

Вертикальные объединения обычно используют для манипуляции с готовыми запросами. Для того, чтобы объединить две выборки, нужно, чтобы у них совпадали имена атрибутов.

Объединение UNION

Объединяет кортежи выборок, при этом убирает дубли.

Полное объединение UNION ALL

Объединяет кортежи выборок, при этом не убирает дубли.

Пересечение INTERSECT

Возвращает кортежи, которые есть в обоих отношениях.

Вычитание EXCEPT

Возвращает кортежи, которые есть в первом, но нет во втором отношениях.

Оконные функции

[В процессе написания]

• http://www.postgresql.org/docs/9.3/static/tutorial-window.html
• https://habrahabr.ru/post/268983/

Создание представлений CREATE VIEW

[В процессе написания]

• http://postgresql.ru.net/gruber/ch20.html
• http://www.postgresql.org/docs/9.3/static/sql-createview.html

Комплексные выборки

Для выполнения этих заданий используйте всё, что знаете.

1. Выберите 5 своих любимых (или случайных) сериалов, поля: название сериала, количество эпизодов, среднее количество персонажей в каждом эпизоде, название эпизода с наибольшим количеством актеров, количество актеров в этом эпизоде.
2. Выберите топ 250 фильмов (см. соответствующие записи в movie_info) и для них выберите: название фильма, суммарные кассовые сборы, количество человек, участвовавших над созданием фильма, средний доход с фильма на человека: чистая прибыль (сборы минус бюджет) поделенная на количество людей.
3. Выберите топ 10 фильмов, над созданием которых потрудилось больше всего людей (таблица complete_cast), поля: название фильма, количество всех, кто участвовал в создании, количество актеров
4. Выберите топ 10 актеров, которые снялись в наибольшем количестве фильмов, поля: имя актера, дата рождения, количество фактов, количество фильмов, в которых он снялся, кинокомпания, с которой актер сотрудника над наибольшим количеством фильмов.
5. Выберите топ 10 кинорежиссеров, которые сняли фильмы с наибольшим количеством задействованных людей, поля: название фильма, среднее количество людей, которые принимают участие в фильме режиссера, дата рождения режиссера, количество фактов о режиссере.