Язык запросов Google Visualization API позволяет выполнять различные манипуляции с данными при запросе к источнику данных.

Оглавление

Введение

Обычно визуализации ожидают данные в какой-то конкретной форме. Например, круговая диаграмма может ожидать данные в виде двух столбцов: текстовую метку и числовое значение. Данные в источнике данных могут не совсем соответствовать этой структуре. Например, источник данных может иметь более двух столбцов или порядок столбцов может не соответствовать порядку, ожидаемому на круговой диаграмме.

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

Синтаксис языка запросов аналогичен SQL. Разработчики, знакомые с SQL, должны иметь возможность быстро изучить и использовать этот язык запросов. В Интернете доступно множество учебных пособий по SQL . Между этим языком запросов и SQL существуют некоторые различия, которые описаны в разделе синтаксиса .

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

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

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

Настройка запроса из 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);

Установка запроса в URL-адресе источника данных

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

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

Пример:

Рассмотрим следующую строку запроса для электронной таблицы Google. (Обратите внимание, что идентификаторы столбцов в электронных таблицах всегда представляют собой буквы; текст заголовка столбца, отображаемый в опубликованной электронной таблице, представляет собой метки, а не идентификаторы. В строке запроса необходимо использовать идентификатор, а не метку.)

select A, sum(B) group by A

В закодированном виде этот запрос принимает вид:

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

Предположим, что это URL-адрес вашей таблицы:

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4

Добавьте /gviz/tq?tq= YOUR_QUERY_STRING к URL-адресу электронной таблицы, чтобы получить окончательную строку запроса:

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A

Используйте приведенный ниже инструмент для кодирования или декодирования строки запроса:

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

Обзор

Синтаксис языка запросов Google Visualization API аналогичен синтаксису SQL. Однако это подмножество SQL с некоторыми собственными функциями, которые вам необходимо изучить. Если вы знакомы с SQL, изучить его не составит большого труда.

Таблица, используемая во всех примерах:

В этом разделе все примеры запросов относятся к следующей таблице. Заголовки столбцов являются идентификаторами столбцов.

Языковые положения

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

Выбирать

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

Элементами в предложении select могут быть идентификаторы столбцов или выходные данные функций агрегирования , скалярных функций или операторов .

Примеры:

select *

select dept, salary

select max(salary)

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

select `email address`, name, `date`

Выполнение следующего запроса к таблице примера :

select lunchTime, name

Возвращает следующий ответ:

Где

where используется для возврата только тех строк, которые соответствуют указанному условию.

Простые операторы сравнения: <=, <, >, >=, =, !=, <> . Оба оператора сравнения != <> означают «не равно». Строки сравниваются по лексикографическому значению. Обратите внимание, что равенство обозначается = , а не == , как в большинстве компьютерных языков. Сравнение с null выполняется с использованием is null или is not null .

Вы можете объединить несколько условий, используя логические операторы and , or , и not . Круглые скобки можно использовать для определения явного приоритета.

Предложениеwhere также поддерживает некоторые более сложные операторы сравнения строк. Эти операторы принимают в качестве аргументов две строки; любые нестроковые аргументы (например, даты или числа) перед сравнением будут преобразованы в строки. Сопоставление строк чувствительно к регистру (чтобы обойти это, вы можете использовать скалярные функции upper() или lower() ).

• contains — совпадение подстроки. целое contains часть верно, если часть находится где-то внутри целого . Пример: where name contains 'John' соответствует «Джон», «Джон Адамс», «Длинный Джон Сильвер», но не «Джон Адамс».
• starts with — совпадение префикса. значение starts with префикса. Значение true, если префикс находится в начале значения . Примеры: where dept starts with 'engineering' соответствует «инжиниринг» и «технические менеджеры». where dept starts with 'e' соответствует «инжинирингу», «англу» и «е».
• ends with - совпадение суффикса. значение ends with суффиксом, истинно, если суффикс находится в конце значения . Пример: where role ends with 'y' соответствует «ковбой», «мальчик» и «y».
• matches — совпадение регулярного выражения (preg). Значение haystack matches Needle истинно, если регулярное выражение в Needle соответствует Haystack . Примеры: where country matches '.*ia' соответствует Индии и Нигерии, но не Индиане. Обратите внимание, что это не глобальный поиск, поэтому where country matches 'an' не будет соответствовать «Канада».
• like — текстовый поиск, поддерживающий два подстановочных знака: %, который соответствует нулю или более символам любого типа, и _ (подчеркивание), который соответствует любому одному символу. Это похоже на оператор SQL LIKE. Пример: where name like fre% соответствует «fre», «fred» и «freddy».

Примеры:

where salary >= 600
where dept != 'Eng' and date '2005-01-21' < hireDate
where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null

Выполнение следующего запроса к таблице примера :

select name where salary > 700

Возвращает следующий ответ:

Группа по

Предложение group by используется для агрегирования значений по строкам. Для каждой отдельной комбинации значений в предложении group-by создается отдельная строка. Данные автоматически сортируются по столбцам группировки, если иное не указано в предложении order by .

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

Примеры:

select dept, max(salary) group by dept

Выполнение следующего запроса к таблице примера :

select lunchTime, avg(salary), count(age) group by isSenior,lunchTime

Возвращает следующий ответ:

Вращаться

Предложение pivot используется для преобразования отдельных значений в столбцах в новые столбцы. Например, сводка по столбцу «год» приведет к созданию таблицы со столбцом для каждого отдельного года, который присутствует в исходной таблице. Это может быть полезно, если, например, визуализация линейной диаграммы рисует каждый столбец как отдельную линию. Если вы хотите нарисовать отдельную строку для каждого года, а «год» — это один из столбцов исходной таблицы, то хорошим вариантом будет использование операции поворота для выполнения необходимого преобразования данных.

Примечание. Если вы используете предложение pivot таблицы, то каждый столбец , указанный в предложении select , должен быть либо указан в предложении group by , либо обернут функцией агрегирования.

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

select sum(salary) pivot dept

Возвращает следующий ответ:

Это потому, что 2100 — это сумма зарплат отдела английского, 800 — отдела маркетинга и т. д.

Использование pivot вместе с group by может быть еще более полезным, поскольку создает таблицу, в которой каждая ячейка содержит результат агрегирования для соответствующей строки и соответствующего столбца. Например, выполнив следующий запрос к таблице примера :

select dept, sum(salary)
  group by dept
  pivot lunchTime

Возвращает следующий ответ:

Вы также можете «инвертировать» эту таблицу, переключая столбцы и строки, переключаясь между pivot столбцами и group by столбцам. Выполнение следующего запроса к таблице примера :

select lunchTime, sum(salary)
  group by lunchTime
  pivot dept

Возвращает следующий ответ:

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

select sum(salary)
  pivot dept, lunchTime

Возвращает следующий ответ:

Обратите внимание, что только комбинациям, которые появляются в исходной таблице, присваиваются столбцы в таблице ответов. Вот почему нет столбца «Маркетинг», 12:00:00 или «Продажи», 13:00:00.

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

select sum(salary), max(lunchTime)
  pivot dept

Возвращает следующий ответ:

Вы можете объединить несколько агрегатов в предложении select , несколько столбцов в предложении group by и несколько столбцов в предложении pivot таблицы. На внутреннем уровне агрегирование выполняется путем объединения столбцов в группе по и сводных предложений.

Столбцы, указанные в pivot предложении, могут не отображаться в предложениях select , group by или order by . При использовании pivot предложение order by не может содержать столбцы агрегирования. Причина этого в том, что для каждой агрегации, указанной в предложении select , в таблице результатов создается множество столбцов. Однако при использовании pivot вы можете форматировать столбцы агрегирования. Результатом такого формата является то, что все новые столбцы, относящиеся к конкретному агрегированию, генерируемые операцией сведения, форматируются по указанному шаблону. В приведенном выше примере добавление format sum(salary) "some_format_string" повлияет на следующие столбцы: Сумма-зарплата инженера, Сумма-зарплата маркетинга и Сумма-зарплата продаж.

Вы можете пометить столбцы агрегирования. Если в предложении label не указана метка, метка столбца, созданного в результате поворота, состоит из списка значений в сводных столбцах, типа агрегации (мин, максимум, сумма, ...) и метка агрегированного столбца. Например «Англ.,12:00:00 сумма зарплаты». Если в предложении select была указана только одна агрегация, то часть агрегации удаляется из метки и сохраняется только список значений в сводных столбцах. Например «Анг,12:00:00». Если в предложении label указана метка для столбца агрегирования, запрошенная метка добавляется к списку значений как в том случае, если в предложении select имеется только одно агрегирование, так и в том случае, если их несколько. Например, label sum(salary) "sumsal" приведет к появлению в столбцах меток "Eng,12:00:00 sumsal", "Eng,13:00:00 sumsal" и т. д.

Сортировать по

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

Элементами в предложении order by могут быть идентификаторы столбцов или выходные данные функций агрегирования , скалярных функций или операторов .

Примеры:

order by dept, salary desc

select dept, max(salary) group by dept order by max(salary)

Лимит

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

Пример:

limit 100

Компенсировать

Предложение offset используется для пропуска заданного количества первых строк. Если используется предложение limit , сначала применяется offset : например, limit 15 offset 30 возвращает строки с 31 по 45.

Примеры:

offset 10
limit 30 offset 210

Этикетка

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

Элементами в предложении label могут быть идентификаторы столбцов или выходные данные функций агрегирования , скалярных функций или операторов .

Синтаксис:

label column_id label_string [,column_id label_string]

column_id

Идентификатор столбца, которому присвоена метка.

label_string

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

Пример:

В следующем примере для метки столбца отдела устанавливается значение «Отдел», для метки столбца имени — «Имя сотрудника», а для метки столбца местоположения — «Местоположение сотрудника»:

label dept 'Department', name "Employee Name", location 'Employee Location'

Формат

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

Синтаксис шаблона:

number , date , timeofday , datetime

Шаблоны дат и номеров , определенные ICU .

boolean

Шаблон представляет собой string в формате « значение-если-истина : значение-если-ложь ».

Пример:

format salary '#,##0.00', hireDate 'dd-MMM-yyyy', isSenior 'Yes!:Not yet'

Параметры

Предложение options используется для управления дополнительными параметрами выполнения запроса. Возможные ключевые слова, которые могут следовать за предложением options :

• no_format Удаляет форматированные значения из результата и оставляет только базовые значения. Может использоваться, когда конкретная визуализация не использует форматированные значения для уменьшения размера ответа.
• no_values ​​Удаляет базовые значения из результата и оставляет только отформатированные значения. Может использоваться, когда конкретная визуализация использует только отформатированные значения, чтобы уменьшить размер ответа.

Функции манипулирования данными

Существует несколько типов операторов и функций, которые позволяют манипулировать данными или агрегировать их в одном столбце, а также сравнивать или комбинировать данные по нескольким столбцам. Примеры включают sum() (чтобы сложить все значения в столбце), max (чтобы найти наибольшее значение в столбце) и + (чтобы сложить значения двух столбцов в одной строке).

Некоторые функции могут присутствовать в любом предложении; некоторые могут появиться в подмножестве предложений. Это описано ниже.

Пример:

select upper(name), year(startDate)

Следующие функции манипулирования данными определены языком запросов API визуализации Google:

• Функции агрегирования
• Скалярные функции
• Арифметические операторы

Функции агрегирования

Функциям агрегирования передается один идентификатор столбца, и они выполняют действие над всеми значениями в каждой группе (группы задаются предложениями group by или pivot или всеми строками, если эти предложения не используются).

Примеры:

select max(salary)               // Returns a table with one column and one row.
select max(salary) group by dept // Returns a table with the max salary for each dept column value.
select max(salary) pivot dept    // Returns a one-row table with a column for each dept,
                                 //   and the max salary for that department.

Функции агрегирования можно использовать в предложениях select , order by , label , format . Они не могут появляться в where , group by , pivot , limit , offset или options .

Вот поддерживаемые функции агрегирования:

Примечание. Функции агрегации могут принимать в качестве аргумента только идентификатор столбца:

max(startDate)                      // OK
min(firstScore) + min(secondScore)  // OK
max(year(startDate))                // INVALID. max requires column ID
sum(salary + perks)                 // INVALID. sum requires column ID.

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

Скалярные функции работают с нулем или несколькими параметрами для получения другого значения. Скалярным функциям можно передавать любое выражение, результатом которого является параметр соответствующего типа. Обратите внимание, что эти типы — это типы, определенные в разделе «Литералы» данного документа, которые могут немного отличаться от объектов JavaScript с аналогичными названиями.

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

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

year(max(startDate))
datediff(now(), todate(1234567890000))

Скалярные функции можно использовать в любом из следующих предложений: select , where , group by , pivot , order by , label , и format .

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

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

Примеры:

select empSalary - empTax
select 2 * (max(empSalary) / max(empTax))

Определены следующие операторы:

Языковые элементы

Литералы

Литералы — это значения, используемые для сравнения или присваивания. Литералы могут быть string , числами, логическими значениями или различными типами даты и времени. Вот несколько примеров литералов, используемых в синтаксисе запроса:

where startDate < date "2008-03-18"  // date "2008-03-18" is a date literal
limit 30                             // 30 is a numeric literal
format salary '#,##0.00', isSenior 'not yet:of course!'  // '#,##0.00' and 'not yet:of course!' are both string literals

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

string

string литерал должен быть заключен в одинарные или двойные кавычки. Примеры: "fourteen" 'hello world' "It's raining" .

number

Числовые литералы задаются в десятичной записи. Примеры: 3 3.0 3.14 -71 -7.2 .6

boolean

Логические литералы могут быть либо true , либо false .

date

Используйте date ключевого слова, за которой следует string литерал в формате yyyy-MM-dd . Пример: date "2008-03-18" .

timeofday

Используйте ключевое слово timeofday , за которым следует string литерал в формате HH:mm:ss[.SSS] Пример: timeofday "12:30:45" .

datetime

Дата и время с использованием ключевого слова datetime или ключевого слова timestamp , за которым следует string литерал в формате yyyy-MM-dd HH:mm:ss[.sss] . Пример: datetime '2008-03-18 12:30:34.123'

Идентификаторы

Идентификаторы (или идентификаторы) — это текстовые string , идентифицирующие столбцы.

Важно: если ваш идентификатор

• Имеет пробелы,
• Это зарезервированное слово ,
• Содержит что угодно, кроме буквенно-цифровых символов или символов подчеркивания ([a-zA-Z0-9_]), или
• Начинается с цифры

он должен быть заключен в обратные кавычки (а не одинарные).

В противном случае ваш идентификатор не нужно заключать в кавычки. (Обратите внимание, что не все ключевые слова , определенные синтаксисом, являются зарезервированными словами; поэтому, например, вы можете использовать «max» в качестве идентификатора без необходимости заключать его в обратные кавычки.)

Примеры: col1 employee_table `start date` `7 days traffic` `select`

Мы не рекомендуем выбирать идентификатор, требующий обратных кавычек, поскольку можно легко забыть об их использовании или случайно использовать «одинарные кавычки» вместо «обратных кавычек». Это распространенные ошибки, и их часто трудно исправить.

Чувствительность к регистру

Идентификаторы и строковые литералы чувствительны к регистру. Все остальные элементы языка нечувствительны к регистру.

Зарезервированные слова

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

and
asc
by
date
datetime
desc
false
format
group
label
limit
not
offset
options
or
order
pivot
select
timeofday
timestamp
true
where