A linguagem de consulta da API de visualização do Google permite fazer várias manipulações de dados com a consulta à fonte de dados.

Sumário

Introdução

Normalmente, as visualizações esperam dados de uma forma específica. Por exemplo, um gráfico de pizza espera dados como duas colunas: um rótulo de texto e um valor numérico. Os dados na fonte de dados podem não corresponder exatamente a essa estrutura. Por exemplo, a fonte de dados pode ter mais de duas colunas, ou a ordem das colunas pode não corresponder à ordem esperada pelo gráfico de pizza.

A linguagem de consulta permite enviar solicitações de manipulação e formatação de dados para a fonte de dados e garantir que a estrutura e o conteúdo retornados correspondam à estrutura esperada.

A sintaxe da linguagem de consulta é semelhante ao SQL. Os desenvolvedores familiarizados com o SQL precisam aprender e usar essa linguagem de consulta rapidamente. Existem muitos tutoriais de SQL disponíveis na Web. Há algumas diferenças entre essa linguagem de consulta e o SQL, que são descritas na seção syntax.

As fontes de dados não precisam implementar a linguagem de consulta ou, caso façam isso, todos os recursos da linguagem. A menos que você tenha motivos para acreditar o contrário, não dependa de uma fonte de dados para implementar todos os recursos dessa linguagem.

Como usar a linguagem de consulta

É possível anexar uma string de consulta a uma solicitação de fonte de dados de duas maneiras: definindo a string de consulta no código JavaScript ou definindo a string de consulta como um parâmetro no URL da fonte de dados. Se a solicitação não incluir uma string de consulta, o comportamento padrão de uma fonte de dados será retornar todas as linhas e colunas usando a formatação e a ordem padrão de linhas/colunas. Para isso, inclua uma string de consulta na solicitação para uma fonte de dados.

Como configurar a consulta usando JavaScript

Para definir a string de consulta no código JavaScript, chame o método setQuery da classe google.visualization.Query.

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

Configuração da consulta no URL da fonte de dados

A string de consulta pode ser adicionada ao URL da fonte de dados usando o parâmetro tq. Definir a consulta no parâmetro de URL em vez de em JavaScript permite usar facilmente visualizações criadas por outros desenvolvedores e ainda personalizar a consulta.

A string de consulta precisa ser codificada corretamente como parâmetro de URL. É possível codificar um URL usando a função encodeURIComponent do JavaScript ou manualmente, usando a ferramenta de codificação ao final desta seção.

Exemplo:

Considere a seguinte string de consulta para uma Planilha Google. Os IDs de coluna nas planilhas são sempre letras. O texto do cabeçalho da coluna mostrado na planilha publicada são rótulos, não IDs. Use o ID, não o rótulo, na string de consulta.

select A, sum(B) group by A

Quando codificada, essa consulta se torna:

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

Suponha que este seja o URL da sua planilha:

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

Adicione /gviz/tq?tq=YOUR_QUERY_STRING ao URL da planilha para acessar a string de consulta final:

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

Use a ferramenta abaixo para codificar ou decodificar uma string de consulta:

Sintaxe da linguagem

Visão geral

A sintaxe da linguagem de consulta da API de visualização do Google foi projetada para ser semelhante à sintaxe do SQL. No entanto, ele é um subconjunto do SQL, com alguns recursos próprios que você precisará aprender. Se você já conhece o SQL, não deve ser difícil de aprender.

Tabela usada em todos os exemplos:

Ao longo desta seção, todos os exemplos de consultas se referem à tabela a seguir. Os cabeçalhos das colunas são os identificadores.

Cláusulas de idioma

A sintaxe da linguagem de consulta é composta pelas orações a seguir. Cada cláusula começa com uma ou duas palavras-chave. Todas as cláusulas são opcionais. As cláusulas são separadas por espaços. A ordem das cláusulas precisa ser a seguinte:

Selecionar

A cláusula select é usada para especificar as colunas a serem retornadas e a ordem delas. Se essa cláusula não for especificada ou se select * for usado, todas as colunas da tabela da fonte de dados serão retornadas na ordem original. As colunas são referenciadas pelos identificadores, não por rótulos. Por exemplo, em uma Planilha Google, os identificadores de coluna são uma ou duas letras (A, B, C, ...).

Os itens em uma cláusula select podem ser identificadores de coluna ou a saída de funções de agregação, funções escalares ou operadores.

Exemplos:

select *

select dept, salary

select max(salary)

No exemplo a seguir, as aspas são usadas para referenciar IDs de coluna que contêm espaços (endereço de e-mail) ou que são palavras reservadas (data):

select `email address`, name, `date`

Execute a seguinte consulta na tabela de exemplo:

select lunchTime, name

Retorna a seguinte resposta:

Onde

A cláusula where é usada para retornar somente linhas que correspondem a uma condição especificada.

Os operadores de comparação simples são <=, <, >, >=, =, !=, <>. Os dois operadores de comparação != <> significam diferentes. As strings são comparadas por valor lexicográfico. A igualdade é indicada por =, não por ==, como na maioria das linguagens de computador. A comparação com null é feita usando is null ou is not null.

É possível mesclar várias condições usando os operadores lógicos and, or e not. Parênteses podem ser usados para definir precedência explícita.

A cláusula where também suporta alguns operadores de comparação de string mais complexos. Esses operadores usam duas strings como argumentos. Qualquer argumento que não seja string (por exemplo, datas ou números) será convertido em strings antes da comparação. A correspondência de string diferencia maiúsculas de minúsculas. É possível usar funções escalares upper() ou lower() para contornar isso.

• contains: uma correspondência de substring. A propriedade todo contains part será verdadeira se part estiver em qualquer lugar em todo. Exemplo:where name contains 'John' corresponde a "John", "John Adams", "Long John Silver", mas não a "john adams".
• starts with: uma correspondência de prefixo. value starts with prefix será verdadeiro se prefix estiver no início de value. Exemplos:where dept starts with 'engineering' corresponde a "engenheiro" e "gerentes de engenharia". where dept starts with 'e' corresponde a "engineering", "eng" e "e".
• ends with: uma correspondência de sufixo. value ends with suffix é verdadeiro se sufix está no final de value. Exemplo:where role ends with 'y' corresponde a 'cowboy', 'boy' e 'y'.
• matches: uma correspondência de expressão regular (preg). haystack matches needle é verdadeira se a expressão regular em needle corresponder a haystack. Exemplos:where country matches '.*ia' corresponde à Índia e à Nigéria, mas não a Indiana. Essa não é uma pesquisa global, por isso where country matches 'an' não corresponde a "Canadá".
• like: uma pesquisa de texto que aceita dois caracteres curinga: %, que corresponde a zero ou mais caracteres de qualquer tipo, e _ (sublinhado), que corresponde a qualquer caractere. Isso é semelhante ao operador SQL LIKE. Exemplo:where name like fre% corresponde a 'fre', 'fred' e 'freddy'.

Exemplos:

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

Execute a seguinte consulta na tabela de exemplo:

select name where salary > 700

Retorna a seguinte resposta:

Agrupar por

A cláusula group by é usada para agregar valores nas linhas. Uma única linha é criada para cada combinação distinta de valores na cláusula group-by. Os dados são classificados automaticamente pelo agrupamento de colunas, a menos que especificado de outra forma por uma cláusula order by.

Observação:se você usar uma cláusula group by, todas as colunas listadas na select precisarão estar listadas na cláusula group by ou ser unidas por uma função de agregação.

Exemplos:

select dept, max(salary) group by dept

Execute a seguinte consulta na tabela de exemplo:

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

Retorna a seguinte resposta:

Tabela dinâmica

A cláusula pivot é usada para transformar valores distintos nas colunas em novas colunas. Por exemplo, uma tabela dinâmica por uma coluna "ano" produziria uma tabela com uma coluna para cada ano distinto que aparece na tabela original. Isso pode ser útil se, por exemplo, uma visualização de gráfico de linhas desenha cada coluna como uma linha separada. Se você quiser desenhar uma linha separada para cada ano e "ano" for uma das colunas da tabela original, uma boa opção seria usar uma operação dinâmica para fazer a transformação de dados necessária.

Observação: se você usar uma cláusula pivot, cada coluna listada na select precisará estar listada na cláusula group by ou ser unida por uma função de agregação.

Como várias linhas podem conter os mesmos valores para as colunas dinâmicas, a tabela dinâmica implica agregação. Ao usar pivot sem group by, a tabela de resultados conterá exatamente uma linha. Por exemplo, execute a seguinte consulta na tabela de exemplo:

select sum(salary) pivot dept

Retorna a seguinte resposta:

Isso ocorre porque 2100 é a soma dos salários do departamento de Engenharia, 800 do departamento de Marketing etc.

Usar pivot com group by pode ser ainda mais útil, porque cria uma tabela em que cada célula contém o resultado da agregação da linha e da coluna relevantes. Por exemplo, execute esta consulta na tabela de exemplo:

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

Retorna a seguinte resposta:

Também é possível "inverter" a tabela alternando colunas e linhas, alternando entre as colunas pivot e group by. Execute a seguinte consulta na tabela de exemplo:

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

Retorna a seguinte resposta:

Também é possível usar mais de uma coluna na cláusula pivot. Nesse caso, as colunas da tabela de resposta são compostas por todas as combinações exclusivas de valores das colunas na tabela original. Por exemplo, execute a seguinte consulta na tabela de exemplo:

select sum(salary)
  pivot dept, lunchTime

Retorna a seguinte resposta:

Apenas as combinações que aparecem na tabela original recebem colunas na tabela de resposta. É por isso que não há coluna para Marketing,12:00:00 ou para Vendas,13:00:00.

Também é possível usar mais de uma agregação. Por exemplo, execute a seguinte consulta na tabela de exemplo:

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

Retorna a seguinte resposta:

É possível combinar várias agregações na cláusula select, várias colunas na cláusula group by e várias colunas na cláusula pivot. Internamente, a agregação é realizada pela concatenação das colunas nas cláusulas "Agrupar por" e "Dinâmica".

As colunas especificadas na cláusula pivot podem não aparecer nas cláusulas select, group by ou order by. Quando pivot é usado, a cláusula order by não pode conter nenhuma coluna de agregação. O motivo é que, para cada agregação especificada na cláusula select, muitas colunas são geradas na tabela de resultados. No entanto, você pode formatar as colunas de agregação quando pivot é usado. O resultado desse formato é que todas as novas colunas relevantes para a agregação específica que são geradas pela operação de tabela dinâmica são formatadas pelo padrão especificado. No exemplo acima, a adição de format sum(salary) "some_format_string" afetará as seguintes colunas: Salário-salário de eng, Salário de marketing e salário-salário de vendas.

É possível rotular as colunas de agregação. Se nenhum rótulo for especificado na cláusula label, o rótulo de uma coluna produzido como resultado da rotação será composto pela lista de valores das colunas dinâmicas, pelo tipo de agregação (min, max, sum, ...) e pelo rótulo da coluna agregada. Por exemplo, "Eng,12:00:00 sum Salário". Se apenas uma agregação for especificada na cláusula select, a parte da agregação será removida do rótulo e somente a lista de valores nas colunas dinâmicas será mantida. Por exemplo, "Eng,12:00:00". Quando uma cláusula label especifica um rótulo para uma coluna de agregação, o rótulo solicitado é anexado à lista de valores, tanto quando há apenas uma agregação na cláusula select quanto quando há mais de uma. Por exemplo, label sum(salary) "sumsal" resultará nos rótulos de coluna "Eng,12:00:00 sumsal", "Eng,13:00:00 sumsal" etc.

Ordenar por

A cláusula order by é usada para classificar as linhas pelos valores nas colunas especificadas.

Os itens em uma cláusula order by podem ser identificadores de coluna ou a saída de funções de agregação, funções escalares ou operadores.

Exemplos:

order by dept, salary desc

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

Limite

A cláusula limit é usada para limitar o número de linhas retornadas.

Exemplo:

limit 100

Deslocamento

A cláusula offset é usada para pular um determinado número de primeiras linhas. Se uma cláusula limit for usada, offset será aplicada primeiro: por exemplo, limit 15 offset 30 retorna as linhas 31 a 45.

Exemplos:

offset 10
limit 30 offset 210

Marcador

A cláusula label é usada para definir o rótulo de uma ou mais colunas. Observe que não é possível usar um valor de rótulo no lugar de um ID em uma consulta.

Os itens em uma cláusula label podem ser identificadores de coluna ou a saída de funções de agregação, funções escalares ou operadores.

Sintaxe:

label column_id label_string [,column_id label_string]

column_id

O identificador da coluna que está recebendo o rótulo.

label_string

O rótulo a ser atribuído à coluna. Muitas visualizações usam o rótulo da coluna como texto a ser exibido ao usuário final, como um rótulo de legenda em um gráfico de pizza. Os rótulos são literais de string e seguem essas regras de sintaxe.

Exemplo:

O exemplo a seguir define o rótulo da coluna dept como "Departamento", o rótulo da coluna de nome como "Nome do funcionário" e o rótulo da coluna de localização como "Local do funcionário":

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

Formato

A cláusula format é usada para especificar um valor formatado para células em uma ou mais colunas. Os dados retornados devem incluir um valor real e um valor formatado para cada célula em uma coluna formatada. Muitas visualizações usam o valor não formatado para cálculos, mas o valor formatado para exibição. Os padrões especificados nessa cláusula geralmente são retornados na propriedade pattern das colunas correspondentes.

Sintaxe de padrão:

number, date, timeofday, datetime

Os padrões de data e número definidos pela ICU.

boolean

O padrão é um string no formato 'value-if-true:value-if-false'.

Exemplo:

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

Opções

A cláusula options é usada para controlar outras opções para a execução da consulta. As palavras-chave possíveis que podem seguir a cláusula options são:

• no_format: remove valores formatados do resultado e deixa apenas os valores subjacentes. Pode ser usado quando a visualização específica não usa os valores formatados para reduzir o tamanho da resposta.
• no_values Remove valores subjacentes do resultado e deixa apenas os valores formatados. Pode ser usado quando a visualização específica usa apenas os valores formatados para reduzir o tamanho da resposta.

Funções de manipulação de dados

Há vários tipos de operadores e funções que permitem manipular ou agregar dados em uma única coluna, ou comparar ou combinar dados em colunas. Os exemplos incluem sum() (para somar todos os valores em uma coluna), max (para encontrar o maior valor em uma coluna) e + (para somar os valores de duas colunas na mesma linha).

Algumas funções podem aparecer em qualquer cláusula, enquanto outras podem aparecer em um subconjunto de cláusulas. Isso está documentado abaixo.

Exemplo:

select upper(name), year(startDate)

As seguintes funções de manipulação de dados são definidas pela linguagem de consulta da API de visualização do Google:

• Funções de agregação
• Funções escalares
• Operadores aritméticos

Funções de agregação

As funções de agregação recebem um único identificador de coluna e executam uma ação em todos os valores de cada grupo. Os grupos são especificados por cláusulas group by ou pivot ou por todas as linhas, se essas cláusulas não forem usadas.

Exemplos:

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.

As funções de agregação podem ser usadas em cláusulas select, order by, label, format. Elas não podem aparecer em cláusulas where, group by, pivot, limit, offset ou options.

Confira as funções de agregação compatíveis:

Observação:as funções de agregação só podem usar um identificador de coluna como argumento:

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

Funções escalares

As funções escalares operam em zero ou mais parâmetros para produzir outro valor. As funções escalares podem ser transmitidas por qualquer expressão avaliada para o parâmetro do tipo apropriado. Esses tipos são os tipos definidos na seção Literais deste documento, que podem ser um pouco diferentes dos objetos JavaScript com nomes semelhantes.

Observe que o nome da coluna será alterado ao envolvê-la com uma função escalar.

As funções escalares podem assumir como parâmetro qualquer coisa que seja avaliada como um único valor:

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

As funções escalares podem ser usadas em qualquer uma das seguintes cláusulas: select, where, group by, pivot, order by, label, e format.

Operadores aritméticos

É possível usar operadores aritméticos para realizar operações matemáticas em qualquer item avaliado como número único (ou seja, a saída de funções de agregação, operadores ou constantes adequados).

Exemplos:

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

Os seguintes operadores são definidos:

Elementos de idioma

Literais

Literais são valores usados para comparações ou atribuições. Os literais podem ser strings, números, valores booleanos ou vários tipos de data/hora. Veja alguns exemplos de literais usados na sintaxe de consulta:

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

Aqui estão os formatos para cada tipo de literal:

string

Um literal string precisa ser colocado entre aspas simples ou duplas. Exemplos:"fourteen" 'hello world' "It's raining".

number

Os literais numéricos são especificados em notação decimal. Exemplos: 3  3.0  3.14  -71  -7.2  .6

boolean

Os literais booleanos são true ou false.

date

Use a palavra-chave date seguida por um literal string no formato yyyy-MM-dd. Exemplo:date "2008-03-18".

timeofday

Use a palavra-chave timeofday seguida de um literal string no formato HH:mm:ss[.SSS]. Exemplo: timeofday "12:30:45".

datetime

Uma data e hora, usando a palavra-chave datetime ou timestamp seguida de um literal string no formato yyyy-MM-dd HH:mm:ss[.sss]. Exemplo:datetime '2008-03-18 12:30:34.123'

Identificadores

Os identificadores (ou IDs) são strings de texto que identificam colunas.

Importante:se o identificador

• Tem espaços,
• É uma palavra reservada,
• contenha qualquer coisa, exceto caracteres alfanuméricos ou sublinhados ([a-zA-Z0-9_]); ou
• Começa com um dígito

ela precisa estar entre aspas simples (e não aspas simples).

Caso contrário, o identificador não precisará ser citado. Nem todas as palavras-chave definidas pela sintaxe são palavras reservadas. Portanto, por exemplo, você pode usar "máximo" como identificador sem precisar colocá-lo em aspas.

Exemplos:col1   employee_table   `start date`   `7 days traffic`   `select`

Recomendamos não escolher um identificador que exija aspas, porque pode ser fácil se esquecer de usá-las ou de usar acidentalmente "aspas simples" em vez de "acentos graves". Esses são erros comuns e geralmente difíceis de depurar.

Diferenciação entre maiúsculas e minúsculas

Identificadores e literais de string diferenciam maiúsculas de minúsculas. Todos os outros elementos de linguagem são indiferentes a maiúsculas.

Palavras reservadas

As seguintes palavras reservadas precisam estar entre aspas se forem usadas como identificador:

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