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

Компания Google, понимая потребность бизнеса, разработал для своего сервиса Google Таблицы, функцию QUERY. Функция Query позволяет выполнять запросы на базе языка запросов, который поддерживает простой синтаксис SQL языка. Вы выгружаете массив данных в Google таблицу и работаете с этой таблицей, как с базой данных. Это просто, давайте разберемся.

Как выглядит запрос с использованием QUERY:

QUERY(A2:E6; "select avg(A) pivot B")
QUERY(A2:E6; F2; ЛОЖЬ)

Синтаксис - QUERY(данные; запрос; [заголовки]), где

  • Данные – диапазон ячеек с данными, по которым будем выполнять запрос.
  • Запрос – запрос который будем выполнять.
  • Заголовки – необязательный параметр, указывает какое количество строк в заголовке необходимо пропустить.

Тут можно получить более подробную информацию о языке запросов.

Функция Query предоставляет большие возможности для работы с данными Google Таблиц. Она может заменить функционал большинства функций. А SQL подобный язык облегчает работу с ней, т.к. это стандарт, с которым работает армия аналитиков и программистов.

Давайте рассмотрим несколько примеров. Создадим тестовую таблицу.

данные

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

=QUERY('Лист1'!$A$2:$H$7; "select A where (B<>'Программист' and G=true) or ('Зарплата' > 500)") 

Результат:

Можем использовать оператор Group by, например посчитаем среднюю заработную плату по должности

=QUERY('Данные'!$A$2:$H$7; "select B, avg(D) group by B")

Результат:

А теперь, с помощью оператора Pivot, развернем

результат.=QUERY('Данные'!$A$2:$H$7; "select avg(D) pivot B")

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

=QUERY('Данные'!$A$2:$H$7; "select B, max(D) group by B order by max(D) desc")

Функция Query может использовать не только данные из листа текущей таблицы, это могут быть данные загруженные из другой таблицы Google, с помощью оператора ImportRange().

Пример запроса:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vPDvSDmp5TVOSxODTBXmPc_vfdXIgAXmj9jAR8AOFNA/edit#gid=0";"Данные!A:C"); "select *")

Рассмотрим все поддерживаемые операторы и функции SQL подобного языка от Google.

Операторы:
select — перечисленные поля вернуться в результат;
where — задаются условия выборки данных;
group by — группировка данных по перечисленным полям;
pivot — разворачивает таблицу, преобразуя строки в столбцы;
order by — сортировка результата по указанным столбцам;
limit — ограничивает выдачу результата по указанному количеству строк;
offset — позволяет пропустить указанное количество строк в выдаче;
label — изменяет название столбцов;
format — форматирует значения в определенных столбцах с использованием заданных шаблонов;

Операторы в Where:
contains — ищет вхождение подстроки в строке;
starts with — фильтрует по первым символам указанным после оператора;
ends with — фильтрует по конкончанию;
matches — позволяет выполнять регулярные выражения по совпадению.
like — упрощенное регулярное выражение, фильтрует поле по вхождению строки.

Агрегационные функции:
avg() — вернет среднее значение
count() — вернет количество значений
max() — вернет максимальное значение
min() — вернет минимальное значение
sum() — вернет сумму значений

Также поддерживаются скалярные функции, их смысл понятен:
year()
month()
day()
hour()
minute()
second()
millisecond()
quarter()
dayOfWeek()
dateDiff()
toDate()
upper()
lower()

Используйте функцию Query, когда работаете с большими данными.

Есть вопросы  – пишите нам в Telegram или на google@pik.digital

Больше информации о G Suite на  нашем сайте.