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

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:

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.

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.

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:

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ź:

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ź:

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ź:

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ź:

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ź:

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ź:

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ź:

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ź:

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:

select upper(name), year(startDate)

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

• Funkcje agregujące
• Funkcje skalarne
• Operatory arytmetyczne

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:

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.

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:

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