Страница 3 - Объединение сложных запросов

Навигация по уроку

Связанные темы

В анализе данных часто приходится применять объединение запросов, в которых соединяются несколько таблиц. В наших примерах мы будем анализировать посещаемость сайтов по темам и областям. Потребуется сначала вывести все темы и области, а после каждой из них - некоторое итоговое значение, например, "все" (сумма). Сколько раз потребуется писать итоговое значение "все" в качестве значения столбца во втором из соединяемых запросов? Добавляются ли какие-то новые требования к структуре запросов к тем, о которых мы узнали, работая с объединением простых запросов? Обо всем этом мы узнаем из примеров на этой странице.

Работаем с базой данных Mediastar, схема и содержание которой даны здесь.

Скачать архив со скриптом на SQL для установки этой базы со всеми данными можно по этой ссылке.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Пример 9. Вывести среднюю длительность посещения тем медиа (сайтов) по областям и среднюю длительность по каждой области в одной таблице.

Решение. Требуется объединить почти одинаковые запросы. Их отличия в средующем. Первое. Первый запрос выводит названия тем медиа (сайтов), а второй вместо этих названий выводит значение "---Все", которое произвольно указываем как значения столбца Theme. Второе отличие. Первый запрос выводит средние значения просмотров по темам, а второй - по области. Для этого в первом запросе группировка с помощью оператора GROUP BY производится по области (District) и названию темы (Name), а во втором запросе - только по области (District). Упорядочение с применением оператора ORDER BY применяется уже к результату объединения этих запросов.

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

USE mediastar; SELECT c.District, mt.Name AS Theme, AVG(v.Duration) AS AvgDur FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Visit v ON v.Media_ID=m.Media_ID JOIN Reader r ON r.Reader_ID=v.Reader_ID JOIN City c ON c.City_ID=r.City_ID GROUP BY c.District, mt.Name UNION SELECT c.District, '--- Все' AS Theme, AVG(v.Duration) AS AvgDur FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Visit v ON v.Media_ID=m.Media_ID JOIN Reader r ON r.Reader_ID=v.Reader_ID JOIN City c ON c.City_ID=r.City_ID GROUP BY c.District ORDER BY c.District, Theme

В результате выполнения этого запроса будет выведена следующая таблица:

Пример 10. Вывести ровные часы времени входа на сайты по всем темам, идентификаторы читателей, входивших в эти часы на сайты, и час самого раннего времени в период с 10 по 12 число.

Решение. Во втором запросе вновь указываем произвольное значение - '--Самое раннее' как значение столбца Reader. Но с этого начинается несовпадение типов данных в объединяемых запросах: столбец Reader_ID имеет целочисленный тип INT, а во втором запросе прописанное нами произвольное значение '--Самое раннее' имеет тип NVARCHAR. Поэтому в первом запросе с помощью конструкции CAST приводим значения столбца к типу VARCHAR, который совместим с типом NVARCHAR (последний отличается тем, что поддерживает множество различных алфавитов, в том числе русский). Второе приведение типов связано со значениями столбца, в котором окажутся часы начала посещения сайтов. Эти значения имеют тип DATETIME2. А нам нужно получить целочисленный тип INT, так как во втором запросе минимальное значение вычисляется при помощи агрегатной функции MIN (агрегатные функции принимают только целочисленный тип).

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

USE mediastar; SELECT mt.Name AS Theme, CAST((r.Reader_ID) AS VARCHAR) AS Reader, CAST(DATENAME(hour, v.Datetimestart) AS INT) AS Hour FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Visit v ON v.Media_ID=m.Media_ID JOIN Reader r ON r.Reader_ID=v.Reader_ID WHERE DATENAME(day, v.Datetimestart) BETWEEN 10 AND 12 GROUP BY mt.Name, r.Reader_ID, CAST(DATENAME(hour, v.Datetimestart) AS INT) UNION SELECT mt.Name AS Theme, '--Самое раннее' AS Reader, MIN(CAST(DATENAME(hour, v.Datetimestart) AS INT)) AS Hour FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Visit v ON v.Media_ID=m.Media_ID JOIN Reader r ON r.Reader_ID=v.Reader_ID WHERE DATENAME(day, v.Datetimestart) BETWEEN 10 AND 12 GROUP BY mt.Name ORDER BY Theme

В результате выполнения этого запроса будет выведена следующая таблица:

Написать запрос с использованием UNION самостоятельно, а затем посмотреть решение

Пример 11. Вывести значения возраста читателей по темам медиа (сайтов), идентификаторы читателей и средний возраст по каждой теме в одной таблице. Период - с 10 по 12 число.

Правильное решение.

Поделиться с друзьями

Назад<<<

Содержание

Вперёд>>>