Fungsi Agregat di SQL

Membangun laporan dari suatu dataset adalah keterampilan penting jika Anda bekerja dengan data. Karena pada akhirnya, Anda ingin bisa menjawab pertanyaan bisnis krusial menggunakan data yang Anda miliki. Sering kali, jawaban ini disajikan dalam bentuk bagan laporan. Namun terkadang, laporan dalam bentuk tabel juga diperlukan. Pada kedua kasus, Anda mungkin perlu meringkas data dengan perhitungan sederhana. Di SQL, Anda dapat meringkas/mengagregasi data menggunakan fungsi agregat. Dengan fungsi-fungsi ini, Anda akan dapat menjawab pertanyaan seperti:

• Berapakah nilai maksimum untuk some_column_from_the_table? atau
• Apa saja nilai minimum dari some_column_from_the_table terhadap another_column_from_the_table?

dan masih banyak lagi.

Mari mulai melakukan beberapa agregasi data.

Catatan: Agar dapat mengikuti tutorial ini, Anda perlu tahu cara menulis kueri dasar di PostgreSQL (yang akan Anda gunakan sebagai RDBMS). Tutorial ini bisa menjadi pengingat yang baik.

Menyiapkan Database

Pertama-tama, mari siapkan database PostgreSQL dan pulihkan cadangan ini yang berisi tabel yang akan Anda gunakan dalam tutorial ini. Jika Anda ingin mempelajari cara memulihkan cadangan database di PostgreSQL, Anda dapat mengikuti bagian pertama dari tutorial ini.

Jika Anda berhasil memulihkan cadangan, Anda akan melihat sebuah tabel bernama international_debt di dalam database (Anda perlu membuat database terlebih dahulu jika belum ada). Mari kita lihat beberapa baris pertama tabel tersebut secara cepat (kueri select sederhana akan membantu Anda melakukan ini):

Tabel ini memuat informasi tentang statistik utang berbagai negara di seluruh dunia untuk tahun berjalan dalam berbagai kategori (lihat kolom indicator_name dan indicator_code). Kolom debt menunjukkan jumlah utang (dalam USD) yang dimiliki suatu negara pada kategori tertentu. Data ini termasuk dalam domain ekonomi dan sering digunakan untuk menganalisis kondisi ekonomi berbagai negara. Data dikumpulkan dari World Bank.

Sekarang setelah Anda berhasil menyiapkan database, mari jalankan beberapa kueri sederhana untuk mengetahui lebih banyak tentang datanya. Buka alat pgAdmin dan mulai.

Informasi Sederhana Itu Penting

Dari gambar di atas, Anda dapat melihat bahwa ada banyak entri duplikat untuk satu negara tetapi dengan kategori yang berbeda. Pertanyaan yang segera muncul dari sini adalah:

Negara mana saja yang datanya tercatat dalam tabel?

Jika Anda hanya menjalankan kueri select dengan kolom country_name, Anda tidak akan mendapatkan jawaban yang tepat untuk pertanyaan ini karena hasil kueri akan memuat duplikasi. Mari gunakan kata kunci DISTINCT untuk mengatasinya.

select distinct country_name from international_debt;
Didukung oleh

Dan ini akan mengembalikan hasil seperti ini:

Sekarang Anda memiliki jawaban yang cukup untuk pertanyaan di atas. Satu pertanyaan terakhir sebelum Anda lanjut ke fungsi agregat:

Ada berapa jenis indikator utang yang berbeda di tabel?

Kueri untuk menjawabnya mirip seperti di atas. Anda hanya perlu mengubah nama kolom. Jadikan ini sebagai latihan, ya? Hasilnya akan mirip seperti berikut:

Fungsi Agregat

Mari mulai dengan menjalankan kueri yang menggunakan fungsi agregat dan lanjutkan seterusnya. Sepanjang jalan, Anda akan mempelajari lebih jauh tentang sintaks dan konstruksi yang perlu diikuti saat menerapkan fungsi agregat di SQL.

select sum(debt) from international_debt;
Didukung oleh

Dan hasilnya:

Dengan fungsi agregat SUM(), Anda dapat menghitung jumlah aritmetika pada sebuah kolom (yang berisi nilai numerik). Dengan kueri di atas, Anda mengetahui total utang yang tertunggak oleh negara-negara yang tercantum di tabel.

Catatan bahwa SUM() tidak mempertimbangkan nilai NULL saat menghitung jumlah. Sekarang, mari cari jawaban untuk pertanyaan:

Berapakah jumlah utang maksimum?

Di sinilah fungsi agregat MAX() membantu Anda:

select max(debt) from international_debt;
Didukung oleh

Dan jawabannya adalah:

Sama seperti fungsi SUM(), MAX() tidak mempertimbangkan entri NULL saat melakukan perhitungannya. Ada juga fungsi serupa yaitu MIN(). Beri tahu saya nilai minimum dari kolom debt lewat bagian Comments? Sekarang, ada baiknya benar-benar mencari tahu apakah ada entri tidak valid di kolom debt untuk memastikan hasil hingga saat ini benar.

Catatan bahwa Anda menggunakan fungsi-fungsi ini dalam huruf kecil seperti yang ditunjukkan di atas.

Saat Anda mengeksekusi kueri ini:select * from international_debt where debt is null;, Anda seharusnya mendapatkan hasil kosong. Sekarang mari cari tahu jumlah total negara berbeda yang ada di tabel.

select count(distinct(country_name)) from international_debt;
Didukung oleh

Dan Anda melihat ada total 124 negara berbeda yang ada di tabel. Perhatikan dengan saksama urutan fungsi yang Anda terapkan pada kueri di atas. Ya, ini diperbolehkan di sini, yaitu menghubungkan lebih dari satu fungsi agregat secara logis.

Sekarang, misalnya, Anda ingin melihat nilai rata-rata dari kolom debt. Fungsinya adalah AVG():

select avg(debt) from international_debt;

Anda melihat nilainya 1306633214.966397971 (USD). Ada baiknya menyajikan hasil ini dengan nama kolom yang sesuai. Dari hasil di atas, Anda dapat melihat bahwa PostgreSQL mengubah nama kolom menjadi nama fungsi agregat (yang disertakan dalam kueri) saat mengembalikan hasil. Jadi, ada baiknya memberi alias yang tepat pada kolom-kolom ini. Anda dapat melakukannya seperti berikut:

select avg(debt) as Average_Debt_By_A_Country from international_debt;
Didukung oleh

Hasilnya jauh lebih mudah ditafsirkan:

Sekarang mari bawa ini ke tingkat yang sedikit lebih kompleks. Untuk dapat menjawab pertanyaan seperti What are the minimum values of some_column_from_the_table with respect to another_column_from_the_table?, Anda perlu memasangkan fungsi agregat dengan klausa GROUP BY. Mari lihat caranya.

Fungsi Agregat + GROUP BY + lainnya

Misalkan Anda ingin membuat laporan yang menampilkan country_name dan jumlah total utangnya. Berikut contohnya:

Laporan seperti ini cukup sering digunakan di dunia nyata. Jadi, seperti apa kueri untuk mendapatkan laporan seperti ini? Anda harus menggunakan fungsi SUM() pada debt. Dan Anda juga perlu menampilkan country_name bersama jumlah total utang. Mari jalankan kueri berikut:

select country_name, sum(debt) from international_debt;
Didukung oleh

Bukankah ini menghasilkan kesalahan berikut?

ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function

Sekarang mari pahami apa artinya. Saat Anda menggunakan fungsi agregat (seperti SUM() dengan kolom yang tidak diagregasi seperti country_name) Anda perlu memasukkan kolom yang tidak diagregasi ke dalam klausa GROUP BY. Jadi, kueri yang benar adalah:

select country_name, sum(debt) as total_debt from international_debt group by country_name;
Didukung oleh

Dan hasilnya sudah tepat:

Catatan penggunaan alias pada kueri.

Sekarang, misalkan Anda perlu mengurutkan laporan ini berdasarkan total_debt secara menurun. Ingat klausa ORDER BY? Ya, Anda juga dapat memasangkan fungsi agregat dengan klausa ORDER BY:

select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
Didukung oleh

Hasilnya sekarang harus diurutkan secara terbalik:

Catatan kolom yang Anda gunakan dalam klausa ORDER BY.

Sekarang pertanyaan penting lainnya:

Berapakah jumlah utang tertinggi di berbagai kategori (dengan urutan terbalik)?

Anda perlu menggunakan fungsi MAX() di sini. Kueri untuk menjawab pertanyaan ini sekarang tidak sulit untuk ditulis.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
Didukung oleh

Dan Anda mendapatkan laporan yang rapi:

Anda juga dapat membatasi jumlah baris dalam laporan seperti ini. Misalnya Anda hanya ingin menyertakan lima entri teratas pada laporan di atas. Anda bisa melakukannya dengan menggunakan klausa LIMIT.

select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
Didukung oleh

Saatnya laporan terakhir untuk tutorial ini. Anda perlu menyertakan nama negara pada laporan di atas. Bagaimana caranya? Kueri berikut akan memungkinkan Anda melakukan ini:

select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
Didukung oleh

Laporan yang bagus lagi:

Pada kueri di atas, Anda menambahkan kolom country_name setelah klausa SELECT dan juga menambahkannya setelah GROUP BY. Anda dapat memperluas format ini hingga sebanyak yang diperlukan.

Urutan GROUP BY, ORDER BY dan LIMIT sangat penting saat menghasilkan laporan seperti ini. Jika Anda keliru mengubah urutannya, Anda akan menghadapi error. Lihat sendiri:

select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
Didukung oleh

Dan Anda mendapatkan:

ERROR:  syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...Didukung oleh

Pada kueri di atas, Anda menempatkan klausa ORDER BY sebelum GROUP BY yang tidak diizinkan. Faktanya, ini juga tidak berlaku saat Anda tidak menggunakan fungsi agregat. Urutan yang benar adalah - GROUP BY -> ORDER BY -> LIMIT. Selalu ingat ini.

Melangkah Lebih Jauh

Selamat! Anda sampai di akhir tutorial ini. Pada tutorial ini, Anda mempelajari berbagai fungsi agregat di PostgreSQL dan cara menggunakannya untuk menghasilkan laporan yang berguna. Ini memang keterampilan krusial bagi seorang data scientist. Untuk meningkatkan keterampilan SQL Anda secara sistematis, Anda dapat mengikuti kursus DataCamp berikut:

• Joining Data in SQL
• Intermediate SQL
• SQL for Exploratory Data Analysis