Wiki - Факультет компьютерных наук - Вклад [ru]

О факультете

2017-04-23T17:23:19Z

Luc1ph3r: Переименовал «Компьютерные сети и базы данных 2» в «Базы данных 2», т.к. для сетей есть отдельная страница

__NOTOC__

== Учебные курсы факультета компьютерных наук==

=== Курсы за 2016/17 учебный год ===
{| class="wikitable"
|-
! 1 курс !! 2 курс !! 3-4 курс !! майноры
|-
|

[[Математический анализ на ПМИ_2016/2017 | Математический анализ на ПМИ (пилотный поток)]]

[[Линейная алгебра и геометрия_2016/2017 | Линейная алгебра и геометрия на ПМИ]]

[[Дискретная_математика_1_2016/2017 | Дискретная математика-1 на ПМИ]]

[[Основы и методология программирования_2016/2017_пилотный_поток | Основы и методология программирования на ПМИ (пилотный поток)]]

[[Основы и методология программирования_2016/2017 | Основы и методология программирования на ПМИ (основной поток)]]

[[Алгоритмы_и_структуры_данных_на_ПМИ_(пилотный_поток) | Алгоритмы и структуры данных на ПМИ (пилотный поток)]]

[[Алгоритмы_и_структуры_данных_на_ПМИ_(основной_поток) | Алгоритмы и структуры данных на ПМИ (основной поток)]]

[[Алгебра_на_ПМИ_2016/2017 | Алгебра на ПМИ]]

[http://hsealgebra17.wikidot.com/ Алгебра на ПИ]

||

[[Математический анализ_2016/2017 | Математический анализ-3 на ПМИ (основной поток)]]

[[Математически_анализ_3_на_ПМИ_(пилотный_поток) | Математический анализ-3 на ПМИ (пилотный поток)]]

[[DM_2_2016_2017 | Дискретная математика-2 на ПМИ (основной поток)]]

[[Дискретная математика_2_2016/2017 | Дискретная математика-2 на ПМИ (пилотный поток)]]

[[Алгоритмы и структуры данных_2_2016/2017 | Алгоритмы и структуры данных – 2 на ПМИ (основной поток)]]

[[Теория вероятностей_2016/2017 | Теория вероятностей на ПМИ (основной поток)]]

[[Теория_вероятностей_2016/2017_(пилотный_поток) | Теория вероятностей на ПМИ (пилотный поток)]]

[[Архитектура_компьютеров_и_операционные_системы_2016/2017 | Архитектура компьютеров и операционные системы]]

[[Факультатив_теория_вычислений_2016/2017 | Факультатив теория вычислений на ПМИ]]

[[Дополнительные_главы_теории_вероятностей_(факультатив,_2017) | Дополнительные главы теории вероятностей (факультатив)]]

[[Дифференциальные_уравнения_(2_курс,_2016/2017) | Дифференциальные уравнения]]

[[Математическая_статистика_2016/2017_(пилотный_поток) | Математическая статистика на ПМИ (пилотный поток)]]

||

[[НИС_Машинное_обучение_и_приложения_2016/2017 | НИС Машинное обучение и приложения на ПМИ]]

[[Машинное_обучение_1 | Машинное обучение 1 на ПМИ]]

[[Машинное_обучение_2 | Машинное обучение 2 на ПМИ]]

[[Прикладной_статистический_анализ_данных | Прикладной статистический анализ данных на ПМИ]]

[[Численные_методы_в_анализе_данных | Численные методы в анализе данных на ПМИ]]

[http://www.machinelearning.ru/wiki/index.php?title=Статистика_случайных_процессов_(курс_лекций,_ФКН_ВШЭ) Вероятностные модели и статистика случайных процессов на ПМИ]

[http://www.machinelearning.ru/wiki/index.php?title=Opt Методы оптимизации на ПМИ (специализации МОП и РС)]

[[НИС_Распределенные_системы_(осень_2016) | НИС Распределенные системы]]

[[Анализ и верификация алгоритмов биржевой торговли | Анализ и верификация алгоритмов для систем биржевой торговли ]]

[[Программирование_на_графических_процессорах | Программирование на графических процессорах]]

[[ЯРПО | Языки разработки ПО (курс по выбору) на ПМИ]]

[[Data analysis (Software Engineering) 2017 | Data analysis на ПИ]]

[[Базы данных 2 | Базы данных 2 ]]

[[Компьютерные сети 2 | Компьютерные сети 2]]

[[Машинное_обучение_на_больших_данных | Машинное обучение на больших данных на ПМИ]]

||

[[Современные_методы_машинного_обучения_(курс_майнора) | Современные методы машинного обучения (курс майнора)]]

[[Майнор_Интеллектуальный_анализ_данных/Введение_в_программирование_2016/2017 | Введение в программирование (курс майнора)]]

[[Майнор Интеллектуальный анализ данных/Введение в анализ данных | Введение в анализ данных (курс майнора)]]

[[Майнор Интеллектуальный анализ данных/Прикладные задачи анализа данных| Прикладные задачи анализа данных (курс майнора)]]

|}

{|width=100%
|style="vertical-align:top;"|



=== Курсы за 2015/16 учебный год ===
{|
|-
| [[Технологии программирования|Технологии программирования на ПМИ]]
|-
|[[ОиМП-2015|Основы и методология программирования на ПМИ]]
|-
|[[Алгоритмы и структуры данных]]
|-
|[[Линейная алгебра и геометрия_2015/2016 | Линейная алгебра и геометрия на ПМИ]]
|-
|[[Алгебра_2015/2016 | Алгебра на ПМИ]]
|-
|[http://hsealgebra.wikidot.com/ Алгебра на ПИ]
|-
|[[Компьютерные системы]]
|-
|[[Математический анализ на ПМИ_2015/2016 | Математический анализ на ПМИ]]
|-
|[[Факультатив_Матпрактикум | Матпрактикум (факультатив) на ПМИ]]
|-
|[[Data analysis (Software Engineering)]] 
|-
|[[Майнор Интеллектуальный анализ данных/Введение в программирование|Введение в программирование (курс майнора) на ПМИ]]
|-
|[[Майнор Интеллектуальный анализ данных/Введение в анализ данных/2015-2016|Введение в анализ данных (курс майнора) на ПМИ]]
|-
|[[НИС Машинное обучение и приложения|НИС Машинное обучение и приложения на ПМИ]]
|-
|[[Архитектура компьютеров и системное программирование (ПМИ_4, 2015/2016)|Архитектура компьютеров и системное программирование (4 курс)]]
|-
|[[Дифференциальные уравнения (2 курс, 2015/2016)| Дифференциальные уравнения]]
|-
|[[Введение в VBA|Введение в VBA]]
|}

|style="vertical-align:top;"|



=== Курсы за 2014/15 учебный год ===
{|
|-
|[[Основы и методологии программирования]] 
|-
|[[Алгоритмы и структуры данных 2015 | Алгоритмы и структуры данных]] 
|-
|[[Анализ данных (Программная инженерия)]] 
|-
|[[Алгебра_2014/2015 | Алгебра]] 
|-
|[[Magolego_sna_2015| MAGoLEGO Social Network Analysis]]
|}

==== Проектная работа ====
{|
|-
|[[Проектная работа]] 
|-
|[[Учебная практика 1 курс (2016)]]
|-
|[[Проектная работа 2 курс (2016)]]
|}


|}

== Мероприятия факультета компьютерных наук ==
=== Summer School 2015 ===
[[Introduction to Natural Language Processing|Introduction to Natural Language Processing]]
#[[Lecture 1. Introduction|Introduction]]
#[[Lecture 2. Tokenization and word counts|Tokenization and word counts]]
#[[Lecture 3. POS tagging. Key word and phrase extraction|POS tagging. Key word and phrase extraction]]
#[[Lecture 4. Parsing|Parsing]]
#[[Lecture 5. Language sources|Language sources]]
#[[Lecture 6. Synonyms and near-synonyms detection|Synonyms and near-synonyms detection]]
#[[Lecture 8. Suffix trees for NLP|Suffix trees for NLP]]
#[[NLP References|References]]

== Архив ==
* [[Учебная практика 1 курс (2015)]]

Базы данных/Лабораторная работа 1

2016-06-04T11:08:05Z

Luc1ph3r: Добавлена информация о том, где находятся логи.

Задачи: освоить самые необходимые навыки настройки СУБД, получения данных и манипуляции с ними, а также составления простых отчетов.

=== Введение ===

Так как у некоторых групп лабораторные работы начинаются раньше первой лекции, то предлагается [http://wiki.cs.hse.ru/%D0%91%D0%B0%D0%B7%D1%8B_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85/%D0%9E%D1%81%D0%BD%D0%BE%D0%B2%D0%BD%D1%8B%D0%B5_%D1%82%D0%B5%D1%80%D0%BC%D0%B8%D0%BD%D1%8B краткий список терминов], используемых в лабораторной работе.

PostgreSQL - популярная реляционная система управления базами данных. Эта СУБД используется многими крупными компаниями, являясь единственной хорошо развитой свободной альтернативой наряду с MySQL. Но по сравнению с MySQL, PostgreSQL предоставляет больше возможностей для работы с большими объемами данных (не "big data", но до терабайта).

В качестве базы данных в лабораторных работах будет использоваться база фильмов IMDB (сам сайт также использует эту СУБД). Дамп базы достаточно большой, поэтому, если у вас есть возможность, скачайте и импортируйте его заранее.

Рекомендуется использовать Ubuntu 14.04 и PostgreSQL 8.1+. Также нужно примерно 10 Гб места на диске.

Для выполнения запросов подойдет и терминал, но можно использовать IDE (например, DataGrip или любую другую от JetBrains с аналогичным плагином).

База данных, которая используется в лабораторных работах: https://yadi.sk/d/EVhJUiroqgzWj

=== Часть 1: Установка PostgreSQL ===

Первая задача состоит в том, чтобы установить СУБД и проверить ее работоспособность.

Выполните в терминале:

sudo apt-get update && sudo apt-get install postgresql postgresql-contrib

Сервер PostgreSQL создает отдельно пользователя в системе для доступа к базе. Чтобы переключиться на этого пользователя, выполните:

sudo -i -u postgres

Теперь вы можете войти в интерактивный режим работы с СУБД:

psql

Приглашение в интерактивном режиме выглядит так:

postgres=#

Чтобы посмотреть, какие базы уже есть в системе, наберите:

\l

Примерный результат:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

Альтернативно, можно выполнить запрос:

SELECT datname FROM pg_database;

Чтобы работать с конкретной базой, ее нужно выбрать. Выполните \c database_name:

postgres=# \c imdb
You are now connected to database "imdb" as user "postgres".

Чтобы узнать, какие таблицы есть базе, выполните:

\d

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

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Чтобы узнать, какие есть колонки в таблице:

\d+ table

или

\d table

Альтернативный запрос:...

SELECT column_name FROM information_schema.columns WHERE table_name ='table';

=== Часть 2: Основы администрирования PostgreSQL ===

Следующая задача состоит в том, чтобы настроить два важный параметра:

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

Про второй механизм подробнее. Когда вы вносите изменения в данные, они не сразу вступают в силу. СУБД создает diff аналогичный тому, который можно видеть в git. После этого вы начинаете работать с измененной версией, но в других сессиях данные по-прежнему старые. Если вы что-то сделали неправильно, вы можете откатить изменения в своей сессии с помощью команды rollback. Если же все изменения корректны, подтвердите их, выполнив commit. Закоммиченные изменения откатить намного сложнее, поэтому как правило в СУБД отключают опцию autocommit, которая подтверждает изменения автоматически.

Когда вы завершаете сессию, выполняется rollback. Если вы убиваете процесс, то он может еще некоторое время "держать" данные, не давая их изменить.

Приступим к конфигурированию.

PostgreSQL представлен в системе в виде сервиса, управлять которым можно как и обычно через команду service. Как правило, для внесения каких-либо изменений нужно перезапустить сервис.

Конфигурационный файл:

sudo vim /etc/postgresql/9.*/main/postgresql.conf

Допишите или раскомментируйте:

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Управлять некоторыми параметрами можно прямо из сессии с СУБД. Например включение подробного логирования:

SELECT set_config('log_statement', 'all', true);

Если вы используете Ubuntu, то по умолчанию лог-файлы могут быть найдены по пути <code>/var/log/postgresql/*</code>. Если же у вас другой дистрибутив, то конфигурация может отличаться. (Например, для <code>ArchLinux</code> нужно в конфигурационном файле <code>/var/lib/postgres/data/postgresql.conf</code> указать режим логированния с помощью <code>log_destination='stderr'</code> и включить запись в файл <code>logging_collector=on</code>. На самом деле, лучше найти эти строчки в конфигурационном файле и почитать, что они значат. Внутри конфига всё хорошо объяснено).

Чтобы отключить автокоммит, от пользователя postgres допишите в файл или создайте новый, если его нет ~/.psqlrc:

\set AUTOCOMMIT off

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

BEGIN;
-- манипуляции с данными
COMMIT;

=== Часть 3: импорт и экспорт базы данных IMDB ===

Две наиболее важные операции. Выполняйте в сессии пользователя postgres.

Экспортировать базу данных:

pg_dump dbname | gzip > filename.gz

Импортировать базу:

gunzip -c filename.gz | psql dbname

Попробуйте импортировать базу IMDB:

https://yadi.sk/d/EVhJUiroqgzWj

'''Важно:''' прежде чем импортировать дамп, нужно создать базу данных:

В psql выполните:

create database imdb;

'''Важно: прежде чем импортировать дамп, нужно включить автокоммит.'''

Это займет некоторое время (20 минут - норм).

Также можно отдельно импортировать схему и данные частями:

https://yadi.sk/d/759CTPxpqoCs2

Используйте, например: ls imdb3*.gz | xargs gunzip | psql dbname

Также можно импортировать только конкретные таблицы, указав их через ключ --table.

Можно импортировать только схему: --schema-only или только данные: --data-only

==== Структура базы IMDB ====

У каждой таблицы есть идентификатор, указанный как первичный ключ (id). По нему выбирать быстрее всего.

Основные таблицы и их описание:
* title - названия фильмов (поле title) и год выпуска (поле production_year); если это сериал, то также здесь можно найти номер эпизода
* movie_info - характеристики и факты о фильме: movie_id - идентификатор из таблицы title (далее для краткой записи: title.id), info_type_id - идентификатор из таблицы info_type (info_type.id), info - текстовое поле со значением характеристики.
* name - актеры (имя и пол)
* person_info - характеристики и факты об актерах также с названиями характеристик из (info_type.id)
* char_name - роли (имена персонажей)
* cast_info - таблица со связью ролей (person_role_id), актеров (person_id) и фильмов (movie_id)

=== Часть 4: Простые операции CRUD ===

К простым операциям манипуляции данными (Create, Read, Update, Delete) относятся:

* Добавление: INSERT
* Выборка: SELECT
* Обновление: UPDATE
* Удаление: DELETE

==== Добавление данных INSERT ====

Чтобы добавить новую запись в таблицу, нужно вычислить ее идентификатор. Для этого в PostgreSQL используются последовательности - числа, которые меняются по заданным правилам (обычно просто инкрементируются на единицу).

Чтобы посмотреть список всех последовательностей выполните:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

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

Синтаксис INSERT выглядит так: сначала в скобках перечисляются атрибуты, которые будут вставлены, а затем после VALUES в скобках указываются значения. Можно также не перечислять атрибуты, тогда в VALUES нужно по порядку указать значения для всех.

Попробуйте добавить себя в список актеров:

insert into name (id, name, gender) values(nextval('name_id_seq'), 'Ivan Savin', 'm');

Здесь nextval('name_id_seq') генерирует следующее значение для последовательности name_id_seq.

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

select max(id) from name;
select setval('name_id_seq', 5555233);

Если вы отключили автокоммит, то, так как вы вносите изменения в данные, завершите операцию, выполнив:

commit;

Если вы не уверены в своих изменениях, выполните:

rollback;

За одну операцию INSERT можно вставлять несколько строк данных. Для этого после VALUES нужно перечислить кортежи данных через запятую:

insert into name (id, name, gender)
values(nextval('name_id_seq'), 'Dmitry Burmistrov', 'm'),
(nextval('name_id_seq'), 'Victor Yakovlev', 'm');

==== Выборка данных SELECT ====

Для чтения данных из базы используется ключевое слово SELECT, после которого указывается список атрибутов, которые нужно получить в выборке. Если указать вместо списка атрибутов "*", то выберутся все. Самый простой запрос выборки из базы данных выглядит следующим образом:

select * from info_type;

Не пробуйте выбрать все данные из больших таблиц (title, name) - это займет много времени. Если вы хотите выбрать несколько кортежей данных для примера, то ограничьте результаты с помощью LIMIT:

select * from title limit 10;

Условия выборки указываются после ключевого слова WHERE. Условия можно комбинировать с помощью скобок и слов OR и AND. Примеры условий:

* WHERE title='Databases' - простое условие равенства
* WHERE title like '%base%' - поиск по подстроке, "%" - любое количество любых символов
* WHERE created_date > now() - сравнение даты с текущим моментом; см. также http://www.postgresql.org/docs/8.3/static/functions-datetime.html
* WHERE title not in ('Databases', 'Networks') - значение не входит в список
* WHERE not exists (SELECT * FROM ...) - выполняется, если подзапрос вернул хотя бы одну запись
* WHERE artist_id in (SELECT id FROM artist...) - подзапрос определяет множество значений.

Пример запроса с условиями:

select * from title where title like '%Matrix' and production_year=1999;

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

select (select info from info_type where info_type.id=person_info.info_type_id), person_info.info from person_info where person_id=1732058;

Если нужно вывести только уникальные кортежи, используйте distinct:

select distinct production_year from title;

Попробуйте найти ваши любимые фильмы, указывая часть названия и комбинируя условия, указывая год выхода. Попробуйте найти ваших любимых актеров и факты о них.

==== Обновление данных UPDATE ====

Чтобы обновить данные, нужно указать, какие параметры вы хотите обновить и условия выборки обновляемых данных:

update cast_info set person_id=1732058 where movie_id=3514559;

Если вы не укажите условия, то обновятся значения во всей таблице, обычно это не нужно.

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

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

update cast_info set person_id=(select id from name where name='Savin Ivan')
where movie_id=(select id from title where title like 'The Matrix' and production_year=1999);

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

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

==== Удаление данных DELETE ====

Синтаксис удаления данных аналогичен синтаксису выборки за исключением того, что вместо "SELECT * FROM" достаточно написать "DELETE FROM". Будьте внимательны, удаляя данные и проверяйте условия перед отправкой коммита.

Удалите актеров, которые вам не нравятся (или актера, выбранного случайно). Это сделать не так просто, как может показаться, потому что их идентификаторы используются в других таблицах, а нарушать целостность данных нельзя. Для корректного удаления, нужно найти все связи актера с фильмами и удалить сначала их, после этого удалить информацию о них из таблицы фактов, после этого станет доступно удаление.

=== Часть 5: Агрегация данных ===

Часто с помощью СУБД генерируют различные полезные отчеты. В любой популярной СУБД есть агрегирующие функции, с помощью которых, можно собрать статистику о данных. Самая простая статистика: количество записей, удовлетворяющих заданным условиям. Пример выбора количества фильмов в базе:

select count(1) from title where kind_id=(select id from kind_type where kind='movie');

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

Попробуйте вывести, в скольких фильмах снимались ваши любимые актеры.

==== Преобразование атрибутов по некоторым правилам ====

Если нужно преобразовать какой-либо атрибут по правилам (условиям), то можно использоваться конструкцию CASE ... WHEN ... THEN ... END.
В этом случае данная конструкция будет обозначать новый атрибут у записи. Рассмотрим её детальнее.

case
when '''''condition1''''' then '''''result1'''''
when '''''condition2''''' then '''''result2'''''
else '''''result3'''''
end

Если условие '''''condition1''''' верно (т.е. '''true'''), то атрибуту будет
присвоено значение '''''result1'''''. Если '''''condition1''''' неверно, то <code>case</code> перейдёт
к следующему <code>when</code>. Если ни один из '''''condition''''' не выполняется, то атрибуту будет
присвоено значение, указанное в <code>else</code> (если в этом случае отсутствует <code>else</code>, то
атрибут будет равен <code>null</code>).

Представим таблицу <code>T</code>:
{| class="wikitable"
|-
! a
|-
| 1
|-
| 2
|-
| 3
|-
| 10
|}

Добавим теперь к каждой записи новый атрибут, который будет обозначать значение атрибута <code>a</code> словами:
select
a,
case
when a = 1 then 'one'
when a = 2 then 'two'
when a = 3 then 'three'
else 'other'
end
from t;

Результат:
{| class="wikitable"
|-
! a
! case
|-
| 1
| one
|-
| 2
| two
|-
| 3
| three
|-
| 10
| other
|}

Как видно из результата, новый атрибут получил название <code>case</code>. Если мы хотим переименовать его в
желаемый вариант, то это можно сделать с помощью <code>as</code>:
select
a,
case
when a = 1 then 'one'
when a = 2 then 'two'
when a = 3 then 'three'
else 'other'
end as new_attribute
from t;

{| class="wikitable"
|-
! a
! new_attribute
|-
| 1
| one
|-
| 2
| two
|-
| 3
| three
|-
| 10
| other
|}

Также, чтобы каждый раз не писать <code>when a = </code>, можно сразу указать, по какому атрибуту мы бежим:
select
a,
case a
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
else 'other'
end as new_attribute
from t;

Таким образом, используя <code>case..when..</code> можно помечать нужные нам записи для дальнейшей обработки данных.
Например, добавить к фильму атрибут, обозначающий, относится фильм к 20 или 21 веку.

select
*,
case
when production_year >= 1900 and production_year < 2000 then 'XX'
when production_year >= 2000 and production_year < 2100 then 'XXI'
end as century
from title

Сокращённый результат:
{| class="wikitable"
|-
| id
| 2395294
|-
| title
| (1979-03-11)
|-
| production_year
| 1979
|-
| century
| XX
|}

==== Группировка агрегированных данных GROUP BY ====

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

select production_year, count(1) from title
where kind_id=(select id from kind_type where kind='movie')
group by production_year
order by production_year;

В запросе также результаты отсортированы по году выпуска с помощью ORDER BY.

Попробуйте собрать следующие статистики: количество актеров и актрис; среднее количество фильмов в год, выпущенных в XX веке, и, выпущенных в XXI веке (с учетом текущего года).

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

=== Защита лабораторной работы ===
# Показать логи выполнения запросов.
# Прокомментировать для агрегаций, что и как происходит.
# Написать запрос по просьбе преподавателя.

=== Полезные материалы ===

Интерактивный урок по основам SQL запросов (видео лекции + задания): https://www.codeschool.com/courses/try-sql

Базы данных/Лабораторная работа 1

2016-06-03T20:45:57Z

Luc1ph3r: Добавлена информация про использование case..when

Задачи: освоить самые необходимые навыки настройки СУБД, получения данных и манипуляции с ними, а также составления простых отчетов.

=== Введение ===

Так как у некоторых групп лабораторные работы начинаются раньше первой лекции, то предлагается [http://wiki.cs.hse.ru/%D0%91%D0%B0%D0%B7%D1%8B_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85/%D0%9E%D1%81%D0%BD%D0%BE%D0%B2%D0%BD%D1%8B%D0%B5_%D1%82%D0%B5%D1%80%D0%BC%D0%B8%D0%BD%D1%8B краткий список терминов], используемых в лабораторной работе.

PostgreSQL - популярная реляционная система управления базами данных. Эта СУБД используется многими крупными компаниями, являясь единственной хорошо развитой свободной альтернативой наряду с MySQL. Но по сравнению с MySQL, PostgreSQL предоставляет больше возможностей для работы с большими объемами данных (не "big data", но до терабайта).

В качестве базы данных в лабораторных работах будет использоваться база фильмов IMDB (сам сайт также использует эту СУБД). Дамп базы достаточно большой, поэтому, если у вас есть возможность, скачайте и импортируйте его заранее.

Рекомендуется использовать Ubuntu 14.04 и PostgreSQL 8.1+. Также нужно примерно 10 Гб места на диске.

Для выполнения запросов подойдет и терминал, но можно использовать IDE (например, DataGrip или любую другую от JetBrains с аналогичным плагином).

База данных, которая используется в лабораторных работах: https://yadi.sk/d/EVhJUiroqgzWj

=== Часть 1: Установка PostgreSQL ===

Первая задача состоит в том, чтобы установить СУБД и проверить ее работоспособность.

Выполните в терминале:

sudo apt-get update && sudo apt-get install postgresql postgresql-contrib

Сервер PostgreSQL создает отдельно пользователя в системе для доступа к базе. Чтобы переключиться на этого пользователя, выполните:

sudo -i -u postgres

Теперь вы можете войти в интерактивный режим работы с СУБД:

psql

Приглашение в интерактивном режиме выглядит так:

postgres=#

Чтобы посмотреть, какие базы уже есть в системе, наберите:

\l

Примерный результат:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

Альтернативно, можно выполнить запрос:

SELECT datname FROM pg_database;

Чтобы работать с конкретной базой, ее нужно выбрать. Выполните \c database_name:

postgres=# \c imdb
You are now connected to database "imdb" as user "postgres".

Чтобы узнать, какие таблицы есть базе, выполните:

\d

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

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Чтобы узнать, какие есть колонки в таблице:

\d+ table

или

\d table

Альтернативный запрос:...

SELECT column_name FROM information_schema.columns WHERE table_name ='table';

=== Часть 2: Основы администрирования PostgreSQL ===

Следующая задача состоит в том, чтобы настроить два важный параметра:

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

Про второй механизм подробнее. Когда вы вносите изменения в данные, они не сразу вступают в силу. СУБД создает diff аналогичный тому, который можно видеть в git. После этого вы начинаете работать с измененной версией, но в других сессиях данные по-прежнему старые. Если вы что-то сделали неправильно, вы можете откатить изменения в своей сессии с помощью команды rollback. Если же все изменения корректны, подтвердите их, выполнив commit. Закоммиченные изменения откатить намного сложнее, поэтому как правило в СУБД отключают опцию autocommit, которая подтверждает изменения автоматически.

Когда вы завершаете сессию, выполняется rollback. Если вы убиваете процесс, то он может еще некоторое время "держать" данные, не давая их изменить.

Приступим к конфигурированию.

PostgreSQL представлен в системе в виде сервиса, управлять которым можно как и обычно через команду service. Как правило, для внесения каких-либо изменений нужно перезапустить сервис.

Конфигурационный файл:

sudo vim /etc/postgresql/9.*/main/postgresql.conf

Допишите или раскомментируйте:

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Управлять некоторыми параметрами можно прямо из сессии с СУБД. Например включение подробного логирования:

SELECT set_config('log_statement', 'all', true);

Чтобы отключить автокоммит, от пользователя postgres допишите в файл или создайте новый, если его нет ~/.psqlrc:

\set AUTOCOMMIT off

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

BEGIN;
-- манипуляции с данными
COMMIT;

=== Часть 3: импорт и экспорт базы данных IMDB ===

Две наиболее важные операции. Выполняйте в сессии пользователя postgres.

Экспортировать базу данных:

pg_dump dbname | gzip > filename.gz

Импортировать базу:

gunzip -c filename.gz | psql dbname

Попробуйте импортировать базу IMDB:

https://yadi.sk/d/EVhJUiroqgzWj

'''Важно:''' прежде чем импортировать дамп, нужно создать базу данных:

В psql выполните:

create database imdb;

'''Важно: прежде чем импортировать дамп, нужно включить автокоммит.'''

Это займет некоторое время (20 минут - норм).

Также можно отдельно импортировать схему и данные частями:

https://yadi.sk/d/759CTPxpqoCs2

Используйте, например: ls imdb3*.gz | xargs gunzip | psql dbname

Также можно импортировать только конкретные таблицы, указав их через ключ --table.

Можно импортировать только схему: --schema-only или только данные: --data-only

==== Структура базы IMDB ====

У каждой таблицы есть идентификатор, указанный как первичный ключ (id). По нему выбирать быстрее всего.

Основные таблицы и их описание:
* title - названия фильмов (поле title) и год выпуска (поле production_year); если это сериал, то также здесь можно найти номер эпизода
* movie_info - характеристики и факты о фильме: movie_id - идентификатор из таблицы title (далее для краткой записи: title.id), info_type_id - идентификатор из таблицы info_type (info_type.id), info - текстовое поле со значением характеристики.
* name - актеры (имя и пол)
* person_info - характеристики и факты об актерах также с названиями характеристик из (info_type.id)
* char_name - роли (имена персонажей)
* cast_info - таблица со связью ролей (person_role_id), актеров (person_id) и фильмов (movie_id)

=== Часть 4: Простые операции CRUD ===

К простым операциям манипуляции данными (Create, Read, Update, Delete) относятся:

* Добавление: INSERT
* Выборка: SELECT
* Обновление: UPDATE
* Удаление: DELETE

==== Добавление данных INSERT ====

Чтобы добавить новую запись в таблицу, нужно вычислить ее идентификатор. Для этого в PostgreSQL используются последовательности - числа, которые меняются по заданным правилам (обычно просто инкрементируются на единицу).

Чтобы посмотреть список всех последовательностей выполните:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

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

Синтаксис INSERT выглядит так: сначала в скобках перечисляются атрибуты, которые будут вставлены, а затем после VALUES в скобках указываются значения. Можно также не перечислять атрибуты, тогда в VALUES нужно по порядку указать значения для всех.

Попробуйте добавить себя в список актеров:

insert into name (id, name, gender) values(nextval('name_id_seq'), 'Ivan Savin', 'm');

Здесь nextval('name_id_seq') генерирует следующее значение для последовательности name_id_seq.

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

select max(id) from name;
select setval('name_id_seq', 5555233);

Если вы отключили автокоммит, то, так как вы вносите изменения в данные, завершите операцию, выполнив:

commit;

Если вы не уверены в своих изменениях, выполните:

rollback;

За одну операцию INSERT можно вставлять несколько строк данных. Для этого после VALUES нужно перечислить кортежи данных через запятую:

insert into name (id, name, gender)
values(nextval('name_id_seq'), 'Dmitry Burmistrov', 'm'),
(nextval('name_id_seq'), 'Victor Yakovlev', 'm');

==== Выборка данных SELECT ====

Для чтения данных из базы используется ключевое слово SELECT, после которого указывается список атрибутов, которые нужно получить в выборке. Если указать вместо списка атрибутов "*", то выберутся все. Самый простой запрос выборки из базы данных выглядит следующим образом:

select * from info_type;

Не пробуйте выбрать все данные из больших таблиц (title, name) - это займет много времени. Если вы хотите выбрать несколько кортежей данных для примера, то ограничьте результаты с помощью LIMIT:

select * from title limit 10;

Условия выборки указываются после ключевого слова WHERE. Условия можно комбинировать с помощью скобок и слов OR и AND. Примеры условий:

* WHERE title='Databases' - простое условие равенства
* WHERE title like '%base%' - поиск по подстроке, "%" - любое количество любых символов
* WHERE created_date > now() - сравнение даты с текущим моментом; см. также http://www.postgresql.org/docs/8.3/static/functions-datetime.html
* WHERE title not in ('Databases', 'Networks') - значение не входит в список
* WHERE not exists (SELECT * FROM ...) - выполняется, если подзапрос вернул хотя бы одну запись
* WHERE artist_id in (SELECT id FROM artist...) - подзапрос определяет множество значений.

Пример запроса с условиями:

select * from title where title like '%Matrix' and production_year=1999;

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

select (select info from info_type where info_type.id=person_info.info_type_id), person_info.info from person_info where person_id=1732058;

Если нужно вывести только уникальные кортежи, используйте distinct:

select distinct production_year from title;

Попробуйте найти ваши любимые фильмы, указывая часть названия и комбинируя условия, указывая год выхода. Попробуйте найти ваших любимых актеров и факты о них.

==== Обновление данных UPDATE ====

Чтобы обновить данные, нужно указать, какие параметры вы хотите обновить и условия выборки обновляемых данных:

update cast_info set person_id=1732058 where movie_id=3514559;

Если вы не укажите условия, то обновятся значения во всей таблице, обычно это не нужно.

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

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

update cast_info set person_id=(select id from name where name='Savin Ivan')
where movie_id=(select id from title where title like 'The Matrix' and production_year=1999);

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

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

==== Удаление данных DELETE ====

Синтаксис удаления данных аналогичен синтаксису выборки за исключением того, что вместо "SELECT * FROM" достаточно написать "DELETE FROM". Будьте внимательны, удаляя данные и проверяйте условия перед отправкой коммита.

Удалите актеров, которые вам не нравятся (или актера, выбранного случайно). Это сделать не так просто, как может показаться, потому что их идентификаторы используются в других таблицах, а нарушать целостность данных нельзя. Для корректного удаления, нужно найти все связи актера с фильмами и удалить сначала их, после этого удалить информацию о них из таблицы фактов, после этого станет доступно удаление.

=== Часть 5: Агрегация данных ===

Часто с помощью СУБД генерируют различные полезные отчеты. В любой популярной СУБД есть агрегирующие функции, с помощью которых, можно собрать статистику о данных. Самая простая статистика: количество записей, удовлетворяющих заданным условиям. Пример выбора количества фильмов в базе:

select count(1) from title where kind_id=(select id from kind_type where kind='movie');

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

Попробуйте вывести, в скольких фильмах снимались ваши любимые актеры.

==== Преобразование атрибутов по некоторым правилам ====

Если нужно преобразовать какой-либо атрибут по правилам (условиям), то можно использоваться конструкцию CASE ... WHEN ... THEN ... END.
В этом случае данная конструкция будет обозначать новый атрибут у записи. Рассмотрим её детальнее.

case
when '''''condition1''''' then '''''result1'''''
when '''''condition2''''' then '''''result2'''''
else '''''result3'''''
end

Если условие '''''condition1''''' верно (т.е. '''true'''), то атрибуту будет
присвоено значение '''''result1'''''. Если '''''condition1''''' неверно, то <code>case</code> перейдёт
к следующему <code>when</code>. Если ни один из '''''condition''''' не выполняется, то атрибуту будет
присвоено значение, указанное в <code>else</code> (если в этом случае отсутствует <code>else</code>, то
атрибут будет равен <code>null</code>).

Представим таблицу <code>T</code>:
{| class="wikitable"
|-
! a
|-
| 1
|-
| 2
|-
| 3
|-
| 10
|}

Добавим теперь к каждой записи новый атрибут, который будет обозначать значение атрибута <code>a</code> словами:
select
a,
case
when a = 1 then 'one'
when a = 2 then 'two'
when a = 3 then 'three'
else 'other'
end
from t;

Результат:
{| class="wikitable"
|-
! a
! case
|-
| 1
| one
|-
| 2
| two
|-
| 3
| three
|-
| 10
| other
|}

Как видно из результата, новый атрибут получил название <code>case</code>. Если мы хотим переименовать его в
желаемый вариант, то это можно сделать с помощью <code>as</code>:
select
a,
case
when a = 1 then 'one'
when a = 2 then 'two'
when a = 3 then 'three'
else 'other'
end as new_attribute
from t;

{| class="wikitable"
|-
! a
! new_attribute
|-
| 1
| one
|-
| 2
| two
|-
| 3
| three
|-
| 10
| other
|}

Также, чтобы каждый раз не писать <code>when a = </code>, можно сразу указать, по какому атрибуту мы бежим:
select
a,
case a
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
else 'other'
end as new_attribute
from t;

Таким образом, используя <code>case..when..</code> можно помечать нужные нам записи для дальнейшей обработки данных.
Например, добавить к фильму атрибут, обозначающий, относится фильм к 20 или 21 веку.

select
*,
case
when production_year >= 1900 and production_year < 2000 then 'XX'
when production_year >= 2000 and production_year < 2100 then 'XXI'
end as century
from title

Сокращённый результат:
{| class="wikitable"
|-
| id
| 2395294
|-
| title
| (1979-03-11)
|-
| production_year
| 1979
|-
| century
| XX
|}

==== Группировка агрегированных данных GROUP BY ====

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

select production_year, count(1) from title
where kind_id=(select id from kind_type where kind='movie')
group by production_year
order by production_year;

В запросе также результаты отсортированы по году выпуска с помощью ORDER BY.

Попробуйте собрать следующие статистики: количество актеров и актрис; среднее количество фильмов в год, выпущенных в XX веке, и, выпущенных в XXI веке (с учетом текущего года).

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

=== Защита лабораторной работы ===
# Показать логи выполнения запросов.
# Прокомментировать для агрегаций, что и как происходит.
# Написать запрос по просьбе преподавателя.

=== Полезные материалы ===

Интерактивный урок по основам SQL запросов (видео лекции + задания): https://www.codeschool.com/courses/try-sql

Базы данных/Лабораторная работа 2

2016-04-22T18:39:59Z

Luc1ph3r: /* Декартово произведение */

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

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

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

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:

http://i.imgur.com/pDq0n.png

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

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

https://lh3.googleusercontent.com/-7yCwOQ8wjL0/VBPfTmSuq0I/AAAAAAAAF0Q/1LS_wD5cJPY/w1024-h724/LEFT%2Bvs%2BRight%2BOuter%2BJoin%2Bin%2BSQL.png

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

=== Введение в 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.

'''Задания:'''
# Найдите фильмы, в которых нет персонажей.
# Найдите актеров, которые никогда не снимались в фильмах. (двумя способами: с помощью 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 на что-то не получится. Выбирайте сразу количество, не выводя результаты выборки.

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

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

Синтаксис:

select * from t1
union
select * from t2

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

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

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

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

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

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

==== Вычитание EXCEPT ====

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

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

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

Оконные функции позволяют выполнять подзапросы и собирать статистику для каждого результирующего кортежа по некоторому окну данных. Обычно окном данных выступают кортежи сгруппированные по значению какого-либо атрибута.

Для начала создадим небольшой сэмпл данных через представление (см. ниже), чтобы упростить демонстрационные запросы:

create view movie_contries as (
select info as country, movie_id, title, t.production_year
from movie_info mi
join title t on mi.movie_id=t.id and mi.info_type_id=8 and t.kind_id=1
)

Фильмы с указанием страны и года выпуска:
select * from movie_contries;

При этом каждый фильм может упоминаться несколько раз, если в производстве участвовало несколько стран.

Данный запрос позволяет узнать, в каком году страны выпустили свой первый фильм:

select distinct(country), min(production_year) over (partition by country) from movie_contries;

* partition by country - означает, что выборка будет прохоидть для каждой группы с одинаковым значением country
* min(production_year) - для каждой из групп, которые определены далее, выбрать минимальное значение
* distinct(country) - так как over не производит группировку сам и вычисляет результат окна для каждой строки исходного отношения, то добавим distinct, чтобы вернуть только уникальные значения для country и соответствующие значения результатов выборки по окну.

Дополнительно:
* https://habrahabr.ru/post/268983/
* http://www.postgresql.org/docs/9.3/static/tutorial-window.html

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

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

create view female_actors as
select * from name where gender='f'

После этого вы сможете выбирать из представлений как из таблиц:

select count(1) from female_actors;

Представления поддерживают целостность базы данных, и, если обновятся данные в таблице, из которой строится представление, то обновятся данные и в представлении.

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

Дополнительно о синтаксисе:

* http://www.postgresql.org/docs/9.3/static/sql-createview.html

'''Задание:'''
Создайте представление, которое будет показывать проблемы с объектами в базе. Это будет несколько выборок с полями: имя сущности (фильм, актер, компания, персонаж), идентификатор объекта в его таблице, текстовое название объекта, комментарий с описанием проблемы. Эти выборки должны быть объединены с помощью UNION. Выборки следующего содержания:
* Фильмы без актеров (именно фильмы, см. kind_type)
* Актеры, которые нигде не снимались
* Компании, которые не выпустили ни одного фильма.
* Персонажи, не участвовавшие ни в одном фильме.

После создания представления, намеренно испортите некоторые кортежи в таблицах, чтобы выборки возвращали новые значения. Убедитесь, что ваши изменения попадают в результаты выборки из представления.

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

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

# Выберите 5 своих любимых (или случайных) сериалов, поля: название сериала, количество эпизодов, среднее количество персонажей в каждом эпизоде, название эпизода с наибольшим количеством актеров, количество актеров в этом эпизоде.
# Выберите фильмы, в ключевых словах которых есть 'math' и выберите для них: название фильма, суммарные кассовые сборы, количество человек, участвовавших над созданием фильма, средний доход с фильма на человека: чистая прибыль (сборы минус бюджет) поделенная на количество людей.
# Выберите топ 10 фильмов, над созданием которых потрудилось больше всего людей (таблица complete_cast), поля: название фильма, количество всех, кто участвовал в создании, количество актеров
# Выберите топ 10 актеров, которые снялись в наибольшем количестве фильмов, поля: имя актера, дата рождения, количество фактов, количество фильмов, в которых он снялся, кинокомпания, с которой актер сотрудника над наибольшим количеством фильмов.
# Выберите топ 10 кинорежиссеров, которые сняли фильмы с наибольшим количеством задействованных людей, поля: название фильма, среднее количество людей, которые принимают участие в фильме режиссера, дата рождения режиссера, количество фактов о режиссере.

== Защита лабораторной ==
# Объяснить, как работают составленные запросы.
# По просьбе преподавателя модифицировать написанный ранее запрос.

Рекомендуется сохранить все запросы, так как в последующих лабораторных работах они понадобятся.

== Полезные материалы ==
Интерактивные уроки по SQL (уроки строятся на работе с СУБД SQLite, поэтому некоторые функции не будут работать в PostgreSQL, но обычные запросы объясняются хорошо):
# Learn SQL: https://www.codecademy.com/learn/learn-sql
# Table Transformation: https://www.codecademy.com/learn/sql-table-transformation

Базы данных/Лабораторная работа 2

2016-04-22T18:05:25Z

Luc1ph3r: Исправил отображение кода (добавил в рамку)

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

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

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

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:

http://i.imgur.com/pDq0n.png

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

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

https://lh3.googleusercontent.com/-7yCwOQ8wjL0/VBPfTmSuq0I/AAAAAAAAF0Q/1LS_wD5cJPY/w1024-h724/LEFT%2Bvs%2BRight%2BOuter%2BJoin%2Bin%2BSQL.png

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

=== Введение в 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.

'''Задания:'''
# Найдите фильмы, в которых нет персонажей.
# Найдите актеров, которые никогда не снимались в фильмах. (двумя способами: с помощью 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 на что-то не получится. Выбирайте сразу количество, не выводя результаты выборки.

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

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

Синтаксис:

select * from t1
union
select * from t2

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

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

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

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

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

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

==== Вычитание EXCEPT ====

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

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

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

Оконные функции позволяют выполнять подзапросы и собирать статистику для каждого результирующего кортежа по некоторому окну данных. Обычно окном данных выступают кортежи сгруппированные по значению какого-либо атрибута.

Для начала создадим небольшой сэмпл данных через представление (см. ниже), чтобы упростить демонстрационные запросы:

create view movie_contries as (
select info as country, movie_id, title, t.production_year
from movie_info mi
join title t on mi.movie_id=t.id and mi.info_type_id=8 and t.kind_id=1
)

Фильмы с указанием страны и года выпуска:
select * from movie_contries;

При этом каждый фильм может упоминаться несколько раз, если в производстве участвовало несколько стран.

Данный запрос позволяет узнать, в каком году страны выпустили свой первый фильм:

select distinct(country), min(production_year) over (partition by country) from movie_contries;

* partition by country - означает, что выборка будет прохоидть для каждой группы с одинаковым значением country
* min(production_year) - для каждой из групп, которые определены далее, выбрать минимальное значение
* distinct(country) - так как over не производит группировку сам и вычисляет результат окна для каждой строки исходного отношения, то добавим distinct, чтобы вернуть только уникальные значения для country и соответствующие значения результатов выборки по окну.

Дополнительно:
* https://habrahabr.ru/post/268983/
* http://www.postgresql.org/docs/9.3/static/tutorial-window.html

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

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

create view female_actors as
select * from name where gender='f'

После этого вы сможете выбирать из представлений как из таблиц:

select count(1) from female_actors;

Представления поддерживают целостность базы данных, и, если обновятся данные в таблице, из которой строится представление, то обновятся данные и в представлении.

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

Дополнительно о синтаксисе:

* http://www.postgresql.org/docs/9.3/static/sql-createview.html

'''Задание:'''
Создайте представление, которое будет показывать проблемы с объектами в базе. Это будет несколько выборок с полями: имя сущности (фильм, актер, компания, персонаж), идентификатор объекта в его таблице, текстовое название объекта, комментарий с описанием проблемы. Эти выборки должны быть объединены с помощью UNION. Выборки следующего содержания:
* Фильмы без актеров (именно фильмы, см. kind_type)
* Актеры, которые нигде не снимались
* Компании, которые не выпустили ни одного фильма.
* Персонажи, не участвовавшие ни в одном фильме.

После создания представления, намеренно испортите некоторые кортежи в таблицах, чтобы выборки возвращали новые значения. Убедитесь, что ваши изменения попадают в результаты выборки из представления.

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

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

# Выберите 5 своих любимых (или случайных) сериалов, поля: название сериала, количество эпизодов, среднее количество персонажей в каждом эпизоде, название эпизода с наибольшим количеством актеров, количество актеров в этом эпизоде.
# Выберите фильмы, в ключевых словах которых есть 'math' и выберите для них: название фильма, суммарные кассовые сборы, количество человек, участвовавших над созданием фильма, средний доход с фильма на человека: чистая прибыль (сборы минус бюджет) поделенная на количество людей.
# Выберите топ 10 фильмов, над созданием которых потрудилось больше всего людей (таблица complete_cast), поля: название фильма, количество всех, кто участвовал в создании, количество актеров
# Выберите топ 10 актеров, которые снялись в наибольшем количестве фильмов, поля: имя актера, дата рождения, количество фактов, количество фильмов, в которых он снялся, кинокомпания, с которой актер сотрудника над наибольшим количеством фильмов.
# Выберите топ 10 кинорежиссеров, которые сняли фильмы с наибольшим количеством задействованных людей, поля: название фильма, среднее количество людей, которые принимают участие в фильме режиссера, дата рождения режиссера, количество фактов о режиссере.

== Защита лабораторной ==
# Объяснить, как работают составленные запросы.
# По просьбе преподавателя модифицировать написанный ранее запрос.

Рекомендуется сохранить все запросы, так как в последующих лабораторных работах они понадобятся.

== Полезные материалы ==
Интерактивные уроки по SQL (уроки строятся на работе с СУБД SQLite, поэтому некоторые функции не будут работать в PostgreSQL, но обычные запросы объясняются хорошо):
# Learn SQL: https://www.codecademy.com/learn/learn-sql
# Table Transformation: https://www.codecademy.com/learn/sql-table-transformation

Базы данных/Лабораторная работа 2

2016-04-22T13:48:59Z

Luc1ph3r: Добавлены ссылки на интерактивные уроки по SQL запросам.

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

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

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

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:

http://i.imgur.com/pDq0n.png

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

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

https://lh3.googleusercontent.com/-7yCwOQ8wjL0/VBPfTmSuq0I/AAAAAAAAF0Q/1LS_wD5cJPY/w1024-h724/LEFT%2Bvs%2BRight%2BOuter%2BJoin%2Bin%2BSQL.png

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

=== Введение в 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.

'''Задания:'''
# Найдите фильмы, в которых нет персонажей.
# Найдите актеров, которые никогда не снимались в фильмах. (двумя способами: с помощью 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 на что-то не получится. Выбирайте сразу количество, не выводя результаты выборки.

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

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

Синтаксис:

select * from t1
union
select * from t2

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

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

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

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

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

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

==== Вычитание EXCEPT ====

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

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

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

Оконные функции позволяют выполнять подзапросы и собирать статистику для каждого результирующего кортежа по некоторому окну данных. Обычно окном данных выступают кортежи сгруппированные по значению какого-либо атрибута.

Для начала создадим небольшой сэмпл данных через представление (см. ниже), чтобы упростить демонстрационные запросы:

create view movie_contries as (
select info as country, movie_id, title, t.production_year
from movie_info mi
join title t on mi.movie_id=t.id and mi.info_type_id=8 and t.kind_id=1
)

Фильмы с указанием страны и года выпуска:
select * from movie_contries;

При этом каждый фильм может упоминаться несколько раз, если в производстве участвовало несколько стран.

Данный запрос позволяет узнать, в каком году страны выпустили свой первый фильм:

select distinct(country), min(production_year) over (partition by country) from movie_contries;

* partition by country - означает, что выборка будет прохоидть для каждой группы с одинаковым значением country
* min(production_year) - для каждой из групп, которые определены далее, выбрать минимальное значение
* distinct(country) - так как over не производит группировку сам и вычисляет результат окна для каждой строки исходного отношения, то добавим distinct, чтобы вернуть только уникальные значения для country и соответствующие значения результатов выборки по окну.

Дополнительно:
* https://habrahabr.ru/post/268983/
* http://www.postgresql.org/docs/9.3/static/tutorial-window.html

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

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

create view female_actors as
select * from name where gender='f'

После этого вы сможете выбирать из представлений как из таблиц:

select count(1) from female_actors;

Представления поддерживают целостность базы данных, и, если обновятся данные в таблице, из которой строится представление, то обновятся данные и в представлении.

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

Дополнительно о синтаксисе:

* http://www.postgresql.org/docs/9.3/static/sql-createview.html

'''Задание:'''
Создайте представление, которое будет показывать проблемы с объектами в базе. Это будет несколько выборок с полями: имя сущности (фильм, актер, компания, персонаж), идентификатор объекта в его таблице, текстовое название объекта, комментарий с описанием проблемы. Эти выборки должны быть объединены с помощью UNION. Выборки следующего содержания:
* Фильмы без актеров (именно фильмы, см. kind_type)
* Актеры, которые нигде не снимались
* Компании, которые не выпустили ни одного фильма.
* Персонажи, не участвовавшие ни в одном фильме.

После создания представления, намеренно испортите некоторые кортежи в таблицах, чтобы выборки возвращали новые значения. Убедитесь, что ваши изменения попадают в результаты выборки из представления.

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

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

# Выберите 5 своих любимых (или случайных) сериалов, поля: название сериала, количество эпизодов, среднее количество персонажей в каждом эпизоде, название эпизода с наибольшим количеством актеров, количество актеров в этом эпизоде.
# Выберите фильмы, в ключевых словах которых есть 'math' и выберите для них: название фильма, суммарные кассовые сборы, количество человек, участвовавших над созданием фильма, средний доход с фильма на человека: чистая прибыль (сборы минус бюджет) поделенная на количество людей.
# Выберите топ 10 фильмов, над созданием которых потрудилось больше всего людей (таблица complete_cast), поля: название фильма, количество всех, кто участвовал в создании, количество актеров
# Выберите топ 10 актеров, которые снялись в наибольшем количестве фильмов, поля: имя актера, дата рождения, количество фактов, количество фильмов, в которых он снялся, кинокомпания, с которой актер сотрудника над наибольшим количеством фильмов.
# Выберите топ 10 кинорежиссеров, которые сняли фильмы с наибольшим количеством задействованных людей, поля: название фильма, среднее количество людей, которые принимают участие в фильме режиссера, дата рождения режиссера, количество фактов о режиссере.

== Защита лабораторной ==
# Объяснить, как работают составленные запросы.
# По просьбе преподавателя модифицировать написанный ранее запрос.

Рекомендуется сохранить все запросы, так как в последующих лабораторных работах они понадобятся.

== Полезные материалы ==
Интерактивные уроки по SQL (уроки строятся на работе с СУБД SQLite, поэтому некоторые функции не будут работать в PostgreSQL, но обычные запросы объясняются хорошо):
# Learn SQL: https://www.codecademy.com/learn/learn-sql
# Table Transformation: https://www.codecademy.com/learn/sql-table-transformation

Базы данных/Лабораторная работа 1

2016-04-08T08:34:19Z

Luc1ph3r: Добавлена ссылка на интерактивный урок по основам SQL запросов.

Задачи: освоить самые необходимые навыки настройки СУБД, получения данных и манипуляции с ними, а также составления простых отчетов.

=== Введение ===

Так как у некоторых групп лабораторные работы начинаются раньше первой лекции, то предлагается [http://wiki.cs.hse.ru/%D0%91%D0%B0%D0%B7%D1%8B_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85/%D0%9E%D1%81%D0%BD%D0%BE%D0%B2%D0%BD%D1%8B%D0%B5_%D1%82%D0%B5%D1%80%D0%BC%D0%B8%D0%BD%D1%8B краткий список терминов], используемых в лабораторной работе.

PostgreSQL - популярная реляционная система управления базами данных. Эта СУБД используется многими крупными компаниями, являясь единственной хорошо развитой свободной альтернативой наряду с MySQL. Но по сравнению с MySQL, PostgreSQL предоставляет больше возможностей для работы с большими объемами данных (не "big data", но до терабайта).

В качестве базы данных в лабораторных работах будет использоваться база фильмов IMDB (сам сайт также использует эту СУБД). Дамп базы достаточно большой, поэтому, если у вас есть возможность, скачайте и импортируйте его заранее.

Рекомендуется использовать Ubuntu 14.04 и PostgreSQL 8.1+. Также нужно примерно 10 Гб места на диске.

Для выполнения запросов подойдет и терминал, но можно использовать IDE (например, DataGrip или любую другую от JetBrains с аналогичным плагином).

База данных, которая используется в лабораторных работах: https://yadi.sk/d/EVhJUiroqgzWj

=== Часть 1: Установка PostgreSQL ===

Первая задача состоит в том, чтобы установить СУБД и проверить ее работоспособность.

Выполните в терминале:

sudo apt-get update && sudo apt-get install postgresql postgresql-contrib

Сервер PostgreSQL создает отдельно пользователя в системе для доступа к базе. Чтобы переключиться на этого пользователя, выполните:

sudo -i -u postgres

Теперь вы можете войти в интерактивный режим работы с СУБД:

psql

Приглашение в интерактивном режиме выглядит так:

postgres=#

Чтобы посмотреть, какие базы уже есть в системе, наберите:

\l

Примерный результат:

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

Альтернативно, можно выполнить запрос:

SELECT datname FROM pg_database;

Чтобы работать с конкретной базой, ее нужно выбрать. Выполните \c database_name:

postgres=# \c imdb
You are now connected to database "imdb" as user "postgres".

Чтобы узнать, какие таблицы есть базе, выполните:

\d

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

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Чтобы узнать, какие есть колонки в таблице:

\d+ table

или

\d table

Альтернативный запрос:...

SELECT column_name FROM information_schema.columns WHERE table_name ='table';

=== Часть 2: Основы администрирования PostgreSQL ===

Следующая задача состоит в том, чтобы настроить два важный параметра:

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

Про второй механизм подробнее. Когда вы вносите изменения в данные, они не сразу вступают в силу. СУБД создает diff аналогичный тому, который можно видеть в git. После этого вы начинаете работать с измененной версией, но в других сессиях данные по-прежнему старые. Если вы что-то сделали неправильно, вы можете откатить изменения в своей сессии с помощью команды rollback. Если же все изменения корректны, подтвердите их, выполнив commit. Закоммиченные изменения откатить намного сложнее, поэтому как правило в СУБД отключают опцию autocommit, которая подтверждает изменения автоматически.

Когда вы завершаете сессию, выполняется rollback. Если вы убиваете процесс, то он может еще некоторое время "держать" данные, не давая их изменить.

Приступим к конфигурированию.

PostgreSQL представлен в системе в виде сервиса, управлять которым можно как и обычно через команду service. Как правило, для внесения каких-либо изменений нужно перезапустить сервис.

Конфигурационный файл:

sudo vim /etc/postgresql/9.*/main/postgresql.conf

Допишите или раскомментируйте:

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Управлять некоторыми параметрами можно прямо из сессии с СУБД. Например включение подробного логирования:

SELECT set_config('log_statement', 'all', true);

Чтобы отключить автокоммит, от пользователя postgres допишите в файл ~/.psqlrc:

\set AUTOCOMMIT off

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

BEGIN;
-- манипуляции с данными
COMMIT;

=== Часть 3: импорт и экспорт базы данных IMDB ===

Две наиболее важные операции. Выполняйте в сессии пользователя postgres.

Экспортировать базу данных:

pg_dump dbname | gzip > filename.gz

Импортировать базу:

gunzip -c filename.gz | psql dbname

Попробуйте импортировать базу IMDB:

https://yadi.sk/d/EVhJUiroqgzWj

'''Важно:''' прежде чем импортировать дамп, нужно создать базу данных:

В psql выполните:

create database imdb;

Это займет некоторое время (20 минут - норм).

Также можно импортировать только конкретные таблицы, указав их через ключ --table.

Можно импортировать только схему: --schema-only или только данные: --data-only

==== Структура базы IMDB ====

У каждой таблицы есть идентификатор, указанный как первичный ключ (id). По нему выбирать быстрее всего.

Основные таблицы и их описание:
* title - названия фильмов (поле title) и год выпуска (поле production_year); если это сериал, то также здесь можно найти номер эпизода
* movie_info - характеристики и факты о фильме: movie_id - идентификатор из таблицы title (далее для краткой записи: title.id), info_type_id - идентификатор из таблицы info_type (info_type.id), info - текстовое поле со значением характеристики.
* name - актеры (имя и пол)
* person_info - характеристики и факты об актерах также с названиями характеристик из (info_type.id)
* char_name - роли (имена персонажей)
* cast_info - таблица со связью ролей (person_role_id), актеров (person_id) и фильмов (movie_id)

=== Часть 4: Простые операции CRUD ===

К простым операциям манипуляции данными (Create, Read, Update, Delete) относятся:

* Добавление: INSERT
* Выборка: SELECT
* Обновление: UPDATE
* Удаление: DELETE

==== Добавление данных INSERT ====

Чтобы добавить новую запись в таблицу, нужно вычислить ее идентификатор. Для этого в PostgreSQL используются последовательности - числа, которые меняются по заданным правилам (обычно просто инкрементируются на единицу).

Чтобы посмотреть список всех последовательностей выполните:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

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

Синтаксис INSERT выглядит так: сначала в скобках перечисляются атрибуты, которые будут вставлены, а затем после VALUES в скобках указываются значения. Можно также не перечислять атрибуты, тогда в VALUES нужно по порядку указать значения для всех.

Попробуйте добавить себя в список актеров:

insert into name (id, name, gender) values(nextval('name_id_seq'), 'Ivan Savin', 'm');

Здесь nextval('name_id_seq') генерирует следующее значение для последовательности name_id_seq.

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

select max(id) from name;
ALTER SEQUENCE name_id_seq INCREMENT 5555233;

Если вы отключили автокоммит, то, так как вы вносите изменения в данные, завершите операцию, выполнив:

commit;

Если вы не уверены в своих изменениях, выполните:

rollback;

За одну операцию INSERT можно вставлять несколько строк данных. Для этого после VALUES нужно перечислить кортежи данных через запятую:

insert into name (id, name, gender)
values(nextval('name_id_seq'), 'Dmitry Burmistrov', 'm'),
(nextval('name_id_seq'), 'Victor Yakovlev', 'm');

==== Выборка данных SELECT ====

Для чтения данных из базы используется ключевое слово SELECT, после которого указывается список атрибутов, которые нужно получить в выборке. Если указать вместо списка атрибутов "*", то выберутся все. Самый простой запрос выборки из базы данных выглядит следующим образом:

select * from info_type;

Не пробуйте выбрать все данные из больших таблиц (title, name) - это займет много времени. Если вы хотите выбрать несколько кортежей данных для примера, то ограничьте результаты с помощью LIMIT:

select * from title limit 10;

Условия выборки указываются после ключевого слова WHERE. Условия можно комбинировать с помощью скобок и слов OR и AND. Примеры условий:

* WHERE title='Databases' - простое условие равенства
* WHERE title like '%base%' - поиск по подстроке, "%" - любое количество любых символов
* WHERE created_date > now() - сравнение даты с текущим моментом; см. также http://www.postgresql.org/docs/8.3/static/functions-datetime.html
* WHERE title not in ('Databases', 'Networks') - значение не входит в список
* WHERE not exist (SELECT * FROM ...) - выполняется, если подзапрос вернул хотя бы одну запись
* WHERE artist_id in (SELECT id FROM artist...) - подзапрос определяет множество значений.

Пример запроса с условиями:

select * from title where title like '%Matrix' and production_year=1999;

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

select (select info from info_type where info_type.id=person_info.info_type_id), person_info.info from person_info where person_id=1732058;

Попробуйте найти ваши любимые фильмы, указывая часть названия и комбинируя условия, указывая год выхода. Попробуйте найти ваших любимых актеров и факты о них.

==== Обновление данных UPDATE ====

Чтобы обновить данные, нужно указать, какие параметры вы хотите обновить и условия выборки обновляемых данных:

update cast_info set person_id=1732058 where movie_id=3514559;

Если вы не укажите условия, то обновятся значения во всей таблице, обычно это не нужно.

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

update cast_info set person_id=(select id from name where name='Savin Ivan')
where movie_id=(select id from title where title like 'The Matrix' and production_year=1999);

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

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

==== Удаление данных DELETE ====

Синтаксис удаления данных аналогичен синтаксису выборки за исключением того, что вместо "SELECT * FROM" достаточно написать "DELETE FROM". Будьте внимательны, удаляя данные и проверяйте условия перед отправкой коммита.

Удалите актеров, которые вам не нравятся (или актера, выбранного случайно). Это сделать не так просто, как может показаться, потому что их идентификаторы используются в других таблицах, а нарушать целостность данных нельзя. Для корректного удаления, нужно найти все связи актера с фильмами и удалить сначала их, после этого удалить информацию о них из таблицы фактов, после этого станет доступно удаление.

=== Часть 5: Агрегация данных ===

Часто с помощью СУБД генерируют различные полезные отчеты. В любой популярной СУБД есть агрегирующие функции, с помощью которых, можно собрать статистику о данных. Самая простая статистика: количество записей, удовлетворяющих заданным условиям. Пример выбора количества фильмов в базе:

select count(1) from title where kind_id=(select id from kind_type where kind='movie');

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

Попробуйте вывести, в скольких фильмах снимались ваши любимые актеры.

==== Группировка агрегированных данных GROUP BY ====

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

select production_year, count(1) from title
where kind_id=(select id from kind_type where kind='movie')
group by production_year
order by production_year;

В запросе также результаты отсортированы по году выпуска с помощью ORDER BY.

Попробуйте собрать следующие статистики: количество актеров и актрис; среднее количество фильмов в год, выпущенных в XX веке, и, выпущенных в XXI веке (с учетом текущего года).

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

=== Защита лабораторной работы ===
# Показать логи выполнения запросов.
# Прокомментировать для агрегаций, что и как происходит.
# Написать запрос по просьбе семинариста.

=== Полезные материалы ===

Интерактивный урок по основам SQL запросов (видео лекции + задания): https://www.codeschool.com/courses/try-sql