Использование языка R с базами данных

Анализ данных на языке SQL

Язык SQL – самый мощный инструмент для обработки данных, придуманный человеком. Этот простой и выразительный язык запросов поддерживается всеми современными базами данных (в том числе Microsoft, Oracle, IBM) и инструментами анализа и программирования (в том числе Excel).

На данном курсе Вы познакомитесь с базами данных языком запросов SQL. Цель курса – научиться свободно и уверенно пользоваться современными базами данных, в том числе анализировать данные и строить отчёты.

Аудитория курса: аналитики и разработчики отчётов, работающие с базами данных.

По окончании курса Вы будете уметь:

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

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

Продолжительность курса - 24 ак. ч.

Программа курса

Модуль 1. Реляционные базы данных

СУБД SQL Server
Язык запросов SQL
Программа Management Studio
Базы данных и таблицы
Написание и исполнение запросов
Доступ к данным из Excel
Извлечение данных из различных источников

Модуль 2. Простые операции с одной таблицей

Инструкция SELECT
Фильтрация таблицы по столбцам
Фильтрация таблицы по строкам
Использование функций
Типы данных
Обработка неизвестных значений
Сортировка
Операция TOP
Устранение дубликатов
Порядок операций в запросе

Модуль 3. Трансформация таблицы

Агрегация
Группировка
Фильтрация групп

Модуль 4. Модификация данных

Добавление строк
Модификация строк
Удаление строк

Модуль 5. Операции с несколькими таблицами

Использование нескольких таблиц в запросе
Объединение, пересечение и разность множеств
Подзапросы
Использование подзапросов в инструкции SELECT
Соединение таблиц
Использование соединений
Внешние соединения
Коррелированные соединения
Реляционное деление

Модуль 6. Построение отчётов

Сводные таблицы
Вычисление подытогов
Ранжирование
Постраничный вывод
Агрегация со скользящим окном
Функции смещения

Модуль 7. Работа с хранилищами и витринами данных

Операционные базы данных
Хранилища и витрины данных
Структура хранилища
Работа с хранилищем
Аналитическая обработка больших объёмов данных
Статистический анализ данных
Инструменты анализа данных

По окончании обучения на курсе проводится итоговая аттестация. Аттестация проводится в виде теста на последнем занятии или на основании оценок практических работ, выполняемых во время обучения на курсе.

Ирина Никифорова получила PhD по социологии в Технологическом институте Джорджии. В настоящее время она является научным сотрудником НИУ ВШЭ. Она также является доцентом Кафедры методов и технологий социологических исследований факультета социологии кампуса НИУ ВШЭ в Санкт-Петербурге.

Аннотация:

Программы с открытым кодом, такие как система управления базами данных MySQL и язык программирования R, предназначенный для статистических вычислений, стали стандартными средствами анализа данных в академических и прикладных исследованиях. Эти инструменты могут использоваться в различных операционных системах, включая Windows, Linux и Mac OS. Данный курс предназначен для исследователей, обладающих навыками работы с R и специализирующихся в сфере статистического анализа данных в различных дисциплинах, которые хотели бы также освоить базы данных MySQL, методы их интеграции с R и способы разрешения возникающих при этом технических проблем.

В рамках курса будут представлены основные приёмы и средства управления реляционными базами данных, в том числе язык MySQL, правила формирования запросов в SQL, а также интеллектуальный анализ данных с помощью R. Кроме того, будут рассмотрены вопросы, связанные с совместимостью Excel, SPSS и MySQL, и использование статистических средств языка R.

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

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

Программа курса

Структура курса:

Семинар 1: Принципы работы с базами данных и системы управления базами данных; установка и основы использования MySQLи SQLite
Семинар 2: Навигация по базам данных: основы языка SQL и формирование запросов.
Семинар 3: Комплексные запросы в SQL
Семинар 4: Управление данными и совместимость
Семинар 5: Интеллектуальный анализ данных в R.

Bessant, Conrad, Shadforth, Ian, & Oakley, Darren. (2009). Building Bioinformatics Solutions: with Perl, R and MySQL . Oxford, UK: Oxford University Press.
Spector, Phil. (2008). Data Manipulation with R (Use R!) . New York: Springer.
Torgo, Luis. (2010). Data Mining with R: Learning with Case Studies . Chapman & Hall/CRC.
Welling, Luke, & Thomson, Laura. (2003). MySQL Tutorial . Indianapolis, Indiana, USA: MySQL Press.

Power Query - это инструмент интеграции данных в Excel из внешних источников.
Power Query позволяет искать и открывать данные из различных источников доступных онлайн и через корпоративные сети. Он умеет загружать в Excel данные разных типов, форматов и структур, а так же из совершенно разных источников:

Вики источник

Power Query позволяет импортировать внешние (семи)-структурированные источники данных и обрабатывать их в Excel. Пример, ниже показывает крупнейшие города в северной и южной Америках на карте в соответствии с их населением.

(кликабельно)

Импорт и анализ писем в Outlook

Так же имеется возможность импортировать письма, как источник данных, и анализировать их в Outlook. Пример ниже демонстрирует импорт почты и создает гистограмму по числу писем от человека, то есть с кем чаще всего происходит переписка.

(кликабельно)

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

Минусы : Не годится для «обычных» пользователей, сложен в освоении, достаточно медлителен. Нет возможности разделения доступа, ограничения на размер файлов/записей etc.

Дополнительные материалы

Deep Dive into the Query language (видео)
Подборка видео раз и два
Подборка статей от Microsoft

Скачать

MS Power BI

Power BI - это инструмент создания интерактивных бизнес отчетов с возможностью совместной работы, визуализации и интерактивной работы.

Основные возможности:

Быстрая разработка информативных бизнес отчетов и панелей (в сети) – с возможностью взаимодействия и исследования данных.
Автоматическое обновление BI-отчетов и визуализации, при изменении данных
Поддержка языка запросов, в том числе и Power Query. Возможность взаимодействия между участниками на уровне запросов.
Создание каталога данных с индексами для поиска.
Язык запросов близкий к естественному (для бизнес-аналитика) и возможность интерактивной работы.
Поддержка мобильных устройств

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

Пример с запросами и объединением источников: комбинирование источников из Нью-Йоркской фондовой биржи и финансового индекса S&P 500.

(кликабельно)

Плюсы : новый современный продукт, дружелюбный интерфейс, легок в освоении, онлайн решение.

Минусы : решение «сырое» (некоторые компоненты могут работать нестабильно), не работает с OLAP кубами, урезанный функционал в сравнении с конкурентами.

Дополнительные материалы:

Вводное видео в Power BI
Пример применения Power BI для финансового анализа
Обзор возможностей (видео)
Анализ истории World Cup history с помощью Excel и Power BI

Pyramid Analytics

Pyramid Analytics - облачная платформа бизнес-аналитики; имеет три ключевых компонента:

Интеллектуальный анализ данных - Data Discovery
Интерактивная работа с данными и визуализацией - Dashboards
Представление данных аудитории - Publisher

Примеры отчета с гео-визуализацией и возможностью исследования данных.

Вводное видео:

(youtube-канал Pyramid Analytics)

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

PyramidAnalytics - самый мощный инструмент из используемых нами.

Плюсы : легок в освоении, работает с огромным количеством источников, очень широкая функциональность.

Минусы : цена.

Компоненты аналитики MS SQL server (MDS, SSIS, SSAS)

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

Master Data Services - процессы и инструменты управления мастер-данными компании. Подробнее .
(Мастер-данные - это данные бизнеса: о клиентах, продуктах, услугах, персонале, технологиях, материалах etc.)

SQL Server Integration Services - миграция и интеграция данных. Подробнее .

SQL Server Analysis Services OLAP и data mining внутри SQL сервера. Подробнее .

Главный инструмент - мозг

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

(Matplotlib

Взаимодействие между R и python

Интерактивные веб-графики в plotly

Бонус-инструмент

В процессе написания статьи Microsoft анонсировала покупку инструмента аналитики от компании Datazen , поэтому будет полезным его кратко упомянуть.

Datazen - это мульти-платформенное решение, позволяющее построить отчетность, независимо от платформы (PC, iOS, Android, Windows Phone). Отличительной чертой продукта является широкая интеграция и возможность полноценной работы аналитической платформы через мобильные устройства.

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

Минусы : Не работает с OLAP напрямую (необходимо создавать запросы MDX самостоятельно), имеет ряд ограничений (начиная от возможностей интерфейса и заканчивая настройкой функциональности), не продается отдельно, идет в комплекте с MS SQL Enterprise Edition.

В следующей статье мы рассмотрим Datazen и Pyramid Analytics в деталях, а также разберемся как на их основе создавать отчетность.

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

SQL для анализа данных? Что это? Какая от него польза?

SQL расшифровывается как Structured Query Language , что переводится как «язык структурированных запросов ».

Но я люблю другое описание: это Excel на стероидах. Вот как выглядит один и тот же набор данных в Excel и SQL :

Как мы видим, SQL и Excel довольно похожи. Оба представляют данные в виде двухмерной таблицы со столбцами и строками. Оба предельно структурированы и просты для понимания.

Но между ними есть и ряд отличий:

Первое - это производительность . Excel отлично работает с небольшими объёмами данных, но когда у вас 100 000 строк, использование формул становится долгим и неэффективным занятием.
Второе - это способ взаимодействия с базой данных . Excel - это, в первую очередь, графический интерфейс пользователя (GUI ). Он позволяет прокручивать таблицу, вводить формулы, курсором необходимые области. В SQL нет графического интерфейса - здесь используются так называемые SQL-запросы .

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

При анализе данных вы будете использовать SQL для довольно простых задач: суммирования, объединения массивов данных, простых статистических и математических методов. Но с SQL ваша работа станет эффективнее, и вы сможете использовать более крупные массивы данных, чем раньше.

А как насчёт Python, R или bash?

Когда вы начнёте использовать эти языки для анализа данных, то поймёте, что Python и R хороши для одного, а SQL для другого. Основное различие этих языков - в синтаксисе, «функциях » и производительности. Ну а теперь давайте перейдём к практической части!

Шаг 0 – установите SQL-систему управления базами данных

Я буду использовать postgreSQL . Есть и другие виды СУБД SQL . Но все они похожи, поэтому если выучить postgreSQL , адаптация к другому языку займёт всего несколько часов (или даже минут).

На данный момент у вас должно быть три вещи:

Сервер данных с доступом через терминал или iTerm ;
PostgreSQL , установленный на ваш сервер;
Установленный на компьютер Pgadmin4 (или SQL Workbench ).

Примечание : в качестве инструмента для работы с SQL я буду использовать SQL Workbench .

Шаг 1 – Зайдите в свою базу данных SQL через командную строку

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

Откройте Терминал (или iTerm ):
Подключитесь к серверу через SSH .

В моём случае я ввожу:

ssh tomi@[мой_ip_адрес]

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

psql -U tomi -d postgres

psql это сама команда, -U указывает на имя пользователя (в моём случае это “tomi” ), а -d указывает название базы данных (в моём случае postgres , как и у вас ). Приглашение командной строки должно измениться на следующее:

Postgres=>

Готово! Теперь у вас есть полный доступ к базе данных SQL .

Для проверки SQL запроса можете ввести:

На экране появится список всех таблиц данных. Пока что она только одна. Но скоро это изменится:

Примечание : базы данных SQL также называют «реляционными базами данных ».

Шаг 2 – Загружаем данные

В этом разделе мы будем работать с небольшим набором данных под названием zoo («зоопарк »). Можете скачать его отсюда в текстовом формате .tsv .

Но давайте продолжим с SQL запроса примера :

1) Создайте таблицу :

CREATE TABLE zoo (animal varchar(10), uniq_id integer PRIMARY KEY, water_need integer);

2) Проверьте, что таблица точно была создана :

У нас есть новая таблица данных: zoo!

3) Загружаем данные!

Скопируйте запрос:

INSERT INTO zoo (animal,uniq_id,water_need) VALUES ("elephant",1001,500), ("elephant",1002,600), ("elephant",1003,550), ("tiger",1004,300), ("tiger",1005,320), ("tiger",1006,330), ("tiger",1007,290), ("tiger",1008,310), ("zebra",1009,200), ("zebra",1010,220), ("zebra",1011,240), ("zebra",1012,230), ("zebra",1013,220), ("zebra",1014,100), ("zebra",1015,80), ("lion",1016,420), ("lion",1017,600), ("lion",1018,500), ("lion",1019,390), ("kangaroo",1020,410), ("kangaroo",1021,430), ("kangaroo",1022,410);

Если всё прошло успешно, вы получите следующее сообщение:

INSERT 0 22

Самый важный оператор SQL: SELECT

Настало время изучить главный оператор языка запросов SQL . Вот он:

SELECT * FROM имя_таблицы;

Мы будем пользоваться SELECT каждый раз, когда нужно будет прочитать, отфильтровать, преобразовать и суммировать данные. Для начала выберем (английское слово select переводится именно «выбрать» ) всё, что есть в таблице zoo .

SELECT * FROM zoo;

Оператор вернул мне всю таблицу.

SELECT * FROM zoo;

SELECT - основной оператор, сообщающий SQL , что мы хотим прочитать данные из таблицы.
* - указывает на то, что мы хотим выбрать все столбцы.
FROM сообщает SQL , что мы собираемся указать таблицу для чтения данных.
zoo это название таблицы. Его можно заменить на любое другое, если у вас есть несколько таблиц.
; это синтаксис, обязательный для SQL . Каждый запрос должен заканчиваться точкой с запятой. Если случайно её пропустить, SQL будет ждать продолжения запроса, и ничего не выведет на экран.

Рассмотренное выше выражение мы будем регулярно использовать при работе со сложными запросами SQL .

Выбор столбцов

Знак * можно заменить на названия столбцов. Рассмотрим в качестве примера следующий запрос:

SELECT animal, water_need FROM zoo;

Получаем именно то, что ожидали: на экране есть столбцы “animal” и “water_need” , но уже нет “uniq_id” .

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

SELECT animal, animal, animal FROM zoo;

Один и тот же столбец повторится несколько раз… Но так как смысла в этом мало, делать так не стоит.

Показываем несколько первых строк данных - инструкция LIMIT

На данный момент мы работаем с таблицей данных, состоящей, из 22 строк. Чтобы ограничить выборку несколькими первыми строками, воспользуемся инструкцией LIMIT – небольшим «расширением » к базовому запросу:

SELECT * FROM zoo LIMIT 10;

На экран будет выведено только 10 первых строк.

Фильтрация строк - инструкция WHERE

Используя инструкцию WHERE , можно выбрать конкретные строки, основываясь на их значениях. SQL запроса пример:

SELECT * FROM zoo WHERE animal = "elephant";

SELECT * FROM zoo –» это «базовый запрос »
WHERE –» говорит SQL , что вы хотите что-то отфильтровать.
animal = "elephant" –» animal - это название столбца, в котором мы ищем нужное значение, а elephant – само значение. В SQL всегда необходимо добавлять столбец, в котором мы ищем нужное значение.
; –» Не забываем про точку с запятой!

Самопроверка #1

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

Выберите первых трёх зебр (zebra ) из таблицы zoo .

Решение этой задачи будет, по сути, итогом сегодняшней статьи.

Готовы?

Вот моё решение:

SELECT * FROM zoo WHERE animal = "zebra" LIMIT 3;

Согласитесь, SQL - это просто .

И ещё кое-что: синтаксис…

Все SQL запросы должны заканчиваться точкой с запятой (; ). Если вы случайно её пропустите, SQL будет считать, что запрос не закончен, и на экран ничего не выведется. Например:

postgres=> SELECT * FROM zoo postgres->

Как-то не очень… Вот так будет лучше:

Postgres=> SELECT * FROM zoo;

Язык SQL не чувствителен к регистру символов, когда дело касается ключевых слов (SELECT , WHERE , LIMIT и т.д .). Например:

SELECT * FROM zoo;

работает так же, как и

Select * from zoo;

Чувствительность к регистру имён таблиц, столбцов и их значений зависит от настроек. В нашей ситуации (при использовании postgreSQL ) имена таблиц и столбцов не чувствительны к регистру, в отличие от значений полей. Например:

SELECT * FROM zoo WHERE animal = "elephant"; –» РАБОТАЕТ SELECT * FROM ZOO WHERE ANIMAL = ‘elephant’; –» РАБОТАЕТ SELECT * FROM ZOO WHERE ANIMAL = "ELEPHANT"; –» НЕ РАБОТАЕТ

Обратите внимание, что большинство людей в языке запросов SQL пишут ключевые слова заглавными буквами (SELECT, WHERE, LIMIT, etc… ), что повышает читаемость кода.