Proyek: Database Analisis Data Pertanian

Keputusan tani yang baik lahir dari pola yang panjang: kapan harga padi biasanya jatuh, petak mana yang paling produktif per hektar, dan apakah panen raya tahun ini benar-benar menekan harga di pasar. Pola sepanjang itu tidak muat di ingatan — catatan panen tercecer di buku ketua koperasi, harga pasar hanya diingat petani saat menjual, dan pertanyaan “petak mana yang paling menguntungkan?” tidak pernah terjawab dengan angka. Pada proyek ini kita membangun platform data untuk Koperasi Tani Sejahtera: hasil panen setiap petak dicatat sebagai peristiwa bertanggal, harga komoditas tercatat sebagai deret waktu harian, dan keduanya bertemu di analisis produksi-harga yang menjawab pertanyaan-pertanyaan itu dengan query, bukan dengan kira-kira.

Yang Akan Kita Bangun #

Kebutuhan koperasi menuntut dua jenis data yang tumbuh dengan irama berbeda. Buku panen terisi per kejadian — sekali atau dua kali sebulan per petak — sedangkan harga pasar bergerak setiap hari. Database berikut mengakomodasi keduanya:

Tabel lahan dan komoditas sebagai data master: lima petak sebaran desa dengan luas hektar, dan empat komoditas (padi, jagung, kedelai, kacang tanah).
Tabel panen — satu baris satu peristiwa: petak, komoditas, tanggal, hasil dalam kilogram, dan grade kualitas. Dua foreign key ke lahan dan komoditas, plus UNIQUE yang melarang petak sama dipanen komoditas sama dua kali sehari.
Tabel harga_pasar — deret waktu harian harga per kilogram, sengaja dipisahkan dari panen karena harga milik pasar, bukan milik satu panen; keputusan ini dibedah tuntas di Bagian 1.
Validasi positif di skema: hasil panen, luas lahan, dan harga selalu lebih dari nol; kualitas dibatasi enum A/B/C.
Produktivitas per petak dalam kilogram per hektar — pembagian hasil oleh luas yang membuat petak 0,5 hektar bisa bersaing dengan petak 2 hektar — lengkap dengan peringkat RANK.
Kalender panen per bulan dan per kuartal dengan date_trunc serta matriks komoditas memakai agregasi FILTER.
Deret harga 15 bulan (Januari 2025 sampai Maret 2026) berisi 1.800 baris yang dimuat set-based lewat generate_series; perubahan hari-ke-hari dihitung dengan LAG, dan hari yang tidak tercatat ditemukan lalu diisi.
Analisis penutup: nilai setiap panen dihitung dari harga terbaru pada tanggal kejadiannya lewat JOIN LATERAL (pola as-of join), dirata-rata bergerak dengan window frame, lalu dikorelaskan dengan produksi kuartalan untuk menguji mitos “panen raya menekan harga”.

Seluruh angka memakai numeric — hasil kilogram, luas hektar, dan rupiah dihitung exact tanpa pembulatan biner. Data contoh deterministik dengan tanggal terpaku, sehingga setiap output dapat Anda cocokkan persis dengan eksekusi sendiri.

Skema Database #

Kebutuhan itu diterjemahkan menjadi empat tabel. Diagram berikut memperlihatkan relasinya:

erDiagram lahan ||--o{ panen : "dipanen" komoditas ||--o{ panen : "ditanam" komoditas ||--o{ harga_pasar : "dipantau" lahan { integer id PK text nama UK text desa numeric luas_hektar } komoditas { integer id PK text nama UK } panen { bigint id PK integer lahan_id FK integer komoditas_id FK date tanggal numeric hasil_kg kualitas_panen kualitas } harga_pasar { bigint id PK integer komoditas_id FK date tanggal numeric harga_per_kg }

Notasi ||--o{ dibaca “satu ke nol-atau-banyak”. Perhatikan asimetri yang disengaja: panen merujuk dua master sekaligus (lahan dan komoditas) karena satu panen adalah peristiwa “petak ini menanam komoditas itu pada hari itu”, sedangkan harga_pasar hanya merujuk komoditas. Tidak ada garis dari harga_pasar ke panen — harga berlaku untuk pasar keseluruhan, tercatat atau tidak tercatat ada yang memanen. Justru ketidakberhubungan langsung ini yang membuat Bagian 4 menarik: menghitung nilai panen berarti mencocokkan dua deret waktu yang berjalan dengan irama berbeda.

Prasyarat #

Proyek ini menyusun ulang materi yang sudah dipelajari, bukan memperkenalkan fitur baru. Pastikan Anda nyaman dengan chapter berikut:

Tipe Data: Numeric dan Decimal untuk hasil dan rupiah, Date dan Time untuk tanggal panen dan harga, serta Enum untuk grade kualitas.
Filtering dan Sorting: WHERE dan BETWEEN — hampir semua analisis menyaring rentang tanggal.
Agregasi Data: GROUP BY , FILTER pada Aggregate , dan MIN dan MAX untuk rekap bulanan dan matriks kuartal.
Joins: INNER JOIN , LEFT JOIN untuk hari kosong, dan LATERAL Join — bintang utama Bagian 4.
Window Functions: LAG dan LEAD , Moving Average , Window Frame , dan RANK dan DENSE RANK .
Advanced SQL: LATERAL Joins dan Analytical Queries sebagai panduan pola analisis.

Tidak ada extension atau tool tambahan; cukup psql dan PostgreSQL 17 yang terpasang sejak Instalasi PostgreSQL .

Peta Bagian #

Proyek dikerjakan dalam empat bagian berurutan. Setiap bagian menghasilkan database yang benar-benar bekerja pada tahapnya:

Bagian	Fokus
Bagian 1: Perancangan Skema	Dari cerita koperasi ke empat tabel: mengapa harga dipisahkan dari panen, constraint positif, enum kualitas, index jalur analisis, dan uji skema
Bagian 2: Pencatatan Hasil Panen	Buku panen: pencatatan tervalidasi, hasil per komoditas, produktivitas kilogram per hektar dengan RANK, dan kalender panen dengan date_trunc
Bagian 3: Pencatatan Harga Komoditas	Deret harga harian: pencatatan manual, pemuatan massal generate_series, agregasi bulanan, perubahan hari-ke-hari dengan LAG, dan pengisian hari kosong
Bagian 4: Analisis Produksi dan Harga	Finale analitik: rata-rata bergerak dengan window frame, nilai panen as-of join LATERAL, peringkat lahan, korelasi produksi-harga, YoY, dan pola musiman

Setiap bagian mandiri: berkas SQL-nya membangun ulang tabel yang dibutuhkan di awal, sehingga Anda bisa masuk dari bagian mana pun tanpa membawa bekas eksekusi bagian sebelumnya.

Cara Mengikuti Proyek #

Kerjakan tiap bagian sambil mengetik ulang atau menyalin berkas SQL-nya, lalu jalankan dengan psql seperti biasa:

bash

psql -X -f bagian/perancangan-skema/tabel-lahan-komoditas.sql -d pertanian

Aturan mainnya sama dengan seluruh Course: setiap blok kode pada bagian-bagian berikut adalah berkas nyata yang dieksekusi sungguhan, dan blok Output yang Dihasilkan di bawahnya adalah hasil eksekusi tersebut, bukan tulisan tangan. Tanggal dan angka sengaja dipaku — misalnya padi panen raya 10 Maret 2025 seberat 6.000 kg dan harga terendah Rp6.250/kg — agar hasil Anda identik dengan hasil di halaman.

Selain berkas per bagian, halaman overview ini juga membawa artefak lengkap hasil akhir proyek pada bagian Berkas Sumber di bawah halaman ini: database/skema.sql (skema final dengan seluruh constraint, index, dan dokumentasi), database/isi-data.sql (5 petak, 4 komoditas, 16 panen, dan 1.800 baris harga), queries/ (lima query analisis siap pakai: produktivitas, harga bulanan, nilai panen, lahan terbaik, dan produksi vs harga), serta tests/ (dua berkas uji LULUS/GAGAL). Setelah menyelesaikan keempat bagian, terapkan kedua berkas database/ ke database kosong, jalankan berkas uji, lalu bandingkan hasilnya dengan laporan Anda sendiri.

Semua analisis di proyek ini berdiri di atas satu fondasi: skema yang memisahkan peristiwa panen dari deret harga dengan sadar. Mari kita mulai dari keputusan itu di Bagian 1.

Referensi #

# Bagian 1: Perancangan Skema

Rancang tabel lahan, komoditas, panen, dan harga pasar.

Buka → # Bagian 2: Pencatatan Hasil Panen

Catat hasil panen per lahan dan periode dengan validasi konsistensi.

Buka → # Bagian 3: Pencatatan Harga Komoditas

Simpan deret harga komoditas harian dan agregasi periodiknya.

Buka → # Bagian 4: Analisis Produksi dan Harga

Analisis tren dengan window function dan korelasi produksi terhadap harga.

Buka →

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- isi-data.sql — data contoh Koperasi Tani Sejahtera, Januari 2025 sampai
-- Maret 2026. Jalankan setelah database/skema.sql:
--   psql -X -f database/isi-data.sql
-- Berisi: 5 petak lahan, 4 komoditas, 16 peristiwa panen, dan 1.800 baris
-- harga harian (2 hari dicatat manual + deret 4 Januari 2025 s.d. 31 Maret
-- 2026 dari model musiman deterministik; empat tanggal sengaja kosong).

INSERT INTO lahan (nama, desa, luas_hektar) VALUES
    ('Sawah Blumbang',     'Blumbang',    1.20),
    ('Sawah Kaligondang',  'Kaligondang', 0.80),
    ('Ladang Menoreh',     'Menoreh',     1.50),
    ('Ladang Sumbing',     'Sumbing',     2.00),
    ('Kebun Pakem',        'Pakem',       0.50);

INSERT INTO komoditas (nama) VALUES
    ('padi'),
    ('jagung'),
    ('kedelai'),
    ('kacang tanah');

INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas) VALUES
    (1, 1, DATE '2025-03-10', 6000.00, 'A'),
    (1, 1, DATE '2025-10-18', 5760.00, 'B'),
    (1, 1, DATE '2026-03-05', 6240.00, 'A'),
    (2, 1, DATE '2025-03-18', 3680.00, 'A'),
    (2, 1, DATE '2025-10-25', 3840.00, 'A'),
    (2, 1, DATE '2026-03-12', 4000.00, 'A'),
    (3, 2, DATE '2025-02-20', 9000.00, 'A'),
    (3, 3, DATE '2025-06-18', 2700.00, 'B'),
    (3, 2, DATE '2025-10-15', 8250.00, 'A'),
    (3, 3, DATE '2026-02-12', 3000.00, 'A'),
    (4, 2, DATE '2025-02-25', 12000.00, 'A'),
    (4, 2, DATE '2025-10-20', 11000.00, 'B'),
    (4, 2, DATE '2026-02-18', 12600.00, 'A'),
    (5, 4, DATE '2025-05-10', 850.00, 'B'),
    (5, 4, DATE '2025-09-05', 800.00, 'B'),
    (5, 4, DATE '2026-01-15', 950.00, 'A');

INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg) VALUES
    (1, DATE '2025-01-02',  7000.00),
    (2, DATE '2025-01-02',  4700.00),
    (3, DATE '2025-01-02', 11800.00),
    (4, DATE '2025-01-02', 13650.00),
    (1, DATE '2025-01-03',  6950.00),
    (2, DATE '2025-01-03',  4750.00),
    (3, DATE '2025-01-03', 11750.00),
    (4, DATE '2025-01-03', 13700.00);

INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
WITH param (nama, dasar, tren, a1, p1, w1, a2, p2, w2, a3, p3, w3) AS (
    VALUES
        ('padi',         7200.0, 0.70, 900.0,  80, 60.0, 700.0, 300, 50.0,   0.0,  0, 30.0),
        ('jagung',       5000.0, 0.50, 650.0,  55, 55.0, 400.0, 295, 40.0,   0.0,  0, 30.0),
        ('kedelai',     12000.0, 0.90, 700.0, 175, 50.0, 450.0,  45, 40.0,   0.0,  0, 30.0),
        ('kacang tanah', 14000.0, 1.00, 600.0, 135, 40.0, 400.0, 250, 40.0, 350.0, 15, 30.0)
),
hari AS (
    SELECT d::date AS tanggal
    FROM generate_series(DATE '2025-01-03', DATE '2026-03-31', INTERVAL '1 day') AS d
    WHERE d::date NOT IN (DATE '2026-02-16', DATE '2026-03-02',
                          DATE '2026-03-16', DATE '2026-03-23')
)
SELECT k.id,
       h.tanggal,
       round(
           ( p.dasar
             + p.tren * (h.tanggal - DATE '2025-01-02')
             - p.a1 * exp(- power((extract(doy FROM h.tanggal)::int - p.p1) / p.w1, 2))
             - p.a2 * exp(- power((extract(doy FROM h.tanggal)::int - p.p2) / p.w2, 2))
             - p.a3 * exp(- power((extract(doy FROM h.tanggal)::int - p.p3) / p.w3, 2))
             + ((extract(doy FROM h.tanggal)::int % 7) - 3) * 15
             + ((extract(doy FROM h.tanggal)::int % 11) - 5) * 10
           ) / 50) * 50 AS harga_per_kg
FROM komoditas k
JOIN param p ON p.nama = k.nama
CROSS JOIN hari h
ON CONFLICT (komoditas_id, tanggal) DO NOTHING;

sql
-- skema.sql — skema lengkap database analitik pertanian Koperasi Tani
-- Sejahtera. Terapkan pada database kosong:
--   psql -X -f database/skema.sql
-- Isi data contoh dengan database/isi-data.sql setelahnya.
-- Skema ini adalah hasil Bagian 1: dua tabel master (lahan, komoditas),
-- satu tabel peristiwa (panen), satu deret waktu harga (harga_pasar),
-- constraint bisnis, index jalur analisis, dan dokumentasi COMMENT ON.

CREATE TYPE kualitas_panen AS ENUM ('A', 'B', 'C');

CREATE TABLE lahan (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama        text NOT NULL UNIQUE,
    desa        text NOT NULL,
    luas_hektar numeric(6,2) NOT NULL CHECK (luas_hektar > 0)
);

CREATE TABLE komoditas (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL UNIQUE
);

CREATE TABLE panen (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lahan_id     integer NOT NULL REFERENCES lahan (id),
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    hasil_kg     numeric(10,2) NOT NULL,
    kualitas     kualitas_panen NOT NULL,
    CONSTRAINT hasil_panen_positif CHECK (hasil_kg > 0),
    CONSTRAINT satu_panen_per_hari UNIQUE (lahan_id, komoditas_id, tanggal)
);

CREATE TABLE harga_pasar (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    harga_per_kg numeric(10,2) NOT NULL,
    CONSTRAINT harga_pasar_positif CHECK (harga_per_kg > 0),
    CONSTRAINT satu_harga_per_hari UNIQUE (komoditas_id, tanggal)
);

COMMENT ON TABLE lahan IS 'Petak lahan yang dipanen; satu baris satu petak.';
COMMENT ON COLUMN lahan.luas_hektar IS 'Luas petak dalam hektar, dasar hitung produktivitas.';
COMMENT ON TABLE komoditas IS 'Jenis tanaman yang dicatat, mis. padi, jagung, kedelai.';
COMMENT ON TYPE kualitas_panen IS 'Grade hasil panen: A terbaik, B standar, C di bawah standar.';
COMMENT ON TABLE panen IS 'Satu baris = satu peristiwa panen: petak, komoditas, tanggal, hasil.';
COMMENT ON COLUMN panen.hasil_kg IS 'Hasil panen dalam kilogram; selalu positif.';
COMMENT ON TABLE harga_pasar IS 'Deret harga harian per komoditas; milik pasar, bukan milik satu panen.';
COMMENT ON COLUMN harga_pasar.harga_per_kg IS 'Harga per kilogram dalam rupiah pada tanggal tersebut.';

CREATE INDEX idx_panen_tanggal   ON panen (tanggal);
CREATE INDEX idx_panen_lahan     ON panen (lahan_id);
CREATE INDEX idx_panen_komoditas ON panen (komoditas_id);

sql
-- harga-bulanan.sql — ringkasan bulanan deret harga per komoditas:
-- rata-rata, terendah, tertinggi, dan lebar rentang dalam sebulan.
-- Jalankan pada database yang memuat database/skema.sql dan
-- database/isi-data.sql.

SELECT k.nama AS komoditas,
       date_trunc('month', h.tanggal)::date AS bulan,
       round(avg(h.harga_per_kg), 0) AS harga_rata,
       min(h.harga_per_kg) AS terendah,
       max(h.harga_per_kg) AS tertinggi,
       max(h.harga_per_kg) - min(h.harga_per_kg) AS rentang_dalam_bulan,
       count(*) AS baris
FROM harga_pasar h
JOIN komoditas k ON k.id = h.komoditas_id
GROUP BY k.nama, date_trunc('month', h.tanggal)
ORDER BY k.nama, bulan;

sql
-- lahan-terbaik.sql — petak mana yang paling menghasilkan nilai?
-- Peringkat pertama memakai total nilai (cenderung memihak lahan luas);
-- kolom nilai_per_hektar menimbang luas agar petak kecil bisa bersaing.
-- Jalankan pada database yang memuat database/skema.sql dan
-- database/isi-data.sql.

WITH nilai AS (
    SELECT p.lahan_id,
           p.hasil_kg,
           p.hasil_kg * h.harga_per_kg AS nilai
    FROM panen p
    JOIN LATERAL (
        SELECT hp.harga_per_kg
        FROM harga_pasar hp
        WHERE hp.komoditas_id = p.komoditas_id
          AND hp.tanggal <= p.tanggal
        ORDER BY hp.tanggal DESC
        LIMIT 1
    ) h ON true
)
SELECT l.nama AS lahan,
       l.luas_hektar,
       count(*) AS jumlah_panen,
       sum(n.hasil_kg) AS total_kg,
       round(sum(n.nilai), 2) AS total_nilai,
       round(sum(n.nilai) / l.luas_hektar, 0) AS nilai_per_hektar,
       rank() OVER (ORDER BY sum(n.nilai) DESC) AS peringkat_nilai,
       rank() OVER (ORDER BY sum(n.nilai) / l.luas_hektar DESC) AS peringkat_per_hektar
FROM lahan l
JOIN nilai n ON n.lahan_id = l.id
GROUP BY l.nama, l.luas_hektar
ORDER BY peringkat_nilai, lahan;

sql
-- nilai-panen.sql — nilai setiap panen dihitung dengan harga terbaru yang
-- diketahui pada atau sebelum tanggal panen (JOIN LATERAL "as-of join"),
-- lalu dirangkum per komoditas. Jalankan pada database yang memuat
-- database/skema.sql dan database/isi-data.sql.

WITH nilai AS (
    SELECT p.tanggal,
           p.lahan_id,
           p.komoditas_id,
           p.hasil_kg,
           p.hasil_kg * h.harga_per_kg AS nilai
    FROM panen p
    JOIN LATERAL (
        SELECT hp.harga_per_kg
        FROM harga_pasar hp
        WHERE hp.komoditas_id = p.komoditas_id
          AND hp.tanggal <= p.tanggal
        ORDER BY hp.tanggal DESC
        LIMIT 1
    ) h ON true
)
SELECT n.tanggal,
       l.nama AS lahan,
       k.nama AS komoditas,
       n.hasil_kg,
       round(n.nilai, 2) AS nilai_panen
FROM nilai n
JOIN lahan l ON l.id = n.lahan_id
JOIN komoditas k ON k.id = n.komoditas_id
ORDER BY n.tanggal, l.nama;

WITH nilai AS (
    SELECT p.komoditas_id,
           p.hasil_kg,
           p.hasil_kg * h.harga_per_kg AS nilai
    FROM panen p
    JOIN LATERAL (
        SELECT hp.harga_per_kg
        FROM harga_pasar hp
        WHERE hp.komoditas_id = p.komoditas_id
          AND hp.tanggal <= p.tanggal
        ORDER BY hp.tanggal DESC
        LIMIT 1
    ) h ON true
)
SELECT k.nama AS komoditas,
       count(*) AS jumlah_panen,
       sum(n.hasil_kg) AS total_kg,
       round(sum(n.nilai), 2) AS total_nilai
FROM nilai n
JOIN komoditas k ON k.id = n.komoditas_id
GROUP BY k.nama
ORDER BY total_nilai DESC;

sql
-- produksi-vs-harga.sql — apakah panen besar menekan harga?
-- Produksi padi per kuartal (dari panen) digabung harga rata-rata kuartalan
-- (dari harga_pasar) lewat generate_series agar kuartal tanpa panen tetap
-- tampil; LAG menghitung perubahan harga kuartal-ke-kuartal, dan corr()
-- merangkum arah hubungannya. Jalankan pada database yang memuat
-- database/skema.sql dan database/isi-data.sql.

WITH kuartal AS (
    SELECT gs::date AS kuartal
    FROM generate_series(DATE '2025-01-01', DATE '2026-01-01', INTERVAL '3 months') AS gs
),
produksi AS (
    SELECT date_trunc('quarter', p.tanggal)::date AS kuartal,
           sum(p.hasil_kg) AS produksi_kg
    FROM panen p
    JOIN komoditas k ON k.id = p.komoditas_id
    WHERE k.nama = 'padi'
    GROUP BY date_trunc('quarter', p.tanggal)
),
harga AS (
    SELECT date_trunc('quarter', h.tanggal)::date AS kuartal,
           round(avg(h.harga_per_kg), 0) AS harga_rata
    FROM harga_pasar h
    JOIN komoditas k ON k.id = h.komoditas_id
    WHERE k.nama = 'padi'
    GROUP BY date_trunc('quarter', h.tanggal)
)
SELECT ku.kuartal,
       COALESCE(pr.produksi_kg, 0) AS produksi_kg,
       hg.harga_rata,
       lag(hg.harga_rata) OVER (ORDER BY ku.kuartal) AS harga_rata_lalu,
       round(100.0 * (hg.harga_rata - lag(hg.harga_rata) OVER (ORDER BY ku.kuartal))
             / NULLIF(lag(hg.harga_rata) OVER (ORDER BY ku.kuartal), 0), 1) AS perubahan_persen
FROM kuartal ku
LEFT JOIN produksi pr ON pr.kuartal = ku.kuartal
LEFT JOIN harga hg ON hg.kuartal = ku.kuartal
ORDER BY ku.kuartal;

WITH kuartal AS (
    SELECT gs::date AS kuartal
    FROM generate_series(DATE '2025-01-01', DATE '2026-01-01', INTERVAL '3 months') AS gs
),
produksi AS (
    SELECT date_trunc('quarter', p.tanggal)::date AS kuartal,
           sum(p.hasil_kg) AS produksi_kg
    FROM panen p
    JOIN komoditas k ON k.id = p.komoditas_id
    WHERE k.nama = 'padi'
    GROUP BY date_trunc('quarter', p.tanggal)
),
harga AS (
    SELECT date_trunc('quarter', h.tanggal)::date AS kuartal,
           avg(h.harga_per_kg) AS harga_rata
    FROM harga_pasar h
    JOIN komoditas k ON k.id = h.komoditas_id
    WHERE k.nama = 'padi'
    GROUP BY date_trunc('quarter', h.tanggal)
)
SELECT round(corr(COALESCE(pr.produksi_kg, 0), hg.harga_rata)::numeric, 3) AS korelasi_produksi_harga
FROM kuartal ku
LEFT JOIN produksi pr ON pr.kuartal = ku.kuartal
LEFT JOIN harga hg ON hg.kuartal = ku.kuartal;

sql
-- produktivitas-lahan.sql — rekap hasil per petak: total, kilogram per
-- hektar, dan peringkat. Jalankan pada database yang memuat
-- database/skema.sql dan database/isi-data.sql.

WITH per_lahan AS (
    SELECT l.nama AS lahan,
           l.luas_hektar,
           count(p.id) AS jumlah_panen,
           sum(p.hasil_kg) AS total_kg
    FROM lahan l
    LEFT JOIN panen p ON p.lahan_id = l.id
    GROUP BY l.nama, l.luas_hektar
)
SELECT lahan,
       luas_hektar,
       jumlah_panen,
       total_kg,
       round(total_kg / luas_hektar, 0) AS kg_per_hektar,
       rank() OVER (ORDER BY total_kg / luas_hektar DESC) AS peringkat
FROM per_lahan
ORDER BY peringkat, lahan;

sql
-- uji-analitik.sql — memastikan angka-angka analisis pada database terisi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-analitik.sql
-- Lulus bila setiap baris kolom status bernilai LULUS.

SELECT nomor, kasus, status
FROM (
    SELECT 1 AS nomor, 'panen berjumlah 16 baris' AS kasus,
           CASE WHEN (SELECT count(*) FROM panen) = 16
                THEN 'LULUS' ELSE 'GAGAL' END AS status
    UNION ALL
    SELECT 2, 'total hasil seluruh panen 90670 kg',
           CASE WHEN (SELECT sum(hasil_kg) FROM panen) = 90670
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 3, 'deret harga berjumlah 1800 baris',
           CASE WHEN (SELECT count(*) FROM harga_pasar) = 1800
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 4, 'setiap komoditas memiliki 450 baris harga',
           CASE WHEN (SELECT count(*) FROM (
                          SELECT k.nama
                          FROM harga_pasar h
                          JOIN komoditas k ON k.id = h.komoditas_id
                          GROUP BY k.nama
                          HAVING count(*) <> 450) x) = 0
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 5, 'tiga hari Maret 2026 tanpa catatan harga padi',
           CASE WHEN (SELECT count(*) FROM generate_series(DATE '2026-03-01',
                                                           DATE '2026-03-31',
                                                           INTERVAL '1 day') AS d(tanggal)
                      LEFT JOIN harga_pasar h
                             ON h.tanggal = d.tanggal
                            AND h.komoditas_id = (SELECT id FROM komoditas
                                                  WHERE nama = 'padi')
                      WHERE h.harga_per_kg IS NULL) = 3
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 6, 'bulan termurah padi 2025 adalah Maret',
           CASE WHEN (SELECT date_trunc('month', h.tanggal)::date
                      FROM harga_pasar h
                      JOIN komoditas k ON k.id = h.komoditas_id
                      WHERE k.nama = 'padi' AND h.tanggal < DATE '2026-01-01'
                      GROUP BY date_trunc('month', h.tanggal)
                      ORDER BY avg(h.harga_per_kg), date_trunc('month', h.tanggal)
                      LIMIT 1) = DATE '2025-03-01'
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 7, 'korelasi produksi-harga padi bernilai negatif',
           CASE WHEN (SELECT corr(COALESCE(pr.produksi_kg, 0), hg.harga_rata)
                      FROM generate_series(DATE '2025-01-01', DATE '2026-01-01',
                                           INTERVAL '3 months') AS ku(kuartal)
                      LEFT JOIN (
                          SELECT date_trunc('quarter', p.tanggal)::date AS kuartal,
                                 sum(p.hasil_kg) AS produksi_kg
                          FROM panen p
                          JOIN komoditas k ON k.id = p.komoditas_id
                          WHERE k.nama = 'padi'
                          GROUP BY 1) pr ON pr.kuartal = ku.kuartal::date
                      LEFT JOIN (
                          SELECT date_trunc('quarter', h.tanggal)::date AS kuartal,
                                 avg(h.harga_per_kg) AS harga_rata
                          FROM harga_pasar h
                          JOIN komoditas k ON k.id = h.komoditas_id
                          WHERE k.nama = 'padi'
                          GROUP BY 1) hg ON hg.kuartal = ku.kuartal::date) < 0
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 8, 'produksi padi kuartal I 2026 (10240 kg) melampaui kuartal I 2025 (9680 kg)',
           CASE WHEN (SELECT sum(p.hasil_kg)
                      FROM panen p
                      JOIN komoditas k ON k.id = p.komoditas_id
                      WHERE k.nama = 'padi'
                        AND p.tanggal >= DATE '2026-01-01'
                        AND p.tanggal < DATE '2026-04-01') = 10240
                    AND (SELECT sum(p.hasil_kg)
                         FROM panen p
                         JOIN komoditas k ON k.id = p.komoditas_id
                         WHERE k.nama = 'padi'
                           AND p.tanggal >= DATE '2025-01-01'
                           AND p.tanggal < DATE '2025-04-01') = 9680
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 9, 'total nilai seluruh panen Rp 535949000',
           CASE WHEN (SELECT round(sum(p.hasil_kg * h.harga_per_kg), 2)
                      FROM panen p
                      JOIN LATERAL (
                          SELECT hp.harga_per_kg
                          FROM harga_pasar hp
                          WHERE hp.komoditas_id = p.komoditas_id
                            AND hp.tanggal <= p.tanggal
                          ORDER BY hp.tanggal DESC
                          LIMIT 1) h ON true) = 535949000.00
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 10, 'nilai panen terbesar: jagung Ladang Sumbing 2026-02-18 Rp 56700000',
           CASE WHEN (SELECT round(p.hasil_kg * h.harga_per_kg, 2)
                      FROM panen p
                      JOIN lahan l ON l.id = p.lahan_id
                      JOIN LATERAL (
                          SELECT hp.harga_per_kg
                          FROM harga_pasar hp
                          WHERE hp.komoditas_id = p.komoditas_id
                            AND hp.tanggal <= p.tanggal
                          ORDER BY hp.tanggal DESC
                          LIMIT 1) h ON true
                      WHERE l.nama = 'Ladang Sumbing'
                        AND p.tanggal = DATE '2026-02-18') = 56700000.00
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 11, 'peringkat nilai per hektar dimenangkan Sawah Blumbang',
           CASE WHEN (SELECT l.nama
                      FROM lahan l
                      JOIN panen p ON p.lahan_id = l.id
                      JOIN LATERAL (
                          SELECT hp.harga_per_kg
                          FROM harga_pasar hp
                          WHERE hp.komoditas_id = p.komoditas_id
                            AND hp.tanggal <= p.tanggal
                          ORDER BY hp.tanggal DESC
                          LIMIT 1) h ON true
                      GROUP BY l.nama, l.luas_hektar
                      ORDER BY sum(p.hasil_kg * h.harga_per_kg) / l.luas_hektar DESC
                      LIMIT 1) = 'Sawah Blumbang'
                THEN 'LULUS' ELSE 'GAGAL' END
    UNION ALL
    SELECT 12, 'rata-rata bergerak 7 baris padi Maret 2026 meliputi 28 baris',
           CASE WHEN (SELECT count(*)
                      FROM harga_pasar h
                      JOIN komoditas k ON k.id = h.komoditas_id
                      WHERE k.nama = 'padi'
                        AND h.tanggal BETWEEN DATE '2026-03-01' AND DATE '2026-03-31') = 28
                THEN 'LULUS' ELSE 'GAGAL' END
) uji
ORDER BY nomor;

sql
-- uji-integritas.sql — uji constraint bisnis pada database terisi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-integritas.sql
-- Lulus bila setiap baris kolom status bernilai LULUS.
-- Baris uji memakai nama "Lahan Uji Integritas" agar mudah dideteksi bila bocor.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    kasus      text,
    diharapkan text,
    aktual     text,
    status     text
);

DO $$
DECLARE
    v_state text;
BEGIN
    -- Kasus 1: hasil panen nol (CHECK)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2026-03-20', 0.00, 'A');
        INSERT INTO hasil_uji VALUES (1, 'hasil panen nol', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (1, 'hasil panen nol', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 2: hasil panen negatif (CHECK)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2026-03-20', -500.00, 'A');
        INSERT INTO hasil_uji VALUES (2, 'hasil panen negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (2, 'hasil panen negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 3: luas lahan nol (CHECK)
    BEGIN
        INSERT INTO lahan (nama, desa, luas_hektar)
        VALUES ('Lahan Uji Integritas', 'Nowhere', 0);
        INSERT INTO hasil_uji VALUES (3, 'luas lahan nol', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (3, 'luas lahan nol', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 4: harga pasar nol (CHECK)
    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2026-03-20', 0.00);
        INSERT INTO hasil_uji VALUES (4, 'harga pasar nol', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (4, 'harga pasar nol', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 5: kualitas di luar A/B/C (enum)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2026-03-20', 500.00, 'D');
        INSERT INTO hasil_uji VALUES (5, 'kualitas di luar A/B/C', '22P02', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (5, 'kualitas di luar A/B/C', '22P02', v_state,
            CASE WHEN v_state = '22P02' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 6: panen di lahan yang tidak ada (foreign key)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (99, 1, DATE '2026-03-20', 500.00, 'A');
        INSERT INTO hasil_uji VALUES (6, 'panen di lahan yang tidak ada', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (6, 'panen di lahan yang tidak ada', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 7: panen ganda petak-komoditas-hari (UNIQUE)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-10', 500.00, 'A');
        INSERT INTO hasil_uji VALUES (7, 'panen ganda petak-komoditas-hari', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (7, 'panen ganda petak-komoditas-hari', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 8: harga ganda komoditas-hari (UNIQUE)
    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2025-03-10', 6350.00);
        INSERT INTO hasil_uji VALUES (8, 'harga ganda komoditas-hari', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (8, 'harga ganda komoditas-hari', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 9: nama lahan duplikat (UNIQUE)
    BEGIN
        INSERT INTO lahan (nama, desa, luas_hektar)
        VALUES ('Sawah Blumbang', 'Blumbang Baru', 2.00);
        INSERT INTO hasil_uji VALUES (9, 'nama lahan duplikat', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (9, 'nama lahan duplikat', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 10: panen sah tetap diterima (uji positif)
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2026-03-20', 400.00, 'B');
        INSERT INTO hasil_uji VALUES (10, 'panen sah tetap diterima', 'OK', 'OK', 'LULUS');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (10, 'panen sah tetap diterima', 'OK', v_state,
            'GAGAL: ditolak ' || v_state);
    END;
END $$;

-- Bersihkan baris uji positif agar database kembali ke kondisi data contoh.
DELETE FROM panen
WHERE lahan_id = 1 AND komoditas_id = 1 AND tanggal = DATE '2026-03-20';

SELECT nomor, kasus, diharapkan, aktual, status
FROM hasil_uji
ORDER BY nomor;

-- Pemeriksaan bocor: tidak boleh ada baris uji yang tersisa.
SELECT count(*) AS baris_uji_bocor
FROM panen
WHERE tanggal = DATE '2026-03-20';

Proyek: Database Analisis Data Pertanian

Yang Akan Kita Bangun #

Skema Database #

Prasyarat #

Peta Bagian #

Cara Mengikuti Proyek #

Referensi #

Berkas Sumber

database/isi-data.sql

database/skema.sql

queries/harga-bulanan.sql

queries/lahan-terbaik.sql

queries/nilai-panen.sql

queries/produksi-vs-harga.sql

queries/produktivitas-lahan.sql

tests/uji-analitik.sql

tests/uji-integritas.sql