Bahasa Kueri Google Visualization API memungkinkan Anda melakukan berbagai manipulasi data dengan kueri ke sumber data.

Daftar Isi

Pengantar

Biasanya, visualisasi mengharapkan data dalam beberapa bentuk tertentu. Misalnya, diagram lingkaran dapat mengharapkan data sebagai dua kolom: label teks dan nilai numerik. Data dalam sumber data mungkin tidak sama persis dengan struktur ini. Misalnya, sumber data mungkin memiliki lebih dari dua kolom, atau urutan kolom mungkin tidak cocok dengan urutan yang diharapkan oleh diagram lingkaran.

Bahasa kueri memberikan kemampuan untuk mengirimkan manipulasi data dan permintaan pemformatan ke sumber data, serta memastikan bahwa struktur dan konten data yang ditampilkan sesuai dengan struktur yang diharapkan.

Sintaksis bahasa kueri mirip dengan SQL. Developer yang sudah terbiasa dengan SQL harus dapat mempelajari dan menggunakan bahasa kueri ini dengan cepat. Ada banyak tutorial SQL yang tersedia di Web. Ada beberapa perbedaan antara bahasa kueri ini dan SQL yang dijelaskan di bagian sintaksis.

Perhatikan bahwa sumber data tidak diwajibkan untuk menerapkan bahasa kueri, atau jika diperlukan, untuk menerapkan semua fitur bahasa. Kecuali jika memiliki alasan untuk meyakini sebaliknya, Anda tidak boleh bergantung pada sumber data untuk mengimplementasikan semua fitur bahasa ini.

Menggunakan Bahasa Kueri

Anda dapat melampirkan string kueri ke permintaan sumber data dengan dua cara: dengan menetapkan string kueri dari dalam kode JavaScript, atau dengan menetapkan string kueri sebagai parameter di URL sumber data. Jika permintaan Anda tidak menyertakan string kueri, perilaku default untuk sumber data adalah menampilkan semua baris dan kolom menggunakan urutan dan pemformatan baris/kolom default. Anda dapat mengubahnya dengan menyertakan string kueri dalam permintaan ke sumber data.

Menetapkan Kueri dari JavaScript

Untuk menetapkan string kueri dari dalam kode JavaScript, panggil metode setQuery class google.visualization.Query.

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

Menetapkan Kueri di URL Sumber Data

String kueri dapat ditambahkan ke URL sumber data menggunakan parameter tq. Dengan menetapkan kueri di parameter URL, bukan di JavaScript, Anda dapat menggunakan visualisasi yang ditulis oleh developer lain dengan mudah, dan tetap dapat menyesuaikan kueri.

String kueri harus dienkode dengan benar sebagai parameter URL. Anda dapat mengenkode URL menggunakan fungsi encodeURIComponent JavaScript, atau dapat mengenkodenya dengan tangan, menggunakan alat encoding di akhir bagian ini.

Contoh:

Pertimbangkan string kueri berikut untuk Google Spreadsheet. (Perhatikan bahwa ID kolom di spreadsheet selalu huruf; teks judul kolom yang ditampilkan di spreadsheet yang dipublikasikan adalah label, bukan ID. Anda harus menggunakan ID, bukan label, dalam string kueri.)

select A, sum(B) group by A

Jika dienkode, kueri ini menjadi:

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

Asumsikan bahwa ini adalah URL spreadsheet Anda:

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

Tambahkan /gviz/tq?tq=YOUR_QUERY_STRING ke URL spreadsheet untuk mendapatkan string kueri akhir Anda:

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

Gunakan alat di bawah untuk mengenkode atau mendekode string kueri:

Sintaksis Bahasa

Ringkasan

Sintaksis Bahasa Kueri Google Visualization API dirancang agar serupa dengan sintaksis SQL. Namun, ini adalah subset SQL, dengan beberapa fitur sendiri yang perlu Anda pelajari. Jika Anda sudah terbiasa dengan SQL, seharusnya tidak terlalu sulit untuk dipelajari.

Tabel yang digunakan di semua contoh:

Di seluruh bagian ini, semua contoh kueri merujuk pada tabel berikut. Header kolom adalah ID kolom.

Klausul Bahasa

Sintaksis bahasa kueri terdiri dari klausul berikut. Setiap klausul dimulai dengan satu atau dua kata kunci. Semua klausul bersifat opsional. Klausul dipisahkan oleh spasi. Urutan klausul harus sebagai berikut:

Pilih

Klausul select digunakan untuk menentukan kolom yang akan ditampilkan dan urutannya. Jika klausul ini tidak ditentukan, atau jika select * digunakan, semua kolom tabel sumber data akan ditampilkan, dalam urutan aslinya. Kolom direferensikan oleh ID (bukan oleh label). Misalnya, di Google Spreadsheet, ID kolom adalah huruf kolom satu atau dua karakter (A, B, C, ...).

Item dalam klausul select dapat berupa ID kolom, atau output fungsi agregasi, fungsi skalar, atau operator.

Contoh:

select *

select dept, salary

select max(salary)

Dalam contoh berikut, tanda kutip belakang digunakan untuk mereferensikan ID kolom yang berisi spasi (alamat email) atau kata yang dicadangkan (tanggal):

select `email address`, name, `date`

Menjalankan kueri berikut di tabel contoh:

select lunchTime, name

Menampilkan respons berikut:

Lokasi

Klausa where digunakan untuk hanya menampilkan baris yang cocok dengan kondisi yang ditentukan.

Operator perbandingan sederhana adalah <=, <, >, >=, =, !=, <>. Kedua operator perbandingan != <> berarti tidak sama. String dibandingkan dengan nilai leksikografis. Perhatikan bahwa persamaan ditunjukkan dengan =, bukan == seperti pada sebagian besar bahasa komputer. Membandingkan dengan null dilakukan menggunakan is null atau is not null.

Anda dapat menggabungkan beberapa kondisi menggunakan operator logika and, or, dan not. Tanda kurung dapat digunakan untuk menentukan prioritas eksplisit.

Klausa di mana juga mendukung beberapa operator perbandingan string yang lebih kompleks. Operator ini menggunakan dua string sebagai argumen; setiap argumen non-string (misalnya, tanggal atau angka) akan dikonversi menjadi string sebelum perbandingan. Pencocokan string peka huruf besar/kecil (Anda dapat menggunakan fungsi skalar upper() atau lower() untuk mengatasinya).

• contains - Pencocokan substring. seluruh bagian contains benar jika bagian berada di mana pun dalam seluruh. Contoh: where name contains 'John' cocok dengan 'John's, 'John Adams', 'Long John Silver', tetapi tidak 'john adams'.
• starts with - Pencocokan awalan. nilai starts with awalan benar jika awalan berada di awal nilai. Contoh: where dept starts with 'engineering' cocok dengan 'engineering' dan 'engineering manager'. where dept starts with 'e' cocok dengan 'engineering', 'eng', dan 'e'.
• ends with - Pencocokan akhiran. value ends with suffix bernilai benar jika suffix berada di akhir value. Contoh: where role ends with 'y' cocok dengan 'koboi', 'boy', dan 'y'.
• matches - Pencocokan ekspresi reguler (preg). haystack matches needle adalah benar jika ekspresi reguler di needle cocok dengan haystack. Contoh: where country matches '.*ia' cocok dengan India dan Nigeria, tetapi tidak cocok dengan Indiana. Perhatikan bahwa ini bukanlah penelusuran global, jadi where country matches 'an' tidak akan cocok dengan 'Kanada'.
• like - Penelusuran teks yang mendukung dua karakter pengganti: %, yang cocok dengan nol atau lebih karakter apa pun, dan _ (garis bawah), yang cocok dengan satu karakter apa pun. Ini serupa dengan operator SUKA SQL. Contoh: where name like fre% cocok dengan 'fre', 'fred', dan 'freddy'.

Contoh:

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

Menjalankan kueri berikut di tabel contoh:

select name where salary > 700

Menampilkan respons berikut:

Kelompokkan Menurut

Klausa group by digunakan untuk menggabungkan nilai di seluruh baris. Satu baris dibuat untuk setiap kombinasi nilai yang berbeda dalam klausa grup menurut. Data diurutkan secara otomatis berdasarkan kolom pengelompokan, kecuali jika ditentukan oleh klausa order by.

Catatan: Jika Anda menggunakan klausul group by, maka setiap kolom yang tercantum dalam klausul select harus tercantum dalam klausul group by, atau digabungkan dengan fungsi agregasi.

Contoh:

select dept, max(salary) group by dept

Menjalankan kueri berikut di tabel contoh:

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

Menampilkan respons berikut:

Pivot

Klausul pivot digunakan untuk mengubah nilai yang berbeda dalam kolom menjadi kolom baru. Misalnya, pivot menurut kolom 'tahun' akan menghasilkan tabel dengan kolom untuk setiap tahun berbeda yang muncul di tabel asli. Tindakan ini berguna jika, misalnya, visualisasi diagram garis menggambar setiap kolom sebagai baris terpisah. Jika Anda ingin menggambar garis terpisah untuk setiap tahun, dan 'year' adalah salah satu kolom dari tabel asli, opsi yang bagus adalah menggunakan operasi pivot untuk melakukan transformasi data yang diperlukan.

Catatan: Jika Anda menggunakan klausa pivot, maka setiap kolom yang tercantum dalam klausa select harus tercantum dalam klausa group by, atau digabungkan dengan fungsi agregasi

Karena beberapa baris dapat berisi nilai yang sama untuk kolom pivot, pivot menyiratkan agregasi. Perhatikan bahwa saat menggunakan pivot tanpa menggunakan group by, tabel hasil akan berisi tepat satu baris. Misalnya, menjalankan kueri berikut di tabel contoh:

select sum(salary) pivot dept

Menampilkan respons berikut:

Ini karena 2100 adalah jumlah gaji untuk departemen Eng, 800 untuk departemen Pemasaran, dsb.

Penggunaan pivot bersama dengan group by dapat menjadi lebih berguna, karena membuat tabel berisi setiap sel yang berisi hasil agregasi untuk baris yang relevan dan kolom yang relevan. Misalnya, menjalankan kueri berikut pada tabel contoh:

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

Menampilkan respons berikut:

Anda juga dapat "membalik" tabel ini, beralih kolom dan baris, dengan beralih antara kolom pivot dan kolom group by. Menjalankan kueri berikut di tabel contoh:

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

Menampilkan respons berikut:

Anda juga dapat menggunakan lebih dari satu kolom dalam klausul pivot. Dalam hal ini, kolom tabel respons terdiri dari semua kombinasi unik nilai dalam kolom yang ada di tabel asli. Misalnya, menjalankan kueri berikut pada tabel contoh:

select sum(salary)
  pivot dept, lunchTime

Menampilkan respons berikut:

Perhatikan bahwa hanya kombinasi yang muncul di tabel asli yang diberi kolom di tabel respons. Oleh karena itu, tidak ada kolom untuk Pemasaran, 12:00:00 atau untuk Penjualan, 13:00:00.

Menggunakan lebih dari satu agregasi juga memungkinkan. Misalnya, menjalankan kueri berikut pada tabel contoh:

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

Menampilkan respons berikut:

Anda dapat menggabungkan beberapa agregasi dalam klausa select, beberapa kolom dalam klausul group by, dan beberapa kolom dalam klausul pivot. Secara internal, agregasi dilakukan oleh gabungan kolom dalam klausul pivot dan pivot grup.

Kolom yang ditentukan dalam klausa pivot mungkin tidak muncul dalam klausa select, group by, atau order by. Saat pivot digunakan, klausul order by tidak boleh berisi kolom agregasi apa pun. Alasannya adalah untuk setiap agregasi yang ditentukan dalam klausul select, banyak kolom yang dihasilkan dalam tabel hasil. Namun, Anda dapat memformat kolom agregasi saat pivot digunakan. Dengan format tersebut, semua kolom baru yang relevan dengan agregasi tertentu, yang dihasilkan oleh operasi pivot, akan diformat berdasarkan pola tertentu. Dalam contoh di atas, menambahkan format sum(salary) "some_format_string" akan memengaruhi kolom berikut: Eng sum-gaji, Jumlah gaji pemasaran, dan Jumlah gaji penjualan.

Anda dapat memberi label pada kolom agregasi. Jika tidak ada label yang ditentukan dalam klausa label, label kolom yang dihasilkan sebagai hasil dari pivot akan terdiri dari daftar nilai dalam kolom pivot, jenis agregasi (min, maks, jumlah, ...) dan label kolom gabungan. Misalnya, "Eng,12:00:00 jumlah Gaji". Jika hanya satu agregasi yang ditentukan dalam klausa select, maka bagian agregasi akan dihapus dari label, dan hanya daftar nilai dalam kolom pivot yang akan disimpan. Misalnya "Eng,12:00:00". Jika klausul label menentukan label untuk kolom agregasi, label yang diminta akan ditambahkan ke daftar nilai, jika hanya ada satu agregasi dalam klausa select, dan jika ada lebih dari satu. Misalnya, label sum(salary) "sumsal" akan menghasilkan label kolom "Eng,12:00:00 sumsal", "Eng,13:00:00 sumsal", dll.

Urutkan Menurut

Klausa order by digunakan untuk mengurutkan baris menurut nilai dalam kolom yang ditentukan.

Item dalam klausul order by dapat berupa ID kolom, atau output fungsi agregasi, fungsi skalar, atau operator.

Contoh:

order by dept, salary desc

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

Batas

Klausa limit digunakan untuk membatasi jumlah baris yang ditampilkan.

Contoh:

limit 100

Offset

Klausa offset digunakan untuk melewati sejumlah baris pertama. Jika klausa limit digunakan, offset akan diterapkan terlebih dahulu: misalnya, limit 15 offset 30 menampilkan baris 31 sampai 45.

Contoh:

offset 10
limit 30 offset 210

Label

Klausa label digunakan untuk menetapkan label untuk satu atau beberapa kolom. Perhatikan bahwa Anda tidak dapat menggunakan nilai label sebagai pengganti ID dalam kueri.

Item dalam klausul label dapat berupa ID kolom, atau output fungsi agregasi, fungsi skalar, atau operator.

Sintaksis:

label column_id label_string [,column_id label_string]

column_id

ID kolom yang diberi label.

label_string

Label yang akan ditetapkan ke kolom tersebut. Banyak visualisasi menggunakan label kolom sebagai teks untuk ditampilkan kepada pengguna akhir, seperti label legenda dalam diagram lingkaran. Label merupakan literal string, dan mengikuti aturan sintaksis tersebut.

Contoh:

Contoh berikut menetapkan label untuk kolom dept ke "Departemen", label untuk kolom nama ke "Nama Karyawan", dan label untuk kolom lokasi ke "Lokasi Karyawan":

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

Format

Klausa format digunakan untuk menentukan nilai terformat untuk sel dalam satu atau beberapa kolom. Data yang ditampilkan harus menyertakan nilai sebenarnya dan nilai berformat untuk setiap sel dalam kolom berformat. Banyak visualisasi menggunakan nilai yang tidak diformat untuk penghitungan, tetapi nilai berformat untuk tampilan. Pola yang Anda tentukan di klausa ini biasanya ditampilkan di properti pattern pada kolom yang sesuai.

Sintaksis Pola:

number, date, timeofday, datetime

Pola tanggal dan angka yang ditentukan oleh ICU.

boolean

Pola berupa string dalam format 'value-if-true:value-if-false'.

Contoh:

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

Opsi

Klausa options digunakan untuk mengontrol opsi tambahan untuk eksekusi kueri. Kemungkinan kata kunci yang dapat mengikuti klausa options adalah:

• no_format Menghapus nilai berformat dari hasil, dan hanya menyisakan nilai yang mendasarinya. Dapat digunakan saat visualisasi tertentu tidak menggunakan nilai berformat untuk mengurangi ukuran respons.
• no_values Menghapus nilai yang mendasarinya dari hasil, dan hanya menyisakan nilai yang diformat. Dapat digunakan jika visualisasi tertentu hanya menggunakan nilai berformat untuk mengurangi ukuran respons.

Fungsi Manipulasi Data

Ada beberapa jenis operator dan fungsi yang memungkinkan Anda memanipulasi atau menggabungkan data dalam satu kolom, atau membandingkan atau menggabungkan data di seluruh kolom. Contohnya termasuk jumlah() (untuk menambahkan semua nilai dalam kolom), maks (untuk menemukan nilai terbesar dalam kolom), dan + (untuk menambahkan nilai dua kolom secara bersamaan dalam baris yang sama).

Beberapa fungsi dapat muncul di klausa apa pun; beberapa dapat muncul di subset klausa. Hal ini didokumentasikan di bawah.

Contoh:

select upper(name), year(startDate)

Fungsi manipulasi data berikut ditentukan oleh bahasa kueri Google Visualization API:

• Fungsi Agregasi
• Fungsi Skalar
• Operator Aritmetika

Fungsi Agregasi

Fungsi agregasi diteruskan ke satu ID kolom, dan melakukan tindakan di semua nilai dalam setiap grup (grup ditentukan oleh klausa group by atau pivot, atau semua baris jika klausul tersebut tidak digunakan).

Contoh:

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.

Fungsi agregasi dapat digunakan dalam klausa select, order by, label, format. Baris tersebut tidak boleh muncul di klausa where, group by, pivot, limit, offset, atau options.

Berikut adalah fungsi agregasi yang didukung:

Catatan: Fungsi agregasi hanya dapat menggunakan ID kolom sebagai argumen:

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

Fungsi Skalar

Fungsi skalar beroperasi dengan lebih dari nol parameter atau lebih untuk menghasilkan nilai lain. Fungsi skalar dapat diteruskan ke ekspresi apa pun yang dievaluasi ke parameter jenis yang sesuai. Perhatikan bahwa jenis ini adalah jenis yang ditentukan di bagian Literal dokumen ini, yang mungkin sedikit berbeda dari objek JavaScript yang bernama serupa.

Perhatikan bahwa nama kolom akan diubah dengan menggabungkannya dengan fungsi skalar.

Fungsi skalar dapat mengambil apa pun sebagai parameter yang bernilai satu nilai:

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

Fungsi skalar dapat digunakan dalam klausa berikut: select, where, group by, pivot, order by, label,, dan format.

Operator Aritmetika

Anda dapat menggunakan operator aritmatika untuk menjalankan operasi matematika pada apa pun yang mengevaluasi ke angka tunggal (yaitu, output dari fungsi agregat, operator, atau konstanta yang sesuai).

Contoh:

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

Operator berikut ditentukan:

Elemen Bahasa

Literal

Literal adalah nilai yang digunakan untuk perbandingan atau penetapan. Literal dapat berupa string, angka, nilai boolean, atau berbagai jenis tanggal/waktu. Berikut adalah beberapa contoh literal yang digunakan dalam sintaksis kueri:

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

Berikut adalah format untuk setiap jenis literal:

string

Literal string harus diapit dalam tanda kutip tunggal atau ganda. Contoh: "fourteen" 'hello world' "It's raining".

number

Literal angka ditentukan dalam notasi desimal. Contoh: 3  3.0  3.14  -71  -7.2  .6

boolean

Literal Boolean dapat berupa true atau false.

date

Gunakan kata kunci date diikuti dengan literal string dalam format yyyy-MM-dd. Contoh: date "2008-03-18".

timeofday

Gunakan kata kunci timeofday diikuti dengan literal string dalam format HH:mm:ss[.SSS] Contoh: timeofday "12:30:45".

datetime

Tanggal dan waktu, menggunakan kata kunci datetime atau kata kunci timestamp diikuti dengan literal string dalam format yyyy-MM-dd HH:mm:ss[.sss]. Contoh: datetime '2008-03-18 12:30:34.123'

ID

ID (atau ID) adalah string teks yang mengidentifikasi kolom.

Penting: Jika ID Anda

• Memiliki spasi,
• Merupakan kata yang dicadangkan,
• Berisi karakter apa pun kecuali karakter alfanumerik atau garis bawah ([a-zA-Z0-9_]), atau
• Diawali dengan digit

harus diapit tanda kutip belakang (bukan tanda kutip tunggal).

Jika tidak, ID Anda tidak perlu dikutip. (Perhatikan bahwa tidak semua kata kunci yang ditentukan oleh sintaksis adalah kata yang dicadangkan; jadi, misalnya, Anda dapat menggunakan "max" sebagai ID, tanpa harus mengutipnya kembali.)

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

Sebaiknya jangan pilih ID yang mewajibkan tanda kutip belakang, karena lupa menggunakan tanda kutip belakang, atau menggunakan 'tanda kutip tunggal' secara tidak sengaja, bukan `tanda kutip belakang`. Hal ini merupakan kesalahan umum, dan sering kali sulit di-debug.

Sensitivitas Kasus

ID dan literal string peka huruf besar/kecil. Semua elemen bahasa lainnya tidak peka huruf besar/kecil.

Kata yang Direservasi

Kata-kata yang dicadangkan berikut harus diberi tanda kutip jika digunakan sebagai ID:

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