Bagian-bagian sebelumnya menumpuk dua jenis catatan yang hidup di waktu yang berbeda: panen yang datang sekali musim dan harga pasar yang bergerak hampir setiap hari. Bagian penutup proyek ini menyatukan keduanya menjadi informasi yang bisa diambil keputusan: berapa nilai setiap panen pada saat kejadiannya, apakah panen raya benar-benar menekan harga, bagaimana tren harga setelah diperhalus, dan petak mana yang paling menghasilkan nilai. Seluruh analisis memakai perangkat yang sudah kita kuasai di Section Window Functions dan Advanced SQL — kini diterapkan pada data yang nyata.
Fondasi: Seluruh Dunia Proyek dalam Satu Berkas #
Analisis produksi-harga membutuhkan kedua jenis data sekaligus, sehingga berkas fondasi mengulang skema dari Bagian 1, data panen dari Bagian 2, dan deret harga dari Bagian 3 — lengkap dengan constraint yang sama:
sql
siapkan.sql
-- siapkan.sql — fondasi Bagian 4: seluruh dunia proyek dalam satu berkas.
-- Skema dari Bagian 1, data panen dari Bagian 2, deret harga dari Bagian 3;
-- analisis produksi-harga membutuhkan keduanya sekaligus.
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)
);
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;Output yang Dihasilkan:
text
CREATE TYPE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 4
INSERT 0 16
INSERT 0 8
INSERT 0 1792Tiga keputusan pada berkas fondasi ini menentukan analisis yang mengikuti. Tabel panen dibatasi UNIQUE (lahan_id, komoditas_id, tanggal) — satu catatan per petak-komoditas per hari — dan ON CONFLICT DO NOTHING menjaga pembangkitan data tetap aman diputar ulang. Deret harga dibangkitkan dengan komponen sinus tahunan: harga naik-turun mengikuti musim kalender dengan puncak dan lembah yang bisa diprediksi, ditambah derau harian kecil — bentuk yang menyerupai harga komoditas sungguhan. Hasilnya 1792 baris harga (456 hari × 4 komoditas) menopang seluruh tren di bawah.
Nilai Panen: Join As-of dengan LATERAL #
Berapa nilai sebuah panen? Jawabannya bergantung pada harga pada saat panen itu terjadi — bukan harga hari ini, bukan harga rata-rata bulan itu. Pola yang tepat adalah as-of join: untuk tiap panen, ambil harga terbaru yang diketahui pada atau sebelum tanggalnya, dengan JOIN LATERAL ... ORDER BY tanggal DESC LIMIT 1:
sql
nilai-panen.sql
-- nilai-panen.sql — menghitung nilai setiap panen pada saat kejadiannya.
-- JOIN LATERAL mengambil harga terbaru yang diketahui pada atau sebelum
-- tanggal panen: harga <= p.tanggal, urut turun, ambil satu. Query kedua
-- merangkum nilai per komoditas lewat CTE yang sama.
SELECT p.tanggal,
l.nama AS lahan,
k.nama AS komoditas,
p.hasil_kg,
p.kualitas,
h.harga_per_kg,
round(p.hasil_kg * h.harga_per_kg, 2) AS nilai_panen
FROM panen p
JOIN lahan l ON l.id = p.lahan_id
JOIN komoditas k ON k.id = p.komoditas_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
ORDER BY p.tanggal, p.id;
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;Output yang Dihasilkan:
text
tanggal | lahan | komoditas | hasil_kg | kualitas | harga_per_kg | nilai_panen
------------+-------------------+--------------+----------+----------+--------------+-------------
2025-02-20 | Ladang Menoreh | jagung | 9000.00 | A | 4400.00 | 39600000.00
2025-02-25 | Ladang Sumbing | jagung | 12000.00 | A | 4300.00 | 51600000.00
2025-03-10 | Sawah Blumbang | padi | 6000.00 | A | 6400.00 | 38400000.00
2025-03-18 | Sawah Kaligondang | padi | 3680.00 | A | 6250.00 | 23000000.00
2025-05-10 | Kebun Pakem | kacang tanah | 850.00 | B | 13600.00 | 11560000.00
2025-06-18 | Ladang Menoreh | kedelai | 2700.00 | B | 11400.00 | 30780000.00
2025-09-05 | Kebun Pakem | kacang tanah | 800.00 | B | 13850.00 | 11080000.00
2025-10-15 | Ladang Menoreh | jagung | 8250.00 | A | 4700.00 | 38775000.00
2025-10-18 | Sawah Blumbang | padi | 5760.00 | B | 6750.00 | 38880000.00
2025-10-20 | Ladang Sumbing | jagung | 11000.00 | B | 4800.00 | 52800000.00
2025-10-25 | Sawah Kaligondang | padi | 3840.00 | A | 6700.00 | 25728000.00
2026-01-15 | Kebun Pakem | kacang tanah | 950.00 | A | 14000.00 | 13300000.00
2026-02-12 | Ladang Menoreh | kedelai | 3000.00 | A | 11950.00 | 35850000.00
2026-02-18 | Ladang Sumbing | jagung | 12600.00 | A | 4500.00 | 56700000.00
2026-03-05 | Sawah Blumbang | padi | 6240.00 | A | 6650.00 | 41496000.00
2026-03-12 | Sawah Kaligondang | padi | 4000.00 | A | 6600.00 | 26400000.00
(16 rows)
komoditas | jumlah_panen | total_kg | total_nilai
--------------+--------------+----------+--------------
jagung | 5 | 52850.00 | 239475000.00
padi | 6 | 29520.00 | 193904000.00
kedelai | 2 | 5700.00 | 66630000.00
kacang tanah | 3 | 2600.00 | 35940000.00
(4 rows)Dua hal layak diperhatikan dari teknik ini. Pertama, JOIN LATERAL memungkinkan subquery membaca kolom p.tanggal dan p.komoditas_id dari baris panen yang sedang diproses — — hal yang tidak bisa dilakukan join biasa lakukan dengan LIMIT 1. Kedua, query kedua menggunakan CTE untuk menghitung nilai per panen sekali, lalu meringkasnya per komoditas: nilai tidak pernah dihitung dua kali. Pola as-of join ini juga muncul di dunia keuangan (harga transaksi historis) dan audit (harga kontrak pada tanggal kejadian).
Apakah Panen Besar Menekan Harga? #
Intuisi petani berkata: saat panen raya membanjiri pasar, harga jatuh. Data kita bisa menguji intuisi itu secara kuantitatif — menggabungkan produksi kuartalan padi dengan harga rata-rata kuartalan, mengisi kuartal kosong dengan generate_series, menghitung perubahan harga dengan LAG, lalu meringkas arah hubungan dengan corr():
-- 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.
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;Output yang Dihasilkan:
text
kuartal | produksi_kg | harga_rata | harga_rata_lalu | perubahan_persen
------------+-------------+------------+-----------------+------------------
2025-01-01 | 9680.00 | 6623 | |
2025-04-01 | 0 | 6879 | 6623 | 3.9
2025-07-01 | 0 | 7197 | 6879 | 4.6
2025-10-01 | 9600.00 | 6923 | 7197 | -3.8
2026-01-01 | 10240.00 | 6891 | 6923 | -0.5
(5 rows)
korelasi_produksi_harga
-------------------------
-0.599
(1 row)Angka penutup jawabannya tegas: korelasi -0.599 negatif dan cukup kuat — kuartal dengan produksi padi tinggi cenderung diikuti harga rata-rata yang lebih rendah, persis seperti intuisi hukum penawaran. corr() adalah agregat statistik bawaan PostgreSQL; hasilnya selalu antara -1 dan 1, dan data kita menempatkannya di zona “hubungan berlawanan arah yang nyata”. Query pertama menunjukkan mekanismenya per kuartal: kolom perubahan_persen memakai LAG untuk membandingkan harga kuartalan secara berurutan.
Rata-Rata Bergerak: Meratakan Derau Harian #
Harga harian berisik; trennya baru terlihat setelah diperhalus. Rata-rata bergerak 7-baris dengan window frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW melakukan penghalusan itu langsung di SQL:
-- rata-rata-bergerak.sql — meratakan fluktuasi harian dengan window frame.
-- Deret harga padi Maret 2026; rata_bergerak_7 adalah rata-rata 7 baris
-- (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — bukan 7 hari kalender,
-- karena tiga hari Maret tidak memiliki catatan.
SELECT h.tanggal,
h.harga_per_kg,
round(avg(h.harga_per_kg) OVER (
ORDER BY h.tanggal
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 0) AS rata_bergerak_7
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'
ORDER BY h.tanggal;Output yang Dihasilkan:
text
tanggal | harga_per_kg | rata_bergerak_7
------------+--------------+-----------------
2026-03-01 | 6700.00 | 6700
2026-03-03 | 6750.00 | 6725
2026-03-04 | 6650.00 | 6700
2026-03-05 | 6650.00 | 6688
2026-03-06 | 6700.00 | 6690
2026-03-07 | 6600.00 | 6675
2026-03-08 | 6600.00 | 6664
2026-03-09 | 6650.00 | 6657
2026-03-10 | 6650.00 | 6643
2026-03-11 | 6550.00 | 6629
2026-03-12 | 6600.00 | 6621
2026-03-13 | 6600.00 | 6607
2026-03-14 | 6650.00 | 6614
2026-03-15 | 6650.00 | 6621
2026-03-17 | 6700.00 | 6629
2026-03-18 | 6500.00 | 6607
2026-03-19 | 6550.00 | 6607
2026-03-20 | 6550.00 | 6600
2026-03-21 | 6600.00 | 6600
2026-03-22 | 6600.00 | 6593
2026-03-24 | 6650.00 | 6593
2026-03-25 | 6600.00 | 6579
2026-03-26 | 6600.00 | 6593
2026-03-27 | 6650.00 | 6607
2026-03-28 | 6700.00 | 6629
2026-03-29 | 6600.00 | 6629
2026-03-30 | 6650.00 | 6636
2026-03-31 | 6650.00 | 6636
(28 rows)Perhatikan perbedaan halus yang dijelaskan komentarnya: frame ROWS menghitung 7 baris, bukan 7 hari kalender. Tiga hari Maret tidak memiliki catatan harga, sehingga rata-rata di akhir bulan menjangkau lebih jauh ke belakang dari sekadar seminggu. Bila jendela harus benar-benar kalender, RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW pada timestamp adalah jawabannya — materi lengkapnya ada di chapter Window Frame.
Tahunan dan Musiman dengan FILTER #
Dua pertanyaan terakhir membentang lintas tahun: bagaimana harga kuartal I tahun ini dibanding tahun lalu, dan apakah ada pola musiman yang berulang tiap tahun? Klausa FILTER menjawab keduanya tanpa join tambahan:
-- yoy-dan-musim.sql — perbandingan tahunan dan pola musiman.
-- Query pertama: harga rata-rata kuartal I 2025 vs kuartal I 2026 per
-- komoditas dengan agregasi FILTER (year-over-year).
-- Query kedua: harga rata-rata per kuartal-kalender (1-4) lintas tahun —
-- pola musiman: kuartal panen raya murah, kuartal paceklik mahal.
SELECT k.nama AS komoditas,
round(avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'), 0) AS harga_q1_2025,
round(avg(h.harga_per_kg) FILTER (WHERE h.tanggal >= DATE '2026-01-01'), 0) AS harga_q1_2026,
round(100.0 * (avg(h.harga_per_kg) FILTER (WHERE h.tanggal >= DATE '2026-01-01')
- avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'))
/ avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'), 1) AS perubahan_persen
FROM harga_pasar h
JOIN komoditas k ON k.id = h.komoditas_id
WHERE h.tanggal < DATE '2025-04-01' OR h.tanggal >= DATE '2026-01-01'
GROUP BY k.nama
ORDER BY k.nama;
SELECT k.nama AS komoditas,
extract(quarter FROM h.tanggal)::int AS kuartal,
round(avg(h.harga_per_kg), 0) AS harga_rata,
count(*) AS baris
FROM harga_pasar h
JOIN komoditas k ON k.id = h.komoditas_id
GROUP BY k.nama, extract(quarter FROM h.tanggal)
ORDER BY k.nama, kuartal;Output yang Dihasilkan:
text
komoditas | harga_q1_2025 | harga_q1_2026 | perubahan_persen
--------------+---------------+---------------+------------------
jagung | 4499 | 4683 | 4.1
kacang tanah | 13862 | 14218 | 2.6
kedelai | 11716 | 12047 | 2.8
padi | 6623 | 6891 | 4.0
(4 rows)
komoditas | kuartal | harga_rata | baris
--------------+---------+------------+-------
jagung | 1 | 4589 | 175
jagung | 2 | 4942 | 91
jagung | 3 | 5044 | 92
jagung | 4 | 4921 | 92
kacang tanah | 1 | 14037 | 175
kacang tanah | 2 | 13715 | 91
kacang tanah | 3 | 13959 | 92
kacang tanah | 4 | 14253 | 92
kedelai | 1 | 11878 | 175
kedelai | 2 | 11716 | 91
kedelai | 3 | 11916 | 92
kedelai | 4 | 12283 | 92
padi | 1 | 6755 | 175
padi | 2 | 6879 | 91
padi | 3 | 7197 | 92
padi | 4 | 6923 | 92
(16 rows)Query pertama menghitung dua rata-rata dari baris yang sama lewat dua FILTER berbeda — satu per periode — lalu menyusun persentase perubahannya, pola year-over-year dari chapter Query Pattern untuk Data Analisis. Query kedua membagi data per kuartal kalender lintas tahun: komponen sinus tahunan pada data kita menjadikan kuartal tertentu konsisten lebih murah atau lebih mahal — itulah pola musiman yang dicari petani untuk memilih waktu jual.
Peringkat Lahan: Dua Ukuran yang Berbeda Cerita #
Analisis penutup memberi peringkat petak berdasarkan nilai panen — dengan dua kolom peringkat yang sengaja berbeda: total nilai mutlak dan nilai per hektar:
-- 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.
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;Output yang Dihasilkan:
text
lahan | luas_hektar | jumlah_panen | total_kg | total_nilai | nilai_per_hektar | peringkat_nilai | peringkat_per_hektar
-------------------+-------------+--------------+----------+--------------+------------------+-----------------+----------------------
Ladang Sumbing | 2.00 | 3 | 35600.00 | 161100000.00 | 80550000 | 1 | 4
Ladang Menoreh | 1.50 | 4 | 22950.00 | 145005000.00 | 96670000 | 2 | 2
Sawah Blumbang | 1.20 | 3 | 18000.00 | 118776000.00 | 98980000 | 3 | 1
Sawah Kaligondang | 0.80 | 3 | 11520.00 | 75128000.00 | 93910000 | 4 | 3
Kebun Pakem | 0.50 | 3 | 2600.00 | 35940000.00 | 71880000 | 5 | 5
(5 rows)Perbedaan kedua peringkat itulah pelajaran penutupnya. Petak seluas dua hektar menang total nilai karena volume — tetapi petak setengah hektar yang dikelola intensif bisa menang nilai per hektar, ukuran efisiensi yang lebih adil untuk membandingkan produktivitas. Dua rank() OVER (...) pada satu query memperlihatkan betapa murahnya menghadirkan beberapa sudut pandang sekaligus dengan window function.
Penutup Proyek #
Proyek analitik pertanian ini menuntaskan perjalanan Course PostgreSQL dengan gaya yang berbeda dari lima proyek sebelumnya: bukan membangun sistem transaksional, melainkan mengubah catatan operasional menjadi keputusan. As-of join menetapkan nilai historis, korelasi menguji intuisi pasar, rata-rata bergerak membedakan tren dari derau, dan peringkat ganda menunjukkan bahwa jawaban bergantung pada pertanyaan. Perangkatnya seluruhnya SQL — tanpa satu baris kode di luar database.
Dengan ini enam proyek selesai dan seluruh materi Course PostgreSQL utuh. Selanjutnya tinggal satu cara memantapkan semuanya: pakai — buka kembali Section Sumber Belajar, pilih jalur sesuai peran Anda, dan bangun database untuk masalah Anda sendiri.
Referensi #
Berkas Sumber
Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.
lahan-terbaik.sql
Unduh berkassql
-- 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.
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;
nilai-panen.sql
Unduh berkassql
-- nilai-panen.sql — menghitung nilai setiap panen pada saat kejadiannya.
-- JOIN LATERAL mengambil harga terbaru yang diketahui pada atau sebelum
-- tanggal panen: harga <= p.tanggal, urut turun, ambil satu. Query kedua
-- merangkum nilai per komoditas lewat CTE yang sama.
SELECT p.tanggal,
l.nama AS lahan,
k.nama AS komoditas,
p.hasil_kg,
p.kualitas,
h.harga_per_kg,
round(p.hasil_kg * h.harga_per_kg, 2) AS nilai_panen
FROM panen p
JOIN lahan l ON l.id = p.lahan_id
JOIN komoditas k ON k.id = p.komoditas_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
ORDER BY p.tanggal, p.id;
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;
produksi-vs-harga.sql
Unduh berkassql
-- 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.
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;
rata-rata-bergerak.sql
Unduh berkassql
-- rata-rata-bergerak.sql — meratakan fluktuasi harian dengan window frame.
-- Deret harga padi Maret 2026; rata_bergerak_7 adalah rata-rata 7 baris
-- (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — bukan 7 hari kalender,
-- karena tiga hari Maret tidak memiliki catatan.
SELECT h.tanggal,
h.harga_per_kg,
round(avg(h.harga_per_kg) OVER (
ORDER BY h.tanggal
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 0) AS rata_bergerak_7
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'
ORDER BY h.tanggal;
siapkan.sql
Unduh berkassql
-- siapkan.sql — fondasi Bagian 4: seluruh dunia proyek dalam satu berkas.
-- Skema dari Bagian 1, data panen dari Bagian 2, deret harga dari Bagian 3;
-- analisis produksi-harga membutuhkan keduanya sekaligus.
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)
);
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;
yoy-dan-musim.sql
Unduh berkassql
-- yoy-dan-musim.sql — perbandingan tahunan dan pola musiman.
-- Query pertama: harga rata-rata kuartal I 2025 vs kuartal I 2026 per
-- komoditas dengan agregasi FILTER (year-over-year).
-- Query kedua: harga rata-rata per kuartal-kalender (1-4) lintas tahun —
-- pola musiman: kuartal panen raya murah, kuartal paceklik mahal.
SELECT k.nama AS komoditas,
round(avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'), 0) AS harga_q1_2025,
round(avg(h.harga_per_kg) FILTER (WHERE h.tanggal >= DATE '2026-01-01'), 0) AS harga_q1_2026,
round(100.0 * (avg(h.harga_per_kg) FILTER (WHERE h.tanggal >= DATE '2026-01-01')
- avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'))
/ avg(h.harga_per_kg) FILTER (WHERE h.tanggal < DATE '2025-04-01'), 1) AS perubahan_persen
FROM harga_pasar h
JOIN komoditas k ON k.id = h.komoditas_id
WHERE h.tanggal < DATE '2025-04-01' OR h.tanggal >= DATE '2026-01-01'
GROUP BY k.nama
ORDER BY k.nama;
SELECT k.nama AS komoditas,
extract(quarter FROM h.tanggal)::int AS kuartal,
round(avg(h.harga_per_kg), 0) AS harga_rata,
count(*) AS baris
FROM harga_pasar h
JOIN komoditas k ON k.id = h.komoditas_id
GROUP BY k.nama, extract(quarter FROM h.tanggal)
ORDER BY k.nama, kuartal;