Query Language-Referenz (Version 0.7)

Mit der Abfragesprache Google Visualization API können Sie verschiedene Datenbearbeitungen mit der Abfrage an die Datenquelle durchführen.

Inhalt

Einführung

In der Regel werden in Visualisierungen Daten in einer bestimmten Form erwartet. Ein Kreisdiagramm kann Daten in zwei Spalten erwarten: ein Textlabel und einen numerischen Wert. Die Daten in der Datenquelle stimmen möglicherweise nicht genau mit dieser Struktur überein. Beispielsweise kann die Datenquelle mehr als zwei Spalten enthalten oder die Reihenfolge der Spalten stimmt nicht mit der im Kreisdiagramm erwarteten Reihenfolge überein.

Mit der Abfragesprache können Sie Datenbearbeitungs- und Formatierungsanfragen an die Datenquelle senden. Die zurückgegebene Datenstruktur und die entsprechenden Inhalte müssen der erwarteten Struktur entsprechen.

Die Syntax der Abfragesprache ähnelt SQL. Entwickler, die mit SQL vertraut sind, sollten diese Abfragesprache schnell erlernen und verwenden können. Im Web sind viele SQL-Anleitungen verfügbar. Es gibt einige Unterschiede zwischen dieser Abfragesprache und SQL, die im Abschnitt Syntax beschrieben werden.

Datenquellen sind nicht erforderlich, um die Abfragesprache zu implementieren oder, wenn dies der Fall ist, alle Funktionen der Sprache zu implementieren. Sie sollten sich nicht auf eine Datenquelle verlassen, um alle Features dieser Sprache zu implementieren, es sei denn, Sie haben Grund zur Annahme.

Abfragesprache verwenden

Sie haben zwei Möglichkeiten, einen Abfragestring an eine Datenquellenanfrage anzuhängen: Sie legen den Abfragestring im JavaScript-Code fest oder Sie legen ihn in der Datenquellen-URL als Parameter fest. Enthält Ihre Anfrage keinen Abfragestring, werden standardmäßig alle Zeilen und Spalten mit der standardmäßigen Zeilen-/Spaltenreihenfolge und -formatierung einer Datenquelle zurückgegeben. Sie können dies ändern, indem Sie einen Abfragestring in Ihre Anfrage an eine Datenquelle einfügen.

Abfrage über JavaScript einrichten

Wenn Sie den Abfragestring im JavaScript-Code festlegen möchten, rufen Sie die Methode setQuery der Klasse google.visualization.Query auf.

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

Abfrage in der URL der Datenquelle festlegen

Der Abfragestring kann der Datenquelle über den Parameter tq hinzugefügt werden. Wenn Sie die Abfrage im URL-Parameter statt in JavaScript festlegen, können Sie Visualisierungen von anderen Entwicklern ganz einfach verwenden und die Abfrage weiter anpassen.

Der Abfragestring muss als URL-Parameter richtig codiert sein. Sie können eine URL mit der JavaScript-Funktion encodeURIComponent oder mit dem Codierungstool am Ende dieses Abschnitts manuell codieren.

Beispiel:

Betrachten Sie den folgenden Abfragestring für eine Google-Tabelle. Beachten Sie, dass die Spalten-IDs in Tabellen immer Buchstaben sind. Die in der veröffentlichten Tabelle angezeigten Spaltenüberschriften sind Labels, keine IDs. Verwenden Sie im Abfragestring die ID, nicht das Label.

select A, sum(B) group by A

Bei der Codierung wird diese Abfrage so:

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

Angenommen, das ist die URL Ihrer Tabelle:

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

Fügen Sie der Tabellen-URL /gviz/tq?tq=YOUR_QUERY_STRING hinzu, um den endgültigen Abfragestring abzurufen:

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

Verwenden Sie das folgende Tool, um einen Abfragestring zu codieren oder zu decodieren:

Hinweis: Für den Zugriff auf private Tabellendaten müssen explizite Autorisierungsdaten über OAuth übergeben werden. Weitere Informationen finden Sie im Abschnitt Google-Tabellen: Autorisierung.

Syntax der Sprache

Übersicht

Die Syntax der Abfragesprache der Google Visualization API ähnelt der SQL-Syntax. Es handelt sich jedoch um eine Teilmenge von SQL mit einigen eigenen Features, die Sie kennen sollten. Wenn Sie mit SQL vertraut sind, sollte es nicht schwer zu erlernen sein.

Datentabellen

In diesem Dokument wird der Begriff Datentabelle verwendet, um auf die Ergebnismenge einer Abfrage zu verweisen. Eine Datentabelle besteht aus Zeilen und Spalten. Jede Spalte in einer Datentabelle hat die folgenden Attribute:

  • ID (oder Spalten-ID). Wird zum Referenzieren von Spalten in der Abfrage verwendet. Sie sollten in einer Abfrage nie auf eine Spalte mit Label, sondern nur auf eine ID verweisen. Tipp: Verwenden Sie keine IDs mit Leerzeichen. Leerzeichen sind schwer zu verwalten und können dazu führen, dass Sie bei der Codierung kleine, aber schwer zu findende Fehler machen. Außerdem muss eine ID mit Leerzeichen in Anführungszeichen gesetzt werden.
  • Label. Ein string, der normalerweise Endnutzern angezeigt wird. z. B. als Legende in einem Kreisdiagramm oder als Spaltenüberschrift in einer Tabelle.
  • Datentyp: Unterstützte Datentypen sind string, number, boolean, date, datetime und timeofday. Alle Werte einer Spalte haben einen Datentyp, der dem Spaltentyp entspricht, oder einen null-Wert. Diese Typen sind den JavaScript-Typen ähnlich, aber nicht identisch. Weitere Informationen finden Sie im Abschnitt Literale auf dieser Seite.
  • Formatierungsmuster: In der Datenquelle können Formatierungsmuster für einige oder alle Spalten definiert sein. Sie können dieses Muster überschreiben, indem Sie eine Formatklausel einbinden.

In allen Beispielen verwendete Tabelle:

In diesem Abschnitt beziehen sich alle Beispiele für Abfragen auf die folgende Tabelle. Die Spaltenüberschriften sind die Spaltenkennungen.

Name
string
dept
string
mittag
timeofday
Gehalt
number
Einstellungsdatum
date
Alter
number
isSenior
boolean
SeniorStartTime
datetime
JohnKleinbuchstabe Eng12:00:00
1.000
2005-03-1935true02.12.2007 15:56:00
DaveKleinbuchstabe Eng12:00:00
500
2006-04-1927falsenull
SallyKleinbuchstabe Eng13:00:00
600
2005-10-1030falsenull
BennoVertrieb12:00:00
400
2002-10-1032true09.03.2005 12:30:00
DanaVertrieb12:00:00
350
2004-09-0825falsenull
MichaelMarketing13:00:00
800
2005-01-1024true30.12.2007 14:40:00

Sprachklauseln

Die Syntax der Abfragesprache setzt sich aus den folgenden Klauseln zusammen. Jede Klausel beginnt mit einem oder zwei Keywords. Alle Klauseln sind optional. Klauseln sind durch Leerzeichen getrennt. Die Klauseln müssen folgende Reihenfolge haben:

Klausel Nutzung
select Damit wird festgelegt, welche Spalten in welcher Reihenfolge zurückgegeben werden sollen. Wenn nichts angegeben ist, werden alle Spalten der Tabelle in ihrer Standardreihenfolge zurückgegeben.
where Gibt nur Zeilen zurück, die eine Bedingung erfüllen. Wenn nichts angegeben ist, werden alle Zeilen zurückgegeben.
group by Aggregiert Werte aus mehreren Zeilen.
pivot Wandelt unterschiedliche Werte in Spalten in neue Spalten um.
order by Sortiert Zeilen nach Werten in Spalten.
limit Begrenzt die Anzahl der zurückgegebenen Zeilen.
offset Überspringt eine bestimmte Anzahl von ersten Zeilen.
label Legt Labels für Spalten fest.
format Formatiert die Werte in bestimmten Spalten anhand bestimmter Formatierungsmuster.
options Legt zusätzliche Optionen fest.
from Die Klausel from wurde aus der Sprache entfernt.

 

Auswählen

Mit der select-Klausel werden die zurückzugebenden Spalten und ihre Reihenfolge angegeben. Wenn diese Klausel nicht angegeben oder select * verwendet wird, werden alle Spalten der Datenquellentabelle in ihrer ursprünglichen Reihenfolge zurückgegeben. Spalten werden von den Kennungen referenziert, nicht von Labels. In einer Google-Tabelle sind Spaltenkennungen beispielsweise der ein- oder zweistellige Spaltenbuchstaben (A, B, C, ...).

Elemente in einer select-Klausel können Spaltenkennungen oder die Ausgabe von Aggregationsfunktionen, skalaren Funktionen oder Operatoren sein.

Beispiele:

select *
select dept, salary
select max(salary)

Im folgenden Beispiel werden Anführungszeichen verwendet, um auf Spalten-IDs zu verweisen, die Leerzeichen (E-Mail-Adresse) oder reservierte Wörter (Datum) enthalten:

select `email address`, name, `date`

Die folgende Abfrage wird in der Beispieltabelle ausgeführt:

select lunchTime, name

Gibt die folgende Antwort zurück:

mittagessen name
12:00:00John
12:00:00Dave
13:00:00Sally
12:00:00Benno
12:00:00Dana
13:00:00Michael

Wo

Mit der where-Klausel werden nur Zeilen zurückgegeben, die eine bestimmte Bedingung erfüllen.

Die einfachen Vergleichsoperatoren sind <=, <, >, >=, =, !=, <>. Die beiden Vergleichsoperatoren != <> bedeuten „ungleich“. Strings werden mit lexikografischen Werten verglichen. Beachten Sie, dass Gleichheitszeichen wie in den meisten Computersprachen durch = und nicht == angezeigt werden. Der Vergleich mit null wird mit is null oder is not null durchgeführt.

Sie können mehrere Bedingungen mit den logischen Operatoren and, or und not zusammenführen. In Klammern kann eine explizite Priorität definiert werden.

Die Wo-Klausel unterstützt auch einige komplexere Vergleichsoperatoren für Strings. Diese Operatoren nehmen zwei Strings als Argumente an. Alle Nicht-String-Argumente (z. B. Datumsangaben oder Zahlen) werden vor dem Vergleich in Strings konvertiert. Beim Stringabgleich wird zwischen Groß- und Kleinschreibung unterschieden. Sie können die skalaren Funktionen upper() oder lower() verwenden, um dies zu umgehen.

  • contains: Eine Teilstring-Übereinstimmung. Ganz contains Teil ist „wahr“, wenn Teil irgendwo innerhalb von ganz liegt. Beispiel: where name contains 'John' stimmt mit „John“, „John Adams“, „Long John Silber“, aber nicht mit „john adams“ überein.
  • starts with: Eine Präfixübereinstimmung. Wert starts with Präfix ist wahr, wenn Präfix am Anfang von Wert steht. Beispiele: where dept starts with 'engineering' stimmt mit „Ingenieure“ und „Ingenieure“ überein. where dept starts with 'e' stimmt mit „engineering“, „eng“ und „e“ überein.
  • ends with: Eine Suffixübereinstimmung. Wert ends with suffix ist „true“, wenn suffix am Ende von value steht. Beispiel: where role ends with 'y' stimmt mit „cowboy“, „boy“ und „y“ überein.
  • matches: Eine Übereinstimmung mit einem regulären Ausdruck (preg). haystack matches needle ist wahr, wenn der reguläre Ausdruck in needle mit haystack übereinstimmt. Beispiele: where country matches '.*ia' stimmt mit Indien und Nigeria überein, aber nicht mit Indiana. Diese Suchanfrage ist nicht global, daher entspricht where country matches 'an' nicht „Kanada“.
  • like: Eine Textsuche, die zwei Platzhalter unterstützt: %, die null oder mehr Zeichen jeglicher Art enthält, und _ (Unterstrich), der einem beliebigen Zeichen entspricht. Dies ähnelt dem SQL-LIKE-Operator. Beispiel: where name like fre% stimmt mit „fre“, „fred“ und „freddy“ überein.

Beispiele:

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

Die folgende Abfrage wird in der Beispieltabelle ausgeführt:

select name where salary > 700

Gibt die folgende Antwort zurück:

name
John
Michael

Gruppieren nach

Mit der group by-Klausel werden Werte in Zeilen zusammengefasst. Für jede einzelne Wertekombination in der Gruppierung nach Klausel wird eine einzelne Zeile erstellt. Die Daten werden automatisch nach Gruppierungsspalten sortiert, sofern nichts anderes mit einer order by-Klausel angegeben ist.

Hinweis: Wenn Sie eine group by-Klausel verwenden, muss jede Spalte, die in der select-Klausel aufgeführt ist, entweder in der group by-Klausel aufgeführt oder von einer Aggregationsfunktion umschlossen werden.

Beispiele:

select dept, max(salary) group by dept

Die folgende Abfrage wird in der Beispieltabelle ausgeführt:

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

Gibt die folgende Antwort zurück:

mittagessen durchschn. Gehalt Alter
12:00:00 425 2
13:00:00 600 1
12:00:00 700 2
13:00:00 800 1

Pivot-Tabelle

Mit der pivot-Klausel werden einzelne Werte in Spalten in neue Spalten umgewandelt. Beispielsweise ergibt ein Pivot der Spalte „year“ eine Tabelle mit einer Spalte für jedes einzelne Jahr, das in der ursprünglichen Tabelle enthalten ist. Das kann hilfreich sein, wenn in einer Visualisierung des Liniendiagramms beispielsweise jede Spalte als separate Linie gezeichnet wird. Wenn Sie für jedes Jahr eine separate Linie zeichnen möchten und „year“ eine der Spalten der ursprünglichen Tabelle ist, empfiehlt es sich, einen Pivot-Vorgang auszuführen, um die erforderliche Datentransformation durchzuführen.

Hinweis: Wenn Sie eine pivot-Klausel verwenden, muss jede Spalte, die in der select-Klausel aufgeführt ist, entweder in der group by-Klausel aufgelistet oder von einer Aggregationsfunktion umschlossen werden.

Da mehrere Zeilen dieselben Werte für die Pivot-Spalten enthalten können, bedeutet Pivot eine Aggregation. Wenn Sie pivot ohne group by verwenden, enthält die Ergebnistabelle genau eine Zeile. Führen Sie beispielsweise die folgende Abfrage in der Beispieltabelle aus:

select sum(salary) pivot dept

Gibt die folgende Antwort zurück:

Gehälter für Entwickler (in englischer Sprache) Summengehalt für Marketing Gesamtgehalt
2.100 800 750

Grund: 2.100 ist die Summe der Gehälter für die Entwicklungsabteilung, 800 für die Marketingabteilung usw.

Die Verwendung von pivot in Kombination mit group by kann noch nützlicher sein, da eine Tabelle erstellt wird, in der jede Zelle das Ergebnis der Aggregation für die relevante Zeile und die entsprechende Spalte enthält. Führen Sie beispielsweise die folgende Abfrage in der Beispieltabelle aus:

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

Gibt die folgende Antwort zurück:

Abt. 12:00:00 Summe Gehalt 13:00:00 Gehaltsabrechnung
Kleinbuchstabe Eng 1.500 600
Marketing null 800
Vertrieb 750 null

Sie können diese Tabelle auch „umkehren“. Dabei wechseln Sie zwischen den Spalten pivot und group by. Die folgende Abfrage wird in der Beispieltabelle ausgeführt:

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

Gibt die folgende Antwort zurück:

mittagessen Gehälter für Entwickler (in englischer Sprache) Summengehalt für Marketing Gesamtgehalt
12:00:00 1.500 null 750
13:00:00 600 800 null

Sie können auch mehr als eine Spalte in der pivot-Klausel verwenden. In einem solchen Fall bestehen die Spalten der Antworttabelle aus allen eindeutigen Kombinationen von Werten in den Spalten der ursprünglichen Tabelle. Führen Sie beispielsweise die folgende Abfrage in der Beispieltabelle aus:

select sum(salary)
  pivot dept, lunchTime

Gibt die folgende Antwort zurück:

engl., 12:00:00 Gehaltsabrechnung Sum, Gehalt, 13:00:00 Marketing,11:00:00 Gehalt Verkäufe,12:00:00 Gehalt
1.500 600 800 750

Beachten Sie, dass nur die Kombinationen aus der ursprünglichen Tabelle Spalten in der Antworttabelle erhalten. Aus diesem Grund gibt es keine Spalte für Marketing,12:00:00 oder für Vertrieb,13:00:00.

Die Verwendung mehrerer Aggregationen ist ebenfalls möglich. Führen Sie beispielsweise die folgende Abfrage in der Beispieltabelle aus:

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

Gibt die folgende Antwort zurück:

Gehälter für Entwickler (in englischer Sprache) Summengehalt für Marketing Gesamtgehalt Max. Mittagspause Marketing: maximale Mittagszeit Max. Mittagspause
2.100 800 750 13:00:00 13:00:00 12:00:00

Sie können mehrere Zusammenfassungen in der select-Klausel, mehrere Spalten in der group by-Klausel und mehrere Spalten in der pivot-Klausel kombinieren. Intern wird die Aggregation durch die Verkettung der Spalten in der Gruppe von und durch Pivot-Klauseln durchgeführt.

Die in der pivot-Klausel angegebenen Spalten werden möglicherweise nicht in den select-, group by- oder order by-Klauseln angezeigt. Wenn pivot verwendet wird, darf die order by-Klausel keine Aggregationsspalten enthalten. Der Grund dafür ist, dass für jede in der select-Klausel angegebene Aggregation viele Spalten in der Ergebnistabelle generiert werden. Sie können jedoch Aggregationsspalten formatieren, wenn pivot verwendet wird. Das Ergebnis eines solchen Formats ist, dass alle neuen Spalten, die für die spezifische Aggregation relevant sind und vom Pivot-Vorgang generiert werden, nach dem angegebenen Muster formatiert sind. Im Beispiel oben wirkt sich das Hinzufügen von format sum(salary) "some_format_string" auf die folgenden Spalten aus: Entwickler-Gesamtgehalt, Marketing-Gehalt und Verkaufs-Gesamtgehalt.

Sie können Aggregationsspalten mit Labels versehen. Wenn in der label-Klausel kein Label angegeben ist, besteht das Label einer Spalte, die durch eine Pivoting-Funktion generiert wird, aus der Liste der Werte in den Pivot-Spalten, dem Zusammenfassungstyp (Min., Maximum, Summe,...) und dem Label der aggregierten Spalte. Beispiel: „12:00:00 sum geary“. Wenn in der select-Klausel nur eine Aggregation angegeben wurde, wird der Aggregationsteil aus dem Label entfernt und nur die Liste der Werte in den Pivot-Spalten beibehalten. Beispiel: „Eng,12:00:00“. Wenn eine label-Klausel ein Label für eine Aggregationsspalte angibt, wird das angeforderte Label an die Liste der Werte angehängt, sowohl wenn nur eine Aggregation in der select-Klausel vorhanden ist als auch mehr als eine. Beispielsweise führt label sum(salary) "sumsal" zu den Spaltenlabels „Eng,12:00:00 sumsal“, „Eng,13:00:00 sumsal“ usw.

Sortieren nach

Mit der order by-Klausel werden die Zeilen nach den Werten in den angegebenen Spalten sortiert.

Elemente in einer order by-Klausel können Spaltenkennungen oder die Ausgabe von Aggregationsfunktionen, skalaren Funktionen oder Operatoren sein.

Beispiele:

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

Beschränkungen

Mit der limit-Klausel wird die Anzahl der zurückgegebenen Zeilen begrenzt.

Beispiel:

limit 100

Offset

Mit der offset-Klausel wird eine bestimmte Anzahl von ersten Zeilen übersprungen. Wenn eine limit-Klausel verwendet wird, wird zuerst offset angewendet. limit 15 offset 30 gibt beispielsweise die Zeilen 31 bis 45 zurück.

Beispiele:

offset 10
limit 30 offset 210

Label

Mit der label-Klausel wird das Label für eine oder mehrere Spalten festgelegt. Beachten Sie, dass Sie in einer Abfrage keinen Labelwert anstelle einer ID verwenden können.

Elemente in einer label-Klausel können Spaltenkennungen oder die Ausgabe von Aggregationsfunktionen, skalaren Funktionen oder Operatoren sein.

Syntax:

label column_id label_string [,column_id label_string]
column_id
Die Kennzeichnung der Spalte, der das Label zugewiesen wird.
label_string
Das Label, das dieser Spalte zugewiesen werden soll. Viele Visualisierungen verwenden das Spaltenlabel als Text, der dem Endnutzer angezeigt wird, z. B. ein Legendenlabel in einem Kreisdiagramm. Labels sind Stringliterale und folgen diesen Syntaxregeln.

Beispiel:

Im folgenden Beispiel wird das Label für die Spalte „dept“ (Abteilung) auf „Department“ (Abteilung), das Label für die Namensspalte auf „Employee Name“ (Mitarbeitername) und das Label für die Standortspalte auf „Employee Location“ (Mitarbeiterstandort) gesetzt:

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

Format

Mit der format-Klausel wird ein formatierter Wert für Zellen in einer oder mehreren Spalten angegeben. Die zurückgegebenen Daten müssen sowohl einen tatsächlichen Wert als auch einen formatierten Wert für jede Zelle in einer formatierten Spalte enthalten. Viele Visualisierungen verwenden den unformatierten Wert für Berechnungen, den formatierten Wert für die Anzeige. Die in dieser Klausel angegebenen Muster werden normalerweise im Attribut pattern der entsprechenden Spalten zurückgegeben.

Mustersyntax:

number, date, timeofday, datetime
Das vom ICU definierte Muster für Datum und Zahl.
boolean
Das Muster ist ein string im Format value-if-true:value-if-false.

Beispiel:

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

 

Optionen

Mit der options-Klausel werden zusätzliche Optionen für die Abfrageausführung gesteuert. Mögliche Keywords, die auf die Klausel options folgen können:

  • no_format Entfernt formatierte Werte aus dem Ergebnis und behält nur die zugrunde liegenden Werte bei. Kann verwendet werden, wenn die spezifische Visualisierung nicht die formatierten Werte verwendet, um die Größe der Antwort zu reduzieren.
  • no_values Entfernt zugrunde liegende Werte und behält nur die formatierten Werte bei. Kann verwendet werden, wenn bei der spezifischen Visualisierung nur die formatierten Werte verwendet werden, um die Größe der Antwort zu verringern.

Datenbearbeitungsfunktionen

Es gibt verschiedene Arten von Operatoren und Funktionen, mit denen Sie Daten in einer einzelnen Spalte bearbeiten oder aggregieren oder Daten über Spalten hinweg vergleichen oder kombinieren können. Beispiele sind „sum()“ (zum Hinzufügen aller Werte in einer Spalte), „max“ (zum Ermitteln des größten Werts in einer Spalte) und „+“ (zum Hinzufügen der Werte von zwei Spalten in derselben Zeile).

Einige Funktionen können in einer beliebigen Klausel, andere jedoch nur in einem Teil enthalten sein. Dies ist unten dokumentiert.

Beispiel:

In dieser Tabelle... Wenn wir diese Abfrage anwenden... Wir erhalten dieses Ergebnis.
Name Gehalt Steuer StartDate
Sharon 1.000 100 01.01.2009
Avital 2.000 200 21.01.2008
Moran 3.000 300 12.02.2008
select upper(name), year(startDate)
Name Jahr(Startdatum)
AVITAL 2008
MORAN 2008
Sharon 2009

 

Die folgenden Datenbearbeitungsfunktionen werden durch die Abfragesprache der Google Visualization API definiert:

 

Aggregationsfunktionen

Aggregationsfunktionen übergeben eine einzelne Spalte mit Kennzeichnung und führen eine Aktion für alle Werte in jeder Gruppe aus (Gruppen werden durch group by- oder pivot-Klauseln oder alle Zeilen angegeben, wenn diese Klauseln nicht verwendet werden).

Beispiele:

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.

Aggregationsfunktionen können in den Klauseln select, order by, label und format verwendet werden. Sie können nicht in den Klauseln where, group by, pivot, limit, offset oder options verwendet werden.

Folgende Aggregationsfunktionen werden unterstützt:

Name Beschreibung Unterstützte Spaltentypen Rückgabetyp
avg() Gibt den Durchschnittswert aller Werte in der Spalte einer Gruppe zurück. number number
count() Gibt die Anzahl der Elemente in der angegebenen Spalte für eine Gruppe zurück. Nullzellen werden nicht gezählt. Alle Typen number
max() Gibt den Höchstwert in der Spalte für eine Gruppe zurück. Datumsangaben werden kleiner als früher, string-Werte alphabetisch verglichen und unterscheiden Groß- und Kleinschreibung. Alle Typen Gleicher Typ wie Spalte
min() Gibt den Minimalwert in der Spalte für eine Gruppe zurück. Datumsangaben werden kleiner als vorher, string-Werte alphabetisch verglichen und unterscheiden Groß- und Kleinschreibung. Alle Typen Gleicher Typ wie Spalte
sum() Gibt die Summe aller Werte in der Spalte einer Gruppe zurück. number number

Hinweis:Aggregationsfunktionen können nur eine Spalten-ID als Argument verwenden:

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

Skalare Funktionen

Skalare Funktionen verarbeiten über null oder mehr Parameter, um einen weiteren Wert zu erzeugen. Skalarfunktionen können jeden Ausdruck übergeben werden, der als Parameter des entsprechenden Typs ausgewertet wird. Beachten Sie, dass diese Typen im Abschnitt Literale dieses Dokuments definiert sind und sich geringfügig von den JavaScript-Objekten mit ähnlichen Namen unterscheiden können.

Der Spaltenname wird durch eine skalare Funktion geändert.

Skalare Funktionen können als Parameter alles annehmen, was einen einzelnen Wert ergibt:

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

Skalarfunktionen können in jeder der folgenden Klauseln verwendet werden: select, where, group by, pivot, order by, label, und format.

Name
year()

Gibt den Jahreswert aus einem Datums- oder Datum/Uhrzeit-Wert zurück. Beispiel: year(date "2009-02-05") gibt 2009 zurück.

Parameter:Ein Parameter vom Typ date oder datetime
Rückgabetyp: number
month()

Gibt den nullbasierten Monatswert aus einem Datums- oder Datum/Uhrzeit-Wert zurück. Beispiel: month(date "2009-02-05") gibt 1 zurück. Hinweis: Die Monate basieren auf 0. Die Funktion gibt also 0 für Januar, 1 für Februar usw. zurück.

Parameter:Ein Parameter vom Typ date oder datetime
Rückgabetyp: number
day()

Gibt den Tag des Monats aus einem date- oder Datum/Uhrzeit-Wert zurück. Beispiel: day(date "2009-02-05") gibt 5 zurück.

Parameter: Ein Parameter vom Typ date oder datetime
Rückgabetyp: number
hour()

Gibt den Stundenwert aus einem Datum/Uhrzeit- oder timeofday-Wert zurück. Beispiel: hour(timeofday "12:03:17") gibt 12 zurück.

Parameter:Ein Parameter vom Typ datetime oder timeofday
Rückgabetyp: number
minute()

Gibt den Minutenwert aus einem datetime- oder timeofday-Wert zurück. Beispiel: minute(timeofday "12:03:17") gibt 3 zurück.

Parameter: Ein Parameter vom Typ datetime oder timeofday
Rückgabetyp: number
second()

Gibt den zweiten Wert aus einem datetime- oder timeofday-Wert zurück. Beispiel: second(timeofday "12:03:17") gibt 17 zurück.

Parameter:Ein Parameter vom Typ datetime oder timeofday
Rückgabetyp: number
millisecond()

Gibt den Millisekundenteil eines datetime- oder timeofday-Werts zurück. Beispiel: millisecond(timeofday "12:03:17.123") gibt 123 zurück.

Parameter: Ein Parameter vom Typ datetime oder timeofday
Rückgabetyp: number
quarter()

Gibt das Quartal eines date- oder datetime-Werts zurück. Beispiel: quarter(date "2009-02-05") gibt 1 zurück. Beachten Sie, dass Quartale auf 1 basieren, sodass die Funktion 1 für das erste Quartal, 2 für das zweite usw. zurückgibt.

Parameter:Ein Parameter vom Typ date oder datetime
Rückgabetyp: number
dayOfWeek()

Gibt den Wochentag aus einem date- oder datetime-Wert zurück. Beispiel: dayOfWeek(date "2009-02-26") gibt 5 zurück. Beachten Sie, dass Tage auf 1 basieren, sodass die Funktion 1 für Sonntag, 2 für Montag usw. zurückgibt.

Parameter:Ein Parameter vom Typ date oder datetime
Rückgabetyp: number
now()

Gibt einen Datum/Uhrzeit-Wert zurück, der den aktuellen datetime in der GMT-Zeitzone darstellt.

Parameter: Keine
Rückgabetyp: datetime
dateDiff()

Gibt die Differenz in Tagen zwischen zwei date- oder datetime-Werten zurück. Hinweis: Bei der Berechnung werden nur die date-Teile der Werte verwendet. Die Funktion gibt daher immer einen ganzzahligen Wert zurück. Beispiel: dateDiff(date "2008-03-13", date "2008-02-12") gibt 29 zurück, dateDiff(date "2009-02-13", date "2009-03-13") gibt -29 zurück. Zeitwerte werden vor dem Vergleich abgeschnitten.

Parameter: Zwei Parameter vom Typ date oder datetime (einer von beiden)
Rückgabetyp: number
toDate()

Wandelt den angegebenen Wert in einen date-Wert um.

  • Bei date wird der gleiche Wert zurückgegeben.
  • Bei datetime wird der date-Teil zurückgegeben. Beispiel: toDate(dateTime "2009-01-01 12:00:00") gibt „2009-01-01“ zurück.
  • Bei number N gibt es nach der Epoche date N Millisekunden zurück. Als Epoche wird der 1. Januar 1970, 00:00:00 GMT definiert. Beispiel: toDate(1234567890000) gibt „2009-02-13“ zurück.
Parameter: Ein Parameter vom Typ date, datetime oder number
Rückgabetyp: date
upper()

Gibt die angegebene string in Großbuchstaben zurück. Beispiel: upper("foo") gibt „FOO“ zurück.

Parameter:Ein Parameter vom Typ string
Rückgabetyp: string
lower()

Gibt die angegebene string in Kleinbuchstaben zurück. Beispiel: lower("Bar") gibt „bar“ zurück.

Parameter: Ein Parameter vom Typ string
Rückgabetyp: string

Arithmetische Operatoren

Mit arithmetischen Operatoren können Sie mathematische Vorgänge an beliebigen Stellen ausführen, die eine einzelne Zahl ergeben, d. h. die Ausgabe geeigneter Aggregatfunktionen, Operatoren oder Konstanten.

Beispiele:

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

Die folgenden Operatoren sind definiert:

Name Beschreibung Parameter Rückgabetyp
+ Gibt die Summe von zwei number-Werten zurück. Zwei number-Sekunden number
- Gibt die Differenz zwischen zwei number-Werten zurück. Zwei number-Sekunden number
* Gibt das Produkt von zwei number-Werten zurück. Zwei number-Sekunden number
/ Gibt den Quotienten von zwei number-Werten zurück. Die Division durch null gibt null zurück. Zwei number-Sekunden number

Sprachelemente

Literale

Literale sind Werte für Vergleiche oder Zuweisungen. Literale können string-Werte, Zahlen, boolesche Werte oder verschiedene Datums-/Uhrzeittypen sein. Hier sind einige Beispiele für Literale, die in der Abfragesyntax verwendet werden:

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

Hier die Formate für die einzelnen Literaltypen:

string
Ein string-Literal sollte in einfache oder doppelte Anführungszeichen gesetzt werden. Beispiele: "fourteen" 'hello world' "It's raining".
number
Numerische Literale werden in Dezimalschreibweise angegeben. Beispiele: 3  3.0  3.14  -71  -7.2  .6
boolean
Boolesche Literale sind entweder true oder false.
date
Verwenden Sie das Keyword date gefolgt von einem string-Literal im Format yyyy-MM-dd. Beispiel: date "2008-03-18".
timeofday
Verwenden Sie das Keyword timeofday gefolgt von einem string-Literal im Format HH:mm:ss[.SSS]. Beispiel: timeofday "12:30:45".
datetime
Ein Datum und eine Uhrzeit mit dem Keyword datetime oder dem Keyword timestamp, gefolgt von einem string-Literal im Format yyyy-MM-dd HH:mm:ss[.sss]. Beispiel:datetime '2008-03-18 12:30:34.123'

Kennungen

Kennungen (IDs) sind string-Texte, die Spalten kennzeichnen.

Wichtig: Wenn Ihre Kennung

  • Has
  • Ist ein reserviertes Wort,
  • Enthält alles außer alphanumerische Zeichen oder Unterstriche ([a-zA-Z0-9_]) oder
  • Beginnt mit einer Ziffer

Sie muss in Anführungszeichen gesetzt werden (nicht in einfache Anführungszeichen).

Andernfalls muss die ID nicht in Anführungszeichen gesetzt werden. Beachten Sie, dass nicht alle durch die Syntax definierten Keywords reservierte Wörter sind. Sie können also z. B. „max“ als Kennung verwenden, ohne diese in Anführungszeichen setzen zu müssen.

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

Wir raten davon ab, eine Kennung zu wählen, für die Anführungszeichen erforderlich sind, da leicht vergessen werden kann, Anführungszeichen zu verwenden oder versehentlich einfache Anführungszeichen anstelle von Anführungszeichen zu verwenden. Dies sind häufige Fehler und oft schwer zu beheben.

Groß-/Kleinschreibung

Bei Kennungen und Stringliteralen wird zwischen Groß- und Kleinschreibung unterschieden. Bei allen anderen Sprachelementen wird die Groß- und Kleinschreibung nicht berücksichtigt.

Reservierte Wörter

Die folgenden reservierten Wörter müssen in Anführungszeichen gesetzt werden, wenn sie als Kennung verwendet werden:

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