Знакомство с SQL — Введение в дата-аналитику

По своей сути Google Sheets близки к таблицам в базах данных. Потому мы можем сформировать таблицу в Google Sheets, используя таблицу как базу данных. Но это не все: еще мы можем использовать специальные команды.

Команды в Google Sheets похожи на SQL-запросы к базам данных. Официально они называются Google Visualization API Query Language — их мы и изучим в этом уроке.

Чтобы использовать SQL-подобные запросы, нужно создать функцию QUERY. В этом уроке мы будем работать с этой функцией на примере таблицы Hotels:

eyJpZCI6ImViYTU0OGRhNzNjYjUxMmIwYTQ0YjUyMjNhNGQzZTdhLnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=6954186e9c01a241c49bc0ce84a997d5d0dc7024658b84cbdf968405de2233ee

Она содержит такие столбцы:

Id — уникальный идентификатор отеля
Name — название отеля
Stars — количество звезд у отеля (от трех до пяти)
Rating — средняя оценка отеля по шкале от 0 до 10 по мнению гостей
TwinRoomPrice — стартовая цена за комнату с двумя кроватями
City — название города, в котором находится отель (Bandung, Denpasar, или Surabaya)

Синтаксис

Функция QUERY пишется так:

=QUERY(data, query, [headers])

Рассмотрим подробнее ее параметры:

data — диапазон ячеек с данными (мы выделим всю таблицу целиком)
query — SQL-подобный запрос, который нужно выполнить
headers — количество строк с заголовками (обычно это первая строка таблицы)

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

Теперь рассмотрим синтаксис SQL — языка запросов к базе данных. У него есть жесткий порядок ключевых слов:

eyJpZCI6ImQ2ZjIyMTkwZmY3NTI2NWNjY2Q2MGI2ZmM1NDcwYWFiLnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=ffd5b13d78765e72f1af375fc0246b244087f01059330cb51166593a989d7aab

Обсудим подробнее значения этих ключевых слов:

SELECT — «выбрать». Здесь мы указываем, какие данные попадут в наш запрос и как мы их назовем. Например, это может быть столбец SELECT Name
FROM — «из». Здесь указываем название таблицы, из которых будут черпаться данные
JOIN — «соединить». С его помощью мы пишем, по какому столбцу искать соответствия таблице А в таблице Б
WHERE — «где». Это константное условие фильтрации, с помощью которого мы отсеиваем нужные данные. Например, можно отсеять данные, написав запрос WHERE Rating>5
GROUP BY — «группировать по». Это параметр группировки, который определяет, по каким столбцам будет группироваться итоговый запрос
HAVING — «имеющий». Это агрегатное условие фильтрации, по нему мы отсеиваем данные, используя в виде фильтра агрегатную функцию (например, HAVING AVG(Stars)>3)
ORDER BY — «сортировать по». Это параметр сортировки, который меняет порядок отображения данных в итоговой таблице по возрастанию или по убыванию столбцов

Если хотите попрактиковать запросы именно в SQL-формате, можно воспользоваться сервисом DB Fiddle.

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

CRUD: Базовые операции с табличными данными

CRUD (Create, Read, Update и Delete) — это аббревиатура, которая обозначает четыре базовые операции с базами данных.

Чтобы совершать эти операции, мы можем использовать такие операторы:

Создать новые данные в таблице — INSERT
Прочитать данные — SELECT
Обновить данные — UPDATE
Удалить данные — DELETE

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

Пишем первый запрос к Google Sheets

Когда новички изучают другие языки программирования, они в первую очередь учатся писать фразу Hello, world. Аналог этой фразы в мире SQL — это команда SELECT * FROM.

Напишем аналогичный запрос внутри Google Sheets. Сначала выберем свободную ячейку справа от таблицы Hotels, и напишем в ней такой запрос:

=QUERY(A1:F23, ″SELECT *″)

Убедимся, что мы захватили диапазоном всю таблицу сразу:

eyJpZCI6ImU2YTk1ZjM5NWIwOTdmNjZhOWE0OTUzNDQ4ZTcwODcxLnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=b18abdc3fff351e120adf830adc226d2bd9e1ad9b88fe3d55d342d8448bace30

Обратите внимание на отличия от SQL. Когда мы пишем SQL-подобные запросы с QUERY, то ключевое слово FROM нужно ставить перед SELECT. В нашем случае мы обозначили его диапазоном A1:F23.

Символ * обозначает «всё». Другими словами, мы выбираем все без исключения данные из выбранного диапазона значений.

Теперь нажмем Enter:

eyJpZCI6IjIxMmMyYzQ3ZGFhMzMzYjQ1YWUxNGNkOTQxNmFlMTU4LnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=ed69a0f56d6b6e6ae80f337dc909425d7837eb90f591d58a9332060230ad78f3

Мы получили таблицу, полностью идентичную изначальной. Так и должно быть.

Использование столбцов и условий

Предположим, что мы решили отфильтровать только трехзвездочные отели и показать для них три колонки: Name, Rating и TwinRoomPrice.

Чтобы выполнить эту задачу, обратимся к столбцам напрямую:

=QUERY(A1:F23, ″SELECT B, D, E WHERE C=3″)

Обратите внимание, что названия колонок разделены запятыми, а ключевые слова запроса — нет. Вместо названий колонок из таблицы, мы использовали буквы, которыми называются столбцы в Google Sheets.

На картинке ниже видно, как именно запрос обращается к колонкам:

eyJpZCI6ImZlM2EzM2YwNzVhODQyNmEzNzQ1MzM1NDIzYjNlMWI0LnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=0fd872b0b0c9937f5908d1cdf0a2096fff8c4bb9e0f62b46509ca3761bfec0b7

Когда мы жмем Enter, Google Sheets создает аккуратную таблицу с тремя столбцами. Теперь мы видим только трехзвездочные отели с названием, рейтингом и ценой размещения в двухместном номере:

eyJpZCI6IjljZDUzYTQzOWViMGY4ZThlZmI1YzQ0NzQ4MDE1MDk0LnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=bde2ecb5d17c1b9be64fa19265a5f0c4e548d3a28c4eb2173641399e45300082

Аналогичный запрос в среде SQL будет выглядеть так:

SELECT Name, Rating, TwinRoomPrice
FROM Hotels
WHERE Stars=3;

Выводы

В этом уроке вы научились писать запросы к табличным данным. Теперь вы знаете, что манипулировать табличными данными в Google Sheets помогает Google Visualization API Query Language. Этот подход интуитивно более понятен. Его использовать намного проще, чем логические и математические функции в Google Sheets.

Самостоятельная работа

Booking.com — это популярная платформа для поиска отелей. Ежедневно ей пользуются миллионы людей: бронируют жилье и оставляют отзывы. В свою очередь, отели и собственники жилья отвечают на отзывы гостей.

Перейдите по ссылке и скопируйте оттуда данные с отзывами об отеле La Veranda Hotel. В документе уже настроены фильтры для выполнения запросов. Они позволяют фильтровать данные по следующим значениям: