ГУАП
КАФЕДРА № 41
ОТЧЕТ ЗАЩИЩЕН С ОЦЕНКОЙ
ПРЕПОДАВАТЕЛЬ
ассистент |
|
|
|
В.В. Боженко |
|
|
|
|
|
|
|
|
|
|
должность, уч. степень, звание |
|
подпись, дата |
|
инициалы, фамилия |
ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №2
Связь СУБД MySQL и R. Визуализация данных в R
по курсу: БАЗЫ ДАННЫХ
РАБОТУ ВЫПОЛНИЛ
СТУДЕНТ ГР. №
подпись, дата |
|
инициалы, фамилия |
Санкт-Петербург 2022
Цель работы
Произвести связь базы данных в MySQL и Python, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в Python.
Описание предметной области
Описание предметной области в соответствии с индивидуальным вариантом №10.
Музыкальные группы дают концерты, которые проводятся в концертных залах. О музыкальной группе должна храниться следующая информация: название группы, количество альбомов, сайт группы, стиль исполнения, год создания группы. О предстоящем концерте должна присутствовать информация: название концерта, название выступающей группы, дата концерта, место проведения концерта. Билеты на концерт можно заказать через Интернет. При выступлении в концертном зале цена билета зависит от места расположения (фанзона, VIP-места, сидячие). Концертный зал характеризуется номером концертного зала, адресом концертного зала, номером телефона, количеством мест в зале.
Схема данных
Схема данных в соответствии с рисунком 1.
Рисунок 1 – Схема данных
2
Данные таблиц
Данные таблицы «musicalgroup» в соответствии с рисунком 2.
Рисунок 2 – Таблица «musicalgroup»
Данные таблицы «concerthall» в соответствии с рисунком 3.
Рисунок 3 – Таблица «concerthall»
Данные таблицы «location» в соответствии с рисунком 4.
Рисунок 4 – Таблица «location»
Данные таблицы «concert» в соответствии с рисунком 5.
Рисунок 5 – Таблица «concert»
Данные таблицы «contract» в соответствии с рисунком 6.
Рисунок 6 – Таблица «contract»
Данные таблицы «ticket» в соответствии с рисунком 7.
3
Рисунок 7 – Таблица «ticket»
Данные таблицы «orders» в соответствии с рисунком 8.
Рисунок 8 – Таблица «orders»
4
Ход работы
1) Осуществили связь R и БД в MySQL. Выполнили один запрос на выборку всех данных из таблицы musicalgroup. Код и результат в соответствии с листингом 1 и рисунком 9.
Листинг 1 – Код подключения и запроса на вывод данных таблицы
#install.packages("ggplot2")
#install.packages("RMySQL")
#install.packages("plotly")
library(RMySQL)
library(ggplot2)
library(plotly)
#соединение с БД 'tram_depot' con <- dbConnect(RMySQL::MySQL(),
user='root',
password='12345678',
dbname='mg',
host='127.0.0.1') dbSendQuery(con, "SET NAMES 'CP1251'")
# Получение данных из таблицы routes
res <- dbSendQuery(con, "SELECT * FROM musicalgroup") stat <- dbFetch(res)
dbClearResult(res)
Рисунок 9 – Результат подключения к БД и запроса на вывод данных из
таблицы musicalgroup
5
2) Выполнили 3 запроса в скрипте R с использованием агрегатных функций, сортировки, группировки для своей БД, результаты которых представил в виде графиков.
Первый запрос подсчитывает количество проданных билетов на концерты музыкальных групп. Код запроса в R в соответствии с листингом 2,
результат в соответствии с рисунком 10, график запроса в соответствии с рисунком 11.
Листинг 2 – Код запроса 1 и отображение графика
res <- dbSendQuery(con,
"SELECT BandName, count(*) as ticket_count
FROM MusicalGroup JOIN Concert USING(MusicalGroupID)
JOIN Ticket USING(ConcertID) GROUP BY BandName
ORDER BY 2 DESC") stat <- dbFetch(res) dbClearResult(res)
p <- ggplot(data=stat, aes(x=BandName, y=ticket_count, fill=ticket_count)) +
geom_bar(stat="identity") + ylab("Кол-во билетов") + xlab("Название муз. группы") + ggtitle("Кол-во проданных билетов") + theme_bw() + theme(legend.position="none")
p
Рисунок 10 – Результат запроса 1
6
Рисунок 11 – График запроса 1
Второй запрос подсчитывает количество проданных билетов по группам ценовых категорий по концертам музыкальных групп. Код запроса в R в
соответствии с листингом 2, результат в соответствии с рисунком 12, график запроса в соответствии с рисунком 13.
Листинг 3 – Код запроса 2 и отображение графика
res <- dbSendQuery(con,
"SELECT BandName, LocationName , count(*) as ticket_count
FROM MusicalGroup
JOIN Concert USING(MusicalGroupID) JOIN Ticket USING(ConcertID)
JOIN Location USING(LocationID) GROUP BY BandName, LocationName ORDER BY 1, 2;")
stat <- dbFetch(res) dbClearResult(res)
p <- ggplot(data=stat, aes(x=BandName, y=ticket_count, fill=LocationName)) +
geom_bar(stat="identity") + ylab("Кол-во билетов") + xlab("Название муз. группы") + ggtitle("Кол-во проданных билетов") + theme_bw()
p
7
Рисунок 12 – Результат запроса 2
Рисунок 13 – График запроса 2
8
Третий запрос подсчитывает количество заказов по датам за определенный период. Код запроса в R в соответствии с листингом 2,
результат в соответствии с рисунком 14, график запроса в соответствии с рисунком 15.
Листинг 4 – Код запроса 3 и отображение графика
res <- dbSendQuery(con,
"SELECT OrderDate, count(*) AS OrderCount FROM orders
WHERE OrderDate between '2021-11-15' and '2021-12-31' GROUP BY 1
ORDER BY 1;") stat <- dbFetch(res) dbClearResult(res)
p <- ggplot(data=stat, aes(x=OrderDate, y=OrderCount, size=OrderCount)) +
geom_point() + ylab("Кол-во заказов") + xlab("Дата") +
ggtitle("Кол-во оформленных заказов") + theme_bw()
p
Рисунок 14 – Результат запроса 3
Рисунок 15 – График запроса 3
9
Вывод
Выполнив лабораторную работу, мы научились устанавливать соединение с БД с помощью RMySql, извлекать из нее данные, обрабатывать их c помощью ggplot2, проводить анализ и визуализировать с применением библиотек Matplotlib. Полученные в ходе выполнения лабораторной работы знания позволяют работать с данными из БД при помощи сторонних библиотек для R, что значительно расширяет возможности для анализа больших данных, с которыми приходится сталкиваться при выполнении задач реального мира.
10