Dokumentacja języka zapytań (wersja 0.7)

Język zapytań interfejsu GoogleVisual API API umożliwia wykonywanie różnych operacji na danych za pomocą zapytań kierowanych do źródła danych.

Spis treści

  1. Wprowadzenie
  2. Używanie języka zapytań
    1. Ustawianie zapytania na podstawie JavaScriptu
    2. Ustawianie zapytania w adresie URL źródła danych
  3. Składnia języka
    1. Overview
    2. Klauzule językowe
      1. Wybierz
      2. Gdzie
      3. Grupuj według
      4. Tabela przestawna
      5. Porządkuj według
      6. Limit
      7. Przesunięcie
      8. Etykieta
      9. Format
      10. Opcje
    3. Funkcje manipulacji danymi
      1. Funkcje agregujące
      2. Funkcje skalarne
      3. Operatory arytmetyczne
    4. Elementy języka
      1. Literytorium
      2. Identyfikatory
      3. Uwzględnianie wielkości liter
      4. Zarezerwowane słowa

Wprowadzenie

Wizualizacje zwykle wymagają danych w określonej formie. Na przykład na wykresie kołowym dane mogą być wyświetlane w dwóch kolumnach: etykiety tekstowej i wartości liczbowej. Dane w źródle danych mogą nie pasować dokładnie do tej struktury. Na przykład źródło danych może zawierać więcej niż 2 kolumny lub kolejność kolumn może nie odpowiadać kolejności oczekiwanej na wykresie kołowym.

Język zapytań umożliwia wysyłanie do źródła danych żądań manipulowania danymi i formatowania oraz zapewnia, że zwrócona struktura i zawartość danych pasują do oczekiwanej struktury.

Składnia języka zapytań jest podobna do SQL. Deweloperzy znający SQL powinni mieć możliwość szybkiego opanowania tego języka zapytań i jego obsługi. W internecie dostępnych jest wiele samouczków SQL. Występują pewne różnice między tym językiem zapytań a SQL, które zostały opisane w sekcji syntax.

Pamiętaj, że źródła danych nie są wymagane do implementacji języka zapytań, a jeśli tak, do implementacji wszystkich funkcji tego języka. Jeśli nie masz powodu, aby sądzić, że jest inaczej, nie korzystaj ze źródła danych przy wdrażaniu wszystkich funkcji w danym języku.

Używanie języka zapytań

Ciąg zapytania możesz dołączyć do żądania źródła danych na 2 sposoby: ustawiając ciąg zapytania w kodzie JavaScript lub ustawiając go jako parametr w adresie URL źródła danych. Jeśli żądanie nie zawiera ciągu zapytania, domyślnym działaniem źródła danych jest zwrócenie wszystkich wierszy i kolumn z zachowaniem domyślnej kolejności wierszy i kolumn oraz domyślnego formatowania. Możesz to zmienić, dodając ciąg zapytania do źródła danych.

Ustawianie zapytania z JavaScriptu

Aby ustawić ciąg zapytania z poziomu kodu JavaScript, wywołaj metodę setQuery klasy google.visualization.Query.

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

Ustawianie zapytania w adresie URL źródła danych

Ciąg zapytania można dodać do adresu URL źródła danych za pomocą parametru tq. Ustawienie zapytania w parametrze adresu URL zamiast w JavaScript umożliwia łatwe korzystanie z wizualizacji przygotowanych przez innych deweloperów i dalsze dostosowywanie zapytania.

Ciąg zapytania musi być odpowiednio zakodowany jako parametr adresu URL. Możesz zakodować URL za pomocą funkcji JavaScriptu encodeURIComponent lub ręcznie zakodować go za pomocą narzędzia do kodowania znajdującego się na końcu tej sekcji.

Przykład:

Przeanalizuj poniższy ciąg zapytania dotyczący arkusza kalkulacyjnego Google. (Pamiętaj, że identyfikatory kolumn w arkuszach kalkulacyjnych zawsze są literami – w opublikowanym arkuszu kalkulacyjnym tekst nagłówków kolumn to etykiety, a nie identyfikatory. W ciągu zapytania musisz użyć identyfikatora, a nie etykiety).

select A, sum(B) group by A

Po zakodowaniu zapytanie wygląda tak:

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

Załóżmy, że to jest adres URL Twojego arkusza kalkulacyjnego:

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

Dodaj /gviz/tq?tq=YOUR_QUERY_STRING do adresu URL arkusza kalkulacyjnego, aby uzyskać końcowy ciąg zapytania:

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

Aby zakodować lub zdekodować ciąg zapytania, użyj tego narzędzia:

Uwaga: aby uzyskać dostęp do danych z prywatnego arkusza kalkulacyjnego, musisz przekazać jawne dane uwierzytelniające przy użyciu protokołu OAuth. Więcej informacji znajdziesz w sekcji Arkusze kalkulacyjne Google: autoryzacja.

Składnia języka

Omówienie

Składnia zapytań w interfejsie Google Welcome API Query Language została zaprojektowana tak, aby była podobna do składni SQL. Jest to jednak tylko podzbiór SQL z kilkoma funkcjami, które musisz poznać. Jeśli znasz język SQL, nauka go nie powinna być zbyt trudna.

Tabele danych

W tym dokumencie termin tabela danych odnosi się do zbioru wyników zapytania. Tabela danych składa się z wierszy i kolumn. Każda kolumna w tabeli danych ma te właściwości:

  • Identifier (lub identyfikator kolumny). Służy do odwoływania się do kolumn w zapytaniu. Pamiętaj, że nigdy nie próbuj odwoływać się w zapytaniu do kolumny według etykiety, a jedynie według identyfikatora. Wskazówka: nie używaj identyfikatorów zawierających spacje. Spacje są trudne do zarządzania i mogą powodować występowanie małych, ale trudnych do znalezienia błędów w kodowaniu. Dodatkowo identyfikator zawierający spacje musi być umieszczony w cudzysłowie.
  • Etykieta. string, który zwykle jest wyświetlany użytkownikom. Może to być na przykład legenda na wykresie kołowym lub nagłówek kolumny w tabeli.
  • Typ danych. Obsługiwane typy danych to string, number, boolean, date, datetime i timeofday. Wszystkie wartości w kolumnie będą miały typ danych zgodny z typem kolumny lub wartością null. Te typy są podobne, ale nie identyczne, do typów JavaScript. Ich opis znajdziesz w sekcji Literały na tej stronie.
  • Wzorzec formatowania. Źródło danych może określać wzorce formatowania niektórych lub wszystkich kolumn. Możesz zastąpić ten wzorzec, dodając klauzulę formatu.

Tabela używana we wszystkich przykładach:

W tej sekcji wszystkie przykłady zapytań odnoszą się do tabeli poniżej. Nagłówki kolumn to identyfikatory kolumn.

nazwa
string		 dział
string		 lunchTime
timeofday
wynagrodzenie
number
 hireDate
date		 wiek
number		 isSenior
boolean		 seniorityStartTime
datetime
JohnEng12:00:00
1000
2005-03-1935prawda2007-12-02 15:56:00
DawidEng12:00:00
500
2006-04-1927falsebrak
SandraEng13:00:00
600
2005-10-1030falsebrak
BartekSprzedaż12:00:00
400
2002-10-1032prawda2005-03-09 12:30:00
danaSprzedaż12:00:00
350
2004-09-0825falsebrak
MichałDział marketingu13:00:00
800
2005-01-1024prawda2007-12-30 14:40:00

Klauzule językowe

Składnia języka zapytań składa się z następujących klauzul. Każda klauzula rozpoczyna się od 1 lub 2 słów kluczowych. Wszystkie klauzule są opcjonalne. Klauzule są rozdzielone spacjami. Kolejność klauzul musi być następująca:

Klauzula Wykorzystanie
select Określa, które kolumny mają być zwracane i w jakiej kolejności. Jeśli nazwa zostanie pominięta, zwracane będą wszystkie kolumny tabeli w domyślnej kolejności.
where Zwraca tylko wiersze spełniające warunek. Jeśli nazwa zostanie pominięta, zwracane są wszystkie wiersze.
group by Agreguje wartości z różnych wierszy.
pivot Przekształca różne wartości w kolumnach w nowe kolumny.
order by Sortuje wiersze według wartości w kolumnach.
limit Ogranicza liczbę zwróconych wierszy.
offset Pomija określoną liczbę pierwszych wierszy.
label Ustawia etykiety kolumn.
format Formatuje wartości w określonych kolumnach na podstawie podanych wzorców formatowania.
options Ustawia dodatkowe opcje.
from Klauzula from została usunięta z języka.

 

Wybierz

Klauzula select służy do określania zwracanych kolumn i ich kolejności. Jeśli ta klauzula nie jest określona lub jest używana funkcja select *, zwracane są wszystkie kolumny tabeli źródeł danych w pierwotnej kolejności. Do kolumn odwołują się identyfikatory (a nie etykiety). Na przykład w arkuszu kalkulacyjnym Google identyfikatory kolumn to jedno- lub dwuznakowe litery kolumn (A, B, C, ...).

Elementy w klauzuli select mogą być identyfikatorami kolumn lub danymi wyjściowymi funkcji agregacji, funkcji skalarnych lub operatorów.

Przykłady:

select *

select dept, salary

select max(salary)

W tym przykładzie cudzysłowy są używane do odwoływania się do identyfikatorów kolumn, które zawierają spacje (adres e-mail) lub są zarezerwowanymi słowami (data):

select `email address`, name, `date`

Uruchomienie zapytania do przykładowej tabeli:

select lunchTime, name

Zwraca tę odpowiedź:

lunchTime nazwa
12:00:00John
12:00:00Dawid
13:00:00Sandra
12:00:00Bartek
12:00:00dana
13:00:00Michał

Gdzie

Klauzula where służy do zwracania tylko wierszy spełniających określony warunek.

Proste operatory porównania to <=, <, >, >=, =, !=, <>. Oba operatory porównania != <> oznaczają „nie równa się”. Ciągi znaków są porównywane z wartością leksykograficzną. Pamiętaj, że równość jest oznaczona symbolem =, a nie ==, jak w większości języków komputerowych. Porównywanie z null przeprowadza się przy użyciu is null lub is not null.

Za pomocą operatorów logicznych and, or i not możesz połączyć wiele warunków. Nawiasy pozwalają określić priorytet.

Klauzula gdzie obsługuje też niektóre bardziej złożone operatory porównania ciągów znaków. Te operatory przyjmują 2 ciągi jako argumenty. Wszystkie argumenty niebędące ciągami (na przykład daty lub liczby) zostaną przekonwertowane na ciągi przed porównaniem. W dopasowywaniu ciągu znaków wielkość liter ma znaczenie (aby obejść ten problem, możesz użyć funkcji skalarnych upper() lub lower()).

  • contains – dopasowanie podłańcucha. Parametr cała contains część ma wartość prawda, jeśli element part jest w dowolnym miejscu w obrębie całości. Przykład: where name contains 'John' odpowiada ciągom „Jan”, „Jan Adams”, „Long John Silver”, ale nie „jan adams”.
  • starts with – dopasowanie prefiksu. wartość starts with prefiks to prawda, jeśli prefiks znajduje się na początku elementu value. Przykłady: where dept starts with 'engineering' pasuje do słów „inżynieria” i „menedżerowie inżynierii”. Wyrażenie where dept starts with 'e' pasuje do ciągów „inżynieria”, „eng” i „e”.
  • ends with – dopasowanie sufiksu. wartość ends with sufiks to prawda, jeśli sufiks jest na końcu argumentu wartość. Przykład: where role ends with 'y' odpowiada ciągom „cowboy”, „boy” i „y”.
  • matches – dopasowanie wyrażenia regularnego (preg). haystack matches haystack to wartość true, jeśli wyrażenie regularne w argumencie haystack pasuje do słowa haystack. Przykłady: where country matches '.*ia' wskazuje dopasowanie do Indii i Nigerii, ale nie do Indiany. Pamiętaj, że to nie jest wyszukiwanie globalne, więc where country matches 'an' nie będzie pasować do słowa „Kanada”.
  • like – wyszukiwanie tekstowe, które obsługuje dwa symbole wieloznaczne: % oznaczający zero lub więcej znaków dowolnego rodzaju oraz _ (podkreślenie), który pasuje do dowolnego znaku. Jest to podobne do operatora SQL like. Przykład: where name like fre% pasuje do „fre”, „fred” i „freddy”.

Przykłady:

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

Uruchomienie zapytania do przykładowej tabeli:

select name where salary > 700

Zwraca tę odpowiedź:

nazwa
John
Michał

Grupuj według

Klauzula group by służy do agregacji wartości z różnych wierszy. Na każdą odrębną kombinację wartości w klauzuli grupowania tworzony jest 1 wiersz. Dane są automatycznie sortowane według kolumn grupowania, chyba że w klauzuli order by określono inaczej.

Uwaga: jeśli używasz klauzuli group by, każda kolumna wymieniona w klauzuli select musi być wymieniona w klauzuli group by lub być opakowana przez funkcję agregacji.

Przykłady:

select dept, max(salary) group by dept

Uruchomienie zapytania do przykładowej tabeli:

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

Zwraca tę odpowiedź:

lunchTime śr. wynagrodzenie count-age
12:00:00 425 2
13:00:00 600 1
12:00:00 700 2
13:00:00 800 1

Tabela przestawna

Klauzula pivot służy do przekształcania różnych wartości w kolumnach w nowe kolumny. Na przykład przestawienie według kolumny „rok” spowoduje utworzenie tabeli z kolumnami dotyczącymi poszczególnych lat, które znajdują się w pierwotnej tabeli. Przydaje się to, gdy np. wizualizacja na wykresie liniowym rysuje każdą kolumnę jako oddzielną linię. Jeśli chcesz narysować osobną linię dla każdego roku, a „rok” jest jedną z kolumn pierwotnej tabeli, dobrym rozwiązaniem będzie użycie operacji przestawnej do przeprowadzenia niezbędnego przekształcenia danych.

Uwaga: jeśli używasz klauzuli pivot, każda kolumna wymieniona w klauzuli select musi być wymieniona w klauzuli group by lub być opakowana przez funkcję agregacji

Wiele wierszy może zawierać te same wartości w kolumnach tabeli przestawnej, dlatego Tabela przestawna oznacza agregację. Pamiętaj, że jeśli używasz funkcji pivot bez użycia group by, tabela wynikowa będzie zawierać dokładnie 1 wiersz. Na przykład uruchomienie tego zapytania na przykładowej tabeli:

select sum(salary) pivot dept

Zwraca tę odpowiedź:

Wynagrodzenie angielskie Łączne wynagrodzenie marketingowe Suma sprzedaży i wynagrodzenie
2100 800 750

Dzieje się tak, ponieważ 2100 to suma wynagrodzenia w dziale inżynierii, 800 w dziale marketingu itd.

Użycie właściwości pivot razem z parametrem group by może być jeszcze bardziej przydatne, ponieważ tworzy tabelę, w której każda komórka zawiera wynik agregacji z odpowiedniego wiersza i odpowiedniej kolumny. Na przykład uruchomienie tego zapytania w przykładowej tabeli:

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

Zwraca tę odpowiedź:

dział 12:00:00 – łączne wynagrodzenie 13:00:00 – łączne wynagrodzenie
Eng 1500 600
Dział marketingu brak 800
Sprzedaż 750 brak

Możesz też „odwrócić” tę tabelę, zmieniając kolumny i wiersze, przełączając się między kolumnami pivot i group by. Wykonaj to zapytanie do przykładowej tabeli:

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

Zwraca tę odpowiedź:

lunchTime Wynagrodzenie angielskie Łączne wynagrodzenie marketingowe Suma sprzedaży i wynagrodzenie
12:00:00 1500 brak 750
13:00:00 600 800 brak

W klauzuli pivot możesz też używać więcej niż 1 kolumny. W takim przypadku kolumny tabeli odpowiedzi składają się ze wszystkich unikalnych kombinacji wartości w kolumnach, które istnieją w pierwotnej tabeli. Na przykład uruchomienie tego zapytania na przykładowej tabeli:

select sum(salary)
  pivot dept, lunchTime

Zwraca tę odpowiedź:

Eng,12:00:00 suma-salary Eng,13:00:00 suma-salary Marketing,13:00:00 – łączne wynagrodzenie Sprzedaż,12:00:00 – suma wynagrodzenia
1500 600 800 750

Pamiętaj, że kolumny w tabeli odpowiedzi mają tylko kombinacje, które są widoczne w oryginalnej tabeli. Właśnie dlatego nie ma kolumny Marketing,12:00:00 ani Sales,13:00:00.

Możliwe jest też użycie więcej niż 1 agregacji. Na przykład uruchomienie tego zapytania w przykładowej tabeli:

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

Zwraca tę odpowiedź:

Wynagrodzenie angielskie Łączne wynagrodzenie marketingowe Suma sprzedaży i wynagrodzenie Maks. czas lunchu Marketing max-lunchTime Sprzedaż maks.–lunchTime
2100 800 750 13:00:00 13:00:00 12:00:00

Możesz połączyć różne agregacje w klauzuli select, w kilku kolumnach w klauzuli group by oraz w kilku kolumnach w klauzuli pivot. Wewnętrznie agregacja jest wykonywana przez konkatenację kolumn w klauzulach „grupuj według” i „przestaw”.

Kolumny określone w klauzuli pivot nie mogą występować w klauzulach select, group by ani order by. Jeśli używasz pivot, klauzula order by nie może zawierać żadnych kolumn agregacji. Dzieje się tak, ponieważ w przypadku każdej agregacji określonej w klauzuli select w tabeli wyników jest generowanych wiele kolumn. Kolumny agregacji możesz jednak sformatować, gdy używasz atrybutu pivot. W efekcie takiego formatu wszystkie nowe kolumny istotne dla danej agregacji, które są generowane przez operację tabeli przestawnej, są sformatowane zgodnie z określonym wzorcem. W przykładzie powyżej dodanie właściwości format sum(salary) "some_format_string" wpłynie na te kolumny: suma wynagrodzenia dla inżynierów, suma wynagrodzenia za marketing i suma wynagrodzenia za sprzedaż.

Kolumny agregacji możesz oznaczać etykietami. Jeśli w klauzuli label nie określisz etykiety, etykieta kolumny utworzonej w wyniku przestawienia obejmuje listę wartości w kolumnach tabeli przestawnej, typ agregacji (min., maks., suma...) oraz etykietę kolumny agregacji. Na przykład „Eng,12:00:00 suma zarobków”. Jeśli w klauzuli select określono tylko 1 agregację, z etykiety zostanie usunięta część agregacji i zachowana zostanie tylko lista wartości w kolumnach tabeli przestawnej. Na przykład „Eng,12:00:00”. Gdy klauzula label określa etykietę kolumny agregacji, żądana etykieta jest dołączana do listy wartości zarówno wtedy, gdy w klauzuli select występuje tylko 1 agregacja, jak i gdy jest ich więcej. Na przykład label sum(salary) "sumsal" spowoduje powstanie etykiet kolumn „Eng,12:00:00 sumsal”, „Eng,13:00:00 sumsal” itp.

Porządkuj według

Klauzula order by służy do sortowania wierszy według wartości w określonych kolumnach.

Elementy w klauzuli order by mogą być identyfikatorami kolumn lub danymi wyjściowymi funkcji agregacji, funkcji skalarnych lub operatorów.

Przykłady:

order by dept, salary desc

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

Limit

Klauzula limit służy do ograniczania liczby zwracanych wierszy.

Przykład:

limit 100

Odsunięcie

Klauzula offset służy do pomijania określonej liczby pierwszych wierszy. Jeśli używana jest klauzula limit, najpierw stosowana jest klauzula offset, np. limit 15 offset 30 zwraca wiersze od 31 do 45.

Przykłady:

offset 10
limit 30 offset 210

Etykieta

Klauzula label służy do ustawiania etykiety co najmniej jednej kolumny. Pamiętaj, że nie można używać w zapytaniu wartości etykiety zamiast identyfikatora.

Elementy w klauzuli label mogą być identyfikatorami kolumn lub danymi wyjściowymi funkcji agregacji, funkcji skalarnych lub operatorów.

Składnia:

label column_id label_string [,column_id label_string]
column_id
Identyfikator kolumny, do której została przypisana etykieta.
label_string
Etykieta, która ma zostać przypisana do tej kolumny. Wiele wizualizacji wykorzystuje etykietę kolumny jako tekst wyświetlany użytkownikowi, np. etykietę legendy na wykresie kołowym. Etykiety to literały ciągów znaków i są zgodne z tymi regułami składni.

Przykład:

W poniższym przykładzie ustawisz etykietę kolumny działu na „Dział”, etykietę kolumny nazw na „Imię i nazwisko pracownika”, a etykietę kolumny lokalizacji na „Lokalizacja pracownika”:

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

Formatowanie

Klauzula format służy do określania sformatowanej wartości w przypadku komórek w co najmniej 1 kolumnie. Zwracane dane powinny zawierać zarówno wartość rzeczywistą, jak i sformatowaną każdą komórkę w sformatowanej kolumnie. Wiele wizualizacji korzysta z niesformatowanej wartości w obliczeniach, ale do wyświetlania wartości sformatowanej. Wzorce określone w tej klauzuli są zwykle zwracane we właściwości pattern w odpowiednich kolumnach.

Składnia wzorca:

number, date, timeofday, datetime
Wzorce dat i liczby zdefiniowane przez ICU.
boolean
Wzorzec to string w formacie „wartość-jeśli-prawda:wartość-jeśli-fałsz”.

Przykład:

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

 

Opcje

Klauzula options służy do kontrolowania dodatkowych opcji wykonywania zapytań. Możliwe słowa kluczowe, po których następuje klauzula options:

  • no_format Usuwa sformatowane wartości z wyniku i pozostawia tylko wartości bazowe. Można jej używać, gdy konkretna wizualizacja nie korzysta ze sformatowanych wartości, aby zmniejszyć rozmiar odpowiedzi.
  • no_values Usuwa wartości bazowe z wyniku i pozostawia tylko wartości sformatowane. Można jej używać, gdy konkretna wizualizacja korzysta tylko z wartości sformatowanych w celu zmniejszenia rozmiaru odpowiedzi.

Funkcje manipulacji danymi

Istnieje kilka rodzajów operatorów i funkcji, które umożliwiają wykonywanie operacji na danych lub ich agregowanie w jednej kolumnie oraz porównywanie i łączenie danych w różnych kolumnach. Przykładami są suma() (aby dodać wszystkie wartości w kolumnie), max (aby znaleźć największą wartość w kolumnie) lub + (aby dodać wartości z dwóch kolumn w tym samym wierszu).

Niektóre funkcje mogą występować w dowolnej klauzuli, a inne w podzbiorze klauzul. Opisaliśmy to poniżej.

Przykład:

Biorąc pod uwagę tę tabelę... Jeśli zastosujemy to zapytanie... To wynik.
Nazwa Pensja Podatek StartDate
Pola 1000 100 01.01.2009
Avit 2000 200 21.01.2008
Moran 3000 300 12.02.2008 
select upper(name), year(startDate)
Nazwa rok(Data rozpoczęcia)
AVITAL 2008
MORAN 2008
SHARON 2009

 

Te funkcje manipulowania danymi są zdefiniowane w języku zapytań interfejsu GoogleVisual API:

 

Funkcje agregujące

Funkcje agregujące są przekazywane do jednego identyfikatora w pojedynczej kolumnie i wykonują działanie względem wszystkich wartości w każdej grupie (grupy są określane przez klauzule group by lub pivot albo do wszystkich wierszy, jeśli te klauzule nie są używane).

Przykłady:

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.

Funkcji agregacji można używać w klauzulach select, order by, label i format. Nie mogą występować w klauzulach where, group by, pivot, limit, offset ani options.

Obsługiwane funkcje agregacji:

Nazwa Opis Obsługiwane typy kolumn Typ zwracanej wartości
avg() Zwraca średnią wartość wszystkich wartości w kolumnie dla danej grupy. number number
count() Zwraca liczbę elementów w określonej kolumnie dla grupy. Puste komórki nie są zliczane. Dowolny typ number
max() Zwraca maksymalną wartość w kolumnie dla grupy. Daty są porównywane z mniejszymi wcześniejszymi datami, a elementy string są porównywane alfabetycznie z uwzględnieniem wielkości liter. Dowolny typ Taki sam typ jak kolumna
min() Zwraca minimalną wartość w kolumnie dla grupy. Daty są porównywane, ponieważ wcześniej są mniejsze, a pola string są porównywane alfabetycznie z uwzględnieniem wielkości liter. Dowolny typ Taki sam typ jak kolumna
sum() Zwraca sumę wszystkich wartości w kolumnie. number number

Uwaga: funkcje agregujące mogą przyjmować tylko identyfikator kolumny jako 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.

Funkcje skalarne

Funkcje skalarne działają na podstawie zera lub większej liczby parametrów w celu wygenerowania kolejnej wartości. Funkcje skalarne mogą być przekazywane do dowolnego wyrażenia, które ocenia wartość parametru odpowiedniego typu. Te typy to typy zdefiniowane w sekcji Literals tego dokumentu, które mogą się nieznacznie różnić od obiektów JavaScript o podobnych nazwach.

Zwróć uwagę, że nazwa kolumny zmieni się przez owinięcie jej funkcją skalarną.

Funkcje skalarne mogą przyjmować jako parametr wszystko, co zwraca jedną wartość:

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

Funkcji skalarnych można używać w dowolnych z tych klauzul: select, where, group by, pivot, order by, label, i format.

Nazwa
year()

Zwraca wartość roku z wartości daty lub daty i godziny. Na przykład: year(date "2009-02-05") zwraca wartość 2009.

Parametry: 1 parametr typu date lub datetime
Typ zwrotu: number
month()

Zwraca liczoną od zera wartość miesiąca z wartości daty lub daty i godziny. Na przykład: month(date "2009-02-05") zwraca 1. Uwaga: liczba miesięcy jest mierzona od 0, więc funkcja zwraca 0 dla stycznia, 1 dla lutego itd.

Parametry: 1 parametr typu date lub datetime
Typ zwrotu: number
day()

Zwraca dzień miesiąca z wartości date lub daty i godziny. Na przykład: day(date "2009-02-05") zwraca 5.

Parametry: 1 parametr typu date lub datetime
Typ zwrotu: number
hour()

Zwraca wartość godziny z wartości daty i godziny lub wartości timeofday. Na przykład: hour(timeofday "12:03:17") zwraca liczbę 12.

Parametry: 1 parametr typu datetime lub timeofday
Typ zwrotu: number
minute()

Zwraca wartość minut z wartości datetime lub timeofday. Na przykład: minute(timeofday "12:03:17") zwraca liczbę 3.

Parametry: 1 parametr typu datetime lub timeofday
Typ zwrotu: number
second()

Zwraca drugą wartość z wartości datetime lub timeofday. Na przykład: second(timeofday "12:03:17") zwraca liczbę 17.

Parametry: 1 parametr typu datetime lub timeofday
Typ zwrotu: number
millisecond()

Zwraca wyrażoną w milisekundach część wartości datetime lub timeofday. Na przykład: millisecond(timeofday "12:03:17.123") zwraca liczbę 123.

Parametry: 1 parametr typu datetime lub timeofday
Typ zwrotu: number
quarter()

Zwraca kwartał z wartości date lub datetime. Na przykład: quarter(date "2009-02-05") zwraca 1. Pamiętaj, że liczba kwartałów jest liczona od 1, więc funkcja zwraca 1 dla pierwszego kwartału, 2 dla drugiego itd.

Parametry: 1 parametr typu date lub datetime
Typ zwrotu: number
dayOfWeek()

Zwraca dzień tygodnia z wartości date lub datetime. Na przykład: dayOfWeek(date "2009-02-26") zwraca 5. Pamiętaj, że liczba dni jest liczona od 1, więc funkcja zwraca 1 dla niedzieli, 2 – poniedziałek itd.

Parametry: 1 parametr typu date lub datetime
Typ zwrotu: number
now()

Zwraca wartość daty i godziny reprezentującą bieżące datetime w strefie czasowej GMT.

Parametry: brak
Typ zwrotu: datetime
dateDiff()

Zwraca różnicę w dniach między dwiema wartościami date lub datetime. Uwaga: w obliczeniach używane są tylko części date wartości, więc funkcja zawsze zwraca wartość całkowitą. Na przykład: dateDiff(date "2008-03-13", date "2008-02-12") zwraca liczbę 29, a dateDiff(date "2009-02-13", date "2009-03-13") zwraca liczbę –29. Przed porównaniem wartości czasowe są obcinane.

Parametry: 2 parametry typu date lub datetime (po jednym w każdym)
Typ zwrotu: number
toDate()

Przekształca podaną wartość na wartość date.

  • Zwracana jest ta sama wartość dla funkcji date.
  • Zwraca część date, a podany element: datetime. Przykład: toDate(dateTime "2009-01-01 12:00:00") zwraca „2009-01-01”.
  • Z uwzględnieniem number N zwraca date N milisekund po epoki. Epoka to 1 stycznia 1970 roku o godzinie 00:00:00 czasu GMT. Przykład: toDate(1234567890000) zwraca „2009-02-13”.
Parametry: 1 parametr typu date, datetime lub number
Typ zwrotu: date
upper()

Zwraca wartość string zapisaną wielkimi literami. Na przykład: upper("foo") zwraca „FOO”.

Parametry: 1 parametr typu string.
Typ zwrotu: string
lower()

Zwraca wartość string zapisaną małymi literami. Na przykład: lower("Bar") zwraca „bar”.

Parametry: 1 parametr typu string.
Typ zwrotu: string

Operatory arytmetyczne

Operatorów arytmetycznych możesz używać do wykonywania działań matematycznych na dowolnych liczbach, które zwracają pojedynczą liczbę (czyli dane wyjściowe odpowiednich funkcji agregujących, operatorów lub stałych).

Przykłady:

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

Zdefiniowano te operatory:

Nazwa Opis Parametry Typ zwracanej wartości
+ Zwraca sumę dwóch wartości funkcji number. 2 number number
- Zwraca różnicę między dwiema wartościami number. 2 number number
* Zwraca iloczyn dwóch funkcji number. 2 number number
/ Zwraca iloraz dwóch funkcji number. Dzielenie przez zero zwraca wartość null. 2 number number

Elementy języka

Litery

Literały to wartości używane w porównaniach i przypisaniach. Literały mogą mieć postać string, liczby, wartości logiczne lub różne typy dat i godzin. Oto kilka przykładów literałów używanych w składni zapytania:

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

Oto formaty poszczególnych typów literału:

string
Literał string powinien być ujęty w cudzysłowy pojedyncze lub podwójne. Przykłady: "fourteen" 'hello world' "It's raining".
number
Literały liczbowe są określone w notacji dziesiętnej. Przykłady: 3  3.0  3.14  -71  -7.2  .6
boolean
Litery wartości logiczne to true lub false.
date
Użyj słowa kluczowego date, a po nim literału string w formacie yyyy-MM-dd. Przykład: date "2008-03-18".
timeofday
Użyj słowa kluczowego timeofday, po którym następuje literał string, w formacie HH:mm:ss[.SSS] Przykład: timeofday "12:30:45".
datetime
Data i godzina, używająca słowa kluczowego datetime lub słowa kluczowego timestamp oraz literału string w formacie yyyy-MM-dd HH:mm:ss[.sss]. Przykład: datetime '2008-03-18 12:30:34.123'

Identyfikatory

Identyfikatory to tekstowe string określające kolumny.

Ważne: jeśli Twój identyfikator

  • Zawiera spacje
  • Jest zastrzeżonym słowem,
  • Nazwa koloru zawiera wszystko poza znakami alfanumerycznymi i podkreśleniami ([a–zA–Z0–9_]), lub
  • Zaczyna się od cyfry

muszą być one ujęte w cudzysłów (a nie pojedyncze cudzysłowy).

W przeciwnym razie nie musisz cytować Twojego identyfikatora. (Pamiętaj, że nie wszystkie słowa kluczowe zdefiniowane w składni są zarezerwowanymi słowami, więc możesz na przykład użyć identyfikatora „max” bez konieczności cudzysłowu.

Przykłady: col1   employee_table   `start date`   `7 days traffic`   `select`

Odradzamy wybieranie identyfikatora, który wymaga cudzysłowów cofniętych, ponieważ łatwo jest zapomnieć o cudzysłowach lub przypadkowo użyć „pojedynczych cudzysłowów” zamiast „cudzysłowów zwrotnych”. Są to typowe błędy i często trudne do debugowania.

Uwzględnianie wielkości liter

Wielkość liter w identyfikatorach i literałach ciągów znaków jest rozróżniana. W pozostałych elementach językowych wielkość liter nie jest rozróżniana.

Zarezerwowane słowa

Następujące zarezerwowane słowa muszą być ujęte w cudzysłów, jeśli są używane jako identyfikator:

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