Запросы к данным с помощью QUERY

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

Вместо того чтобы комбинировать FILTER, SORT и SUMIF, Вы записываете одну понятную инструкцию. Например, запрос показать только строки региона Восток, отсортированные по продажам превращается в одну формулу.

QUERY есть только в Google Таблицах. В Excel такой функции нет, поэтому весь этот урок посвящён только Google Таблицам.

QUERY состоит из трёх частей:

• данные диапазон для чтения, например A1:D100
• запрос текстовая строка с инструкциями в кавычках
• заголовки необязательное число, указывающее, сколько строк заголовков содержится в данных

Строка запроса — место, где происходит всё самое интересное. Она всегда заключается в двойные кавычки.

Конструкция SELECT определяет, какие столбцы вернуть. Внутри QUERY столбцы обозначаются буквами столбцов таблицы (A, B, C), а не названиями заголовков.

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

Представьте таблицу продаж в A1:D7 с заголовками Регион, Представитель, Товар, Продажи:

• A: Регион (Восток, Запад)
• B: Представитель (Ann, Bo, Cy)
• C: Товар (Ручки, Бумага)
• D: Продажи (числа)

Мы будем использовать этот набор данных до конца урока. Представляйте его, когда видите ниже формулу QUERY.

Конструкция WHERE оставляет только строки, которые проходят проверку. Сравнивайте столбец со значением с помощью таких операторов, как =, >, < и >=.

Текстовые значения заключаются в одинарные кавычки внутри строки запроса, поскольку весь запрос уже заключён в двойные кавычки.

Для числовых фильтров кавычки не нужны. Чтобы увидеть только продажи с высокой суммой, сравните столбец D непосредственно с числом.

В результате останутся только строки, где Sales больше 500, поэтому в отчёте будут выделены важные сделки.

Используйте AND и OR, чтобы объединять проверки, как в SQL.

• AND требует, чтобы оба условия были истинными
• OR требует, чтобы истинным было хотя бы одно условие

В этом примере остаются строки региона Восток, в которых продажи также превысили 300.

Добавьте LIMIT, чтобы ограничить количество возвращаемых строк. Это удобно для предварительного просмотра первых N результатов или для поддержания порядка на панели мониторинга.

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

Сначала это сбивает с толку почти всех. Весь запрос представляет собой одну текстовую строку Google Таблиц, поэтому для неё используются двойные кавычки. Любой текст, с которым Вы выполняете сравнение, должен использовать одинарные кавычки внутри этой строки.

Запись WHERE A = "East" нарушает формулу, потому что внутренняя двойная кавычка преждевременно завершает строку. Всегда используйте WHERE A = 'East'.

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

Если в ячейке F1 указано название региона, эта формула фильтрует данные по тому, что ввёл пользователь.

Третий аргумент сообщает QUERY, сколько строк заголовков содержится в данных. Передайте 1, если первая строка содержит такие подписи, как Region и Sales.

Если опустить этот аргумент, QUERY пытается угадать, и иногда ошибается: добавляет заголовок в результаты или воспринимает числа как текст. Явное указание 1 делает результаты предсказуемыми.

Проверьте понимание основ QUERY.

Вы изучили основные принципы QUERY в Google Таблицах:

• QUERY(data, query, headers) — это структура функции
• SELECT выбирает столбцы по буквам; * означает все столбцы
• WHERE фильтрует строки в сочетании с AND и OR
• Для текста используются одинарные кавычки; для чисел кавычки не нужны
• & подставляет значения ячеек для интерактивных фильтров

Далее Вы отсортируете и сгруппируете эти результаты, чтобы получить сводки.