SQL для анализа данных
Если вы хотите стать специалистом по обработке и анализу данных, без 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 postgrespsql это сама команда, -U указывает на имя пользователя (в моём случае это “tomi”), а -d указывает название базы данных (в моём случае postgres, как и у вас). Приглашение командной строки должно измениться на следующее:
postgres=>
Готово! Теперь у вас есть полный доступ к базе данных SQL.
- Для проверки SQL запроса можете ввести:
dt
На экране появится список всех таблиц данных. Пока что она только одна. Но скоро это изменится:
Примечание: базы данных SQL также называют «реляционными базами данных».
Шаг 2 – Загружаем данные
В этом разделе мы будем работать с небольшим набором данных под названием zoo («зоопарк»). Можете скачать его отсюда в текстовом формате .tsv.
Но давайте продолжим с SQL запроса примера:
1) Создайте таблицу:
CREATE TABLE zoo (
animal varchar(10),
uniq_id integer PRIMARY KEY,
water_need integer
);
2) Проверьте, что таблица точно была создана:
dtУ нас есть новая таблица данных: 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…), что повышает читаемость кода.
Заключение
Мы сделали первый шаг к использованию SQL для анализа данных! Поздравляю! Теперь вы можете писать основные запросы. Но это только начало!