El lenguaje de consulta de la API de visualización de Google te permite manipular los datos varias veces con la consulta a la fuente de datos.

Índice

Introducción

Por lo general, las visualizaciones esperan datos de alguna forma específica. Por ejemplo, un gráfico circular puede esperar datos en dos columnas: una etiqueta de texto y un valor numérico. Es posible que los datos de la fuente de datos no coincidan exactamente con esta estructura. Por ejemplo, la fuente de datos puede tener más de dos columnas o el orden de las columnas puede no coincidir con el orden que espera el gráfico circular.

El lenguaje de consulta permite enviar solicitudes de manipulación y formateo de datos a la fuente de datos, además de garantizar que la estructura y el contenido de los datos que se muestran coincidan con la estructura esperada.

La sintaxis del lenguaje de consulta es similar a SQL. Los desarrolladores familiarizados con SQL deberían poder aprender y usar rápidamente este lenguaje de consulta. Hay muchos instructivos de SQL disponibles en la Web. Hay algunas diferencias entre este lenguaje de consulta y SQL, que se describen en la sección de syntax.

Ten en cuenta que las fuentes de datos no son necesarias para implementar el lenguaje de consulta o, si lo hacen, para implementar todas las funciones del lenguaje. A menos que tengas razones para creer lo contrario, no deberías depender de una fuente de datos para implementar todas las funciones de este lenguaje.

Usa el lenguaje de consulta

Puedes adjuntar una cadena de consulta a una solicitud de fuente de datos de dos maneras: configurando la cadena de consulta desde el código JavaScript o configurando la cadena de consulta como un parámetro en la URL de la fuente de datos. Si tu solicitud no incluye una cadena de consulta, el comportamiento predeterminado de una fuente de datos consiste en mostrar todas las filas y columnas con su orden y formato predeterminados de filas y columnas. Puedes cambiar eso si incluyes una cadena de consulta en tu solicitud a una fuente de datos.

Cómo configurar la consulta desde JavaScript

Para establecer la cadena de consulta desde el código JavaScript, llama al método setQuery de la clase google.visualization.Query.

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

Cómo configurar la consulta en la URL de la fuente de datos

La cadena de consulta se puede agregar a la URL de la fuente de datos con el parámetro tq. Si configuras la consulta en el parámetro de URL en lugar de en JavaScript, podrás usar con facilidad las visualizaciones escritas por otros desarrolladores y, aun así, personalizar la consulta.

La cadena de consulta debe estar codificada correctamente como un parámetro de URL. Puedes codificar una URL con la función encodeURIComponent de JavaScript o de forma manual con la herramienta de codificación que aparece al final de esta sección.

Ejemplo:

Considera la siguiente cadena de consulta para una hoja de cálculo de Google. (Ten en cuenta que los ID de columna en las hojas de cálculo siempre son letras; el texto de encabezado de las columnas que se muestra en la hoja de cálculo publicada son las etiquetas, no los ID. Debes usar el ID, no la etiqueta, en la cadena de consulta).

select A, sum(B) group by A

Cuando se codifica, esta consulta se convierte en lo siguiente:

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

Supongamos que esta es la URL de tu hoja de cálculo:

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

Agrega /gviz/tq?tq=YOUR_QUERY_STRING a la URL de la hoja de cálculo para obtener la cadena 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

Usa la siguiente herramienta para codificar o decodificar una cadena de consulta:

Sintaxis del lenguaje

Descripción general

La sintaxis del lenguaje de consulta de la API de visualización de Google está diseñada para ser similar a la sintaxis de SQL. Sin embargo, es un subconjunto de SQL, con algunas características propias que deberás aprender. Si estás familiarizado con SQL, no debería ser muy difícil de aprender.

Tabla usada en todos los ejemplos:

En esta sección, todos los ejemplos de consultas hacen referencia a la siguiente tabla. Los encabezados de las columnas son los identificadores de columna.

Cláusulas de idioma

La sintaxis del lenguaje de consulta consta de las siguientes cláusulas. Cada cláusula comienza con una o dos palabras clave. Todas las cláusulas son opcionales. Las cláusulas están separadas por espacios. El orden de las cláusulas debe ser el siguiente:

Seleccionar

La cláusula select se usa para especificar las columnas que se mostrarán y su orden. Si no se especifica esta cláusula, o si se usa select *, se muestran todas las columnas de la tabla de fuente de datos en su orden original. Se hace referencia a las columnas mediante los identificadores (no las etiquetas). Por ejemplo, en una hoja de cálculo de Google, los identificadores de columna son la letra de columna de uno o dos caracteres (A, B, C, ...).

Los elementos de una cláusula select pueden ser identificadores de columna o el resultado de funciones de agregación, funciones escalares o operadores.

Ejemplos:

select *

select dept, salary

select max(salary)

En el siguiente ejemplo, las comillas hacia atrás se usan para hacer referencia a los ID de columna que contienen espacios (dirección de correo electrónico) o que son palabras reservadas (fecha):

select `email address`, name, `date`

Ejecuta la siguiente consulta en la tabla de ejemplo:

select lunchTime, name

Muestra la siguiente respuesta:

Dónde

La cláusula where se usa para mostrar solo las filas que coinciden con una condición específica.

Los operadores de comparación simples son <=, <, >, >=, =, !=, <>. Ambos operadores de comparación != <> significan no igual. Las cadenas se comparan por valor lexicográfico. Ten en cuenta que la igualdad se indica mediante =, no con ==, como en la mayoría de los lenguajes de programación. La comparación con null se realiza usando is null o is not null.

Puedes unir varias condiciones con los operadores lógicos and, or y not. Los paréntesis se pueden usar para definir la prioridad explícita.

La cláusula WHERE también admite algunos operadores de comparación de cadenas más complejos. Estos operadores toman dos strings como argumentos. Cualquier argumento que no sea una string (por ejemplo, fechas o números) se convertirá en strings antes de la comparación. La coincidencia de strings distingue entre mayúsculas y minúsculas (puedes usar las funciones escalares upper() o lower() para solucionar esto).

• contains: Es una coincidencia de subcadena. enter contains part es verdadero si part se encuentra en todo. Ejemplo: where name contains 'John' coincide con "John", "John Adams", "Long John Silver", pero no "john adams".
• starts with: Es una coincidencia de prefijo. value starts with prefix es verdadero si prefix está al principio de value. Ejemplos:where dept starts with 'engineering' coincide con "ingeniería" y "gerente de ingeniería". where dept starts with 'e' coincide con "engineering", "eng" y "e".
• ends with: Es una coincidencia de sufijo. value ends with suffix es verdadero si suffix está al final de value. Ejemplo: where role ends with 'y' coincide con "cowboy", "boy" y "y".
• matches: Es una coincidencia de expresión regular (preg). haystack matches needle es verdadero si la expresión regular en needle coincide con haystack. Ejemplos: where country matches '.*ia' coincide con India y Nigeria, pero no con Indiana. Ten en cuenta que esta no es una búsqueda global, por lo que where country matches 'an' no coincidirá con “Canadá”.
• like: Es una búsqueda de texto que admite dos comodines: %, que coincide con cero o más caracteres de cualquier tipo, y _ (guion bajo), que coincide con cualquier carácter. Esto es similar al operador SQL LIKE. Ejemplo: where name like fre% coincide con "fre", "fred" y "freddy".

Ejemplos:

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

Ejecuta la siguiente consulta en la tabla de ejemplo:

select name where salary > 700

Muestra la siguiente respuesta:

Agrupar por

La cláusula group by se usa para agregar valores en todas las filas. Se crea una sola fila para cada combinación distinta de valores en la cláusula group-by. Los datos se ordenan automáticamente según las columnas de agrupación, a menos que se especifique lo contrario en una cláusula order by.

Nota: Si usas una cláusula group by, cada columna enumerada en la cláusula select debe estar enumerada en la cláusula group by o estar unida por una función de agregación.

Ejemplos:

select dept, max(salary) group by dept

Ejecuta la siguiente consulta en la tabla de ejemplo:

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

Muestra la siguiente respuesta:

Tabla dinámica

La cláusula pivot se usa para transformar valores distintos de columnas en columnas nuevas. Por ejemplo, una dinamización por una columna "año" produciría una tabla con una columna para cada año distinto que aparece en la tabla original. Esto podría ser útil, por ejemplo, si una visualización de gráfico de líneas dibuja cada columna como una línea separada. Si deseas dibujar una línea separada para cada año y “año” es una de las columnas de la tabla original, una buena opción sería usar una operación dinámica para realizar la transformación de datos necesaria.

Nota: Si usas una cláusula pivot, cada columna enumerada en la cláusula select debe estar enumerada en la cláusula group by o estar unida por una función de agregación

Debido a que varias filas pueden contener los mismos valores para las columnas dinámicas, pivot implica agregación. Ten en cuenta que, cuando se usa pivot sin group by, la tabla de resultados contendrá exactamente una fila. Por ejemplo, si ejecutas la siguiente consulta en la tabla de ejemplo:

select sum(salary) pivot dept

Muestra la siguiente respuesta:

Esto se debe a que 2,100 es la suma de los salarios del departamento de Ingeniería, 800 del departamento de Marketing, etcétera.

Usar pivot junto con group by puede ser aún más útil, ya que crea una tabla en la que cada celda contiene el resultado de la agregación de la fila y la columna pertinentes. Por ejemplo, si ejecutas la siguiente consulta en la tabla de ejemplo:

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

Muestra la siguiente respuesta:

También puedes “invertir” esta tabla cambiando las columnas y las filas si alternas entre las columnas pivot y group by. Ejecuta la siguiente consulta en la tabla de ejemplo:

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

Muestra la siguiente respuesta:

También puedes usar más de una columna en la cláusula pivot. En ese caso, las columnas de la tabla de respuesta se componen de todas las combinaciones únicas de valores en las columnas que existen en la tabla original. Por ejemplo, si ejecutas la siguiente consulta en la tabla de ejemplo:

select sum(salary)
  pivot dept, lunchTime

Muestra la siguiente respuesta:

Ten en cuenta que solo las combinaciones que aparecen en la tabla original reciben columnas en la tabla de respuesta. Por eso, no hay una columna para Marketing,12:00:00 ni para Ventas, 13:00:00.

También es posible usar más de una agregación. Por ejemplo, si ejecutas la siguiente consulta en la tabla de ejemplo:

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

Muestra la siguiente respuesta:

Puedes combinar varias agregaciones en la cláusula select, varias columnas en la cláusula group by y varias columnas en la cláusula pivot. De forma interna, la agregación se realiza mediante la concatenación de las columnas en las cláusulas pivot y group by.

Es posible que las columnas especificadas en la cláusula pivot no aparezcan en las cláusulas select, group by o order by. Cuando se usa pivot, la cláusula order by no puede contener ninguna columna de agregación. Esto se debe a que, para cada agregación especificada en la cláusula select, se generan muchas columnas en la tabla de resultados. Sin embargo, puedes formatear las columnas de agregación cuando se usa pivot. El resultado de este formato es que todas las columnas nuevas relevantes para la agregación específica, que genera la operación dinámica, reciben el formato del patrón especificado. En el ejemplo anterior, agregar format sum(salary) "some_format_string" afectará las siguientes columnas: “Eng sum-salary”, “Marketing sum-salary” (salario de suma de marketing) y Sales sum-salary.

Puedes etiquetar las columnas de agregación. Si no se especifica ninguna etiqueta en la cláusula label, la etiqueta de una columna que se produce como resultado de la dinamización se compone de la lista de valores en las columnas dinámicas, el tipo de agregación (min, máximo, suma, ...) y la etiqueta de la columna agregada. Por ejemplo, “Ing,12:00:00 sum Salary”. Si solo se especificó una agregación en la cláusula select, la parte de agregación se quita de la etiqueta y solo se conserva la lista de valores en las columnas dinámicas. Por ejemplo, “Eng,12:00:00”. Cuando una cláusula label especifica una etiqueta para una columna de agregación, la etiqueta solicitada se agrega a la lista de valores, tanto cuando hay una sola agregación en la cláusula select, y cuando hay más de una. Por ejemplo, label sum(salary) "sumsal" mostrará las etiquetas de columna “Eng,12:00:00 sumsal”, “Eng,13:00:00 sumsal”, etc.

Ordenar por

La cláusula order by se usa para ordenar las filas según los valores en las columnas especificadas.

Los elementos en una cláusula order by pueden ser identificadores de columna o el resultado de funciones de agregación, funciones escalares o operadores.

Ejemplos:

order by dept, salary desc

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

Límite

La cláusula limit se usa para limitar la cantidad de filas que se muestran.

Ejemplo:

limit 100

Desfase

La cláusula offset se usa para omitir una cantidad determinada de primeras filas. Si se usa una cláusula limit, offset se aplica primero, por ejemplo, limit 15 offset 30 muestra las filas 31 a 45.

Ejemplos:

offset 10
limit 30 offset 210

Etiqueta

La cláusula label se usa con el fin de configurar la etiqueta para una o más columnas. Ten en cuenta que no puedes usar un valor de etiqueta en lugar de un ID en una consulta.

Los elementos de una cláusula label pueden ser identificadores de columna o el resultado de funciones de agregación, funciones escalares o operadores.

Sintaxis:

label column_id label_string [,column_id label_string]

column_id

: Es el identificador de la columna a la que se le asigna la etiqueta.

label_string

La etiqueta que se asignará a esa columna. Muchas visualizaciones usan la etiqueta de la columna como texto para mostrársela al usuario final; por ejemplo, una etiqueta de leyenda en un gráfico circular. Las etiquetas son literales de string y siguen esas reglas de sintaxis.

Ejemplo:

En el siguiente ejemplo, se establece la etiqueta de la columna de departamento en “Departamento”, la etiqueta de la columna de nombre en “Nombre del empleado” y la etiqueta de la columna de ubicación en “Ubicación de los empleados”:

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

Formato

La cláusula format se usa con el fin de especificar un valor con formato para las celdas en una o más columnas. Los datos que se muestran deben incluir un valor real y un valor con formato para cada celda de una columna con formato. Muchas visualizaciones usan el valor sin formato para los cálculos, pero el valor con formato para la visualización. Los patrones que especificas en esta cláusula se suelen mostrar en la propiedad pattern de las columnas correspondientes.

Sintaxis del patrón:

number, date, timeofday y datetime

Los patrones de fecha y número definidos por la ICU.

boolean

El patrón es un string en el formato “value-if-true:value-if-false”.

Ejemplo:

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

Opciones

La cláusula options se usa con el fin de controlar opciones adicionales para la ejecución de la consulta. Las palabras clave posibles que pueden seguir la cláusula options son las siguientes:

• no_format Quita los valores con formato del resultado y deja solo los valores subyacentes. Se puede usar cuando la visualización específica no usa los valores con formato para reducir el tamaño de la respuesta.
• no_values Quita los valores subyacentes del resultado y deja solo los valores con formato. Se puede usar cuando la visualización específica usa solo los valores con formato para reducir el tamaño de la respuesta.

Funciones de manipulación de datos

Existen varios tipos de operadores y funciones que te permiten manipular o agregar datos en una sola columna, o comparar o combinar datos entre columnas. Entre los ejemplos, se incluyen sum() (para sumar todos los valores de una columna), max (para encontrar el valor más grande de una columna) y + (para sumar los valores de dos columnas juntas en la misma fila).

Algunas funciones pueden aparecer en cualquier cláusula; otras pueden aparecer en un subconjunto de cláusulas. Esto se documenta a continuación.

Ejemplo:

select upper(name), year(startDate)

El lenguaje de consulta de la API de visualización de Google define las siguientes funciones de manipulación de datos:

• Funciones de agregación
• Funciones escalares
• Operadores aritméticos

Funciones de agregación

A las funciones de agregación se les pasa un identificador de una sola columna y realizan una acción en todos los valores de cada grupo (los grupos se especifican con cláusulas group by o pivot, o todas las filas si no se usan esas cláusulas).

Ejemplos:

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.

Las funciones de agregación se pueden usar en las cláusulas select, order by, label y format. No pueden aparecer en las cláusulas where, group by, pivot, limit, offset ni options.

Estas son las funciones de agregación compatibles:

Nota: Las funciones de agregación solo pueden tomar un identificador de columna 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.

Funciones escalares

Las funciones escalares operan sobre cero o más parámetros para producir otro valor. Las funciones escalares se pueden pasar cualquier expresión que se evalúe como el parámetro del tipo adecuado. Ten en cuenta que estos tipos son los tipos definidos en la sección Literales de este documento, que pueden ser levemente diferentes de los objetos JavaScript con nombres similares.

Ten en cuenta que el nombre de la columna se cambiará uniéndolo a una función escalar.

Las funciones escalares pueden tomar como parámetro cualquier cosa que se evalúe como un solo valor:

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

Las funciones escalares se pueden usar en cualquiera de las siguientes cláusulas: select, where, group by, pivot, order by, label, y format.

Operadores aritméticos

Puedes usar operadores aritméticos para realizar operaciones matemáticas sobre cualquier elemento que se evalúe como un solo número (es decir, el resultado de las funciones, los operadores o las constantes de agregación apropiadas).

Ejemplos:

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

Se definen los siguientes operadores:

Elementos del lenguaje

Literales

Los literales son valores que se usan para comparaciones o asignaciones. Los literales pueden ser string, números, valores booleanos o varios tipos de fecha y hora. Estos son algunos ejemplos de literales usados en la sintaxis 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

Estos son los formatos para cada tipo de literal:

string

Un literal de string debe encerrarse entre comillas simples o dobles. Ejemplos: "fourteen" 'hello world' "It's raining".

number

Los literales numéricos se especifican en notación decimal. Ejemplos: 3  3.0  3.14  -71  -7.2  .6

boolean

Los literales booleanos son true o false.

date

Usa la palabra clave date seguida de un literal string en el formato yyyy-MM-dd. Ejemplo: date "2008-03-18".

timeofday

Usa la palabra clave timeofday seguida de un literal de string en el formato HH:mm:ss[.SSS] Ejemplo: timeofday "12:30:45".

datetime

Una fecha y una hora, con la palabra clave datetime o la palabra clave timestamp seguida de un literal de string en el formato yyyy-MM-dd HH:mm:ss[.sss]. Ejemplo: datetime '2008-03-18 12:30:34.123'

Identificadores

Los identificadores (o IDs) son string de texto que identifican columnas.

Importante: Si tu identificador

• Tiene espacios
• Es una palabra reservada,
• Contener cualquier elemento, menos caracteres alfanuméricos o guiones bajos ([a-zA-Z0-9_])
• Comienza con un dígito

debe estar entre comillas simples (no entre comillas simples).

De lo contrario, no es necesario que el identificador entre comillas. (Ten en cuenta que no todas las palabras clave que se definen en la sintaxis son palabras reservadas; por lo tanto, por ejemplo, puedes usar "max" como identificador, sin tener que escribirlo entre comillas).

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

No recomendamos elegir un identificador que requiera comillas inversas, ya que puede ser fácil olvidarse de usar las comillas hacia atrás o usar accidentalmente "comillas simples" en lugar de "comillas inversas". Estos son errores comunes y, a menudo, difíciles de depurar.

Distinción entre mayúsculas y minúsculas

Los identificadores y los literales de cadena distinguen mayúsculas de minúsculas. Los demás elementos del lenguaje no distinguen mayúsculas de minúsculas.

Palabras reservadas

Las siguientes palabras reservadas deben ir entre comillas si se usan como identificador:

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