Функция QUERY [ЧЕРНОВИК]

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

[картинка]

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

Содержание

Введение

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

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

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

Установка запроса в качестве параметра Функции QUERY

Если вы используете функцию 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 для кодировки или сделать это вручную через инструмент в конце раздела Setting the Query in the Data Source URL.
Пример
Рассмотрим пример запроса для Таблицы Google (Запомните, что идентификаторы колонок это всегда буквы. Названия колонок при публикации это не идентификаторы. В запросе необходимо использовать именно идентификаторы колонок)
select A, sum(B) group by A
После кодирования запрос будет выглядеть так:
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
salary
number
hireDate
date
age
number
isSenior
boolean
seniorityStartTime
datetime
JohnEng12:00:00
1000
2005-03-1935true2007-12-02 15:56:00
DaveEng12:00:00
500
2006-04-1927falsenull
SallyEng13:00:00
600
2005-10-1030falsenull
BenSales12:00:00
400
2002-10-1032true2005-03-09 12:30:00
DanaSales12:00:00
350
2004-09-0825falsenull
MikeMarketing13:00:00
800
2005-01-1024true2007-12-30 14:40:00

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

Язык запросов состоит из следующих операторов, состоящих из одного или двух слов. Операторы должны следовать друг за другом в той последовательности, как они указаны в таблице:
Оператор Использование
select Выбирает какие колонки и в каком порядке будут возвращены. Если оператор пропущен, то будут возвращены все колонки таблицы.
where Возвращает только те строки таблицы, которые соответствуют условию. Если оператор пропущен, то будут возвращены все строки таблицы.
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):
select `email address`, name, `date`
Применим следующий запрос к таблице из примера:
select lunchTime, name
Получим результат:
lunchTime name
12:00:00John
12:00:00Dave
13:00:00Sally
12:00:00Ben
12:00:00Dana
13:00:00Mike
Where
Оператор 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`
Применим следующий запрос к таблице из примера:
select lunchTime, name
Получим результат:
lunchTime name
12:00:00John
12:00:00Dave
13:00:00Sally
12:00:00Ben
12:00:00Dana
13:00:00Mike

Популярные сообщения