Блог про интернет-маркетинг для бизнеса

Аналитика

Всемогущая функция Query — подробное руководство

467
0
15
130

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач. Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.

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

Синтаксис QUERY

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

Где:

  • данные — это диапазон ячеек, который будет служить базой данных для SQL запроса;
  • запрос — текст SQL запроса;
  • заголовки — необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.

Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу. Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию». Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию» В доксе, копию которого вы только что создали, существует несколько листов. Лист DB является базой данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL запроса в функции Query

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

  • select — перечисление полей, которые будут возвращены запросом;
  • where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
  • group by — содержит перечень полей, по которым вы хотите группировать результат;
  • pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
  • order by — отвечает за сортировку результатов;
  • limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
  • offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;
  • label — данная кляуза отвечает за название полей, возвращаемых запросом;
  • format — отвечает за формат выводимых данных;
  • options — дает возможность задавать дополнительные параметры вывода данных.

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

=query(DB!A1:L1143;"select * limit 100")

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100» содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100» мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order. Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select.

Обращение к полям базы данных осуществляется через названия столбцов рабочего листа, на котором располагается база данных.

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

  • поле Date — столбец A;
  • поле Campaign — столбец B;
  • поле Sessions — столбец G.

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

Select A, B, G

Далее в запросе идет кляуза Where. При написании запроса кляузы обязательно должны располагаться в таком порядке, в котором были описаны в первом разделе этой статьи. После объявления Where нам необходимо перечислить условия фильтрации. В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date. Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

WHERE
(A >= date'2015-10-22'
AND A <= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')

Мы разбили с помощью скобок фильтрацию данных на две логических части: первая фильтрует по датам, вторая — по названию кампании. На данном этапе формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных, выглядит так:

=query(DB!A1:L1143;"
Select A, B, G
WHERE
(A >= date'2015-10-22'
AND A <= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')")

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат: Формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных

Помимо обычных логических операторов (=, <, >) блок WHERE поддерживает дополнительные операторы фильтрации:

  • contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
  • starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
  • ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
  • matches — соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
  • like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by. По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Order by G desc

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

=query(DB!A1:L1143;"
SELECT A, B, G
WHERE (A &gt;= date'2015-10-22'
AND A &lt;= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')
ORDER BY G DESC")

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Переходим на лист Level_3 и усложняем задачу. В запросах вы можете не только делать выборки, но также проводить различные вычисления и агрегации данных. Для этого в SQL функции Query существует ряд агрегирующих функций и кляуза Group by. Агрегирующие функции:

Функция Описание Поддерживаемый тип данных Возвращаемый тип данных
avg() Возвращает среднее значение для группы Числовой Числовой
count() Возвращает количество значений в группе Любой Числовой
max() Возвращает максимальное значение для группы Любой Аналогичный полю, к которому применяется
min() Возвращает минимальное значение для группы Любой Аналогичный полю, к которому применяется
sum() Возвращает сумму значений в группе Числовой Числовой

Итак, давайте посчитаем данные по каждой кампании:

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

Для решения этой задачи нам понадобятся данные только из двух полей: Campaign (находится в столбце B) и Sessions (находится в столбце G). Все агрегирующие функции прописываются вместе со списком полей для вывода данных в кляузе Select. В случае применения агрегирующих функций все поля, к которым не применяется данный тип функций, являются группирующими полями. Их необходимо перечислить в кляузе Group by. Агрегирующие функции работают обязательно в паре с Group by. Описание кляузы Select будет следующим:

SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)

Далее необходимо сгруппировать данные: в нашем случае требуется группировка только по одному полю Campaign, но вы можете осуществлять группировку по любому количеству столбцов. Описание кляузы Group by очень простое:

GROUP BY B

В кляузе достаточно указать только столбец B, содержащий информацию о названии кампании. Потому что он единственный, к которому мы не применили никакой агрегирующей функции. Наша формула:

=query(DB!A1:L1143;"
SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B")

Получим следующий результат: Отображение кляузы GROUP BY B В принципе, мы получили желаемый результат, но названия столбцов можно подкорректировать с помощью кляузы Label. Результат будет лучше отображаться, если мы отсортируем отчет по названию кампании. Описание кляузы Order by мы уже рассмотрели выше.

Для нужной сортировки достаточно добавить следующую строку после описания:

Order by B

Чтобы переименовать столбцы в таблице, которую возвращает запрос, необходимо добавить описание кляузы Label. Синтаксис достаточно прост: сначала указываете выводимый столбец или функцию, которая будет возвращать значения в результирующую таблицу, и далее в одинарных кавычках указываете нужное название.

Это будет выглядеть так:

B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'

Преобразованная формула:

=query(DB!A1:L1143;"
SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B

ORDER BY B

LABEL
B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'")

А результат, возвращаемый формулой, выглядит так: Результат формулы "Order by B" Все поля названы соответствующим описанию кляузы Label образом. Последнее, что режет глаз в возвращаемой таблице, — формат, в котором выводятся данные в столбце «Среднее». Для корректировки форматов, выводимых запросом данных, требуется описать кляузу Format. Ее описание схоже с описанием Label, но вместо названия поля следует прописать маску вывода данных (также в одинарных кавычках).

Округлим числа в столбце «Среднее» до двух знаков после запятой. Для округления выводимых данных до двух знаков после запятой маска должна выглядеть как ‘0.00’.

Описание кляузы Format

FORMAT avg(G) ‘0.00’

Соответственно, окончательная формула на листе Level_3 выглядит так:

=query(DB!A1:L1143;
"SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B

ORDER BY B

LABEL
B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'

FORMAT
avg(G) '0.00'")

В результате: Результат кляузы Format

Создание перекрестных таблиц (Pivot, скалярные функции)

Чтобы за считанные секунды с помощью функции QUERY создать перекрестную таблицу, следует добавить в запрос описание кляузы Pivot. Построим отчет, в котором в строках будет номер дня недели, в столбцах ˜— тип устройства, а в качестве выводимых значений рассчитаем показатель отказов. Если вы внимательно изучили структуру базы данных, находящейся на листе DB, то наверняка заметили, что у нас нет поля, содержащего информацию о дне недели, как и поля, содержащего информацию о показателе отказов.

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

Скалярные функции

На момент написания статьи SQL в Google Таблицах поддерживает 14 скалярных функций.

Функция Описание
year() Возвращает номер года из «даты» или «даты и времени». Пример: year(date ‘2009-02-05’) вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
month() Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, феврваль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date ‘2009-02-05’) вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату прибавьте 1, month(date "2009-02-05")+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
day() Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date ‘2009-02-05’) вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
hour() Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday ‘12:03:17') вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
minute() Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday ‘12:03:17') вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
second() Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday ‘12:03:17') вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
millisecond() Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday ‘12:03:17.123') вернет 123. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
quarter() Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответствено, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date ‘2009-02-05’) вернет 1. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
dayOfWeek() Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date ‘2015-11-10’) вернет 3, так как 10 ноября 2015 года является вторником. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных:число.
now() Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров. Тип возвращаемых данных: дата и время.
dateDiff() Возвращает разницу в днях между двумя датами. Пример: dateDiff(date ‘2008-03-13’ , date ‘2008-02-12’) вернет 29, так как 10 ноября 2015 года является вторником. Запрашиваемые параметры: два параметра с типом «дата» или «дата и время». Тип возвращаемых данных: число.
toDate Возвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример:
  • toDate(date ‘2008-03-13’) вернет аналогичное значение в формате даты, ‘2008-03-13’.
  • toDate(dateTime‘2013-03-13 11:19:22’) вернет дату ‘2013-03-13’.
  • toDate(1234567890000) вернет дату ‘2009-02-13’.
Запрашиваемые параметры: один параметр с типом дата, дата и время или число. Тип возвращаемых данных: дата.
upper() Преобразует все значения в строке в верхний регистр. Пример: upper( ‘foo') вернет строку ‘FOO’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.
lower() Преобразует все значения в строке в нижний регистр. Пример: upper( ‘Bar') вернет строку ‘bar’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.

Арифметические операторы

Оператор Описание
+ Сложение нескольких числовых значений
- Разница между числовыми значениями
/ Деление числовых значений
* Умножение числовых значений

Для решения нашей задачи потребуется использовать скалярную функцию dayOfWeek для вычисления дня недели, а также арифметический оператор «/» для подсчета показателя отказов.

Давайте определим поля, которые будем использовать в запросе:

  1. Для вычисления дня недели нам потребуется данные поля Date в столбце A.
  2. Данные о типах устройств хранятся в поле Device category в столбце E.
  3. Для расчета показателя отказов потребуются данные полей Bounces и Sessions — в столбцах H и G.

Опишем кляузу Select для нашего запроса. Поскольку в строках у нас будут данные по дням недели, нам достаточно прописать скалярную функцию, которая будет вычислять день недели, а также формулу вычисления показателя отказов.

SELECT dayOfWeek(A), sum(H)/sum(G)

Именно так будет выглядеть описание нужных нам полей. Теперь с помощью кляузы Group by сгруппируем строки по дням недели. Для этого допишем в запрос следующую строку:

GROUP BY dayOfWeek(A)

Уже видно, как меняется показатель отказов в зависимости от дня недели: Видно, как меняется показатель отказов в зависимости от дня недели Чтобы построить перекрестную таблицу, достаточно прописать кляузу Pivot с указанием столбца, значения из которого будут добавлены в виде столбцов. В нашем случае это столбец E, потому что именно он содержит информацию о типе устройств. Дописываем строку:

PIVOT E

Теперь запрос возвращает результат: Запрос возвращает результат Нам остается только добавить последние штрихи: изменить названия столбцов и формат чисел с помощью пунктов LABEL и FORMAT.

Окончательная формула на листе Level_4:

=query(DB!A1:L1143;"
SELECT dayOfWeek(A), sum(H)/sum(G)
GROUP BY dayOfWeek(A)
PIVOT E
LABEL
dayOfWeek(A) 'День недели',
sum(H)/sum(G)''
FORMAT sum(H)/sum(G) '0.00%'")

Получаем отчет: Окончательная формула на листе “Level_4” Строкой LABEL sum(H)/sum(G) ‘’ мы убрали из подписей столбца надпись, содержащую формулу расчета. Строкой FORMAT sum(H)/sum(G) ‘0.00%’ мы передали процентный формат показателю отказов в отчете. В целом, описанного в примерах выше синтаксиса вполне достаточно, чтобы начать активно использовать функцию QUERY, но в завершении статьи хочу показать еще несколько интересных приемов, которые можно взять на вооружение.

Импорт данных с помощью QUERY из другой Google Таблицы

С помощью QUERY вы можете использовать в качестве базы данных другую Google Таблицу. Это можно сделать с помощью сочетания функций ImportRange и QUERY. Я создал новую Google Таблицу, в которую продублировал данные с листа DB из приведенного в начале статьи документа. Чтобы в качестве базы данных использовать данные из другой Google таблицы, в качестве первого аргумента функции Query выступит импортируемый функцией ImportRange диапазон.

Разница в том, что при написании запроса к данным, импортируемым функцией ImportRange, вместо названия столбцов мы указываем их порядковый номер в возвращаемом функцией ImportRange диапазоне. На листе DataImport перепишем запрос, представленный в Level_4 таким образом, чтобы он обращался к данным, находящимся в новой таблице на листе DB_Transfer. Синтаксис функции ImportRange достаточно прост:

IMPORTRANGE(ключ, диапазон)

Где ключ — часть URL Google Таблицы:

ключ — часть URL Google Таблицы

А диапазон — это ссылка на лист и (простите за каламбур) диапазон. В нашем случае диапазоном будет DB_Transfer!A1:L1143. Формула ImportRange:

importrange(“1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo”;”DB_Transfer!A1:L1143”)

Именно ее мы должны указать в качестве данных функции Query. Далее остается переписать запрос так, чтобы ссылаться на столбцы базы данных не по названию, а по порядковому номеру столбца. Определим, к каким столбцам мы обращались с помощью запроса на листе Level_4.

Название Содержание Наименование в таблице Порядковый номер
Date Дата A 1
Device type Тип устройства E 5
Sessions Количество сеансов G 7
Bounces Количество отказов H 8

Определим, к каким столбцам мы обращались с помощью запроса Текст запроса после замены названий столбцов на их порядковый номер:

SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7)
GROUP BY dayOfWeek(Col1)
PIVOT Col5
LABEL
dayOfWeek(Col1) 'День недели',
sum(Col8)/sum(Col7) ''
FORMAT sum(Col8)/sum(Col7) '0.00%'

Как видите, текст запроса практически не изменился, но вместо столбца A мы теперь указываем Col1, вместо столбца E — Col5, вместо G — Col7 и вместо H, соответственно, Col8. Получаем формулу:

=query(IMPORTRANGE("1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo";"DB_Transfer!A1:L1143");"
SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7)
GROUP BY dayOfWeek(Col1)
PIVOT Col5
LABEL
dayOfWeek(Col1) 'День недели',
sum(Col8)/sum(Col7) ''
FORMAT sum(Col8)/sum(Col7) '0.00%'")

В качестве первого аргумента функции Query выступает функция ImportRange с ссылкой на ключ нужной Google таблицы, которую вы можете скопировать из URL Google Таблицы, и ссылки на диапазон, включающий название листа, а также первой и последней ячейки нужного диапазона.

Номера столбцов в запросе идут не со столбца A, а с того, который является первым в указанном вами диапазоне в функции ImportRange. Например, если бы в качестве импортируемого диапазона выступал DB_Transfer!C1:L1143, то данные из столбца C запрашивались ссылкой Col1, поскольку в импортируемом массиве этот столбец является первым.

Окончательную формулу в работе вы можете посмотреть на листе DataImport.

Строим запрос на основе объединения данных из нескольких таблиц с одинаковой структурой

Ещё одна довольно мощная возможность функции QUERY — построение запрос на основе нескольких массивов данных.

Единственным условием для объединения данных является одинаковая структура входящих таблиц.

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

Массив — это виртуальная таблица, которая содержит строки и столбцы.

Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:

  • обратная косая черта «\» — разделяет столбцы. Например, {1 \ A}. Число 1 будет находится в правой левой ячейке массива, буква «A» в ячейке справа. Так мы описали диапазон, содержащий два столбца и одну строку.
  • точка с запятой «;» используется для перехода на следующую строку. Возьмем {1;A}. Этот массив будет состоять из одного столбца и двух строк, в первой строке будет содержаться значение 1, во второй строке буква «A».

Таким образом вы можете два и более диапазона описать в одном массиве, например:

=query({Table1!A1:B5; Table2!A1:B5; Table3!A1:B5};"SELECT * WHERE Col2 > 4")

В данном случае мы обращаемся с запросом к трём диапазонам данных, находящимся на разных листах, объединив их с помощью «;» в массив так, что вторая таблица является продолжением первой, а третья таблица — продолжением второй.

Посмотрите этот пример по ссылке.

Запрос с динамическими параметрами

Синтаксис запросов в функции QUERY сложен для неподготовленного пользователя. Поэтому вы можете добавить на рабочий лист различные интерактивные элементы в виде выпадающего списка, созданного с помощью функции «Проверка данных». Запрос с динамическими параметрами А в тексте запроса — делать ссылки на ячейки, содержащие нужные данные. Например, мы можем динамически задать диапазон дат, который хотим вывести в динамическую таблицу, либо сделать возможность динамически добавлять и убирать различные поля результирующей таблицы. Посмотреть, как это выглядит наглядно, вы можете на листе DinamicQuery.

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

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

Укажите интересующий вас интервал дат в пределах от 24.09.2015 по 25.10.2015, поскольку данные, сгенерированные для тестовой базы и хранящиеся на листе DB, содержат только этот диапазон. Укажите интересующий вас интервал дат Далее в конструкторе отчетов вы можете изменить название полей и оно будет отображаться в финальной таблице. Также можете указать, какие поля требуется вывести в отчет. Еще раз напомню, что необходимо указать как минимум одну меру и одно измерение. В конструкторе отчетов вы можете изменить название полей Во время изменения каких-либо параметров отчет под конструктором будет изменяться динамически. Под конструктором динамически будет изменяться отчет в соответствии с указанными параметрами Формула, которая изменяет запрос в зависимости от настроенных параметров, выглядит так:

=query(DB!A1:L1143;"
Select "&amp;join(",";filter(C7:C11;B7:B11="Да"))&amp;"
WHERE
(A &gt;= date'"&amp;C2&amp;"-"&amp;D2&amp;"-"&amp;E2&amp;"'
AND A &lt;= date'"&amp;C3&amp;"-"&amp;D3&amp;"-"&amp;E3&amp;"')
GROUP BY "&amp;join(",";filter(C7:C11;B7:B11="Да";D7:D11="Измерение"))&amp;"
LABEL "&amp;join(",";filter(E7:E11;B7:B11="Да")))

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

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

Готов отвечать на вопросы в комментариях :)

Комментарии (129)

  1. 0
    7 дней назад

    Алексей, добрый день!

    Мой вопрос - продолжение вопроса Зелимхана Махкетинского - как добавить к выводу столбец с повторяющимися данными (название листа) в том случае, если на входе задаются несколько таблиц из разных файлов:
    =QUERY({

    IMPORTRANGE(D2;"Видимость!A:H");
    IMPORTRANGE(D3;"Видимость!A:H")};
    "Select Col1,
    '"
    &{ IMPORTRANGE(D2;"Видимость!H2");
    IMPORTRANGE(D3;"Видимость!H2")}&"'

    where Col1 is not null";1) В итоге в первом столбце выводятся данные из двух файлов D2 и D3 (в ячейках D2 и D3 находятся хэши документов), а вот во втором столбце - только повторяющиеся значения из ячейки H2 из файла D2

    • 0
      Natalya Kutsenko
      6 дней назад

      Добрый день!

      В вашем случае лучше на каждый лист добавить столбец с его названием, сейчас вы в блок SELECT передаёте массив {IMPORTRANGE(D2;"Видимость!H2");IMPORTRANGE(D3;"Видимость!H2")} и функция отрабатывает неверно.

  2. 0
    19 дней назад

    Алексей здравствуйте.

    Спасибо за знания. 

    Можно ли сделать по категории Costs вывод 1/4 суммы, по остальным категориям всю сумму?

    http://prntscr.com/gi3p36


    • 0
      Michael Karapeichik
      19 дней назад

      Добрый день, пока что SQL в Query не поддерживает в блоке SELECT условный оператор IF как в полноценных БД, поэтому это можно сделать добавив дополнительный столбец в источник т.е. 


      1. дописать в столбец L формулу типа:= if(E3 = "Costs";J3/4;J3)

      2. в первом аргументе функции QUERY расширить массив до $D$3:$L$1000

      3. в запросе вместо sum(J) написать sum(L): SELECT E, sum(H), sum(L), sum(K) where ....

  3. 0
    21 день назад

    И второй вопрос, ответ на который не нашел тоже в интернете.


    Можно ли в запрос добавить отдельную колонку на выходе.

    Например, "select A, B" выведет мне значения этих колонок в два столбца, мне нужно, чтобы напротив них появилась третья колонка, в диапазоне которой повторяется, например, значение ячейки 'Лист1'!D1.

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


    Или все таки нужно в исходном листе заполнить колонку C с повторяющимся значением во всем диапазоне и запросить эту колонку в query тоже?

    • 0
      Зелимхан Махкетинский
      20 дней назад

      Можно, просто конкатенируйте текст запроса со значением ячейки:


      "select A, B, '"&Лист1!D1&"'"


      В таком случае результат запроса вернёт значение столбца A, B и значение из ячейки Лист1!D1.

  4. 0
    21 день назад

    Здравствуйте, Алексей.

    По-моему это единственная развернутая статья по query в гугл докс.

    Помогите разобраться.

    Хочу сделать вывод данных с множеств разных книг. Использую query и importrange.

    Выглядит это примерно так:


    то есть в {...} заключил множество importrange. Все работает.

    Но проблема в том, что в колонке N добавляются и удаляются строки, соответственно, приходится вручную убирать или дописывать importrange в данном запросе.



    В колонке O у меня с помощью JOIN создаются импортрейнджи по каждому листу. Думал может можно будет в какой-нибудь ячейке P2 сделать

    =JOIN(";";O2:O58)

    Таким образом выводится всё это количество importrange в одной ячейке. И соответственно, в самом query думал сослаться на содержание этой ячейки.

    Оказывается так нельзя. По крайней мере у меня не работает так - =query({P2};"select Col3 where Col1 is not null").

    • 0
      Зелимхан Махкетинский
      20 дней назад

      Добрый день!

      Совершенно верно, такой вариант работать не будет потому что он преобразовывает массив ваших importrange из формулы в обычный текст, и не понимает, что с ним надо делать.

      Возможно вам стоит в исходном доксе поиграться с функцией indirect (она преобразует текстовую строку в диапазон), и попробовать собирать весь массив в кучу там?

      Пока ничего другого не придумал к сожалению.

      • 0
        Алексей Селезнёв
        19 дней назад

        Здравствуйте. Решил разбить один файл с множеством листов на несколько по определенной критерии, и в каждом сделал отдельный лист, где через query собираются данные. Дальше создал другой файл, некий свод, куда собираются все сводные данные с других файлов через =query(importrange.......

        Пока работает.

        Но не знаю к чему это приведет, когда данных будет еще больше. Ведь у меня целая пирамида запросов получается)

      • 0
        Алексей Селезнёв
        20 дней назад

        Спасибо за ответ.

        Не могли бы Вы по подробнее объяснить насчет indirect? Не совсем понял.


        У меня много разных таблиц с одинаковой структурой. Эти таблицы добавляются и удаляются со временем. И мне нужно сделать некую сводную таблицу, куда собираются данные из всех этих таблиц. Соответственно, хотелось бы это автоматизировать, чтобы не вводить новый запрос каждый раз вручную, это приведет к ошибкам и могу какую-то таблицу упустить.

  5. 0
    месяц назад

    добрый день, подскажите. в исходной таблице представлены все месяцы 2017 года, как мне сделать выборку по 2-3 месяцам, такое впечатление что query только один раз обращается к указанному столбцу, то есть выбрать за один месяц (в примере это май) получается, а за 2-3 месяца (например май, июнь, июль)- нет. Спасибо


    • 0
      Vitaliian An
      26 дней назад

      Добрый день, вы можете перечислить нужные месяца в блоке WHERE через логический оператор OR.

      WHERE E = "май" OR E = "апрель" OR E = "март"


  6. 0
    месяц назад

    Алексей, спасибо большое за статью!

    Очень полезная  и емкая. Просто супер!

    Наткнулся на небольшую ошибку в тексте:

    • обратная косая черта «/» — разделяет столбцы. Например, {1 / A}. Число 1 будет находится в правой левой ячейке массива, буква «A» в ячейке справа. Так мы описали диапазон, содержащий два столбца и одну строку.
    Обратная косая черта - \ (проверял в Google Docs, работает именно в таком варианте)
  7. 0
    2 месяца назад
    Добрый день! Подскажите, а возможно вызывать не значения ячеек, а формулы из них же, чтобы их в выборке можно было корректировать? 
  8. 0
    4 месяца назад

    Спасибо большое за статью Алексей! У меня такой вопрос, можно ли упорядочить по вычисляемому(агрегирующему) столбцу. Если взять Ваш пример на странице Level_3, можно ли упорядочить по столбцу Общая сумма?

    • 0
      Evgeny Babenko
      4 месяца назад

      Здравствуйте, можно , для этого так и прописывайте в блоке Order By вместо названия поля всю функцию, в примере для Level3 вам надо поменять ORDER BY B на ORDER BY sum(G), и запрос вернёт таблицу отсортированную по столбцу общая сумма.

      Ниже пример:


      =query(DB!A1:L1143;"SELECT
      B,
      avg(G),
      max(G),
      min(G),
      count(G),
      sum(G)
      GROUP BY B

      ORDER BY sum(G)

      LABEL
      B 'Кампания',
      avg(G) 'Среднее',
      max(G) 'Максимальное',
      min(G) 'Минимальное',
      count(G) 'Количество',
      sum(G) 'Общая сумма'

      FORMAT
      avg(G) '0.00'"
      )

  9. 0
    4 месяца назад

    Добрый день, я хотел уточнить, как именно вывести информацию из Google Docs? Из Exсel на сайт, функцию QUERY  прописывать непосредственно в файле html?

    • 0
      Алексей Ефанов
      4 месяца назад

      Нет, HTML не поймёт функцию QUERY, что бы вставить часть таблицы, диаграмму , лист из Google Spreadsheets на сайт надо сделать следующее:


      1. Меню "Файл" - команда "Опубликовать в Интернете"


      2. В открывшемся диалоговом окне "Публикация в интернете" переходим на вкладку встроить, и выбираем лист или диаграмму которую вы хотите встроить в html.


      3. После нажатия кнопки "Опубликовать" в этом же диалоговом окне будет сгенерирован код iframe который вам и надо будет вставить в html документ.


  10. 0
    5 месяцев назад

    Добрый день! 

    Подскажите пожалуйста, как можно объединить содержимое ячеек в подобном выражении:

    =QUERY(IMPORTRANGE("ключ_таблицы";"Базовый список!A:L"); "select Col2, Col3, Col4, Col5, Col8, Col12 where Col9=82 AND Col5<=1999" )

    Где Col2, Col3, Col4 - Фамилия, Имя и Отчество соответственно. Необходимо содержимое этих столбцов в каждой строке объединять в один столбик с пробелом в качестве разделителя. Нужно что то на подобии склейки =(А1&" "&A2)...

    Большое спасибо за статью, и за ранее спасибо за ответ!
    • 0
      Yuriy Melihov
      5 месяцев назад
      Пока вышел из положения создав дополнительный столбик в базовой таблице и сделав склейку там. И вызываю этот столбик вместо изначальных ...
      • 0
        Yuriy Melihov
        5 месяцев назад

        Как оказалось такой способ не работает. Таблица во вкладке  "Базовый список" формируется при заполнении формы на сайте. Когда добавляется новая запись, добавляется соответственно новая строка, и по этому ячейка в столбце со склейкой остается пустой.

        Да и в самом вызове созданного столбца появляется ошибка "QUERY": NO_COLUMN: Col13"...

        • 0
          Yuriy Melihov
          5 месяцев назад

          1. Ошибка "QUERY": NO_COLUMN: Col13" в вашем случае возникает потому что вы в первом аргументе функции не расширили массив к которому обращаетесь запросом.


          2. К сожалению Google Query Language на данный момент не поддерживает конкатенацию полей в запросе, в связи с чем вы можете реализовать это либо через дополнительный столбец, но в вашем случае это невозможно изза того что данные в докс попадют с формы, следовательно остаётся только переделать форму так что бы эти данные были в одном столбце.
          • 0
            Алексей Селезнёв
            5 месяцев назад

            1. Да, не обратил внимания изначально. Спасибо!

            2. Жаль, что пока такой возможности нету. Отдельные поля для ФИО задумывались для раздельного дальнейшего использования. В каких то списках достаточно фамилии... 
            Да и как показала практика, если поле только одно то, что то записать обязательно забудут. Как правило это отчество.. 

            Вам спасибо, из вашей статьи узнал много нового! 

            • 0
              Yuriy Melihov
              5 месяцев назад
              Согласен что с одним полем которое заполняется руками будут проблемы, возможно попробуйте сделать формат ввода, обязательно 3 слова к примеру, не помню но вроде такое можно делать в гугл формах.
  11. 0
    5 месяцев назад

    Добрый день, помогите советом, пожалуйста.

    В ячейка A содержится дата. Группировка по дате происходит, все работает. А когда хочу произвести группировку по месяцу ( GROUP BY month(A)+1 ), то выдает ошибку, даже при том условии, если из select убираю ячейку A.

    Заранее спасибо.

    Сам код:

    =QUERY('DB2'!A1:J1000; "select
    A,
    SUM(E),
    SUM(F),
    SUM(J),
    month(A)+1


    WHERE


    (A >= date'"
    &I9&"-"&J9&"-"&K7&"'
    AND A <= date'"
    &I10&"-"&J10&"-"&K8&"')

    AND

    D CONTAINS '"
    &H4&"'
    AND D CONTAINS '"
    &H3&"'
    AND B CONTAINS '"
    &H2&"'
    AND D CONTAINS '"
    &H5&"'
    AND month(A)+1 CONTAINS '"
    &H6&"'


    GROUP BY A

    ORDER BY A asc

    LABEL


    A 'ДАТА',
    SUM(E) 'ПОКАЗЫ',
    SUM(F) 'КЛИКИ',
    SUM(J) 'ТРАТЫ',
    month(A)+1 'НОМЕР МЕСЯЦА'


    "

    )


  12. 0
    5 месяцев назад

    Алексей, приветствую.

    Спасибо за статью)

    Буду рад если Вы поможете разобраться с причинами появления ошибок в формулах, что я делаю не так?

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

      Полученное решение не удовлетворяет текущий запрос. Реализуема ли эта задача через query?

    https://docs.google.com/spreadsheets/d/1bbbJ_HL1DNgzpwkTo_CDOWqNpj41TsKmxT7cQsKMh3Q/edit?usp=sharing

    • 0
      Dmitriy Napolnov
      5 месяцев назад

      Готово, исправил ваш докс, у вас было две ошибки:


      1. В Group By указывали не все группирующие столбцы, если вы применяете агрегирующие функции то все столбцы к которым эти функции не применяются должны быть перечислены в Group BY.


      2. В запросе названия столбцов всегда должны быть указаны заглавной буквой.


  13. 0
    6 месяцев назад

    Коллеги, 

    LIKE работает, а NOT LIKE -- нет. Кто нибудь сталкивался с проблемой? Как можно решить?

  14. 1
    8 месяцев назад
    Добрый день автору статьи! У меня вопрос по поводу импорта данных с нескольких таблиц одновременно. Скажите возможно ли такое?  Если да - поделитесь примером. Спасибо.
  15. 1
    9 месяцев назад
    Ох эта БД)) где только не столкнешься с ней)
  16. 1
    9 месяцев назад

    Алексей, прекрасная статья и очень информативный блог! Нужен Ваш совет. Возможно, вопрос построен неграмотно, но я еще новичок в работе с большими данными.

    Есть таблица excel в 5 столбов и 200 тысяч строк. Для удобства пользования стоит задача загрузить её в sql. Никогда раньше не имел опыт работы c сервером. Я так понимаю, графические клиенты упрощают импорт таких данных. Какой Вы посоветуете? Сохранять таблицу перед импортом нужно в csv? Можно ли будет потом с ней работать: корректировать столбцы, строить аналитику в sql? Можно в одну таблицу в sql подгружать другие из csv (с одинаковым форматом, соответственно)?
    Просто пытаюсь для себя понять, подходит ли нам такой способ работы с нашими данными. Ведь оформив наши таблицы, будет легче работать с ними по мере их увеличения и отправлять другим пользователям..

  17. 0
    9 месяцев назад

    Алексей, спасибо за статью! Помогите, пожалуйста, в одном вопросе. Я заметил, что данные из одной таблицы подтягиваются в другую, если в настройках первой стоит открытый доступ по ссылке. Если доступ по ссылке в первой таблице закрыть, то формула возвращает ошибку "Не удалось интерпретировать query string...". Как можно этого избежать, не открывая доступа к данным по ссылке?

  18. 0
    10 месяцев назад

    не могли бы подсказать

    вот есть contains в where, т.е. задаем значение содержит, а есть вариант наоборот не содержит?

  19. 0
    10 месяцев назад

    И снова, здравствуйте! И снова вопрос: 

    Как можно выгрузить гугл таблицу с текущими значениями? 

    Опишу задачку вкратце: Есть гугл форма для ежемесячного сбора данных от пользователей , сбор данных идет на лист гугл таблички. На основе этой таблички идет формирование конечных форм документов с претерпевшими изменения данными (формулы и query запросы к вышеописанному листу). Необходимо выгрузить весь файл целиком, для конечной правки (текст и комментарии и т.д), но после выгрузки, все формулы сбиваются, т.к. oOO Calc или MS Excel не знают и  не понимают query. 

    Как выгрузить этот файл с рассчитанными данными (результатами, без формул)?

    Заранее спасибо.

  20. 0
    10 месяцев назад

    Алексей, добрый день!

    Спасибо за подробную инструкцию по query!

    Я пытаюсь создать в ней запрос с функцией, аналогичной sumproduct (хочу посчитать средневзвешенное значение). Делаю это так, как на скрине, но выдается ошибка. Есть ли что-то подобное. Причем в sql такой запрос работает, а в GS -  нет. можете подсказать?

    http://prntscr.com/daprur
    http://prntscr.com/daprsk


    • 0
      Эльза
      10 месяцев назад

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


      Докс необходимо расшарить на alsey.netpeak@gmail.com

  21. 0
    10 месяцев назад

    Добрый день! Не получается составить запрос с подстановкой данных из другой ячейки.

    Не работает: =QUERY(!C2:H141; "SELECT C,E,F,G WHERE E=xxx")

    запрос работает, если xxx - константа, т.е. определенное введенное значение. А хотелось бы, чтобы это была ссылка на динамически изменяемую ячейку не из заданного диапазона. (Вообще хотелось бы, чтобы запрос работал на новом листе, где например в ячейке A1 задавался бы параметр xxx, и запрос обрабатывался в зависимости от заданного значения). Подскажите, пожалуйста, как это сделать. Не хочет работать даже с ячейкой из заданного диапазона. Что я делаю не так? :(

    • 0
      Petr Mechkovskiy
      10 месяцев назад
      Почитал комментарии, разобрался с форматом данных. Все работает, спасибо. Только проблема остается, если подставлять дату.
      • 0
        Petr Mechkovskiy
        10 месяцев назад

        сам же себе и отвечаю :).

        С датами тоже разобрался, только как-то все через... странно в общем :).

        Несмотря на то, что ячейка подстановки типа дата и форма yyyy-mm-dd, все равно выдает ошибку.

        Решение такое: QUERY(!C2:H141; "SELECT C,E,F,G WHERE E=date'"&text(A1;"yyyy-MM-dd")&"')"). Таким образом все работает.

  22. 0
    10 месяцев назад

    Что может быть неверно в данном запросе?

    В чем может быть проблема?

    • 0
      Сергей Сберегаев
      10 месяцев назад

      Доброго дня, извините, что не поздоровался сразу. Всю голову сломал, необходимо получить числовое значение из заполняемого листа Answers! Выдает, то что такого столбца нет. Как так? Заранее спасибо.


  23. 0
    10 месяцев назад

    Как сделать чтобы в выводе результата вообще не было строки с названием столбца, т.е. только данные, никаких заголовков?

    • 0
      Ivan Zdanovich
      10 месяцев назад

      Есть два способа:


      1. Третьим аргументом функции QUERY является, заголовки, заголовки – [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных, если вы установите значение этого аргумента 0, то заголовки не будут подтягиваться, к сожалению этот приём работает не всегда, иногда функция принмает заголовок полей содержащих текстовое значение как одно из значений поля а не как его заголовок.


      2. Воспользуйтесь кляузой LABEL, просто имя заголовков оставьте пустым, пример:

      LABEL
      A '',
      G ''
  24. 0
    10 месяцев назад

    Добрый день. Вопрос к автору или к тем кто понимает. Возможно ли объеденять ячейки по различным условиям? Возможно ли с помощью query сделать как в этом документе? Если да, то буду рад любой помощи, хотя-бы в какую сторону смотреть. Спасибо.


    Ссылка на таблицу: https://docs.google.com/spreadsheets/d/10flhMPaPOqTNxTDkoELtbK9Q7FxmBEDMJoBEUy3bTO4/edit#gid=0

    • 0
      Anton Kolomiets
      10 месяцев назад

      К сожалению на данный момент функция QUERY не поддерживает операцию JOIN, которая в SQL как раз отвечает за объединение таблиц.

      В вашем случае разве что можно создать ещё одну таблицу которая будет с помощью функции VLOOKUP соединять данные из первых двух таблиц, либо опять же с помощью функции VLOOKUP добавить нужные поля в Исходную таблицу 2 и уже на её основе писать запрос с помощью функции QUERY.


      Вот пример с вашими данными, я в вашем доксе добавил лист на котором пример реализации решения вашей задачи.

      https://docs.google.com/spreadsheets/d/10flhMPaPOqTNxTDkoELtbK9Q7FxmBEDMJoBEUy3bTO4/edit#gid=1737372743

      • 0
        Алексей Селезнёв
        10 месяцев назад
        Спасибо за ответ. Действиельно, соеденить данные через ВПР проблемы нет. Проблема соеденить ячейчки, чтобы таблица имела отличный вид. Подскажите, пожалуйста, реализуется ли такое с помощью скриптов? 


  25. 0
    год назад
    Алексей, здравствуйте. Спасибо большое за статью! Не могу до сих пор решить свою задачу с QUERY. Вот моя формула: =ЕСЛИОШИБКА(QUERY(Форма;"select (B) where M > now() and R = '' and S is not null Label (B) ''");"Нет предстоящих аукционов") Формула работает, но не так как хотелось бы. В столбце M указана дата и время по Москве, а now() в query в формате GMT, из-за этого выдача не совсем корректная. Привести now() в формат GMT+3 у меня не получается. Подскажите может быть есть решение?
    • 0
      Kirill Ponomarev
      год назад
      Здравствуйте, в Google Spreadsheets данные о времен  и дате хранится в числовом формате, 1 сутки равны 1, соответсвенно 1 час = 1/24, т.е. для того что бы перевести now() в формат GMT+3 напишите now() + (1/24*3).
      • 0
        Алексей Селезнёв
        год назад

        ОшибкаНе удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:Can't perform the function sum on values that are not numbers

        • 0
          Kirill Ponomarev
          год назад

          А вы могли дать доступ к таблице на alsey.netpeak@gmail.com ? Так мне бы было проще найти ошибку. Скорее всего вы просто внутри кавычек пишите now() + (1/24*3), а его надо конкатенировать т.е. :


          =ЕСЛИОШИБКА(QUERY(Форма;"select (B) where M >"& now() + (1/24*3) &"and R = '' and S is not null Label (B) ''");"Нет предстоящих аукционов")


          • 0
            Алексей Селезнёв
            год назад
            Открыл доступ
            • 0
              Kirill Ponomarev
              год назад

              Первая формула должна быть такой:

              =(QUERY(Форма;"select B,L,datediff(L,date'"&year(now())&"-"&month(now())&"-"&day(now())&"') where L >date'"&year(now())&"-"&month(now())&"-"&day(now())&"' and R = '' and S is null Label B'',L'',datediff(L,date'"&year(now())&"-"&month(now())&"-"&day(now())&"')''"))


              Вторая формула такой:

              =QUERY(Форма;"select B,M,N,dateDiff(M,date'"&year(now())&"-"&month(now())&"-"&day(now())&"') where M > date'"&year(now())&"-"&month(now())&"-"&day(now())&"' and R = '' and S is not null Label B'',M'',N'',dateDiff(M,date'"&year(now())&"-"&month(now())&"-"&day(now())&"')''")


              Доступ к копии вашего файла с исправленными формулами я предоставил.

              Но ни одной строки в вашей форме не попадает под прописанный вами в формуле фильтр.
              • 0
                Алексей Селезнёв
                год назад

                Алексей, спасибо за помощь! Отображает теперь правильно, единственное не показывает остаток времени видимо изза datediff. Можно ли как то сделать чтобы время отображалось?

  26. 0
    год назад
    Алексей, здравствуйте. Спасибо что разжевали query. Супер функция! Скопировал query из Вашего примера Level2, но у меня она не работает. Подскажите пожалуйста в чем проблема? https://docs.google.com/spreadsheets/d/1PnmqhjE-zQMJ7AT9q1t2uCPTUckcQTxpjIYJPuM2u3Q/edit?usp=sharing Спасибо
    • 0
      Michael Karapeichik
      год назад
      Здравствуйте, открыл вашу таблицу, но не нашёл диапазон в котором вы написали формулу с функцией QUERY поэтому на данный момент не могу найти ошибку, введите формулу и напишите в какой диапазон вы её ввели, а я посмотрю и подскажу в чём ошибка.
  27. 0
    год назад

    Алексей, спасибо, что отвечаете на все вопросы и стараетесь всем помочь.
    У меня еще вопрос: пытаюсь вытянуть через query данные из столбца N (числовые), которые равняются числу в ячейке M2: select Col3 where Col3 = '"&M2&"'. Выдает #Н/Д, хотя в Col3 точно есть значение равное M2, и даже не одно. Если меняю = на contains или прописываю вместо М2 число (select Col3 where Col3 = 65862) все работает. И при том проблема такая только с числами, если сравниваю текст тоже все работает. Подскажите, пожалуйста, в чем проблема и как мне ее решить?

  28. 0
    год назад

    Добрый день. Спасибо большое за статью, очень полезно. Могли бы Вы, пожалуйста, помочь. Мне нужно прописать в функции, чтобы она выводила/вытягивала с другой страницы только те айди, которые не указаны выше. Как это выглядит:
    =QUERY(AUTO!A2:J4001;"
    Select A, B
    WHERE
    (B = 'Текст 1')
    AND
    A <> 'Текст 1'!A3:A84'")
    Как видно, у меня есть 2 листа - AUTO и Текст 1. В колонке А на листе Текст 1 уже есть какие-то айди, которые не нужно трогать. А формула должна с листа AUTO на лист Текст 1 вытянуть уникальные не повторяющиеся значения айди.
    Основная проблема возникает в нескольких моментах. 1 - вот здесь A <> 'Текст 1'!A3:A84 функция не воспринимает диапазон'Текст 1'!A3:A84 как лист, мне кажется. 2 - я не уверена, что правильно прописываю последнюю часть по поводу не включающихся значений.
    Буду очень благодарна за помощь!

    • 0
      Анастасия
      год назад

      Добрый день, если не ошибаюсь вам сначала надо пометить ID которых нет в списке на листе "Текст 1", далее вывести просто список этих уникальных ID.

      Если выше я написал всё верно, то изначально с помощью функции VLOOKUP отметьте те которые есть в списке на листе "Текст1", далее в условии WHERE просто фильтруйте данные по этому столбцу, для того что бы получить список уникальных значение используйте блок GROUP BY.

      Можете создать копию вашего докса, оставить там часть данных и я бы помог с написанием запроса.

  29. 0
    год назад

    Добрый день. Подскажите, пожалуйста, как сделать так, чтоб сравнение происходило не с определенной (статической) датой, прописанной вручную или указанной в ячейке, а с именно сегодняшней датой (динамической), которая будет меняться каждый день и будет прописана формульно? Пробую через now(): toDate(dateTime'now()') выдает ошибку #ЗНАЧ!, если прописываю дату вручную - все работает.

    • 0
      AZ
      год назад

      Добрый день, попробуйте использовать функцию today().

      • 0
        Алексей Селезнёв
        год назад

        Не ругается, но и не работает. Если у меня через when происходит сравнение (фильтрация) данных столбца N с today(), в каком формате должны быть данные столбца N: дата или автоматический? Если Вам не трудно, можно пример?

        • 0
          AZ
          год назад

          *через where, а не через when, ошиблась, сори

          • 0
            AZ
            год назад

            Кажется я понял в чём ошибка.

            Вот часть статьи которую вы скорее всего пропустили:

            "Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date.

            Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

            WHERE
            (A >= date'2015-10-22'
            AND A <= date'2015-10-25')
            AND (B = 'Campaign_1'
            OR B = 'Campaign_2')"

            В вашем случае придётся конкатенировать запрос, смотрите пример в случае если даты в базе данных находятся в столбце N.

            where N = date'"&year(today())&"-"&month(today())&"-"&day(today())&"'

            т.е. вы должны в запросе с помощью оператора date указать, что фильтровать вы будете по дате, далее ставим апостроф ' и закрываем двойную кавычку ", после чего вычисляем текущую дату и конкатенируем её в форма YYYY-MM-DD, далее опять конкатенация, открываем двойную кавычку и ставим апостроф, продолжаем писать запрос.

            • 0
              Алексей Селезнёв
              год назад

              Огромное Вам спасибо!! И за статью и за помощь. И простите мою невнимательность.
              Еще вопрос: можно ли сравнивать значения в столбце N не с 1 значением, а с целым массивом, например, с каким-то другим столбцом? То есть фильтровать данные по условию, если значение столбца N = любому из значений столбца K?

              • 0
                AZ
                год назад

                Рад что смог помочь.

                К сожалению фильтрация такого уровня на данный момент функцией query не поддерживается, но как вариант вы можете в базу данных добавить ещё один столбец и с помощью функции VLOOKUP проверять наличие какого либо значения в любом диапазоне, и возвращать TRUE если значение встречается в диапазоне, и FALSE если значение в диапазоне нет. Далее просто в функции QUERY указать фильтрацию название столбца = 'TRUE'. В данном случае функция вернёт только значения которые присутствуют в диапазоне.

                Надеюсь мне удалось описать идею, если нет я могу создать докс с примером.

  30. 0
    год назад

    Здравствуйте. Большое спасибо за описание столь полезного оператора. Пытаюсь воспользоваться им, но все время сумма выводимых данных - 0, хотя это не так. Может быть поможете разобраться? Суть проблемы: есть таблица на одном листе "Расходы", содержащая 3 колонки: дата, сумма и наименование расхода (типа Еда, Развлечения, Коммунальные услуги..). Задача: выводить на другом листе (Траты по месяцам) траты в сумме по месяцам (колонка - месяц/год и название расхода, строка - суммы на год и месяц) на каждый из типов расходов. Казалось бы, просто. Sumif в других местах работает хорошо, и здесь бы сработала, если бы у нас было одно условие, например, суммировать вообще все траты на Еду. Но нам надо по месяцам и годам. Пробовала использовать dsum. Но он не позволяет (ну как я поняла) логически работать с областью поиска, то есть, в данном случае, складывает вообще все, что найдет, хоть по одному параметру подходящее, хоть по двум, хоть по всем. Решила пойти путем query. Сделала еще дополнительные листы: 2016, 2015, 2014.., где вывела с помощью query все траты по всем направлениям, но за какой то один год, то есть на этом новом листе отличаются только месяца, и все в единственном экземпляре: =query('Расходы'!$A2:$P; "select C where (A='Еда')and(O=2016)";1) Все выводится правильно, и вроде бы задача решена, потому дальше остается воспользоваться только функцией sumif и в табличке "Траты по месяцам" и просто прописать жестко формулу для каждого месяца и года в соответствии с листами. Но (!!!) почему то sumif не может сложить то, что выводит query... просто не воспринимает как число, хотя я уже везде указала формат этих ячеек и исходные данные точно чИсла, и конечная ячейка число... а все равно в сумме 0, хотя я точно знаю что не 0... Где еще можно насильно указать формат, чтобы выводимые query данные, можно было использовать как числа? чем собственно они и являются. Или, возможно, есть какой то более простой способ сделать сводку по тратам... Или может быть есть способ суммировать сразу в query... по всем параметрам без промежуточных листов и таблиц. Заранее благодарю за ответ, очень жду.

    • 0
      Luno Lunomania
      год назад

      Добрый день, есть много довольно простых и удобных способов решения вашей задачи. На счёт QUERY мне было бы проще вам помочь если бы вы к примеру сделали копию вашего докса хотя бы с частью данных, я бы посмотрел в чём дело и более точно смог описать решение.

      На самом деле всё можно суммировать и внутри функции QUERY, используя SUM.

      По поводу функции SUMIF, есть её аналог который предназначен именно для суммирования по множеству условий, функция SUMIFS.

      Но в вашем случае думаю наиболее быстрым, удобным и простым решением будет сводная таблица, подробный мануал о работе в сводных таблицах вы можете найти тут http://blog.netpeak.ua/kak-postroit-svodnie-tablitsy-v-excel-libreoffice-openoffice-i-tablitsah-google/

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

      • 1
        Алексей Селезнёв
        год назад

        Спасибо за ответ! Почему то некоторые ячейки так и остались текстом и не суммировались, но я их нашла, удалила и все исправилось. А таблицу сделала потом вообще без query, на одном SUMIFS.

  31. 0
    год назад

    Скажите, пожалуйста, можно ли сделать сортировку по 2ум полям?
    select* order by G, order by C выдает ошибку
    (select* order by G) order by C также выдает ошибку

    • 0
      Johnny Saph
      год назад

      Добрый день, можно. Делается так же, как и в обычном классическом SQL, т.е.

      select* order by G, C

      Предикат order by не надо объявлять повторно, просто перечислите поля по которым необходимо отсортировать массив.

  32. 0
    год назад

    Спасибо за классную статью. Алексей, подскажите пожалуйста можно ли с помощью query отфильтровать значения, которые начинаются с 4. Например если я хочу вывести из DB только строки где количество pageviews начинается с 4 (4, 40-49, 400-499 и т.д.).

    • 1
      Alexey Chichirko
      год назад

      Попробуйте так:
      SELECT A
      WHERE A START WITH 4

    • 1
      Alexey Chichirko
      год назад

      Добрый день, для такой операции вам надо добавить столбец DB в котором будет флаг, например если pageviews начинается с 4 то возвращаем в качества флага 1, если pageviews начинается с другого символа возвращаем 0. А в описание запроса в QUERY укажите этот столбец в качества фильтра WHERE.

      Формула в столбце должна быть примерно такой:
      Предположим что поле pageviws как и в нашем примере у вас расположено в столбце J, и мы пишем формулу для поля flag во второй строке, которую далее можно будет протянуть на весь диапазон DB.

      =IF(LEFT(J2;1) = 4;1;0)

      Данная формула вернёт 1 в случае если первый символ поля pageviews 4, и во всех остальных случаях формула вернёт 0.

      Допустим поле flag у нас расположено в столбце M, в запросе QUERY вам необходимо указать следующее:
      WHERE M = 1

      Таким образом QUERY вернёт все строки в которых поле pagewiews начинается с 4.

  33. 0
    год назад

    @disqus_qRrFuH3qeI:disqus, в выгружаемых данных из GA нет запятых (в доходе например). В вашем примере уже запятые проставлены в листе DB. Вопрос, как заменить точки на запятые, чтобы потом можно было запустить отчет формироваться по расписанию?

    • 0
      Виль Габдуллин
      год назад

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

      http://f3.s.qip.ru/cMfvWjoE.png

      Как её решить:

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

      В нашем случае это столбцы C и D.
      http://f5.s.qip.ru/cMfvWjoF.png

      Выделяем нужные столбцы, кликнув по их именам в таблице, после чего идём в меню формат -> числа -> обычный текст.
      http://f6.s.qip.ru/cMfvWjoG.png

      После чего надо заново запустить процедуру загрузки данных. Меню дополнения -> Google Analytics -> RunReports.
      http://f1.s.qip.ru/cMfvWjoH.png

      В данный момент мы избавились от проблемы некорректной конвертации некоторых значений в поле Cost, загруженных ранее в виде дат.
      http://f6.s.qip.ru/cMfvWjoI.png

      Осталась проблема с разделителем в виде точки.

      2. Создаём новый лист и в ячейке A1 пишем формулу =QUERY(ссылка на диапазон загруженных из GA данных;"SELECT *")
      В нашем случае диапазон загруженных из Google Analytics данных Test!A15:D

      Формула будет выглядеть так:
      =query(Test!A15:D;"SELECT *")

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

      3. На новом листе, в который мы с помощью формулы =query(Test!A15:D;"SELECT *") загрузили данные, в столбце E пишем заголовок "ga:CostNew", а в столбце F "ga:ProductRevenueNew".

      4. В ячейке E2 вводим формулу =if(A2="";"";value(substitute(C2;".";","))) и протягиваем её до конца диапазона(ориентировочно до 1000 строки).

      5. В ячейке F2 вводим формулу =if(A2="";"";value(substitute(D2;".";","))) и протягиваем её до конца диапазона(ориентировочно до 1000 строки).

      Теперь у нас есть база данных, на основе который вы можете строить дальнейшие отчёты.
      http://f3.s.qip.ru/cMfvWjoK.png

      Что делает формула =if(A2="";"";value(substitute(C2;".";","))).

      Часть формулы, substitute(C2;".";","), ищет точку в значении находящемся в ячейке C2 и заменяет её на запятую.

      Часть формулы, value(substitute(C2;".";",")), преобразовывает значение полученные после замены точки на запятую из текста в число.

      Последняя часть if(A2="";"";value(substitute(C2;".";","))) проверяет заполнена ли ячейка в столбце А, если заполнена, возвращает нужное нам значение, преобразованное в число, если ячейка в столбце А не заполнена, то оставляет соответствующую ячейку в столбце E так же пустой.

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

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

      В качестве примера оставляю тут ссылку на докс, который был использован в качестве примера в этом комментарии.
      https://docs.google.com/spreadsheets/d/12HHUZ3IsYGesrbDYk23APA7omaSGmNwpjCPGktEK0FQ/edit#gid=1365411584

      • 0
        Алексей Селезнёв
        год назад

        Как её решить:

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

        В нашем случае это столбцы C и D.

        Выделяем нужные столбцы, кликнув по их именам в таблице, после чего идём в меню формат -> числа -> обычный текст.

        После чего надо заново запустить процедуру загрузки данных. Меню дополнения -> Google Analytics -> RunReports.

        Что делать, если не помогает?

      • 1
        Алексей Селезнёв
        год назад

        Спасибо большое за помощь!

  34. 2
    год назад

    спасибо за материал!!!

  35. 0
    год назад

    Алексей, спасибо за статью! Помогите, пожалуйста, разобраться. Попробовала воспользоваться вложенной функцией =QUERY(IMPORTRANGE("1D8Q7G9vCQS89RyYZPNir4l8HkGfITlrTpHDY0dAmEpI/edit#gid=0";"'Книги план'!A1:JF");"SELECT Col1, Col4, Col5, Col3, Col6, Col16, Col18, Col36, Col37, Col40, Col49, Col97, Col101, Col103, Col105, Col107, Col127, Col129, Col34, Col69, Col71, Col73, Col20, Col38, Col43, Col44, Col42, Col116, Col119, Col121, Col123, Col125, Col131, Col133, Col135, Col165, Col167, Col169, Col171, Col35, Col39, Col85, Col87, Col89, Col22, Col144, Col147, Col149, Col151, Col153, Col179, Col197, Col205, Col207, Col215, Col223, Col225, Col233, Col235 where (Col1 = 1)";-1). Но выдает ошибку: "Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:NO_COLUMNcol1". Что неправильно? Заранее спасибо за ответ.

    • 0
      Ирина Донская
      год назад

      Добрый день, Ирина.

      В данный момент у вас ошибка в описание ключа в функции IMPORTRANGE, в связи с чем в функцию QUERY не передаётся диапазон данных, и вам возвращается ошибка, что столбец 1 отсутствует в диапазоне.

      Как исправить:

      Первое, что стоит сделать - исправить описание ключа с ("1D8Q7G9vCQS89RyYZPNir4l8HkGfITlrTpHDY0dAmEpI/edit#gid=0" на ("1D8Q7G9vCQS89RyYZPNir4l8HkGfITlrTpHDY0dAmEpI"

      • 0
        Алексей Селезнёв
        год назад

        Попробовала - не получается. (((

        • 0
          Ирина Донская
          год назад

          Попробуйте убрать -1 в конце формулы, и в вашем случае в блоке WHERE скобки не обязательны, можно их убрать.

          Возможно, у вас не открыт доступ к листу Книги план'!A1:JF, для того, что бы его открыть, вставьте в любую пустую ячейку в доксе, в который требуется импортировать данные, формулу:

          =IMPORTRANGE("1D8Q7G9vCQS89RyYZPNir4l8HkGfITlrTpHDY0dAmEpI";"'Книги план'!A1:JF")

          Если формула вернёт ошибку #REF наведите на неё курсор мыши и вы увидите следующее сообщение:
          http://img.netpeak.ua/alsey/145492328684_kiss_19kb.jpg

          Нажмите кнопку "Открыть доступ".

          После чего можете удалить формулу из ячейки, и функция QUERY будет работать.

  36. 1
    год назад

    Спасибо огромное!!! Очень-очень полезная статья. Теперь в моём личном топ-1 на блоге :)

  37. 1
    год назад

    Очень круто! Спасибо, Алексей!!

    • 0
      Алексей
      год назад

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

      • 0
        Алексей Селезнёв
        месяц назад

        добрый день Алексей, помогите с query чтобы мой запрос выбирал не только месяц май, но и другие месяцы (например май и июнь). 

Чтобы оставить комментарий, необходимо авторизироваться

Подписаться

на самую полезную рассылку по интернет-маркетингу

Самое

обсуждаемое популярное читаемое