Język zapytań Google wizualizacji interfejsu API umożliwia wykonywanie różnych operacji na danych w ramach zapytania do źródła danych.

Spis treści

Wprowadzenie

Zazwyczaj wizualizacje oczekują danych w określonej formie. Na przykład na wykresie kołowym dane mogą przedstawiać 2 kolumny: etykietę tekstową i wartość liczbową. Dane w źródle danych mogą nie być dokładnie zgodne z tą strukturą. Na przykład źródło danych może mieć więcej niż 2 kolumny lub kolejność kolumn może nie odpowiadać kolejności oczekiwanej przez wykres kołowy.

Język zapytań umożliwia wysyłanie do źródła danych żądań manipulacji danymi i ich formatowanie oraz zapewnia, że zwrócona struktura i zawartość danych są zgodne z oczekiwaną strukturą.

Składnia języka zapytania jest podobna do składni SQL. Deweloperzy, którzy znają się na języku SQL, powinni być w stanie szybko się nauczyć i korzystać z tego języka zapytań. W internecie dostępnych jest wiele samouczków SQL. Istnieją pewne różnice między tym językiem zapytań a językiem SQL, które są opisane w sekcji składnia.

Źródła danych nie są wymagane do implementacji języka zapytań, a jeśli tak, do wdrażania wszystkich funkcji w języku. Jeśli nie masz innych przekonań, nie korzystaj z źródeł danych, aby wdrożyć wszystkie funkcje w tym 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 go w kodzie JavaScript lub jako parametr w adresie URL źródła danych. Jeśli żądanie nie zawiera ciągu zapytania, domyślnym źródłem danych jest zwracanie wszystkich wierszy i kolumn za pomocą domyślnej kolejności i formatowania kolumn. Aby to zmienić, umieść ciąg zapytania w źródle 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 języku JavaScript pozwoli Ci łatwo korzystać z wizualizacji napisanych przez innych deweloperów, a także nadal dostosowywać to zapytanie.

Ciąg zapytania musi być prawidłowo zakodowany jako parametr adresu URL. Możesz zakodować adres URL za pomocą funkcji JavaScript encodeURIComponent lub zakodować go ręcznie przy użyciu narzędzia do kodowania dostępnego na końcu tej sekcji.

Przykład:

Rozważ użycie tego ciągu zapytania dotyczącego arkusza kalkulacyjnego Google. Pamiętaj, że identyfikatory kolumn w arkuszach kalkulacyjnych to zawsze litery, a tekst nagłówka kolumn w opublikowanym arkuszu 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 to zapytanie staje się:

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

Załóżmy, że to 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 otrzymać 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 poniższego narzędzia:

Składnia języka

Omówienie

Składnia języka zapytań w interfejsie API Google do wizualizacji jest podobna do składni SQL. Jest to jednak podzbiór SQL z kilkoma jego funkcjami, których musisz się nauczyć. Znajomość SQL nie powinna być zbyt trudna.

Tabela używana we wszystkich przykładach:

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

Klauzule językowe

Składnia języka zapytania składa się z następujących klauzul. Każda klauzula zaczyna się od jednego lub dwóch słów kluczowych. Wszystkie klauzule są opcjonalne. Klauzule są oddzielone spacjami. Klauzule muszą mieć format:

Wybierz

Klauzula select służy do określenia kolumn, które zostaną zwrócone, oraz ich kolejności. Jeśli ta klauzula nie jest określona lub jeśli pole select * jest używane, zwracane są wszystkie kolumny tabeli źródła danych w oryginalnej kolejności. Odwołują się do kolumn identyfikatory, a nie etykiety. Na przykład w arkuszu kalkulacyjnym Google identyfikatory kolumn to litery jedno- lub dwuznakowe (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 odwołują się do identyfikatorów kolumn, które zawierają spacje (adres e-mail) lub zarezerwowane słowa (data):

select `email address`, name, `date`

Uruchamianie tego zapytania w 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ą nierówne wartości. Ciągi tekstowe są porównywane z wartością leksykograficzną. Pamiętaj, że w przypadku większości języków komputerowych równość jest określana jako =, a nie ==. Porównanie z null odbywa się za pomocą is null lub is not null.

Możesz połączyć wiele warunków za pomocą operatorów logicznych and, or i not. Do określania wyraźnego priorytetu można używać nawiasów.

Klauzula gdzie zawiera też bardziej złożone operatory porównania ciągów znaków. Te operatory wykorzystują 2 ciągi jako argumenty. Wszelkie argumenty niebędące ciągami (na przykład daty lub liczby) zostaną przed przekształceniem w ciągi. W przypadku dopasowania 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. Cała część contains ma wartość true (prawda), jeśli część jest w dowolnym miejscu w całości. Przykład: where name contains 'John' dopasowuje „Jan”, „Jan Kowalski”, „Jan srebrny”, ale nie „jan”.
• starts with – dopasowanie prefiksu. Wartość starts with Prefiks to prawda, jeśli prefiks znajduje się na początku wartości wartość. Przykłady: where dept starts with 'engineering' pasuje do haseł „inżynierowie” i „menedżerowie inżynierii”. where dept starts with 'e' odpowiada operatorom „inżynieria”, „eng” i „e”.
• ends with – dopasowanie sufiksu. Wartość ends with Suffix ma wartość true (prawda), jeśli suffix jest na końcu value. Przykład: where role ends with 'y' dopasowuje „kowboj”, „chłopiec” i „y”.
• matches – dopasowanie wyrażenia regularnego (preg). haystack matchesigła ma wartość prawda, jeśli wyrażenie regularne w elemencie needle pasuje do haystack. Przykłady: where country matches '.*ia' pasuje do Indii i Nigerii, ale nie do Indiany. Pamiętaj, że nie jest to wyszukiwanie globalne, więc hasło where country matches 'an' nie będzie pasować do słowa „Kanada”.
• like – wyszukiwanie tekstowe obsługujące 2 symbole wieloznaczne: %, który odpowiada dowolnemu znakowi lub więcej, oraz _ (podkreślenie), który odpowiada dowolnemu znakowi. Jest on podobny do operatora SQL LIKE. Przykład: where name like fre% dopasowuje „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

Uruchamianie tego zapytania w przykładowej tabeli:

select name where salary > 700

Zwraca tę odpowiedź:

Grupuj według

Klauzula group by służy do agregowania wartości w wierszach. Pojedynczy wiersz jest tworzony dla każdej innej kombinacji wartości w klauzuli group-by. Dane są automatycznie sortowane według kolumn grupowania, chyba że inaczej określono klauzulę order by.

Uwaga: jeśli używasz klauzuli group by, wszystkie kolumny wymienione w klauzuli select muszą być wymienione w klauzuli group by lub muszą być uwzględnione w funkcji agregacji.

Przykłady:

select dept, max(salary) group by dept

Uruchamianie tego zapytania w 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 kolumną dla każdego oddzielnego roku pojawiającego się w oryginalnej tabeli. Przydaje się to np. wtedy, gdy wizualizacja wykresu liniowego zawiera każdą kolumnę jako oddzielną linię. Jeśli chcesz narysować oddzielny wiersz dla każdego roku, a „rok” jest jedną z kolumn pierwotnej tabeli, dobrym rozwiązaniem będzie użycie operacji przestawnej, która zapewni niezbędną przekształcenie danych.

Uwaga: jeśli używasz klauzuli pivot, wszystkie kolumny zawarte w klauzuli select muszą być wymienione w klauzuli group by lub muszą być uwzględnione w funkcji agregacji.

Ponieważ wiele wierszy może zawierać te same wartości w kolumnach przestawnych, oznacza to, że tabela przestawna jest agregowana. Pamiętaj, że jeśli używasz pivot bez group by, tabela wyników będzie zawierać tylko jeden wiersz. Na przykład możesz uruchomić to zapytanie w przykładowej tabeli:

select sum(salary) pivot dept

Zwraca tę odpowiedź:

Dzieje się tak, ponieważ 2100 to suma zarobków dla działu IT, 800 dla działu marketingu itp.

Użycie funkcji pivot w połączeniu z funkcją group by może być jeszcze bardziej przydatne, ponieważ tworzy tabelę, w której każda komórka zawiera wynik agregacji określonego wiersza i odpowiedniej kolumny. Na przykład w przykładowej tabeli możesz uruchomić to zapytanie:

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

Zwraca tę odpowiedź:

Możesz też „odwrócić” tę tabelę, przełączając kolumny i wiersze, przełączając się między kolumnami pivot i group by. Uruchamianie tego zapytania w przykładowej tabeli:

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

Zwraca tę odpowiedź:

W klauzuli pivot możesz też użyć więcej niż jednej kolumny. W takim przypadku kolumny tabeli odpowiedzi składają się z wszystkich unikalnych kombinacji wartości w kolumnach, które istnieją w pierwotnej tabeli. Na przykład w przykładowej tabeli możesz uruchomić to zapytanie:

select sum(salary)
  pivot dept, lunchTime

Zwraca tę odpowiedź:

Zwróć uwagę, że tylko kombinacje pojawiające się w oryginalnej tabeli zawierają w tabeli odpowiedzi kolumny. Dlatego nie ma kolumny na potrzeby marketingu, 12:00:00 ani sprzedaży.13:00:00.

Możesz też użyć więcej niż jednej agregacji. Na przykład w przykładowej tabeli możesz uruchomić to zapytanie:

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

Zwraca tę odpowiedź:

Możesz połączyć wiele agregacji w klauzuli select, wielu kolumnach w klauzuli group by i wielu kolumnach w klauzuli pivot. Agregacja jest wykonywana przez połączenie kolumn w grupie według klauzul tabeli przestawnej.

Kolumny określone w klauzuli pivot nie mogą występować w klauzulach select, group by lub order by. W przypadku użycia właściwości 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 wiele kolumn jest generowanych w tabeli wyników. Możesz jednak sformatować kolumny agregacji, gdy używasz atrybutu pivot. W wyniku tego formatu wszystkie nowe kolumny istotne dla określonej agregacji wygenerowane przez operację przestawiania są formatowane według określonego wzorca. W powyższym przykładzie dodanie kolumny format sum(salary) "some_format_string" wpłynie na te kolumny: wynagrodzenie sumy, wynagrodzenie marketingowe i wynagrodzenie sumy sprzedaży.

Kolumny agregacji można oznaczyć etykietami. Jeśli w klauzuli label nie określono żadnej etykiety, etykieta kolumny utworzonej w wyniku tabeli przestawnej składa się z listy wartości w kolumnach przestawnych, typu agregacji (min., maks., sumy...) i etykiety kolumny zbiorczej. Na przykład „Eg.,12:00:00 sumy wynagrodzenia”. Jeśli w klauzuli select określono tylko jedną agregację, z etykiety usunięto część agregacji i zachowana zostanie tylko lista wartości z kolumn przestawnych. Na przykład „Eg.,12:00:00”. Gdy klauzula label określa etykietę w kolumnie agregacji, żądana etykieta jest dodawana do listy wartości, gdy występuje tylko 1 agregacja w klauzuli select i więcej niż 1. label sum(salary) "sumsal" spowoduje na przykład dodanie etykiet kolumn: „Eg, 12:00:00 sums”, „Eng,13:00:00 sums” itp.

Zamów wg

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 ograniczenia liczby zwróconych wierszy.

Przykład:

limit 100

Odsunięcie

Klauzula offset służy do pomijania podanej liczby pierwszych wierszy. W przypadku użycia klauzuli limit najpierw stosowana jest wartość 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 w co najmniej jednej kolumnie. Pamiętaj, że nie możesz użyć wartości etykiety zamiast identyfikatora w zapytaniu.

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 jest przypisana etykieta.

label_string

Etykieta, którą można przypisać do tej kolumny. Wiele wizualizacji wykorzystuje etykietę kolumny jako tekst do wyświetlania użytkownikowi, np. legendę na wykresie kołowym. Etykiety są literami ciągu znaków i podlegają tym regułom składni.

Przykład:

W tym przykładzie etykieta kolumny „Dział” to „Dział”, etykieta kolumny nazwy to „Nazwa pracownika”, a etykieta kolumny „Lokalizacja” to „Lokalizacja pracownika”.

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

Format

Klauzula format służy do określania sformatowanej wartości komórek w co najmniej jednej kolumnie. Zwracane dane powinny zawierać zarówno rzeczywistą, jak i sformatowaną wartość każdej komórki w sformatowanej kolumnie. Wiele wizualizacji korzysta z niesformatowanych wartości do obliczeń, ale z wartości formatowania w postaci reklam displayowych. 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 daty i liczby zdefiniowane przez wzór.

boolean

Wzorzec to string w formacie „value-if-true:value-if-false”.

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ń. Po sekcji options mogą występować następujące słowa kluczowe:

• no_format Usuwa sformatowane wartości z wyniku i pozostawia tylko wartości bazowe. Tej opcji można używać, gdy konkretna wizualizacja nie używa sformatowanych wartości, aby zmniejszyć rozmiar odpowiedzi.
• no_values powoduje usunięcie podstawowych wartości z wyniku i pozostawia tylko wartości sformatowane. Tego ustawienia można użyć, jeśli konkretną wizualizację używa tylko sformatowanych wartości, aby zmniejszyć rozmiar odpowiedzi.

Funkcje manipulowania danymi

Istnieje kilka rodzajów operatorów i funkcji, które pozwalają manipulować danymi w jednej kolumnie lub je porównywać lub łączyć. Może to być np. funkcja sum() (dodanie wszystkich wartości do kolumny), maksymalna (aby znaleźć największą wartość w kolumnie) lub + (dodanie wartości z 2 kolumn do tego samego wiersza).

Niektóre funkcje mogą występować w dowolnej klauzuli, a niektóre w podzbiorze klauzul. Dokumentujemy to poniżej.

Przykład:

select upper(name), year(startDate)

Te funkcje manipulacji danymi są definiowane przez język zapytania Google wizualizacji interfejsu API:

• Funkcje agregacji
• Funkcje skalarne
• Operatory arytmetyczne

Funkcje agregacji

Funkcje agregacji przekazują pojedynczą kolumnę identifier i wykonują działanie na wszystkich wartościach w każdej grupie (grupy są określane za pomocą klauzul group by lub pivot albo wszystkich wierszy, jeśli nie są one 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, format. Nie mogą występować w klauzulach where, group by, pivot, limit, offset ani options.

Oto obsługiwane funkcje agregacji:

Uwaga: funkcje agregacji mogą przyjmować jako identyfikator kolumny tylko 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 obsługują więcej niż zero parametrów, co pozwala uzyskać inną wartość. Funkcje skalarne mogą być przekazywane do każdego wyrażenia, które ocenia wartość parametru odpowiedniego typu. Pamiętaj, że są to typy zdefiniowane w sekcji Literals w tym dokumencie. Mogą one nieznacznie różnić się od obiektów JavaScript o podobnej nazwie.

Pamiętaj, że nazwa kolumny zostanie zmieniona, dodając do niej funkcję skalarną.

Funkcje skalarne mogą przyjmować jako parametry, które oceniają pojedynczą wartość:

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

Funkcje skalarne mogą być używane w dowolnym z tych klauzul: select, where, group by, pivot, order by, label, i format.

Operatory arytmetyczne

Operatory arytmetyczne umożliwiają wykonywanie operacji matematycznych na dowolnych wartościach, które otrzymują pojedynczy wynik (tj. dane wyjściowe odpowiednich funkcji zbiorczych, operatorów lub stałych).

Przykłady:

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

Zdefiniowano następujące operatory:

Elementy języka

Literie

Literały to wartości używane do porównań i przypisań. Literatury mogą obejmować string, liczby, wartości logiczne lub różne typy daty/godziny. Oto kilka przykładów literałów używanych w składni zapytań:

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 rodzajów literałów:

string

Literał string powinien być umieszczony w cudzysłowie prostym lub podwójnym. Przykłady: "fourteen" 'hello world' "It's raining".

number

Dosłowne liczby są określone w zapisie dziesiętnym. Przykłady: 3  3.0  3.14  -71  -7.2  .6

boolean

Dosłowne wartości logiczne to true lub false.

date

Użyj słowa kluczowego date, a następnie literału string w formacie yyyy-MM-dd. Przykład: date "2008-03-18".

timeofday

Użyj słowa kluczowego timeofday, a następnie literału string w formacie HH:mm:ss[.SSS] Przykład: timeofday "12:30:45".

datetime

Data i godzina za pomocą słowa kluczowego datetime lub słowa kluczowego timestamp, a następnie literału string w formacie yyyy-MM-dd HH:mm:ss[.sss]. Przykład: datetime '2008-03-18 12:30:34.123'

Identyfikatory

Identyfikatory (identyfikatory) to tekst string, który identyfikuje kolumny.

Ważne: jeśli Twój identyfikator

• Zawiera spacje
• to zarezerwowane słowo,
• Zawiera wszystkie znaki lub znaki alfanumeryczne ([a-zA-Z0-9_]) lub
• Zaczyna się cyfrą

muszą być w cudzysłowie prostym (a nie w cudzysłowie prostym).

W przeciwnym razie Twój identyfikator nie musi być cytowany. (Pamiętaj, że nie wszystkie słowa kluczowe zdefiniowane przez składnię są zarezerwowanymi słowami, dlatego możesz zastosować na przykład wartość „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, ponieważ łatwo jest zapomnieć o cudzysłowach lub przypadkowo użyć „cudzysłowów prostych” zamiast „cudzysłowów prostych”. Są to typowe błędy i często trudno je debugować.

Wielkość liter

W identyfikatorach i literach ciągu znaków wielkość liter ma znaczenie. W pozostałych elementach językowych nie jest rozróżniana wielkość liter.

Zarezerwowane słowa

Jeśli używasz tego identyfikatora jako identyfikatora, musisz używać cudzysłowu prostego:

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