Le langage de requête de l'API Google Visualization vous permet d'effectuer diverses manipulations de données avec la requête envoyée à la source de données.

Sommaire

Présentation

En règle générale, les visualisations attendent des données sous une forme spécifique. Par exemple, un graphique à secteurs peut s'attendre à des données dans deux colonnes: une étiquette de texte et une valeur numérique. Les données de la source de données peuvent ne pas correspondre exactement à cette structure. Par exemple, la source de données peut comporter plus de deux colonnes ou l'ordre des colonnes peut ne pas correspondre à l'ordre attendu par le graphique à secteurs.

Le langage de requête permet d'envoyer des requêtes de manipulation et de mise en forme des données à la source de données, et de s'assurer que la structure et le contenu des données renvoyés correspondent à la structure attendue.

La syntaxe du langage de requête est similaire à celle de SQL. Les développeurs qui connaissent SQL doivent pouvoir apprendre et utiliser rapidement ce langage de requête. De nombreux tutoriels SQL sont disponibles sur le Web. Il existe des différences entre ce langage de requête et SQL, qui sont décrites dans la section syntax.

Notez que les sources de données ne sont pas nécessaires pour implémenter le langage de requête ou, le cas échéant, pour implémenter toutes ses fonctionnalités. Sauf indication contraire, vous ne devez pas dépendre d'une source de données pour implémenter toutes les fonctionnalités de ce langage.

Utiliser le langage de requête

Vous pouvez associer une chaîne de requête à une requête de source de données de deux manières: en définissant la chaîne de requête à partir du code JavaScript ou en la définissant en tant que paramètre dans l'URL de la source de données. Si votre requête n'inclut pas de chaîne de requête, le comportement par défaut d'une source de données consiste à renvoyer toutes les lignes et colonnes en utilisant l'ordre et la mise en forme des lignes et des colonnes par défaut. Vous pouvez modifier cela en incluant une chaîne de requête dans votre requête adressée à une source de données.

Définir la requête à partir d'un code JavaScript

Pour définir la chaîne de requête à partir du code JavaScript, appelez la méthode setQuery de la 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);

Définition de la requête dans l'URL de la source de données

La chaîne de requête peut être ajoutée à l'URL de la source de données à l'aide du paramètre tq. Définir la requête dans le paramètre d'URL plutôt qu'en JavaScript vous permet d'utiliser facilement des visualisations écrites par d'autres développeurs tout en continuant à personnaliser la requête.

La chaîne de requête doit être correctement encodée en tant que paramètre d'URL. Vous pouvez encoder une URL à l'aide de la fonction JavaScript encodeURIComponent ou manuellement, à l'aide de l'outil d'encodage présenté à la fin de cette section.

Exemple :

Prenons l'exemple de la chaîne de requête suivante pour une feuille de calcul Google. (Notez que les ID de colonne des feuilles de calcul sont toujours des lettres. Le texte des en-têtes de colonne affiché dans la feuille de calcul publiée correspond à des libellés, et non à des ID. Vous devez utiliser l'ID, et non l'étiquette, dans votre chaîne de requête.)

select A, sum(B) group by A

Une fois encodée, cette requête devient:

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

Supposons qu'il s'agisse de l'URL de votre feuille de calcul:

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

Ajoutez /gviz/tq?tq=YOUR_QUERY_STRING à l'URL de la feuille de calcul pour obtenir votre chaîne de requête finale:

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

Utilisez l'outil ci-dessous pour encoder ou décoder une chaîne de requête:

Syntaxe du langage

Présentation

La syntaxe du langage de requête de l'API Google Visualization est conçue pour être similaire à la syntaxe SQL. Cependant, il s'agit d'un sous-ensemble de SQL, comportant ses propres fonctionnalités que vous devrez apprendre. Si vous maîtrisez SQL, cela ne devrait pas être trop difficile à apprendre.

Tableau utilisé dans tous les exemples:

Dans cette section, tous les exemples de requêtes font référence au tableau suivant. Les en-têtes de colonne correspondent aux identifiants des colonnes.

Clauses relatives au langage

La syntaxe du langage de requête est composée des clauses suivantes. Chaque clause commence par un ou deux mots clés. Toutes les clauses sont facultatives. Les clauses sont séparées par des espaces. Les clauses doivent être classées dans l'ordre suivant:

Sélectionner

La clause select permet de spécifier les colonnes à renvoyer et leur ordre. Si cette clause n'est pas spécifiée ou si select * est utilisé, toutes les colonnes de la table de source de données sont renvoyées, dans leur ordre d'origine. Les colonnes sont référencées par les identifiants (et non par les étiquettes). Par exemple, dans une feuille de calcul Google, les identifiants de colonne sont la lettre de colonne d'un ou deux caractères (A, B, C, ...).

Les éléments d'une clause select peuvent être des identifiants de colonne ou le résultat de fonctions d'agrégation, de fonctions scalaires ou d'opérateurs.

Exemples :

select *

select dept, salary

select max(salary)

Dans l'exemple suivant, les guillemets sont utilisés pour faire référence aux ID de colonne contenant des espaces (adresse e-mail) ou des mots réservés (date):

select `email address`, name, `date`

Exécutez la requête suivante sur l'exemple de table:

select lunchTime, name

Renvoie la réponse suivante:

Emplacement

La clause where permet de ne renvoyer que les lignes correspondant à une condition spécifiée.

Les opérateurs de comparaison simples sont <=, <, >, >=, =, !=, <>. Les deux opérateurs de comparaison != <> signifient "différent de". Les chaînes sont comparées par valeur lexicographique. Notez que l'égalité est indiquée par =, et non par ==, comme dans la plupart des langages informatiques. La comparaison avec null est effectuée à l'aide de is null ou is not null.

Vous pouvez joindre plusieurs conditions à l'aide des opérateurs logiques and, or et not. Les parenthèses peuvent être utilisées pour définir une priorité explicite.

La clause WHERE accepte également des opérateurs de comparaison de chaînes plus complexes. Ces opérateurs utilisent deux chaînes en tant qu'arguments. Tous les arguments autres que des chaînes (par exemple, des dates ou des nombres) seront convertis en chaînes avant la comparaison. La correspondance de chaîne est sensible à la casse (vous pouvez utiliser les fonctions scalaires upper() ou lower() pour contourner ce problème).

• contains : correspondance de sous-chaîne. whole contains part est "true" si part se trouve n'importe où dans whole. Exemple:where name contains 'John' correspond à "John", "John Adams", "Long John Silver", mais pas à "john adams".
• starts with - Correspondance de préfixe. valeur starts with préfixe est "true" si préfixe se trouve au début de valeur. Exemples:where dept starts with 'engineering' correspond à "ingénieurs" et "responsables de l'ingénierie". where dept starts with 'e' correspond à "engineering", "eng" et "e".
• ends with - Une correspondance de suffixe. valeur ends with suffixe est "true" si suffixe se trouve à la fin de valeur. Exemple:where role ends with 'y' renvoie "cow-boy", "boy" et "y".
• matches : correspondance avec l'expression régulière (preg). haystack matches haystack est défini sur "true" si l'expression régulière dans haystack correspond à haystack. Exemples:where country matches '.*ia' correspond à l'Inde et au Nigeria, mais pas à Indiana. Notez qu'il ne s'agit pas d'une recherche globale. Par conséquent, where country matches 'an' ne correspondra pas à "Canada".
• like : recherche de texte acceptant deux caractères génériques : %, qui correspond à zéro ou plusieurs caractères de n'importe quel type, et _ (trait de soulignement), qui correspond à n'importe quel caractère. Cette méthode est semblable à l'opérateur SQL LIKE. Exemple:where name like fre% correspond à "fre", "fred" et "freddy".

Exemples :

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

Exécutez la requête suivante sur l'exemple de table:

select name where salary > 700

Renvoie la réponse suivante:

Grouper par

La clause group by permet d'agréger les valeurs entre les lignes. Une seule ligne est créée pour chaque combinaison distincte de valeurs de la clause de regroupement. Les données sont automatiquement triées en fonction des colonnes de regroupement, sauf indication contraire dans une clause order by.

Remarque:Si vous utilisez une clause group by, chaque colonne répertoriée dans la clause select doit être répertoriée dans la clause group by ou être encapsulée par une fonction d'agrégation.

Exemples :

select dept, max(salary) group by dept

Exécutez la requête suivante sur l'exemple de table:

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

Renvoie la réponse suivante:

Tableau croisé dynamique

La clause pivot permet de transformer en nouvelles colonnes les valeurs distinctes présentes dans les colonnes. Par exemple, un tableau croisé dynamique basé sur une colonne "année" produira un tableau avec une colonne pour chaque année distincte qui apparaît dans le tableau d'origine. Cela peut être utile si, par exemple, une visualisation sous forme de graphique en courbes dessine chaque colonne sous la forme d'une ligne distincte. Si vous souhaitez tracer une ligne distincte pour chaque année et que "year" (année) est l'une des colonnes de la table d'origine, une bonne option consiste à utiliser une opération de tableau croisé dynamique pour effectuer la transformation de données nécessaire.

Remarque:Si vous utilisez une clause pivot, chaque colonne répertoriée dans la clause select doit être répertoriée dans la clause group by ou être encapsulée par une fonction d'agrégation.

Étant donné que plusieurs lignes peuvent contenir les mêmes valeurs pour les colonnes d'un tableau croisé dynamique, le tableau croisé dynamique implique une agrégation. Notez que lorsque vous utilisez pivot sans group by, la table de résultats ne contient qu'une seule ligne. Par exemple, exécutez la requête suivante sur l'exemple de table:

select sum(salary) pivot dept

Renvoie la réponse suivante:

En effet, 2 100 correspond à la somme des salaires du département Eng, 800 pour le département Marketing, etc.

L'utilisation de pivot avec group by peut être encore plus utile, car elle crée une table dans laquelle chaque cellule contient le résultat de l'agrégation pour la ligne et la colonne pertinentes. Par exemple, exécutez la requête suivante sur l'exemple de table:

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

Renvoie la réponse suivante:

Vous pouvez également "inverser" cette table, en changeant de colonne et de ligne, en basculant entre les colonnes pivot et group by. Exécutez la requête suivante sur l'exemple de table:

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

Renvoie la réponse suivante:

Vous pouvez également utiliser plusieurs colonnes dans la clause pivot. Dans ce cas, les colonnes de la table de réponse sont composées de toutes les combinaisons uniques de valeurs des colonnes qui existent dans la table d'origine. Par exemple, exécutez la requête suivante sur l'exemple de table:

select sum(salary)
  pivot dept, lunchTime

Renvoie la réponse suivante:

Notez que seules les combinaisons qui apparaissent dans la table d'origine reçoivent des colonnes dans la table de réponses. C'est pourquoi il n'y a pas de colonne pour "Marketing,12:00:00" ni pour "Ventes,13:00:00".

Il est également possible d'utiliser plusieurs agrégations. Par exemple, exécutez la requête suivante sur l'exemple de table:

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

Renvoie la réponse suivante:

Vous pouvez combiner plusieurs agrégations dans la clause select, plusieurs colonnes dans la clause group by et plusieurs colonnes dans la clause pivot. En interne, l'agrégation est effectuée par la concaténation des colonnes des clauses group by et pivot.

Les colonnes spécifiées dans la clause pivot ne peuvent pas apparaître dans les clauses select, group by ou order by. Lorsque pivot est utilisé, la clause order by ne peut contenir aucune colonne d'agrégation. En effet, pour chaque agrégation spécifiée dans la clause select, de nombreuses colonnes sont générées dans la table de résultats. Toutefois, vous pouvez mettre en forme les colonnes d'agrégation lorsque pivot est utilisé. Le résultat d'un tel format est que toutes les nouvelles colonnes pertinentes pour une agrégation spécifique, qui sont générées par l'opération de tableau croisé dynamique, sont formatées selon le modèle spécifié. Dans l'exemple ci-dessus, l'ajout de format sum(salary) "some_format_string" aura une incidence sur les colonnes suivantes: Salaire Ing., Marketing sum-salary et Sales sum-salary.

Vous pouvez étiqueter les colonnes d'agrégation. Si aucun libellé n'est spécifié dans la clause label, le libellé d'une colonne générée à la suite du croisement dynamique est composé de la liste des valeurs des colonnes de tableau croisé dynamique, du type d'agrégation (min, max, somme, etc.) et de l'étiquette de la colonne agrégée. Par exemple : "Eng,12:00:00 sum Salary". Si une seule agrégation a été spécifiée dans la clause select, la partie d'agrégation est supprimée du libellé et seule la liste des valeurs des colonnes de tableau croisé dynamique est conservée. Exemple : "Eng,12:00:00". Lorsqu'une clause label spécifie un libellé pour une colonne d'agrégation, l'étiquette demandée est ajoutée à la liste des valeurs, à la fois lorsqu'il n'y a qu'une seule agrégation dans la clause select et lorsqu'il y en a plusieurs. Par exemple, label sum(salary) "sumsal" génère les libellés de colonne "Eng,12:00:00 sumsal", "Eng,13:00:00 sumsal", etc.

Trier par

La clause order by permet de trier les lignes en fonction des valeurs dans les colonnes spécifiées.

Les éléments d'une clause order by peuvent être des identifiants de colonne ou le résultat de fonctions d'agrégation, de fonctions scalaires ou d'opérateurs.

Exemples :

order by dept, salary desc

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

Limite

La clause limit permet de limiter le nombre de lignes renvoyées.

Exemple :

limit 100

Décalage

La clause offset permet d'ignorer un nombre donné de premières lignes. Si une clause limit est utilisée, offset est appliqué en premier: par exemple, limit 15 offset 30 renvoie les lignes 31 à 45.

Exemples :

offset 10
limit 30 offset 210

Libellé

La clause label permet de définir l'étiquette d'une ou de plusieurs colonnes. Notez que vous ne pouvez pas utiliser une valeur d'étiquette à la place d'un ID dans une requête.

Les éléments d'une clause label peuvent être des identifiants de colonne ou le résultat de fonctions d'agrégation, de fonctions scalaires ou d'opérateurs.

Syntaxe :

label column_id label_string [,column_id label_string]

column_id

Identifiant de la colonne à laquelle est attribuée l'étiquette.

label_string

Libellé à attribuer à cette colonne. De nombreuses visualisations utilisent l'étiquette de colonne comme texte à afficher pour l'utilisateur final, comme une étiquette de légende dans un graphique à secteurs. Les étiquettes sont des littéraux de chaîne et respectent ces règles de syntaxe.

Exemple :

L'exemple suivant définit le libellé de la colonne "dept" sur "Department", le libellé de la colonne "name" sur "Employee Name" et le libellé de la colonne "location" sur "Employee Location":

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

Format

La clause format permet de spécifier une valeur mise en forme pour les cellules d'une ou de plusieurs colonnes. Les données renvoyées doivent inclure à la fois une valeur réelle et une valeur mise en forme pour chaque cellule d'une colonne mise en forme. De nombreuses visualisations utilisent la valeur non formatée pour les calculs, mais la valeur formatée pour l'affichage. Les modèles que vous spécifiez dans cette clause sont généralement renvoyés dans la propriété pattern des colonnes correspondantes.

Syntaxe du modèle:

number, date, timeofday et datetime

Modèles de date et de nombre définis par l'ICU.

boolean

Le modèle est un string au format value-if-true:value-if-false.

Exemple :

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

Options

La clause options permet de contrôler des options supplémentaires pour l'exécution des requêtes. Voici les mots clés possibles pouvant suivre la clause options:

• no_format supprime les valeurs mises en forme du résultat et ne conserve que les valeurs sous-jacentes. Peut être utilisé lorsque la visualisation spécifique n'utilise pas les valeurs mises en forme afin de réduire la taille de la réponse.
• no_values supprime les valeurs sous-jacentes du résultat et ne conserve que les valeurs mises en forme. Peut être utilisé lorsque la visualisation spécifique n'utilise que les valeurs mises en forme afin de réduire la taille de la réponse.

Fonctions de manipulation de données

Plusieurs types d'opérateurs et de fonctions vous permettent de manipuler ou d'agréger des données dans une seule colonne, ou de comparer ou de combiner des données sur plusieurs colonnes. Exemples : sum() (pour ajouter toutes les valeurs d'une colonne), max (pour trouver la plus grande valeur dans une colonne) et + (pour additionner les valeurs de deux colonnes sur la même ligne).

Certaines fonctions peuvent apparaître dans n'importe quelle clause ; d'autres peuvent apparaître dans un sous-ensemble de clauses. Ce point est décrit ci-dessous.

Exemple :

select upper(name), year(startDate)

Les fonctions de manipulation de données suivantes sont définies par le langage de requête de l'API de visualisation Google:

• Fonctions d'agrégation
• Fonctions scalaires
• Opérateurs arithmétiques

Fonctions d'agrégation

Les fonctions d'agrégation reçoivent un seul identifiant de colonne et effectuent une action sur toutes les valeurs de chaque groupe (les groupes sont spécifiés par les clauses group by ou pivot, ou par toutes les lignes si ces clauses ne sont pas utilisées).

Exemples :

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.

Les fonctions d'agrégation peuvent être utilisées dans les clauses select, order by, label et format. Elles ne peuvent pas apparaître dans les clauses where, group by, pivot, limit, offset ou options.

Voici les fonctions d'agrégation compatibles:

Remarque : Les fonctions d'agrégation ne peuvent utiliser qu'un identifiant de colonne comme argument.

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

Fonctions scalaires

Les fonctions scalaires opèrent sur zéro ou plusieurs paramètres pour produire une autre valeur. Les fonctions scalaires peuvent transmettre n'importe quelle expression renvoyant le paramètre du type approprié. Notez que ces types sont définis dans la section Littéraux de ce document, et qu'ils peuvent être légèrement différents des objets JavaScript portant un nom similaire.

Notez que le nom de la colonne sera modifié en l'encapsulant avec une fonction scalaire.

Les fonctions scalaires peuvent utiliser comme paramètre tout élément qui renvoie une valeur unique:

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

Les fonctions scalaires peuvent être utilisées dans n'importe laquelle des clauses suivantes: select, where, group by, pivot, order by, label, et format.

Opérateurs arithmétiques

Vous pouvez utiliser des opérateurs arithmétiques pour effectuer des opérations mathématiques sur tout élément qui renvoie un nombre unique (c'est-à-dire la sortie des constantes, opérateurs ou fonctions d'agrégation appropriés).

Exemples :

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

Les opérateurs suivants sont définis:

Éléments linguistiques

Littéraux

Les littéraux sont des valeurs utilisées pour les comparaisons ou les attributions. Les littéraux peuvent être des string, des nombres, des valeurs booléennes ou divers types de date/heure. Voici quelques exemples de littéraux utilisés dans la syntaxe de requête:

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

Voici les formats pour chaque type de littéral:

string

Un littéral string doit être encadré par des guillemets simples ou doubles. Exemples:"fourteen" 'hello world' "It's raining".

number

Les littéraux numériques sont spécifiés en notation décimale. Exemples: 3  3.0  3.14  -71  -7.2  .6

boolean

Les littéraux booléens sont true ou false.

date

Utilisez le mot clé date suivi d'un littéral string au format yyyy-MM-dd. Exemple:date "2008-03-18".

timeofday

Utilisez le mot clé timeofday suivi d'un littéral string au format HH:mm:ss[.SSS]. Exemple:timeofday "12:30:45".

datetime

Date et heure, en utilisant le mot clé datetime ou le mot clé timestamp suivi d'un littéral string au format yyyy-MM-dd HH:mm:ss[.sss]. Exemple:datetime '2008-03-18 12:30:34.123'

Identifiants

Les identifiants (ou ID) sont des éléments textuels string qui identifient les colonnes.

Important:Si votre identifiant

• Comporte des espaces,
• est un terme réservé ;
• ne contient que des caractères alphanumériques ou des traits de soulignement ([a-zA-Z0-9_]) ; ou
• Commence par un chiffre

il doit être entouré de guillemets (et non de guillemets simples).

Dans le cas contraire,il n'est pas nécessaire de placer votre identifiant entre guillemets. (Notez que les mots clés définis par la syntaxe ne sont pas tous des mots réservés. Par exemple, vous pouvez utiliser "max" comme identifiant, sans avoir à le mettre entre guillemets.)

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

Nous vous déconseillons de choisir un identifiant qui nécessite des guillemets arrières, car il peut être facile d'oublier d'utiliser ces guillemets ou d'utiliser accidentellement des "guillemets simples" au lieu de "guillemets arrière". Il s'agit d'erreurs courantes et souvent difficiles à déboguer.

Sensibilité à la casse

Les identifiants et les littéraux de chaîne sont sensibles à la casse. Les autres éléments de langue ne sont pas sensibles à la casse.

Mots réservés

Les mots réservés suivants doivent être placés entre guillemets s'ils sont utilisés comme identifiant:

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