Mulailah membuat kueri data dengan Bahasa Kueri sederhana ini

Bekerja dengan data menjadi keterampilan yang semakin penting di tempat kerja modern.

Data tidak lagi menjadi domain analis dan insinyur perangkat lunak. Dengan teknologi saat ini, siapa pun dapat bekerja dengan data untuk menganalisis tren dan menginformasikan pengambilan keputusan mereka.

Konsep dasar saat bekerja dengan data adalah 'membuat kueri' kumpulan data. Ini untuk secara harfiah mengajukan pertanyaan tentang sekumpulan data. Bahasa kueri adalah bahasa perangkat lunak yang menyediakan sintaksis untuk mengajukan pertanyaan semacam itu.

Jika Anda tidak memiliki pengalaman menulis kueri, pertanyaan itu mungkin tampak sedikit menakutkan. Namun, dengan sedikit latihan, Anda bisa menguasai dasar-dasarnya.

Inilah cara Anda memulai di Google Sheets.

Bahasa Kueri Google Visualization API

Anda mungkin sudah menggunakan Google Sheets untuk sebagian besar pekerjaan Anda sehari-hari. Mungkin Anda sudah terbiasa menggunakannya untuk menghasilkan bagan dan grafik.

Bahasa Kueri Google Visualization API adalah keajaiban yang bekerja di balik layar untuk memungkinkan hal ini.

Tetapi tahukah Anda bahwa Anda dapat mengakses bahasa ini melalui QUERY()fungsinya? Ini bisa menjadi alat yang ampuh untuk bekerja dengan lembaran data yang besar.

Ada banyak kesamaan antara bahasa kueri dan SQL.

Dalam kedua kasus tersebut, Anda menentukan kumpulan data kolom dan baris, dan memilih kolom dan baris yang berbeda dengan menentukan berbagai kriteria dan kondisi.

Pada artikel ini, contoh data akan berasal dari file CSV besar yang berisi hasil pertandingan sepak bola internasional antara tahun 1872 dan 2019. Anda dapat mendownload data tersebut dari Kaggle.

Di Google Spreadsheet baru, unggah file CSV. Anda dapat memilih semua data dengan Ctrl + A (atau Cmd + A di Mac).

Dari pita menu, pilih Data> Rentang bernama ... dan panggil rentang yang dipilih seperti 'data'. Ini akan membuatnya lebih mudah untuk dikerjakan.

Sekarang, Anda siap untuk mulai melakukan kueri data. Buat tab baru di lembar bentang, dan di sel A1, buat QUERY()rumus baru .

Dapatkan semua pertandingan Inggris

Kueri pertama ini menemukan semua baris dalam kumpulan data di mana Inggris adalah tim tuan rumah atau tim tamu.

The QUERY()Rumus membutuhkan setidaknya dua argumen. Yang pertama adalah rentang bernama, yang akan menjadi kumpulan data yang dikueri. Yang kedua adalah string yang berisi kueri sebenarnya.

=QUERY(data, "SELECT * WHERE B = 'England' OR C = 'England'")

Mari kita uraikan ini.

SELECT *meminta untuk mengembalikan semua kolom dalam kumpulan data. Jika Anda hanya menginginkan kolom A, B dan C, Anda akan menulis SELECT A, B, C.

Selanjutnya, Anda menyertakan filter untuk hanya menemukan baris di mana kolom B atau kolom C berisi tim 'England'. Pastikan untuk menggunakan tanda kutip tunggal untuk string di dalam kueri. Tanda kutip ganda digunakan untuk membuka dan menutup kueri itu sendiri.

Rumus ini mengembalikan semua baris tempat Inggris bermain. Jika Anda ingin mencari tim lain, cukup ubah kondisi di filter.

Hitung semua pertandingan persahabatan

Selanjutnya, mari kita hitung berapa banyak pertandingan persahabatan di kumpulan data.

=QUERY(data, "SELECT COUNT(A) WHERE F = 'Friendly'")

Ini memanfaatkan fungsi Query Language COUNT(). Ini adalah contoh fungsi agregat. Fungsi agregat meringkas banyak baris menjadi satu.

Misalnya, dalam kumpulan data ini terdapat 16.716 baris dengan kolom F sama dengan 'Friendly'. Alih-alih mengembalikan semua baris ini, kueri mengembalikan satu baris - yang menghitungnya sebagai gantinya.

Contoh lain dari fungsi agregat termasuk MAX(), MIN()dan AVG(). Alih-alih mengembalikan semua baris yang cocok dengan kueri, ia menemukan nilai maksimum, minimum dan rata-rata sebagai gantinya.

Kelompok berdasarkan turnamen

Fungsi agregat dapat melakukan lebih banyak jika Anda menggunakan GROUP BYpernyataan di sampingnya. Kueri ini mencari tahu berapa banyak pertandingan yang telah dimainkan oleh setiap jenis turnamen.

=QUERY(data, "SELECT F, COUNT(A) GROUP BY F")

Kueri ini mengelompokkan kumpulan data dengan masing-masing nilai di kolom F. Kemudian menghitung berapa banyak baris yang ada di setiap grup.

Anda dapat menggunakan GROUP BYlebih dari satu kolom. Misalnya, untuk menemukan berapa banyak pertandingan yang telah dimainkan di setiap negara menurut turnamen, gunakan kueri di bawah ini:

=QUERY(data, "SELECT H, F, COUNT(A) GROUP BY H, F")

Mari kita coba beberapa pemfilteran lanjutan.

Dapatkan semua pertandingan Inggris vs Jerman

Anda dapat menentukan logika filter yang lebih kompleks menggunakan kata kunci ANDdan OR. Agar mudah dibaca, sebaiknya gunakan tanda kurung di sekitar setiap bagian filter.

Misalnya, untuk menemukan semua pertandingan antara Inggris dan Jerman:

=QUERY(data, "SELECT * WHERE (B = 'England' AND C = 'Germany') OR (C = 'England' AND B ='Germany')")

Filter ini memiliki dua kriteria - satu di mana Inggris adalah tim tuan rumah dan Jerman pergi, dan yang lainnya sebaliknya.

Menggunakan validasi data memudahkan untuk memilih dua tim dalam kumpulan data.

Kemudian, Anda dapat menulis kueri yang menggunakan nilai sel yang berbeda dalam filternya. Ingatlah untuk menggunakan tanda kutip tunggal untuk mengidentifikasi string dalam kueri, dan tanda kutip ganda untuk membuka dan menutup bagian kueri yang berbeda.

=QUERY(data, "SELECT * WHERE (B = '"&B1&"' AND C = '"&B2&"') OR (C = '"&B1&"' AND B ='"&B2&"')")

Mencari tren

Fungsi dan filter agregat membuat alat canggih saat digunakan dalam kombinasi. Setelah Anda merasa nyaman dengan cara kerjanya, Anda dapat mulai mencari semua jenis tren menarik dalam kumpulan data Anda.

Misalnya, kueri di bawah ini menemukan sasaran rata-rata per game, setiap tahun sejak 1900.

=QUERY(data, "SELECT YEAR(A), AVG(D) + AVG(E) WHERE YEAR(A) >= 1900 GROUP BY YEAR(A)")

Jika Anda memplot hasil kueri sebagai grafik garis, Anda dapat segera mulai melihat tren dari waktu ke waktu.

Mengurutkan hasil

Terkadang, Anda tidak tertarik untuk menemukan semua baris yang cocok dalam satu set data. Seringkali, Anda ingin mengurutkannya berdasarkan beberapa kriteria. Mungkin Anda hanya ingin menemukan sepuluh rekaman teratas.

Kueri ini menemukan sepuluh kecocokan skor tertinggi dalam kumpulan data.

=QUERY(data, "SELECT * ORDER BY (D+E) DESC LIMIT 10")

Perhatikan ORDER BYpernyataannya. Ini mengurutkan baris sesuai dengan kolom yang ditentukan. Di sini, kueri mengurutkan hasil berdasarkan jumlah gol yang dicetak dalam permainan.

Kata DESCkunci menunjukkan untuk mengurutkan dalam urutan menurun ( ASCkata kunci akan mengurutkannya dalam urutan naik).

Akhirnya, LIMITkata kunci membatasi keluaran ke sejumlah baris (dalam hal ini, sepuluh).

Sepertinya ada beberapa permainan sepihak yang indah di Oseania!

Kota mana yang paling sering menyelenggarakan pertandingan Piala Dunia?

Dan sekarang untuk satu contoh terakhir untuk menyatukan semuanya dan mewujudkan imajinasi Anda.

Kueri ini menemukan sepuluh kota teratas yang menyelenggarakan pertandingan Piala Dunia FIFA terbanyak.

=QUERY(data, "SELECT G, COUNT(A) WHERE F = 'FIFA World Cup' GROUP BY G ORDER BY COUNT(A) DESC LIMIT 10")

Sekarang giliranmu

Semoga artikel ini bermanfaat bagi Anda. Jika Anda merasa nyaman dengan logika di setiap contoh, maka Anda siap untuk mencoba SQL yang sebenarnya.

Ini akan memperkenalkan konsep-konsep seperti GABUNG, kueri bersarang, dan fungsi WINDOW. Ketika Anda menguasai ini, kekuatan Anda untuk memanipulasi data akan menembus atap.

Ada banyak cara untuk memulai mempelajari SQL. Cobalah contoh interaktif di w3schools!