googlesheetsquery

Mengkueri Google Spreadsheet

Bahasa Kueri Google adalah cara yang cepat dan ampuh untuk menganalisis data di lembar Google Anda yang meminjam banyak pola yang digunakan dalam SQL. Tutorial ini akan membiasakan Anda dengan dasar-dasar cara menggunakan kueri untuk menjawab pertanyaan spesifik dari kumpulan data sampel.

Impor Data Sampel

Pertama, kita memerlukan beberapa contoh data untuk dimainkan. Kami akan menggunakan fungsi impor untuk mengambil tabel data populasi dunia dari Wikipedia:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)

Fungsi ini menemukan tabel ke-3 pada URL di atas, mengambil data dari setiap sel, dan mengimpornya ke Google Sheet Anda. Dingin! Namun, mari kita lakukan satu hal lagi untuk membuat kueri data ini sedikit lebih mudah.

Klik sel mana pun yang berisi data untuk menyorotnya, lalu tekan CMD + A untuk menyorot seluruh tabel. Dengan seluruh tabel disorot, klik Data > Named Ranges untuk membuka panel Named Ranges — panel akan terbang keluar dari kanan.

Rentang bernama adalah nama panggilan yang dapat Anda berikan kepada sekelompok sel. Mari kita beri nama 'negara' kita.

Besar! Sekarang kami siap untuk mulai membuat kueri!

Sorot sel J1 — kita akan menggunakan sel ini untuk mengetikkan semua kueri di bawah ini:

Dasar-dasar Kueri

Tunjukkan kepada saya salinan seluruh kumpulan data:

=QUERY(countries, "select *",1)

Tunjukkan pada saya kolom Negara dan Wilayah saja:

=QUERY(countries, "select B, C",1)

Menjadi spesifik dengan Klausul 'Di Mana'

Klausa WHERE adalah cara terbaik untuk mulai mengajukan pertanyaan yang lebih spesifik dari kumpulan data Anda.

Tunjukkan kepada saya negara-negara dengan populasi lebih dari atau sama dengan 100 juta:

=QUERY(countries, "SELECT B, E where E >= 100000000")

Tunjukkan negara-negara dengan 'es' di namanya:

=QUERY(countries, "SELECT B where B contains 'es'")

Tunjukkan negara-negara yang namanya tidak ada 'es':

=QUERY(countries, "SELECT B where not B contains 'es'")

Tunjukkan negara yang dimulai dengan 'S':

=QUERY(countries, "SELECT B where B STARTS WITH 'S'")

Tunjukkan negara yang diakhiri dengan 's' (ingat untuk membedakan huruf besar-kecil:

=QUERY(countries, "SELECT B where B ENDS WITH 's'")

Tunjukkan negara-negara yang mengandung 'United':

=QUERY(countries, "SELECT B where B CONTAINS 'United'")

Mari kita tambahkan lebih spesifik lagi pada klausa AND!

Tunjukkan semua negara di Amerika yang juga memiliki populasi kurang dari 100.000:

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)

Klausa AND dapat dirangkai secara ad infinitum.

Kueri ini menampilkan semua negara di Amerika dengan populasi kurang dari 100.000 dan lebih dari 10.000:

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)

Nilai Agregat

Mungkin salah satu hal pertama yang ingin Anda lakukan saat menjelajahi kumpulan data adalah melihat beberapa agregasi sederhana: nilai maks, min, dan rata-rata di bidang tertentu. Mari kita cari jumlah penduduk maksimum, minimum, dan rata-rata tahun 2017 untuk negara-negara dalam kumpulan data kami:

=QUERY(countries, "select max(F), min(F), avg(F)", 1)

Itu keren,

Dengan sedikit modifikasi kita dapat melihat populasi maksimal, minimum, dan rata-rata di setiap benua:

=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)

'Hitung' dan 'Kelompokkan Berdasarkan' bersama-sama merupakan cara hebat lainnya untuk menjelajahi kumpulan data. Jika digabungkan, keduanya memungkinkan Anda menghitung item dalam satu atau beberapa kolom dan melihat serta menggabungkan tampilan.

Mari kita gunakan dua klausa ini untuk melihat berapa banyak negara dalam daftar kita di setiap wilayah:

=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)

Mari tambahkan klausa 'ORDER BY' untuk menunjukkan kepada kita daftar semua wilayah, yang diurutkan dari tertinggi ke terendah:

=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)

Klausa 'PIVOT' akan menggabungkan nilai ke dalam satu baris.

Misalnya, jika kita ingin menampilkan jumlah seluruh populasi pada tahun 2017 berdasarkan wilayah, kita dapat menggunakan:

=QUERY(countries, "SELECT sum(F) pivot C", 1)

Melakukan Beberapa Matematika

Anda juga dapat menganalisis data dengan melakukan operasi matematika sebagai bagian dari kueri Anda.

Sekarang mari kita tampilkan daftar negara-negara dengan jumlah penduduk tahun 2017 sebagai persentase dari total penduduk dunia:

=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)

Data persentase kami terlihat keren, tetapi header kolomnya sangat mudah dibaca. Kita dapat mengklik sel tersebut dan mengganti namanya — namun kita juga dapat menetapkan nama tersebut sebagai bagian dari kueri dengan menambahkan parameter 'label':

=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)

Referensi

Dokumentasi Bahasa Kueri Google: https://developers.google.com/chart/interactive/docs/querylingual

Ikhtisar Fungsi Kueri Google Sheets dengan Coding Is For Losers: https://codingisforlosers.com/google-sheets-query-function/#why