Всемогущая функция Query — подробное руководство. Добавление запросов (Power Query) Запросы в mysql из php mysqli query

".
Возможно, ребятам из московского офиса некогда, поэтому далее будет вольный перевод Query Language Reference (Version 0.7).
ИПП Визуализации Google. Язык запросов (Версия 0.7)
Язык запросов ИПП Визуализации Google позволяет выполнять манипуляции с данными через запрос к источнику данных.

Как правило, для визуализации необходимо предоставить данные в какой-то конкретной форме. Например, круговая диаграмма требует двух колонок: текстовой метки и числового значения. Источник данных при этом может не соответствовать этой структуре. Он может содержать большее количество колонок, или их порядок будет отличным от требуемого для диаграммы.
Язык запросов позволяет структурировать данные таким образом, которые нам необходимы для достижения результата.
Синтаксис языка запросов похож на SQL . Те, кто обладает навыком использования SQL, смогут быстро освоить язык запросов. В разделе синтаксиса указаны различия в этих языках.
Обратите внимание, что для визуализации не всегда есть необходимость использовать язык запросов. Требуется основание, чтобы его использовать. К содержанию .

Использование языка запросов

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

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

Установка запроса из JavaScript

Чтобы установить строку запроса из кода JavaScript, необходимо вызвать метод setQuery класса google.visualization.Query .

var query = new google.visualization.Query(DATA_SOURCE_URL);
query.setQuery("select dept, sum(salary) group by dept");
query.send(handleQueryResponse);

Строка запроса к источнику данных может быть добавлена с помощью параметра tq . Таким образом возможно использование визуализаций, которые были написаны другими разработчиками, просто корректируя свой набор данных.
Строка запроса должна быть правильно закодирована как URL-параметр. Вы можете использовать функцию JavaScript encodeURIComponent для кодировки или сделать это вручную через инструмент в конце раздела .

Пример

Рассмотрим пример запроса для Таблицы Google (Запомните, что идентификаторы колонок это всегда буквы. Названия колонок при публикации это не идентификаторы. В запросе необходимо использовать именно идентификаторы колонок)
После кодирования запрос будет выглядеть так:

select%20A%2C%20sum(B)%20group%20by%20A

Положим, что адрес Таблицы с данными следующий:

http://spreadsheets.google.com/a/google.com/tq?key=ABCDE

Добавим параметр запроса &tq=YOUR_QUERY_STRING к ссылке на Таблицу и получим готовую строку запроса

http://spreadsheets.google.com/a/google.com/tq?key=ABCDE&tq=select%A%2C%20sum(B)%20group%20by%20A

Синтаксис языка

Обзор

Я зык запросов очень похож на SQL. Тем не менее есть некоторые особенности, которые необходимо учесть.
Таблица данных
Этот документ содержит понятие "Таблица данных" для обозначения результатов запроса. Таблица состоит из строк и колонок. Каждая колонка обладает следующими свойствами:

Идентификатор (или ID колонки) . Используется для ссылки на колонки Таблицы. Обратите внимание, что ссылаться на колонку необходимо только по идентификатору. Трюк : старайтесь не использовать пробелы в идентификаторах, это может вызвать затруднения в редактировании запроса. Кроме того, все идентификаторы с пробелами должны быть заключены в обратные кавычки.
Метка . Это строка, которая как правило отображается для конечных пользователей. Например, легенда в круговой диаграмме или заголовок табличной диаграммы. Не применимо для функции QUERY() в Таблицах Google .
Типы данных . Поддерживаются следующие типы данных: строка (string), число (number), логический тип (boolean), дата (date), дата и время (datetime), время (timeofday). Все значения в колонке будут иметь тип данных этой колонки или null. Эти типы данных очень похожи на типы из JavaScript, не не идентичны. Они описаны в разделе Литералы.
Шаблоны форматирования . Таблицы данных может поддерживать шаблоны форматирования для всех или некоторых колонок. Они описаны в разделе Форматирование.

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

name string	dept string	lunchTime timeofday	hireDate date	age number	isSenior boolean	seniorityStartTime datetime
John	Eng	12:00:00	2005-03-19	35	true	2007-12-02 15:56:00
Dave	Eng	12:00:00	2006-04-19	27	false	null
Sally	Eng	13:00:00	2005-10-10	30	false	null
Ben	Sales	12:00:00	2002-10-10	32	true	2005-03-09 12:30:00
Dana	Sales	12:00:00	2004-09-08	25	false	null
Mike	Marketing	13:00:00	2005-01-10	24	true	2007-12-30 14:40:00

Операторы языка

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

Оператор	Использование
select	Выбирает какие колонки и в каком порядке будут возвращены. Если оператор пропущен, то будут возвращены все колонки таблицы.
	Возвращает только те строки таблицы, которые соответствуют условию. Если оператор пропущен, то будут возвращены все строки таблицы.
group by	Группирует значения по строкам.
pivot	Преобразует уникальные значения в колонках в новые колонки по примеру сводной таблицы.
order by	Сортирует строки в зависимости от значений.
limit	Устанавливает лимит строк, которые будут возвращены.
offset	Пропускает заданное количество строк из выдачи.
label	Устанавливает подписи для колонок.
format	Форматирует значения в заданных колонках согласно шаблонам форматирования.
options	Устанавливает дополнительные параметры.
from	Оператор from не используется.

Select

Оператор select указывает, какие колонки должны быть возвращены и в каком порядке. Если оператор не указан, или указан select * будут возвращены все колонки таблицы в их изначальном порядке. На колонки можно сослаться через идентификаторы, но не через метки. Идентификаторами в Таблицах Google являются имена столбцов из одной или двух букв (A, B, C, ..., AC, ... , DC, ...).
Параметрами оператора могут быть идентификаторы колонок, агрегатные функции, скалярные функции или арифметические операторы.

Примеры:

select *
select dept, salary
select max(salary)

email address date ):

Получим результат:

lunchTime	name
12:00:00	John
12:00:00	Dave
13:00:00	Sally
12:00:00	Ben
12:00:00	Dana
13:00:00	Mike

Оператор where используется, чтобы вернуть только те строки, которые удовлетворяют условиям.
Для описания условий используются простые операторы сравнения <=, <, >, >=, =, !=, <> . Оба оператора!=, <> означают не равно . Строки сравниваются по их лексикографическому значению. Замечание, равенство проверяется через оператор = , а не через == как во многих языках . Сравнение с null производится через is null и is not null . При использовании QUERY() в Таблицах Google, обычно, строковое поле имеет пустое значение "" , а не null .
Вы можете объединить несколько условий используя логические операторы and, or, not . Чтобы определить явный приоритет используются скобки.

Оператор where также использует дополнительные операторы для более сложного сравнения строк. Эти операторы используют два аргумента, которые должны быть строками, любой нестроковый аргумент (дата, число) будет преобразован в строку перед сравнением. Сравнение зависит от регистра символов (для сглаживания этой особенности используйте скалярные функции upper(), lower()).
contains - соответствие подстроке. Выражение вернет истину, если часть строки будет полностью соответствовать заданному аргументу - подстроке. Например, where name contains "John" вернет "John", "John Adams", "Long John Silver" но не "john adams" .

Примеры:

select *
select dept, salary
select max(salary)

Следующий пример показывает использование ссылок на идентификаторы с пробелами (email address ) и на идентификаторы, совпадающие с именами зарезервированных слов (date ):

select `email address`, name, `date`

Применим следующий запрос к таблице из примера:
Получим результат:

lunchTime	name
12:00:00	John
12:00:00	Dave
13:00:00	Sally
12:00:00	Ben
12:00:00	Dana
13:00:00	Mike

resource mysql_query (string query [, resource link_identifier])

mysql_query() посылает запрос активной базе данных сервера, на который ссылается переданный указатель. Если параметр link_identifier опущен, используется последнее открытое соединение. Если открытые соединения отсутствуют, функция пытается соединиться с СУБД, аналогично функции mysql_connect() без параметров. Результат запроса буфферизируется.

Замечание: Строка запроса НЕ должна заканчиваться точкой с запятой.

Только для запросов SELECT, SHOW, EXPLAIN, DESCRIBE, mysql_query() возвращает указатель на результат запроса, или FALSE если запрос не был выполнен.
В остальных случаях (INSERT, UPDATE, DELETE, DROP, и т.п.), mysql_query() возвращает TRUE в случае успешного запроса и FALSE в случае ошибки. Значение не равное FALSE говорит о том, что запрос был выполнен успешно. Он не говорит о количестве затронутых или возвращённых рядов. Вполне возможна ситуация, когда успешный запрос не затронет ни одного ряда.

Следующий запрос составлен неправильно и mysql_query() вернёт FALSE :

mysql_query() также считается ошибочным и вернёт FALSE , если у вас не хватает прав на работу с указанной в запросе таблицей.

Работая с результатами запросов, вы можете использовать функцию mysql_num_rows() , чтобы найти число, возвращённых запросом SELECT, рядов, или mysql_affected_rows() , чтобы найти число рядов, обработанных запросами DELETE, INSERT, REPLACE, или UPDATE.

Только для запросов SELECT, SHOW, DESCRIBE, EXPLAIN, функция mysql_query() возвращает указатель на результат, который можно использовать в функции mysql_fetch_array() и других функциях, работающих с результатами запросов. Когда работа с результатом окончена, вы можете освободить ресурсы, используемые для его хранения, с помощью функции

Думаю, все слышали о правиле Парето. В любой сфере 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 >= date"2015-10-22" AND A <= date"2015-10-25") AND (B = "Campaign_1" OR B = "Campaign_2") ORDER BY G DESC")

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

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать . Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция "Скачать и преобразовать" в Excel 2016 .

В Power Query операция Добавить создает новый запрос, содержащий все строки из первого запроса, а затем все строки из второго запроса.

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

Выполнение добавления

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

Встроенное добавление

Промежуточное добавление

См. также:

Примечание: Редактор запросов отображается только при загрузке, редактировании или создании нового запроса с помощью Power Query . В видео показано окно редактора запросов , которое отображается после изменения запроса в книге Excel. Чтобы просмотреть редактор запросов , не загружая и не изменяя существующий запрос в книге, в разделе Получение внешних данных на вкладке ленты Power Query выберите Из других источников > Пустой запрос . В видео показан один из способов отображения редактора запросов .

Спасибо Евгению Намоконову за помощь в подготовке материала.

Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их.

Синтаксис функции:

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

данные - это исходный диапазон, который будет обрабатываться и из которого мы будем формировать выборку;
запрос на языке API визуализации Google (идентичный SQL), указанный в кавычках, с соблюдением определенных правил, которые мы обсудим далее;
заголовки - количество строк с заголовками в исходном диапазоне. По умолчанию равен -1 (минус одному), и это означает, что количество строк с заголовками будет определяться автоматически.

Итак, правила формирования запросов:

Запрос указывается в кавычках.
В запросе используются ключевые слова:
1. SELECT - определяет, какие столбцы из исходной таблицы выгружать и в каком порядке. Например: «SELECT A, C, D, B». Если пропустить или указать звездочку («SELECT *») вместо заголовков столбцов, будут грузиться все столбцы в исходном порядке.
2. WHERE - ключевое слово, после которого следуют условия, по которым происходит отбор. Без него будут загружаться все строки исходного диапазона.
3. GROUP BY - группирует значения по заданным полям.
4. PIVOT - позволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля исходной таблицы.
5. ORDER BY - задает сортировку. Например: «ORDER BY C DESC» - сортировка по столбцу C по убыванию.
6. LIMIT - ограничивает количество возвращаемых строк. Например: «LIMIT 50».
7. OFFSET - пропускает заданное количество строк от начала диапазона. Например: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, то есть при использовании LIMIT 70 OFFSET 30 будут возвращены строки с 31‑й до 100-й.
8. FORMAT - определяет формат определенных столбцов по заданному шаблону.
9. 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 — номер столбца.