Лабораторная работа №2
Связь СУБД MySQL и R. Визуализация данных в R.
Цель работы: произвести связь базы данных в MySQL и R, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в R.
Пример 1: есть база данных tram_depot, в которой есть таблица routes:
Рисунок 1 – Таблица с маршрутами
Выполним подключение к БД MySQL и построим гистограмму, где по оси х будет название маршрута, а по оси у – количество остановок.
Один из вариантов построения:
library(RMySQL)
library(ggplot2)
library(plotly)
#соединение с БД 'tram_depot'
con <- dbConnect(RMySQL::MySQL(), user='root', password='', dbname='tram_depot', host='127.0.0.1')
# Получение данных из таблицы routes
res <- dbSendQuery(con, "SELECT * FROM routes") stat <- dbFetch(res)
dbClearResult(res)
# Построение гистограммы:
p <- ggplot(stat, aes(x=ROUTE_NAME, y=N_STOPS,fill=N_STOPS)) +geom_bar(stat="identity") +
ylab("Количество остановок") +xlab("Название маршрута") +ggtitle("Количество остановок") +theme_bw() +theme(legend.position="none") p
Рисунок 2 – Результат построения
Пример 2: Есть база данных «site», обработаем данные, находящиеся в таблицах wp_comments и wp_terms.
1
В Листинге 1 представлен программный код подключения к базе данных и построения гистограммы, показывающей распределение количества комментариев по постам на основе данных, хранящихся таблице wp_comments.
На рис.3 представлен пример полученной гистограммы, где по оси х отображены «id» поста, а по оси y – количество комментариев.
Листинг 1 – Распределение количества комментариев по постам
library(RMySQL)
library(ggplot2)
library(plotly)
#соединение с БД
con <- dbConnect(RMySQL::MySQL(), user='root', password='', dbname='site', host='127.0.0.1')
# Получение данных из таблицы wp_comments
res <- dbSendQuery(con, "SELECT * FROM wp_comments") stat <- dbFetch(res)
dbClearResult(res)
tbl <- as.data.frame(table(stat$comment_post_ID))
# Построение гистограммы:
p <- ggplot(tbl, aes(x=Var1, y=Freq, fill=Var1)) +geom_bar(stat="identity")
+
ylab("Количество комментариев") +xlab("Номер поста") +ggtitle("Количество комментариев под постами") +theme_bw() +theme(legend.position="none")
p ggplotly(p)
Рисунок 3 - Распределение количества комментариев по постам
В листинге 2 представлен программный код, с помощью которого можно увидеть распределение количества записей по категориям. Данный статистический анализ основан на данных, хранящихся в таблице wp_terms. На рис.4 представлена круговая диаграмма, визуализирующая данное распределение.
Листинг 2 – Распределение количества записей по категориям
library(RMySQL)
library(ggplot2)
library(plotly)
2
con <- dbConnect(RMySQL::MySQL(), user='root', password='', dbname='site', host='127.0.0.1') # соединение с БД
# Извлечение данных:
res <- dbSendQuery(con, "SELECT * FROM wp_terms") stat <- dbFetch(res)
tbl <- stat[c(1,2,3,4,5,12),]
res <- dbSendQuery(con, "SELECT * FROM wp_term_relationships")
stat2 <- dbFetch(res)
tbl2 <- as.data.frame(table(stat2$term_taxonomy_id)) tbl2 <- as.data.frame(tbl2[c(1,2,3,4,5,12),])
# Построение диаграммы:
p <- plot_ly(tbl2, labels = ~tbl$name, values = ~tbl2$Freq, type = 'pie',
textposition = 'inside', textinfo = 'label+percent',
insidetextfont = list(color = '#FFFFFF')) %>%
layout(title = 'Количество опубликованных записей по категориям', xaxis = list(showgrid = FALSE, zeroline = FALSE,
showticklabels = FALSE),
yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
p
Рисунок 4 - Распределение количества записей по категориям
Порядок выполнения работы:
1.Использовать предметную область и БД, созданную в предыдущем семестре в СУБД MySQL. Описать вашу предметную область в отчете, обязательно предоставить схему данных из MySQL Workbench. Возможно вам потребуется заполнить таблицы бОльшим количеством данных для наглядности.
2.Осуществить связь R и вашей БД в MySQL.
3
3.Выполнить один запрос на выборку всех данных из одной таблицы на выбор в вашей БД, продемонстрировать результат.
4.Выполнить 3 запроса в скрипте R с использованием агрегатных функций, сортировки, группировки для вашей БД, результаты которых можно представить в виде графика или гистограммы.
Для своей БД вам необходимо самостоятельно придумать полезные запросы, которые могут использоваться для получения той или иной статистики в вашей предметной области.
5.Выполнить визуализацию с помощью R.
Графики должны быть наглядными, график обязательно должен иметь заголовок, подписи к осям, а также отображать показательные значения (например, название фильма, а не id фильма).
Обязательно сделать графики двух разных видов (примеры: горизонтальная и вертикальная столбчатая диаграмма, групповая столбчатая диаграмма, круговая диаграмма, график).
6. К полученным графикам необходимо дать пояснения, прокомментировать результаты.
Содержание отчета:
1.Титульный лист.
2.Цель работы.
3.Вариант задания (описание вашего варианта: тема работы, описание предметной области, а также схема данных).
4.Ход работы: пояснения по ходу выполнения работы.
4.1Код для запроса на выборку и скриншот результата (пункт 3).
4.2Текстовое описание созданных запросов, SQL-код, скриншоты полученных графиков и пояснения к ним (пункты 4-6).
5. Полный листинг с комментариями (в виде текста, не скриншотами). 6. Вывод в развернутом формате с описанием результатов работы.
7. Список источников.
Все пункты отчета являются обязательными и при отсутствии какого-то из пунктов лабораторная работа либо не будет принята, либо оценка за работу будет снижена. Также в тексте отчетов необходимо приводить пояснения по ходу выполнения работы, обязательно ссылаться в тексте отчета на рисунки, таблицы и листинги.
4