Теория: Знакомство с SQL

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

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

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

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

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

Синтаксис

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

=QUERY(data, query, [headers])

Если же у вас Гугл и Гугл-таблицы с русскоязычным интерфейсом, то в формуле параметры будут отделяться точкой с запятой

=QUERY(data; query; [headers])

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

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

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

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

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

• 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 *″)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы

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