В мире информационных технологий одной из наиболее важных задач является поиск нужной информации в огромных базах данных. Именно поэтому существует множество различных систем, которые позволяют пользователю быстро и эффективно находить интересующую его информацию. Одним из таких инструментов является функция Query, которая может использоваться в различных системах, таких как базы данных, поисковые системы и другие.
Что такое функция Query?
Функция Query представляет собой инструмент для получения данных из базы данных или другой системы. Ее основной задачей является поиск информации по заданным критериям, например, определенным значениям полей в таблице базы данных. Query позволяет сформировать запрос, в котором можно указать все необходимые критерии для поиска нужной информации. В результате выполнения запроса функция Query возвращает все записи, которые соответствуют указанным критериям.
Как использовать функцию Query?
Использование функции Query может отличаться в зависимости от того, в какой системе она применяется. Однако, в целом, процесс использования функции Query можно разбить на несколько этапов.
Определение критериев поиска
Первый шаг в использовании функции Query — определение критериев поиска. Критерии поиска могут включать в себя любые значения полей, которые необходимы для поиска нужной информации. Например, если мы ищем всех клиентов, у которых возраст больше 30 лет, то критерием поиска может быть значение поля «возраст» больше 30.
Формирование запроса
После определения критериев поиска необходимо сформировать запрос. Запрос должен содержать все необходимые критерии поиска, а также определение того, из какой таблицы или базы данных необходимо получить данные. В запросе можно указать также другие параметры, такие как порядок сортировки или количество возвращаемых записей.
Выполнение запроса
После того, как запрос сформирован, его можно выполнить. Результатом выполнения запроса будет набор записей, которые соответствуют указанным критериям поиска.
Пример использования функции Query
Для того, чтобы проиллюстрировать использование функции Query, рассмотрим пример. Предположим, у нас естьбаза данных с информацией о клиентах некоторой компании. База данных содержит таблицу с полями «Имя», «Фамилия», «Возраст», «Адрес», «Телефон» и «E-mail». Мы хотим найти всех клиентов, чьи фамилии начинаются на букву «S» и чей возраст больше 25 лет.
Определение критериев поиска
В данном случае, наши критерии поиска будут следующими:
- Фамилия начинается на букву «S»
- Возраст больше 25 лет
Формирование запроса
Запрос для данного поиска может выглядеть следующим образом:
SELECT * FROM clients WHERE surname LIKE ‘S%’ AND age > 25;
Этот запрос выберет все записи из таблицы «clients», у которых фамилия начинается на букву «S» и возраст больше 25 лет.
Выполнение запроса
После выполнения запроса, мы получим набор записей, которые соответствуют нашим критериям поиска. Для примера, допустим, что база данных содержит следующие записи:
| Имя | Фамилия | Возраст | Адрес | Телефон | |
| John | Smith | 28 | 123 Main St. | (555) 555-1234 | john.smith@example.com |
| Mary | Stevens | 22 | 456 Elm St. | (555) 555-5678 | mary.stevens@example.com |
| Sarah | Jones | 30 | 789 Oak St. | (555) 555-9012 | sarah.jones@example.com |
| Mark | Sanders | 27 | 111 Pine St. | (555) 555-3456 | mark.sanders@example.com |
После выполнения нашего запроса, мы получим две записи:
| Имя | Фамилия | Возраст | Адрес | Телефон | |
| John | Smith | 28 | 123 Main St. | (555) 555-1234 | john.smith@example.com |
| Mark | Sanders | 27 | 111 Pine St. | (555) 555-3456 | mark.sanders@example.com |
Эти записи соответствуют нашим критериям поиска.
Заключение
Функция Query — мощный инструмент для поиска информации в базах данных и других системах. Она позволяет быстро и эффективно находить нужную информацию, используя заданные критерии поиска. Чтобы использовать функцию Query, необходимо определить критерии поиска, сформировать запрос и выполнить его. При правильном использовании функции Query можно значительно ускорить процесс поиска информации и повысить эффективность работы с базами данных и другими системами.
Однако, следует помнить, что неправильное использование функции Query может привести к ошибкам или неправильному результату поиска. Поэтому, перед использованием функции Query, необходимо тщательно продумать критерии поиска и проверить корректность запроса.
В целом, функция Query является одним из основных инструментов для работы с базами данных и другими системами, и ее использование может значительно упростить поиск нужной информации.
27.03.2023
Выполняет запросы на базе языка запросов API визуализации Google.
Пример использования
QUERY(A2:E6; "select avg(A) pivot B")
QUERY(A2:E6; F2; ЛОЖЬ)
Синтаксис
QUERY(данные; запрос; [заголовки])
-
данные– диапазон ячеек, для которого нужно выполнить запрос.-
Каждый столбец
данныхможет содержать только логические, числовые (включая типы дата/время) или строковые значения. -
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.
-
-
запрос– запрос на выполнение, записанный на языке запросов API визуализации Google.-
Значение параметра
запросдолжно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую соответствующий текст. -
На странице https://developers.google.com/chart/interactive/docs/querylanguage можно получить более подробную информацию о языке запросов.
-
-
заголовки– [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части разделаданных. В случае, если параметр опущен или равен-1, его значение вычисляется автоматически в зависимости от содержимогоданных.
Примеры
Пример данных.
Возвращает строки, удовлетворяющие заданному условию, используя операторы Select и Where.
Суммирует значения Заработной платы во всех строках, используя операторы Select и Group by.
Находит различающиеся значения в столбцах и переносит их в новые столбцы.
Суммирует значения Отдел во всех строках и сортирует их по максимальному значению Заработной платы.
Определяет количество заголовочных строк во входном диапазоне, после чего преобразует заголовок из нескольких строк в однострочный.
Эта информация оказалась полезной?
Как можно улучшить эту статью?
Спасибо Евгению Намоконову за помощь в подготовке материала.
Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.
Синтаксис функции:
=QUERY(данные; запрос; [заголовки])
- данные — это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
- запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
- заголовки — количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.
Итак, правила формирования запросов:
- Запрос указывается в кавычках.
- В запросе используются ключевые слова:
- SELECT — определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
- WHERE — ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
- GROUP BY — группирует значения по заданным полям.
- PIVOT — позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
- ORDER BY — задает сортировку. Например: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
- LIMIT — ограничивает количество возвращаемых строк. Например: «LIMIT 50».
- OFFSET — пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
- FORMAT — определяет формат определенных столбцов по заданному шаблону.
- LABEL — позволяет переименовать столбцы в выдаче. Например, «LABEL MAX(D) ‘Среднее в 2016 году'». Вместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».
Справка от Google по языку запросов API находится по ссылке: https://developers.google.com/chart/interactive/docs/querylanguage
Рассмотрим несколько примеров применения QUERY на практике.
Простой пример: выбираем книги определенной тематики из таблицы
Из простой исходной таблицы будем формировать список книг по тематике:
При этом тематику будем выбирать из выпадающего списка на отдельном листе:
Функция QUERY для решения этой задачи будет выглядеть следующим образом:
=QUERY(Книги‘!A1:C;«SELECT A, C WHERE B = ‘»&A1&«‘ ORDER BY C DESC»;1)
Мы извлекаем данные из столбцов A и C в диапазоне ‘Книги’!A1:C. Фильтруем данные по столбцу B (тематике) этого диапазона по выбранному критерию из выпадающего списка в ячейке A1. Сортируем по убыванию по столбцу C исходного диапазона и добавляем к нашей выборке заголовки (последний аргумент функции QUERY = 1).
Группируем данные с помощью GROUP BY и PIVOT
Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.
Таблица, с которой мы будем работать:
Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.
Начнем с GROUP BY, текст функции будет таким:
=QUERY(‘Книги‘!A1:C6;»select B, sum(C) group by B»)
Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().
Результат нашей формулы:
С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:
Группировка с помощью PIVOT.
Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы.
Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.
Добавим еще один столбец для группировки.
Видите — два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.
Строим сводную таблицу со средними/максимальными значениями по тематикам
В этом примере мы построим небольшую сводную таблицу, где будут отображены средние значения по тематикам за два года:
QUERY(‘Книги‘!A1:D;«SELECT avg(C), avg(D) pivot B»;1)
Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).
Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):
Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):
Или отобразить и среднее, и максимум, но только по столбцу D:
SELECT avg(D), max(D)
Кейс «Считаем средний чек, выбирая данные с определенной даты»
На скриншоте массив данных, с которым мы будем работать:
Наша задача: отобрать строки с продажами начиная с 1 апреля и посчитать по ним средний чек, используя количество клиентов, то есть получить среднее взвешенное.
Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:
Правильно использовать дату в формуле QUERY так:
- QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
- перед датой и перед апострофом нужно написать date;
- можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:
- дата с двух сторон обрамляется одиночными кавычками (‘).
Вернемся к тому, что у нас получилось. Наша формула выдала вот такой массив данных:
Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):
Чтобы получить средний чек, получившееся число нужно разделить на общую сумму клиентов в отобранных строках. Чтобы закрепить использование QUERY, опять воспользуемся этой формулой.
Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:
Наконец, совмещаем формулы:
Все работает, ура! 53 (этот результат видно на всплывающей подсказке в верхнем левом углу) — средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.
Кейс «QUERY и выпадающий список»
Возьмем табличку с продажами книг. На ее основе будем делать отчет с выпадающим списком, в котором будут все тематики, и формулой QUERY, выводящей книги выбранной тематики и сортирующей их по продажам.
Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных.
В Правилах выбираем Значение из списка, перечисляем все наши тематики через запятую и нажимаем Сохранить:
Список получился вот таким:
В соседнюю ячейку А2 впишем следующую формулу:
=QUERY(‘Книги‘!A1:C13,«SELECT A, C WHERE B = ‘»&A1&«‘ ORDER BY C DESC»)
И разберем ее по частям:
- ‘Книги’!A1:C13 — исходный диапазон, таблица с продажами, книгами и тематиками.
- SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
- WHERE B = ‘»&A1&»‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
- ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.
Результат:
Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.
Если бы в нашей исходной таблице была дата, мы могли бы добавить ее в условие QUERY и выводить данные за выбранный день, месяц, неделю — таким образом можно получить готовый отчет по продажам, который не нужно каждый раз заново собирать.
Query по нескольким диапазонам данных
В качестве первого аргумента функции QUERY можно указать массив, состоящий из нескольких диапазонов данных. Главное, чтобы эти диапазоны были таблицами с одинаковой структурой.
Диапазоны указываются через точку с запятой в фигурных скобках:
=QUERY({Диапазон 1; Диапазон 2; Диапазон 3; Диапазон 4};…
Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N — номер столбца.
Канал «Google Таблицы — это просто» в Телеграме
Спасибо Евгению Намоконову за помощь в подготовке кейсов для этой статьи. Мы с Евгением ведем канал в Телеграме по Google Таблицам.
Регулярно публикуем новые кейсы и советы. Заглядывайте в гости:
https://telegram.me/google_sheets
Почему QUERY? Потому что это одна из самых мощных функций гугл-таблиц с обширным функционалом, который можно комбинировать и изменять по вашему желанию. Зная хотя бы базовый функционал QUERY, вы можете перекрыть ею огромную часть взаимодействия с таблицами. А еще оптимизируете и автоматизируете процессы, откажетесь от ручных данных и отчетов.
По своему синтаксису QUERY напоминает язык запросов SQL, и в ней правда есть где экспериментировать — она может быть простой, а может состоять из более чем 10 строк.
В этом мануале мы взяли в качестве примера данные о фильмах из Википедии и исследовали различные примеры их использования с помощью QUERY. Я буду предлагать решения, которыми лично пользуюсь в работе.
Например, вам нужно отфильтровать данные или создать динамическую «выпадашку», которая будет сортировать список по статической или динамической дате, названием либо ограничить данные (режиссер = Кевин Смит). В таком случае QUERY пригодится. Финальный результат нашей работы вы найдете по ссылке (в этом файле приведены все примеры последующих формул). Let’s go!
Содержание мануала по QUERY:
#1. Основной функционал, конструкции и синтаксис
#2. Формулы импорта — например, выгрузка таблицы из википедии или импортирование данных из другой таблицы
#3. ORDER BY — сортировка по значению
#4. LIMIT/OFFSET — выведем топ-5 для вашей таблицы
#5. Переименование колонки таблицы с помощью LABEL
#6. GROUP BY — группировка и агрегация данных (складывать, умножать)
#1. Основной функционал, построение конструкции и синтаксис QUERY
Базовый синтаксис выглядит так:
=QUERY(диапазон данных; “SELECT (вызыватели и ключевые слова)”)
Стандартный вызов QUERY выглядит так:
=QUERY(A:B; “SELECT * ”)
где А:В – это диапазон, а «*» — вызов всех колонок из таблицы. В нашем случае SELECT * равен SELECT А, В — то есть все колонки из нашего диапазона.
При вызове импорта колонки будут называться не А и В, а Col1 и Col2. Мы можем писать ключевые слова любым регистром — можно QUERY, а можно query, так же SELECT, или select. Но что касается значений — условия четкие, только верхним регистром A и B или только Col1 и Col2.
Для ограничения колонок, например, нам нужна только колонка А, запрос выглядел бы так:
=QUERY(A:B; «SELECT А»)
Для примера — вот так это выглядит:
А конечный вид, то есть после нажатия Enter — так:
После SELECT * или ввода необходимых колонок SELECT A либо SELECT Col1 начинается ввод других ключевых слов.
Формула будет работать и без них. Другие ключи — дополнительные аспекты, которые расширяют ваши возможности.
Вы можете использовать их по отдельности, а можете комбинировать. Но порядок имеет значение. Если использовать сначала ORDER BY, а затем WHERE, то гугл-таблицы обозначат это как ошибку и формула не будет функционировать.
#2. Формулы импорта в QUERY
Если нужно вывести таблицу с другого ресурса или из другой таблицы, функция QUERY поддерживает стандартные веб-функции гугл-таблиц для этого (скрин взят из гугл справки):
Наиболее распространенной из них является IMPORTRANGE, которая позволяет импортировать данные из другой google-таблицы.
Формулы импорта находятся в блоке «диапазон» QUERY.
Синтаксис
IMPORTRANGE = IMPORTRANGE(«код ссылки»; «диапазон»)
где код ссылки это:
А диапазон выглядит как название листа, из которого будет импорт, затем восклицательный знак и колонки без пробелов, например:
Basics! A:B — лист с названием Basics, колонки с А по В.
Пример финального вида формулы:
=QUERY(IMPORTRANGE(«1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE»;»Basics!A:B»); «SELECT *»)
Важно: после написания формулы она подсветится красным.
Так и должно быть. Нажимаем Enter и смотрим на сообщение:
«Нужно подключить эти электронные таблицы. Когда целевая таблица впервые собирает данные из новой исходной таблицы, необходимо предоставить разрешение».
Нажимаем «Разрешить доступ» и получаем импортированную таблицу.
Если вы хотите использовать несколько таблиц или диапазонов — это можно сделать с помощью ARRAYFORMULA. Однако не забывайте, что данных не будет, если не открыт доступ к таблицам. При использовании формулы массива (array) такого сообщения об ошибке не будет, данные просто не будут выведены. Вам необходимо отдельно открыть доступ к каждой таблице, а затем группировать их в 1 диапазон.
Формула IMPORTHTML похожа на IMPORTRANGE. Вот пример, который мы будем использовать в статье:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/List_of_horror_films_of_2023″;»Table»); «Select * «)
Как вы видите, разница в том, что здесь мы вставляем полную ссылку на источник, из которого получаем необходимую таблицу. Вы можете скопировать линк из формулы и посмотреть ее составляющие.
* Выше, в описании QUERY, я обращала внимание, что при использовании стандартного диапазона, то есть
=QUERY(A:D; «SELECT А, C, D»)
вы используете название колонки, то есть конкретную букву — А, C, D
Если вы используете импорт, то вместо A будет Col1, вместо C — Col3, вместо D — Col4. И не забывайте о регистре.
Введете
=QUERY(IMPORTRANGE(«1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE»;»Basics!A:B»); «SELECT A»)
будет ошибка.
Введете
=QUERY(IMPORTRANGE(«1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE»;»Basics!A:B»); «SELECT col1»)
или
=QUERY(IMPORTRANGE(«1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE»;»Basics!A:B»); «SELECT COL1»)
будет ошибка.
Правильный вариант:
=QUERY(IMPORTRANGE(«1awflL8d91pZvUvese4f8MRI_U32RiaBFRw5uvvAJefE»;»Basics!A:B»); «SELECT Col1»)
*Выше я говорила об импорте из нескольких таблиц. Основное условие — таблицы должны быть схожи по структуре.
Цель — создать 1 общую таблицу из table1, table2, table3.
Делаем отдельный импорт для каждой таблицы, чтобы предоставить доступ и, как выше, нажимаем «Разрешить доступ».
Затем заключаем все это в фигурные скобки и получаем результат.
Вот так выглядит формула:
=QUERY(
{IMPORTRANGE(«1b_gxNwr7KM7HNL4gdMCu-EnclI_TcfmcoF7PaSTCVGQ»;»movie!A:E»);
IMPORTRANGE(«1NK0BCRgfAUch8oX6cwgFaVpACjeoRHsgUSH2c3Rjzc0″;»movie!A:E»);
IMPORTRANGE(«11AEZOjU_QQ-j8Zfw1tRtjeCtkCJ8-SGqKd8jzBkWR20″;»movie!A:E»)};
«SELECT * «)
И вот так — таблица:
#3. Блок сортировки таблицы по критериям (лист ORDER BY нашей таблицы)
Здесь мы будем отталкиваться от топа фильмов: AFI’s 100 Years…100 Movies — 10th Anniversary Edition (источник — Википедия). В своих таблицах вам не обязательно брать другой источник или другую таблицу. Диапазон может быть любым удобным для вашего использования.
Так что начало формулы мы возьмем из предыдущего блока:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select *»)
С помощью SELECT * мы вывели все данные и теперь начинаем их сортировать.
Мы имеем:
- Rank — ранг в формате Текст от 1. до 100. (не число, потому что у нас есть точка в конце)
- 10th anniversary list (2007) — название фильма в формате Текст
- Director — режиссер в формате Текст
- Year — в формате Число (обратите внимание, что именно здесь формат не дата, а само число. Можете попытаться перевести его в формат Дата на вкладке «формат» шапки гугл-таблиц и посмотреть, что будет)
* по желанию, при использовании такой формулы =DATE(D3;1;1), где D3= число «1941», а «1» и «1» — это номер месяца и дня соответственно, вы можете превратить наш год в формате Число в Дату
- Production companies — компания, формат Текст
- Change from 1998 — количество изменений в формате Число
А теперь отсортируем нашу таблицу по колонке Year. Мы можем сделать это с помощью ORDER BY. Этот артикул поддерживает значение DESC — на убывание, а ASC — на возрастание (используется по умолчанию).
Итак, Цель 1: указанный диапазон данных отсортировать по году создания на убывание:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select * ORDER BY Col4 desc»)
Цель 2: Отсортировать по году создания на убывание и по режиссеру на возрастание (помним, что на возрастание, то есть ASC, используется по умолчанию, поэтому его можно не писать).
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select * ORDER BY Col4 desc, Col3″)
Мы просто добавили запятую и дописали, что еще нам нужно отсортировать.
#4. Функции LIMIT/OFFSET в QUERY
Продолжаем использовать наш список из топ-100 фильмов за 100 лет.
Напоминаю, наша базовая формула выглядит так:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select *»)
- LIMIT — для ограничения количества строк. Имейте в виду, что первая строчка (заголовков таблицы) не считается.
Попробуйте задать формуле такой вид и посмотреть, что будет:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select * LIMIT 0″)
А теперь такой:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select * LIMIT 1″)
- OFFSET — говорит, что считать нужно не с первой строки, а, например, с 3-й. В нашей таблице есть колонка Ранга, поэтому вы можете посмотреть результат самостоятельно, как и что выводится.
Займемся целью вывести 2 строки, начиная с 4-й (то есть limit 2 offset 3 — в этом случае третья строка не будет выводиться, offset 3 будет начинаться с 4-й строки таблицы):
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);»Select * LIMIT 2 OFFSET 3″)
И наша таблица выглядит так:
В нашем случае для построения таблицы «Топ-5 фильмов» удачным решением будет использование комбинации LIMIT + ORDER BY. То есть мы сортируем по определенному параметру и выводим первые значения.
Цель: вывести 5 последних фильмов из нашего списка.
Здесь мы используем сортировку на убывание с помощью ORDER BY, а затем ограничение количества строк с помощью LIMIT — и наша формула будет выглядеть следующим образом:
=QUERY(
IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);
«SELECT *
ORDER BY Col4 DESC
LIMIT 5″)
А таблица — так:
#5. Переименование колонок с помощью LABEL
Рассмотрим на предыдущем примере. Смысл состоит в том, что QUERY создает не обычную таблицу, а целый массив. И если вы хотите изменить название колонки, то просто переписать вручную не получится.
Наше название 10th anniversary list (2007) — довольно длинное, так что можно обойтись обычным movie. Перепишем ее: добавляем новое название и получаем ошибку.
«Результат массива не развернут, иначе он переписал бы данные в B2».
Так что удаляем название movie и добавляем в конце нашей формулы LABEL Col2 ‘movie’
Заодно заменим Production companies на companies, а Change from 1998 на changes.
Формула:
=QUERY(IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);
«SELECT *
LABEL Col2 ‘movie’, Col5 ‘companies’, Col6 ‘changes'»)
Таблица:
#6. Функция GROUP BY в QUERY
В этом блоке разберем группирование данных после проведения агрегации над ними.
Для примера имеем такой набор данных:
Наша цель — вывести таблицу, где будут подытожены данные из столбца «Количество» по названию:
То есть мы подытожили строки по полю «Количество» и прописали, что группировка должна быть по параметру «Название».
Получаем вывод — GROUP BY не имеет смысла без агрегации, а агрегация не сработает без GROUP BY.
Поддерживаемые функции агрегации:
Из предыдущего примера из таблицы с колонками «Название» и «Количество» давайте выведем формулу:
=QUERY(A:B;”Select A, sum(B), count(B), avg(B), max(B), min(B) GROUP BY A «)
* в нашем примере было только 2 колонки, из которых 1 агрегирована, а другая сгруппирована. В другой ситуации, где, например, у вас будет 10 колонок и 1 из них будет агрегирована, необходимо будет группировать по всем остальным 9 колонкам. Если вы уже пошли по этому пути, то нельзя оставлять ни одну колонку без агрегации или группировки.
Хотите получать дайджест статей?
Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за подписку!
Проработаем еще один пример.
Цель: вывести топ-10 режиссеров, чьи фильмы встречались наибольшее количество раз в нашем списке из 100 лучших лент за 100 лет в порядке убывания. В итоге должны получиться 2 колонки с названием Director и Count:
=QUERY(
IMPORTHTML(«https://en.wikipedia.org/wiki/AFI%27s_100_Years…100_Movies_(10th_Anniversary_Edition)»;»Table»);
«SELECT Col3, COUNT(Col3)
GROUP BY Col3
ORDER BY COUNT(Col3) DESC
LIMIT 10
LABEL Col3 ‘Director’, COUNT(Col3) ‘Count’
«)
В результате мы получили такую таблицу:
Наша формула выглядит все более громоздкой. Чтобы не потеряться в своем коде, рекомендую разделять его на отдельные блоки, чтобы значения не терялись. Вы можете сделать абзац в формуле с помощью клавиш Alt + Enter.
Следует отметить, что символы:
«+» — суммирование
«-» — вычитание
«/» — деление
«*» — умножение
поддерживаются и не подпадают под группировку по предыдущей логике, потому что мы обрабатываем имеющееся число в существующей ячейке.
На сегодня все. Но мы планируем вторую часть гайда, где разберем кляузу WHERE и топ ошибок в работе с функционалом QUERY.
Хотите получать дайджест статей?
Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за подписку!
The Google Sheets Query function is the most powerful and versatile function in Google Sheets.
It allows you to use data commands to manipulate your data in Google Sheets, and it’s incredibly versatile and powerful.
This single function does the job of many other functions and can replicate most of the functionality of pivot tables.
This video is lesson 14 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge
=QUERY(data, query, [headers])
It takes 3 arguments:
- the range of data you want to analyze
- the query you want to run, enclosed in quotations
- an optional number to say how many header rows there are in your data
Here’s an example QUERY function:
=QUERY(A1:D234,"SELECT B, D",1)
The data range in this example is A1:D234
The query statement is the string inside the quotes, in green. In this case, it tells the function to select columns B and D from the data.
The third argument is the number 1, which tells the function that the original data had a single header row. This argument is optional and, if omitted, will be determined automatically by Sheets.
It’s one of the Google-only functions that are not available in other spreadsheet tools.
QUERY Function Notes
The keywords are not case sensitive, so you can write “SELECT” or “select” and both work.
However, the column letters must be uppercase: A, B, C, etc. otherwise you’ll get an error.
The keywords must appear in this order (of course, you don’t have to use them all):
- select
- where
- group by
- order by
- limit
- label
You’ll see examples of all of these keywords below.
There are a few other keywords but they are much less common. See the full list here.
Google Sheets QUERY Function Template
Click here to open a view-only copy >>
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
Google Sheets QUERY Function Examples
If you want to follow along with the solutions, please make a copy of the Google Sheet template above.
This is what our starting data looks like:
In this tutorial, I have used a named range to identify the data, which makes it much easier and cleaner to use in the QUERY function. Feel free to use the named range “countries” too, which already exists in the template.
If you’re new to named ranges, here’s how you create them:
Select your data range and go to the menu:
Data > Named ranges…
A new pane will show on the right side of your spreadsheet. In the first input box, enter a name for your table of data so you can refer to it easily.
SELECT All
The statement SELECT * retrieves all of the columns from our data table.
To the right side of the table (I’ve used cell G1) type the following Google Sheets QUERY function using the named range notation:
=QUERY(countries,"SELECT *",1)
Notes: if you don’t want to use named ranges then that’s no problem. Your QUERY formula will look like this:
=QUERY(A1:D234,"SELECT *",1)
For the remainder of this article, I’ve used the named range “countries” but feel free to continue using the regular range reference A1:D234 in its place.
The output from this query is our full table again, because SELECT * retrieves all of the columns from the countries table:
Wow, there you go! You’ve written your first QUERY! Pat yourself on the back.
SELECT Specific Columns
What if we don’t want to select every column, but only certain ones?
Modify your Google Sheets QUERY function to read:
=QUERY(countries,"SELECT B, D",1)
This time we’ve selected only columns B and D from the original dataset, so our output will look like this:
Important Note
Remember, our QUERY function is in cell G1, so the output will be in columns G, H, I, etc.
The B and D inside the QUERY select statement refer to the column references back in the original data.
WHERE Keyword
The WHERE keyword specifies a condition that must be satisfied. It filters our data. It comes after the SELECT keyword.
Modify your Google Sheets QUERY function to select only countries that have a population greater than 100 million:
=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)
Our output table is:
Let’s see another WHERE keyword example, this time selecting only European countries. Modify your formula to:
=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)
Notice how there are single quotes around the word ‘Europe’. Contrast this to the numeric example before which did not require single quotes around the number.
Now the output table is:
ORDER BY Keyword
The ORDER BY keyword sorts our data. We can specify the column(s) and direction (ascending or descending). It comes after the SELECT and WHERE keywords.
Let’s sort our data by population from smallest to largest. Modify your formula to add the following ORDER BY keyword, specifying an ascending direction with ASC:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)
The output table:
Modify your QUERY formula to sort the data by country in descending order, Z – A:
=QUERY(countries,"SELECT B, C, D ORDER BY B DESC",1)
Output table:
LIMIT Keyword
The LIMIT keyword restricts the number of results returned. It comes after the SELECT, WHERE, and ORDER BY keywords.
Let’s add a LIMIT keyword to our formula and return only 10 results:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT 10",1)
This now returns only 10 results from our data:
Arithmetic Functions
We can perform standard math operations on numeric columns.
So let’s figure out what percentage of the total world population (7.16 billion) each country accounts for.
We’re going to divide the population column by the total (7,162,119,434) and multiply by 100 to calculate percentages. So, modify our formula to read:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)
I’ve divided the values in column D by the total population (inside the parentheses), then multiplied by 100 to get a percentage.
The output table this time is:
Note – I’ve applied formatting to the output column in Google Sheets to only show 2 decimal places.
LABEL Keyword
That heading for the arithmetic column is pretty ugly right? Well, we can rename it using the LABEL keyword, which comes at the end of the QUERY statement. Try this out:
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage'",1)
=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage' ",1)
Aggregation Functions
We can use other functions in our calculations, for example, min, max, and average.
To calculate the min, max and average populations in your country dataset, use aggregate functions in your query as follows:
=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)
The output returns three values – the max, min and average populations of the dataset, as follows:
GROUP BY Keyword
Ok, take a deep breath. This is the most challenging concept to understand. However, if you’ve ever used pivot tables in Google Sheets (or Excel) then you should be fine with this.
The GROUP BY keyword is used with aggregate functions to summarize data into groups as a pivot table does.
Let’s summarize by continent and count out how many countries per continent. Change your query formula to include a GROUP BY keyword and use the COUNT aggregate function to count how many countries, as follows:
=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)
Note, every column in the SELECT statement (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY keyword (e.g. column C in this case).
The output for this query is:
Let’s see a more complex example, incorporating many different types of keyword. Modify the formula to read:
=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)
This may be easier to read broken out onto multiple lines:
=QUERY(countries,
"SELECT C, count(B), min(D), max(D), avg(D)
GROUP BY C
ORDER BY avg(D) DESC
LIMIT 3",1)
This summarizes our data for each continent, sorts by highest to the lowest average population, and finally limits the results to just the top 3.
The output of this query is:
Advanced Google Sheets QUERY Function Techniques
How to add a total row to your Query formulas
How to use dates as filters in your Query formulas
There are 4 more keywords that haven’t been covered in this article: PIVOT, OFFSET, FORMAT and OPTIONS.
In addition, there are more data manipulation functions available than we’ve discussed above. For example, there are a range of scalar functions for working with dates.
Suppose you have a column of dates in column A of your dataset, and you want to summarize your data by year. You can roll it up by using the YEAR scalar function:
=QUERY(data,"select YEAR(A), COUNT(A) group by YEAR(A)",1)
For more advanced techniques with the QUERY function, have a watch of this lesson from the Advanced 30 course:
This video is lesson 15 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge.
Other Resources
The QUERY Function is covered in days 14 and 15 of my free Advanced Formulas course for Google Sheets: Learn 30 Advanced Formulas in 30 Days
Official Google documentation for the QUERY() function.
Official documentation for Google’s Visualization API Query Language.