Fungsi Agregat — COUNT / SUM / AVG / MIN / MAX

Data yang akan kita gunakan — tabel score

Pada artikel ini kita bekerja dengan fungsi agregat (fungsi yang melipat banyak baris menjadi satu nilai). Ada berapa baris, berapa totalnya, rata-ratanya, minimum, maksimum — semua ini memampatkan banyak baris menjadi satu angka. Lima yang dasar adalah COUNT / SUM / AVG / MIN / MAX, dan hampir semua angka ringkasan dalam sebuah laporan bisa dibangun hanya dengan ini.

Kumpulan datanya adalah tabel score (30 baris = 10 orang × 3 mata pelajaran Math / English / Science). Dengan menyasar poin pada kolom score, kamu akan mengerjakan agregasi seluruh tabel terlebih dahulu, lalu agregasi setelah mempersempit baris dengan WHERE. Agregasi per grup (seperti rata-rata per mata pelajaran) dibahas pada artikel berikutnya, jadi di sini kita fokus pada melipat seluruh tabel — atau seluruh rentang yang dipersempit oleh WHERE — menjadi satu nilai.

Sebelum terjun ke latihan, lihat dulu definisi kolom dan sebuah contoh data dari tabel score.

① Jalankan PRAGMA table_info(score); untuk memeriksa nama kolom, tipe, dan kunci primer.

② Jalankan SELECT * FROM score LIMIT 5; untuk meninjau 5 baris data pertama.

Fungsi agregat: "banyak baris → satu nilai"

Fungsi agregat ditempatkan pada posisi kolom dalam sebuah SELECT.

Yang perlu diwaspadai di sini adalah jangan mencampur kolom biasa dan fungsi agregat dalam `SELECT` yang sama. Kolom biasa seperti name memiliki nilai berbeda per baris, sedangkan fungsi agregat seperti COUNT(*) melipat seluruh tabel (atau seluruh himpunan yang dipersempit oleh `WHERE`) menjadi satu nilai. Kalau kamu menulis keduanya, seperti pada SELECT name, COUNT(*) FROM score;, sisi agregat adalah satu nilai untuk seluruh himpunan sementara name tidak punya jawaban pasti untuk "nilai dari baris yang mana yang harus ditampilkan?". Pada kebanyakan basis data ini adalah sebuah error.

-- Mengambil jumlah, jumlah jenis berbeda, total, dan rata-rata sekaligus
SELECT
  COUNT(*)             AS row_count,
  COUNT(DISTINCT name) AS name_kinds,
  SUM(score)           AS total,
  AVG(score)           AS avg_raw
FROM score
WHERE subject = 'English';

Perbedaan antara COUNT(*) dan COUNT(kolom)

COUNT(*) menghitung jumlah baris itu sendiri. COUNT(kolom) hanya menghitung baris yang kolomnya tidak NULL. Tabel score tidak punya NULL, jadi keduanya menghasilkan 30 yang sama, tetapi untuk kolom yang mengandung NULL kamu akan melihat perbedaan di sini (kita akan memastikan ini nanti memakai tabel yang mengandung NULL). Selain itu, menulis COUNT(DISTINCT kolom) menghitung jumlah jenis berbeda dengan duplikat dihilangkan (pakai pada subject maka kamu mendapat 3, yaitu jumlah mata pelajaran yang berbeda).

Tips — fungsi agregat dan NULL

SUM / AVG / MIN / MAX tidak memasukkan baris NULL ke dalam perhitungan (mengabaikannya). Khususnya, AVG dihitung sebagai jumlah nilai yang bukan NULL ÷ banyaknya nilai yang bukan NULL. Jadi untuk kolom yang mengandung NULL, SUM(kolom) / COUNT(*) (penyebut = semua baris) dan AVG(kolom) (penyebut = banyaknya yang bukan NULL) menghasilkan nilai berbeda. Saat kamu ingin merata-ratakan dengan memperlakukan NULL sebagai 0, tuliskan secara eksplisit dengan sesuatu seperti AVG(COALESCE(kolom, 0)).

Bayangkan sebuah kebutuhan: "Saya ingin menampilkan skala keseluruhan data nilai pada sebuah dashboard dalam satu baris." (Jalankan dengan benar maka penjelasan akan muncul.)

① Dari tabel score, gunakan kelima fungsi agregat untuk mengambil 5 kolom berikut dalam satu baris.

② Beri alias row_count untuk jumlah seluruh baris, total untuk penjumlahan poin, avg_score untuk rata-rata poin, min_score untuk poin terendah, dan max_score untuk poin tertinggi, dengan urutan itu.

③ Karena rata-rata ditampilkan dengan desimal yang panjang, bulatkan ke 2 angka desimal dan beri alias avg_score.

Mempersempit sasaran dengan WHERE sebelum mengagregasi

Menambahkan WHERE membuat hanya baris yang cocok dengan kondisi menjadi masukan bagi fungsi agregat. Urutan eksekusinya adalah "FROM membaca semua baris → WHERE mempersempit baris → baris yang tersisa dilipat oleh fungsi agregat". Dengan kata lain, fungsi agregat hanya melihat baris yang lolos WHERE, dan penjumlahan atau rata-rata dihitung setelah penyaringan. Tambahkan WHERE subject = 'Math' maka hanya 10 baris Math yang menjadi sasaran, jadi rata-ratanya menjadi rata-rata Math saja.

-- Jumlah / rata-rata / nilai tertinggi untuk 10 baris Science saja
SELECT
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_science,
  MAX(score)           AS top_science
FROM score
WHERE subject = 'Science';

Bayangkan sebuah kebutuhan: "Saya ingin memotong nilai Math saja dan menghasilkan rata-rata, maks, dan min."

① Dari tabel score, sasar hanya baris yang subject-nya Math.

② Ambil, dalam satu baris, jumlah baris sasaran dengan alias math_count, nilai rata-rata dengan avg_math, poin tertinggi dengan max_math, dan poin terendah dengan min_math, dengan urutan itu.

③ Bulatkan rata-rata ke 2 angka desimal.

COUNT(kolom) dan NULL, serta pembulatan AVG

Tabel score sejauh ini tidak punya NULL. Mari kita pastikan bagaimana agregasi berubah tergantung ada atau tidaknya NULL, memakai tabel customer yang punya kekosongan (8 orang, dengan NULL pada age / email / country). COUNT(*) adalah jumlah baris, jadi tetap 8, tetapi COUNT(email) hanya menghitung baris yang emailnya terisi, jadi hasilnya 4. Menjalankan COUNT(kolom) pada kolom yang mengandung NULL menghasilkan angka yang lebih kecil daripada COUNT(*) seperti ini.

Selain itu, karena AVG adalah pembagian, ia menghasilkan desimal seperti 31.5. Dalam sebuah laporan akan lebih mudah ditangani kalau kamu membulatkan dengan ROUND(ekspresi, digit), seperti pada ROUND(AVG(age), 2).

-- Bagaimana COUNT dan AVG berubah dengan adanya NULL
SELECT
  COUNT(*)            AS rows_all,
  COUNT(country)      AS with_country,
  ROUND(AVG(age), 2)  AS avg_age
FROM customer;

Bayangkan sebuah kebutuhan: "Data anggota memiliki kolom yang belum terisi (NULL). Saya ingin membandingkan jumlah total dengan jumlah yang benar-benar ada nilainya."

① Dari tabel customer, ambil, dalam satu baris, jumlah total baris dengan alias all_rows, jumlah yang email-nya terisi dengan with_email, jumlah yang age-nya terisi dengan with_age, dan banyaknya nilai country yang berbeda dengan country_kinds, dengan urutan itu.

② Pastikan bahwa menjalankan COUNT(kolom) pada kolom yang ada NULL menghasilkan angka yang lebih kecil daripada COUNT(*).

Bayangkan sebuah kebutuhan: "Saya ingin mengambil hanya nilai yang melampaui rata-rata keseluruhan dan melihat jumlah serta rata-ratanya." Rata-rata keseluruhan adalah 77.93… (kamu sudah memastikannya pada latihan sebelumnya).

① Dari tabel score, sasar hanya baris yang score-nya 78 atau lebih.

② Ambil, dalam satu baris, jumlah baris sasaran dengan alias high_count, nilai rata-ratanya dengan avg_high, dan poin tertinggi dengan max_high, dengan urutan itu.

③ Bulatkan rata-rata ke 2 angka desimal.