Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional
Pelajari cara klausa FILTER dan agregasi kondisional berbasis CASE memungkinkan Anda menghitung beberapa metrik dalam satu pemindaian tabel, alih-alih menjalankan beberapa kueri terpisah.
Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional adalah pelajaran PostgreSQL Performance & Query Optimization gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar PostgreSQL Performance & Query Optimization, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.
Bagian dari pelajaran ini belum diterjemahkan dan ditampilkan dalam bahasa Inggris.
The Problem: Many Counts, One Table
Dashboards often need several metrics from the same table — total orders, paid orders, refunded orders. Running three separate queries scans the table three times. We can do it in one pass.
Conditional Aggregation with CASE
The classic trick wraps a CASE inside an aggregate. Rows that do not match contribute NULL, which COUNT and SUM ignore.
SELECT
COUNT(*) AS total,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid
FROM orders;The Cleaner FILTER Clause
PostgreSQL offers a more readable form: the FILTER clause attached to any aggregate. It expresses intent directly.
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;Why This Is Faster
All metrics are computed in a single scan of the table. The planner reads each row once and updates every aggregate, instead of scanning the table separately for each metric.
FILTER with SUM and AVG
FILTER works with any aggregate, not just COUNT. Compute conditional sums and averages in the same query.
SELECT
SUM(total) FILTER (WHERE status = 'paid') AS revenue,
AVG(total) FILTER (WHERE status = 'paid') AS avg_paid
FROM orders;Combining with GROUP BY
FILTER shines inside grouped queries, producing a pivot-like result with one row per group and several conditional columns.
SELECT
region,
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
GROUP BY region;Pivoting Months into Columns
A common report turns rows into columns. FILTER makes a clean monthly pivot without extension functions.
SELECT
product_id,
SUM(total) FILTER (WHERE month = 1) AS jan,
SUM(total) FILTER (WHERE month = 2) AS feb
FROM sales
GROUP BY product_id;Reading the Plan
EXPLAIN ANALYZE confirms a single Aggregate node over one scan. Compare it against three separate queries to see the saved scans.
EXPLAIN ANALYZE
SELECT
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;FILTER vs WHERE
Remember the difference:
- WHERE removes rows before any aggregate sees them
- FILTER keeps all rows but restricts which ones a specific aggregate counts
Use FILTER when different aggregates need different conditions.
Combining with Indexes
If most metrics target a subset (e.g. only recent rows), add a WHERE for the shared condition so an index narrows the scan, then use FILTER for the per-metric splits.
SELECT
COUNT(*) FILTER (WHERE status = 'paid') AS paid
FROM orders
WHERE created_at >= now() - interval '30 days';Counting Distinct Conditionally
FILTER also pairs with COUNT(DISTINCT ...), letting you count unique customers per status in one scan instead of several grouped queries.
SELECT
COUNT(DISTINCT customer_id) FILTER (WHERE status = 'paid') AS paying_customers
FROM orders;Quick Check
Test your conditional aggregation knowledge.
Recap
You learned conditional aggregation:
- Compute many metrics in one scan with FILTER or CASE
- FILTER is more readable and works with any aggregate
- Combine with GROUP BY for pivot-style reports
- WHERE removes rows; FILTER restricts a single aggregate
- Add a shared WHERE so indexes narrow the scan
Pertanyaan yang Sering Diajukan
Apakah pelajaran “Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional” gratis?
Ya — teks lengkap “Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus PostgreSQL Performance & Query Optimization, upgrade ke CoddyKit PRO. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.
Apa yang akan aku pelajari di “Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional”?
Pelajari cara klausa FILTER dan agregasi kondisional berbasis CASE memungkinkan Anda menghitung beberapa metrik dalam satu pemindaian tabel, alih-alih menjalankan beberapa kueri terpisah. Kamu berlatih PostgreSQL Performance & Query Optimization dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.
Apakah aku perlu pengalaman untuk memulai PostgreSQL Performance & Query Optimization?
Tidak diperlukan pengalaman sebelumnya. PostgreSQL Performance & Query Optimization di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.
Berapa lama pelajaran “Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional” memakan waktu?
Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.
Bisakah aku menulis dan menjalankan kode dalam pelajaran PostgreSQL Performance & Query Optimization ini?
Ya. Setiap pelajaran PostgreSQL Performance & Query Optimization menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.
Semua pelajaran dalam kursus ini
- Mengoptimalkan Agregat dan Fungsi Jendela
- CTE Rekursif dan Kueri Graf
- Menggunakan Tampilan Terwujud untuk Performa
- Mengoptimalkan Kueri dengan FILTER dan Agregasi Kondisional