Il linguaggio di query dell'API Google Visualization consente di eseguire varie manipolazioni dei dati con la query all'origine dati.

Indice

Introduzione

In genere, le visualizzazioni prevedono che i dati abbiano una forma specifica. Ad esempio, un grafico a torta potrebbe prevedere i dati sotto forma di due colonne: un'etichetta di testo e un valore numerico. I dati all'interno dell'origine dati potrebbero non corrispondere esattamente a questa struttura. Ad esempio, l'origine dati potrebbe avere più di due colonne oppure l'ordine delle colonne potrebbe non corrispondere a quello previsto dal grafico a torta.

Il linguaggio di query consente di inviare richieste di manipolazione e formattazione dei dati all'origine dati e di garantire che la struttura e i contenuti dei dati restituiti corrispondano alla struttura prevista.

La sintassi del linguaggio di query è simile a SQL. Gli sviluppatori che hanno familiarità con SQL devono essere in grado di imparare e utilizzare rapidamente questo linguaggio di query. Sul web sono disponibili molti tutorial SQL. Esistono alcune differenze tra questo linguaggio di query e SQL, descritte nella sezione sulla syntax.

Tieni presente che le origini dati non sono necessarie per implementare il linguaggio di query o, in caso affermativo, per implementare tutte le funzionalità del linguaggio. A meno che tu non abbia motivo di credere diversamente, non devi dipendere da un'origine dati per implementare tutte le funzionalità di questo linguaggio.

Utilizzare il linguaggio di query

Puoi collegare una stringa di query a una richiesta dell'origine dati in due modi: impostando la stringa di query dal codice JavaScript oppure impostandola come parametro nell'URL origine dati. Se la tua richiesta non include una stringa di query, il comportamento predefinito per un'origine dati è restituire tutte le righe e le colonne utilizzando l'ordine e la formattazione predefiniti per riga/colonna. Per modificarlo, includi una stringa di query nella richiesta a un'origine dati.

Impostazione della query da JavaScript

Per impostare la stringa di query dall'interno del codice JavaScript, chiama il metodo setQuery della 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);

Impostazione della query nell'URL origine dati

La stringa di query può essere aggiunta all'URL dell'origine dati utilizzando il parametro tq. L'impostazione della query nel parametro URL anziché in JavaScript ti consente di utilizzare facilmente visualizzazioni scritte da altri sviluppatori e di poter comunque personalizzare la query.

La stringa di query deve essere codificata correttamente come parametro URL. Puoi codificare un URL utilizzando la funzione encodeURIComponent di JavaScript oppure puoi codificarlo a mano utilizzando lo strumento di codifica alla fine di questa sezione.

Esempio:

Considera la seguente stringa di query per un foglio di lavoro Google. Tieni presente che gli ID colonna nei fogli di lavoro sono sempre lettere; il testo dell'intestazione di colonna mostrato nel foglio di lavoro pubblicato sono etichette, non ID. Devi utilizzare l'ID, non l'etichetta, nella stringa di query.)

select A, sum(B) group by A

Se codificata, questa query diventa:

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

Supponiamo che questo sia l'URL del tuo foglio di lavoro:

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

Aggiungi /gviz/tq?tq=YOUR_QUERY_STRING all'URL del foglio di lavoro per ottenere la stringa di query finale:

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

Utilizza lo strumento seguente per codificare o decodificare una stringa di query:

Sintassi della lingua

Panoramica

La sintassi del linguaggio di query dell'API Google Visualization è simile a quella SQL. Tuttavia, è un sottoinsieme di SQL, con alcune funzionalità proprie che dovrai conoscere. Se hai familiarità con SQL, non dovrebbe essere troppo difficile da imparare.

Tabella utilizzata in tutti gli esempi:

In questa sezione, tutti gli esempi di query fanno riferimento alla seguente tabella. Le intestazioni di colonna sono gli identificatori di colonna.

Clausole linguistiche

La sintassi del linguaggio di query è composta dalle seguenti clausole. Ogni clausola inizia con una o due parole chiave. Tutte le clausole sono facoltative. Le clausole sono separate da spazi. L'ordine delle clausole deve essere il seguente:

Seleziona

La clausola select viene utilizzata per specificare le colonne da restituire e il relativo ordine. Se questa clausola non viene specificata o se viene utilizzato select *, vengono restituite tutte le colonne della tabella dell'origine dati, nell'ordine originale. Le colonne sono indicate dagli identificatori (non dalle etichette). Ad esempio, in un foglio di lavoro Google, gli identificatori di colonna sono le lettere di colonna di uno o due caratteri (A, B, C, ...).

Gli elementi in una clausola select possono essere identificatori di colonna o l'output di funzioni di aggregazione, funzioni scalabili o operatori.

Esempi:

select *

select dept, salary

select max(salary)

Nell'esempio seguente, le virgolette vengono utilizzate per fare riferimento agli ID di colonna che contengono spazi (indirizzo email) o che sono parole riservate (data):

select `email address`, name, `date`

Esecuzione della seguente query sulla tabella di esempio:

select lunchTime, name

Restituisce la seguente risposta:

Dove

La clausola where viene utilizzata per restituire solo le righe che corrispondono a una condizione specificata.

Gli operatori di confronto semplici sono <=, <, >, >=, =, !=, <>. Entrambi gli operatori di confronto != <> significano non uguale. Le stringhe vengono confrontate in base al valore lessicografico. Tieni presente che l'uguaglianza è indicata da =, non da == come nella maggior parte dei linguaggi per computer. Il confronto con null viene eseguito utilizzando is null o is not null.

Puoi unire più condizioni utilizzando gli operatori logici and, or e not. Le parentesi possono essere utilizzate per definire la precedenza esplicita.

La clausola WHERE supporta anche alcuni operatori di confronto tra stringhe più complessi. Questi operatori prendono due stringhe come argomenti. Eventuali argomenti non stringa (ad esempio date o numeri) verranno convertiti in stringhe prima del confronto. La corrispondenza delle stringhe è sensibile alle maiuscole (puoi utilizzare le funzioni scalabili upper() o lower() per risolvere il problema).

• contains: corrispondenza di una sottostringa. whole contains part è true se part è in qualsiasi punto all'interno di whole. Esempio: where name contains 'John' corrisponde a "Mario", "John Adams", "Long John Silver" ma non a "john adams".
• starts with: una corrispondenza del prefisso. value starts with prefisso è vero se prefisso è all'inizio di valore. Esempi: where dept starts with 'engineering' corrisponde a "engineering" e "engineering responsabili". where dept starts with 'e' corrisponde a "engineering", "eng" ed "e".
• ends with: corrispondenza di un suffisso. value ends with suffisso è true se suffisso è alla fine di value. Esempio: where role ends with 'y' corrisponde a "cowboy", "boy" e "y".
• matches: corrispondenza con espressione regolare (preg). haystack matches needle è true se l'espressione regolare in needle corrisponde a haystack. Esempi: where country matches '.*ia' corrisponde a India e Nigeria, ma non in Indiana. Tieni presente che non si tratta di una ricerca globale, pertanto where country matches 'an' non corrisponderà a "Canada".
• like: una ricerca testuale che supporta due caratteri jolly: %, che corrisponde a zero o più caratteri di qualsiasi tipo, e _ (trattino basso), che corrisponde a qualsiasi carattere. È simile all'operatore SQL LIKE. Esempio: where name like fre% corrisponde a "fre", "fred" e "freddy".

Esempi:

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

Esecuzione della seguente query sulla tabella di esempio:

select name where salary > 700

Restituisce la seguente risposta:

Raggruppa per

La clausola group by viene utilizzata per aggregare i valori nelle righe. Viene creata una singola riga per ogni combinazione distinta di valori nella clausola di raggruppamento. I dati vengono ordinati automaticamente in base alle colonne di raggruppamento, se non diversamente specificato da una clausola order by.

Nota:se utilizzi una clausola group by, ogni colonna elencata nella clausola select deve essere elencata nella clausola group by oppure deve essere inclusa in una funzione di aggregazione.

Esempi:

select dept, max(salary) group by dept

Esecuzione della seguente query sulla tabella di esempio:

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

Restituisce la seguente risposta:

Pivot

La clausola pivot viene utilizzata per trasformare i valori distinti delle colonne in nuove colonne. Ad esempio, un pivot basato sulla colonna "anno" produrrà una tabella con una colonna per ogni anno che viene visualizzato nella tabella originale. Questo potrebbe essere utile se, ad esempio, la visualizzazione di un grafico a linee disegna ogni colonna come una linea separata. Se vuoi tracciare una linea separata per ogni anno e "year" è una delle colonne della tabella originale, una buona opzione è quella di utilizzare un'operazione pivot per eseguire la trasformazione necessaria dei dati.

Nota: se utilizzi una clausola pivot, ogni colonna elencata nella clausola select deve essere elencata nella clausola group by o essere aggregata da una funzione di aggregazione.

Poiché più righe possono contenere gli stessi valori per le colonne pivot, il pivot implica l'aggregazione. Tieni presente che, se utilizzi pivot senza utilizzare group by, la tabella dei risultati conterrà esattamente una riga. Ad esempio, se esegui la seguente query sulla tabella di esempio:

select sum(salary) pivot dept

Restituisce la seguente risposta:

Questo perché 2100 è la somma degli stipendi per il dipartimento tecnico, 800 per il reparto marketing ecc.

L'utilizzo di pivot insieme a group by può essere ancora più utile, in quanto crea una tabella in cui ogni cella contiene il risultato dell'aggregazione per la riga e la colonna pertinenti. Ad esempio, se esegui la seguente query sulla tabella di esempio:

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

Restituisce la seguente risposta:

Puoi anche "invertire" questa tabella, alternando colonne e righe, passando dalla colonna pivot alle colonne group by e viceversa. Esecuzione della seguente query sulla tabella di esempio:

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

Restituisce la seguente risposta:

Puoi anche utilizzare più di una colonna nella clausola pivot. In questo caso, le colonne della tabella di risposta sono composte da tutte le combinazioni univoche di valori nelle colonne presenti nella tabella originale. Ad esempio, se esegui la seguente query sulla tabella di esempio:

select sum(salary)
  pivot dept, lunchTime

Restituisce la seguente risposta:

Tieni presente che solo le combinazioni che appaiono nella tabella originale ricevono colonne nella tabella delle risposte. Ecco perché non c'è una colonna per Marketing,12:00:00 o per Vendite,13:00:00.

È anche possibile utilizzare più di un'aggregazione. Ad esempio, se esegui la seguente query sulla tabella di esempio:

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

Restituisce la seguente risposta:

Puoi combinare più aggregazioni nella clausola select, più colonne nella clausola group by e più colonne nella clausola pivot. Internamente, l'aggregazione viene eseguita mediante la concatenazione delle colonne nelle clausole Raggruppa per e pivot.

Le colonne specificate nella clausola pivot non possono essere visualizzate nelle clausole select, group by o order by. Quando si utilizza pivot, la clausola order by non può contenere colonne di aggregazione. Il motivo è che per ogni aggregazione specificata nella clausola select, vengono generate molte colonne nella tabella dei risultati. Tuttavia, puoi formattare le colonne di aggregazione quando si utilizza pivot. Il risultato di questo formato è che tutte le nuove colonne pertinenti all'aggregazione specifica, che vengono generate dall'operazione pivot, sono formattate in base al pattern specificato. Nell'esempio precedente, l'aggiunta di format sum(salary) "some_format_string" inciderà sulle seguenti colonne: somma-stipendio ingegneristico, somma-stipendio marketing e stipendio vendite somma-sala.

Puoi etichettare le colonne di aggregazione. Se nella clausola label non viene specificata alcuna etichetta, l'etichetta di una colonna prodotta come risultato del pivot è composta dall'elenco di valori nelle colonne pivot, dal tipo di aggregazione (min, max, somma e così via) e dall'etichetta della colonna aggregata. Ad esempio "Ing,12:00:00 somma stipendio". Se è stata specificata una sola aggregazione nella clausola select, la parte dell'aggregazione viene rimossa dall'etichetta e viene conservato solo l'elenco dei valori nelle colonne pivot. Ad esempio "Eng,12:00:00". Quando una clausola label specifica un'etichetta per una colonna di aggregazione, l'etichetta richiesta viene aggiunta all'elenco di valori, sia quando esiste una sola aggregazione nella clausola select sia quando è presente più di una. Ad esempio, label sum(salary) "sumsal" restituirà le etichette di colonna "Ing,12:00:00 somma", "Ing,13:00:00 sommale", e così via.

Ordina per

La clausola order by viene utilizzata per ordinare le righe in base ai valori nelle colonne specificate.

Gli elementi in una clausola order by possono essere identificatori di colonna o l'output di funzioni di aggregazione, funzioni scalabili o operatori.

Esempi:

order by dept, salary desc

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

Limite

La clausola limit viene utilizzata per limitare il numero di righe restituite.

Esempio:

limit 100

Offset

La clausola offset viene utilizzata per saltare un determinato numero di prime righe. Se viene utilizzata una clausola limit, viene applicato prima offset: ad esempio, limit 15 offset 30 restituisce le righe da 31 a 45.

Esempi:

offset 10
limit 30 offset 210

Etichetta

La clausola label viene utilizzata per impostare l'etichetta per una o più colonne. Tieni presente che non puoi utilizzare un valore di etichetta al posto dell'ID in una query.

Gli elementi in una clausola label possono essere identificatori di colonna o l'output di funzioni di aggregazione, funzioni scalabili o operatori.

Sintassi:

label column_id label_string [,column_id label_string]

column_id

L'identificatore della colonna a cui viene assegnata l'etichetta.

label_string

L'etichetta da assegnare alla colonna. Molte visualizzazioni utilizzano l'etichetta delle colonne come testo da mostrare all'utente finale, come l'etichetta legenda in un grafico a torta. Le etichette sono valori letterali stringa e seguono queste regole di sintassi.

Esempio:

L'esempio seguente imposta l'etichetta per la colonna dept su "Reparto", l'etichetta per la colonna del nome su "Nome dipendente" e l'etichetta per la colonna Località su "Sede dei dipendenti":

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

Formato

La clausola format viene utilizzata per specificare un valore formattato per le celle in una o più colonne. I dati restituiti devono includere sia un valore effettivo sia un valore formattato per ogni cella di una colonna formattata. Molte visualizzazioni utilizzano il valore non formattato per i calcoli, ma il valore formattato per la visualizzazione. I pattern specificati in questa clausola vengono in genere restituiti nella proprietà pattern delle colonne corrispondenti.

Sintassi dei pattern:

number, date, timeofday e datetime

I pattern per date e numero definiti dalla ICU.

boolean

Il pattern è un string nel formato "value-if-true:value-if-false".

Esempio:

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

Opzioni

La clausola options viene utilizzata per controllare le opzioni aggiuntive per l'esecuzione delle query. Le possibili parole chiave che possono seguire la clausola options sono:

• no_format Rimuove i valori formattati dal risultato e lascia solo i valori sottostanti. Può essere utilizzata quando la visualizzazione specifica non usa i valori formattati per ridurre le dimensioni della risposta.
• no_values rimuove i valori sottostanti dal risultato e lascia solo i valori formattati. Può essere utilizzata quando la visualizzazione specifica usa solo i valori formattati per ridurre le dimensioni della risposta.

Funzioni di manipolazione di dati

Esistono diversi tipi di operatori e funzioni che consentono di manipolare o aggregare i dati in una singola colonna oppure di confrontare o combinare i dati di più colonne. Gli esempi includono sum() (per aggiungere tutti i valori in una colonna), max (per trovare il valore più grande in una colonna) e + (per sommare i valori di due colonne nella stessa riga).

Alcune funzioni possono apparire in qualsiasi clausola, altre in un sottoinsieme di clausole. Ciò è documentato di seguito.

Esempio:

select upper(name), year(startDate)

Le seguenti funzioni di manipolazione dei dati sono definite dal linguaggio di query dell'API Google Visualization:

• Funzioni di aggregazione
• Funzioni scala
• Operatori aritmetici

Funzioni di aggregazione

Le funzioni di aggregazione vengono passate un singolo identificatore di colonna ed eseguono un'azione su tutti i valori di ciascun gruppo (i gruppi sono specificati dalle clausole group by o pivot oppure da tutte le righe se queste clausole non vengono utilizzate).

Esempi:

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.

Le funzioni di aggregazione possono essere utilizzate nelle clausole select, order by, label e format. Non possono essere visualizzate nelle clausole where, group by, pivot, limit, offset o options.

Di seguito sono riportate le funzioni di aggregazione supportate:

Nota: le funzioni di aggregazione possono utilizzare solo un identificatore di colonna come argomento:

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

Funzioni scala

Le funzioni scalari operano su zero o più parametri per produrre un altro valore. Per le funzioni scalari puoi passare qualsiasi espressione che restituisca il parametro del tipo appropriato. Tieni presente che questi tipi sono definiti nella sezione Literali di questo documento, che potrebbero essere leggermente diversi dagli oggetti JavaScript con nomi simili.

Tieni presente che il nome della colonna verrà modificato includendolo con una funzione scalare.

Le funzioni scalari possono utilizzare come parametro tutto ciò che restituisca un singolo valore:

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

Le funzioni scalari possono essere utilizzate in una qualsiasi delle seguenti clausole: select, where, group by, pivot, order by, label, e format.

Operatori aritmetici

Puoi utilizzare operatori aritmetici per eseguire operazioni matematiche su qualsiasi elemento che restituisca un singolo numero (ovvero l'output di funzioni, operatori o costanti aggregate appropriati).

Esempi:

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

Sono definiti i seguenti operatori:

Elementi della lingua

Valori letterali

I valori letterali sono valori utilizzati per i confronti o le assegnazioni. I valori letterali possono essere string, numeri, valori booleani o vari tipi di data/ora. Ecco alcuni esempi di valori letterali utilizzati nella sintassi delle query:

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

Ecco i formati per ciascun tipo di valore letterale:

string

Un valore letterale string deve essere racchiuso tra virgolette singole o doppie. Esempi: "fourteen" 'hello world' "It's raining".

number

I valori letterali numerici vengono specificati in notazione decimale. Esempi: 3  3.0  3.14  -71  -7.2  .6

boolean

I valori letterali booleani sono true o false.

date

Utilizza la parola chiave date seguita da un valore letterale string nel formato yyyy-MM-dd. Esempio: date "2008-03-18".

timeofday

Utilizza la parola chiave timeofday seguita da un valore letterale string nel formato HH:mm:ss[.SSS] Esempio: timeofday "12:30:45".

datetime

Una data e un'ora, utilizzando la parola chiave datetime o la parola chiave timestamp seguiti da un valore letterale string nel formato yyyy-MM-dd HH:mm:ss[.sss]. Esempio: datetime '2008-03-18 12:30:34.123'

Identificatori

Gli identificatori (o ID) sono string di testo che identificano le colonne.

Importante: se l'identificatore

• Include spazi,
• È una parola riservata,
• Contiene qualsiasi cosa, ad eccezione di caratteri alfanumerici o trattini bassi ([a-zA-Z0-9_]), o
• Inizia con un numero

deve essere racchiuso tra virgolette (non virgolette singole).

In caso contrario, non è necessario citare l'identificatore. Tieni presente che non tutte le parole chiave definite dalla sintassi sono riservate; quindi, ad esempio, puoi utilizzare "max" come identificatore, senza doverlo tra virgolette.

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

Consigliamo di non scegliere un identificatore che richieda le virgolette, perché può essere facile dimenticarsi di utilizzare le virgolette o usare per errore "Virgolette singole" invece di "virgolette". Si tratta di errori comuni e spesso difficili da eseguire il debug.

Sensibilità alla custodia

Gli identificatori e i valori letterali delle stringhe sono sensibili alle maiuscole. Tutti gli altri elementi della lingua non fanno distinzione tra maiuscole e minuscole.

Parole riservate

Le seguenti parole riservate devono essere racchiuse tra virgolette se utilizzate come identificatore:

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