QUERY. ФУНКЦИЯ ДЛЯ СОЗДАНИЯ SQL-ЗАПРОСОВ
Функция 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 — определяет формат определенных столбцов по заданному шаблону.
В интернете много справочников и статей по языку SQL.
Справка от Google по языку запросов API находится по ссылке:
.
Далее мы рассмотрим несколько примеров применения 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).
СТРОИМ СВОДНУЮ ТАБЛИЦУ СО СРЕДНИМИ/МАКСИМАЛЬНЫМИ ЗНАЧЕНИЯМИ ПО ТЕМАТИКАМ
В этом примере мы построим небольшую сводную таблицу, где будут отображены средние значения по тематикам за два года:
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)
КЕЙС «ЧИСЛЕННОСТЬ НАСЕЛЕНИЯ»
Для примера возьмем небольшую табличку с численностью населения с 1917 года по наше время.
На этой табличке будем учиться пользоваться QUERY и выводить нужные нам записи.
Первая часть формулы — это выбор массива данных, с которым мы будем работать:
=QUERY(‘Таблица’!A1:F47)
Для самых продвинутых есть вариант с автоматическим расчетом количества строк, которые занимает таблица; этот вариант стоит использовать, если в таблицу заносятся новые данные:
=QUERY(ДВССЫЛ("’Таблица’!A1:F"&5+СЧЁТЗ(‘Таблица’!A6:A))
С помощью формулы СЧЁТЗ мы получаем количество заполненных строк в диапазоне А6:А. Прибавляем к ним 5 — высоту нашей шапки. Получившееся число добавляем к диапазону «Таблица»!a1:f. Дальше при помощи ДВССЫЛ этот текст становится диапазоном ячеек, который можно использовать в формулах.
Если ввести формулу на новый лист в ячейку A1, то она просто отразит всю выбранную табличку.
Переходим ко второй части формулы — это запрос. Тем, кто слышал про SQL, будет совсем легко, а остальным — несложно. Запрос пишется в кавычках, вот его структура:
- [обязательная часть] Select и имена столбцов исходной таблицы, которые мы хотим видеть в результате (столбцы здесь и далее обязательно писать с большой буквы, например: Select A,B,D).
- [необязательная часть] Where, тут задаются условия выборки, которые можно комбинировать с помощью or, and, несколько условий в скобках. Как в формулах.
- [необязательная часть] Далее могут идти команды оrder by — для сортировки, group by — для группировки, label — для задания собственного названия поля, format — для форматирования данных и т. д.
Третья часть формулы — это количество строк заголовка. В нашем случае их 5, так как данные начинаются с 6-й строки. Также можно ввести -1 или не вводить ничего — количество строк будет определено автоматически.
После долгого вступления перейдем к примерам.
В первом примере выберем только годы и общую численность населения, а ее отсортируем по убыванию:
Во втором примере выведем все строки и столбцы начиная с определенного года, а его сделаем переменной. Год поместим в ячейку G; если пользователь его изменит, изменится и результат вывода. Обратите внимание: если переменной окажется текст, то его в формуле нужно будет выделять апострофами, вот так:
=QUERY(‘Таблица’!A1:F47;"select A,B,C,D,E,F where A>=’"&G1&"’";5)
У нас же год — числовое значение, поэтому без апострофов:
КЕЙС «СЧИТАЕМ СРЕДНИЙ ЧЕК, ВЫБИРАЯ ДАННЫЕ С ОПРЕДЕЛЕННОЙ ДАТЫ»
На скриншоте тот массив, который у нас есть:
Наша задача: отобрать строки с продажами начиная с 1 апреля и посчитать по ним средний чек, используя количество клиентов, то есть получить среднее взвешенное.
Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:
Правильно использовать дату в формуле QUERY так:
1) QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием "yyyy-mm-dd";
2) перед датой и перед апострофом нужно написать date;
3) можно и не давать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:
4) дата с двух сторон обрамляется одиночными кавычками (‘).
Вернемся к тому, что у нас получилось. Наша формула выдала вот такой массив данных:
Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (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};...)
