Postgres Window Function: FIRST_VALUE vs LAST_VALUE

Bandingkan harga menu antara yang paling murah dan paling mahal

Hasbullah Abu Bakar

Jul 18, 2024

gray concrete buildings at daytime — Photo by Marten Bjork on Unsplash

Hari ini kita akan buat analisa menu makanan dalam sebuah kedai makan. Matlamat analisa kita kali ini adalah:

Mendapatkan harga menu paling murah dan paling mahal dalam setiap kategori
Bandingkan harga menu dengan menu paling mahal dan murah

Mari kita mulakan

Cipta Table menu

CREATE TABLE menu (
    menuid VARCHAR(256) PRIMARY KEY,
    nama_menu VARCHAR(100),
    harga_menu numeric,
    kategori_menu VARCHAR(100)
);

Sedikit maklumat tentang table ini:

menuid - Unik ID kepada menu itu
nama_menu - Nama Menu
harga_menu - Harga Menu
kategori_menu - Kategori Menu (contohnya: Malaysian, Latin America)

Disebabkan kita henda menggunakaan UUID sebagai primay key untuk menuid, create extentsion uuid dengan:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Mari insert data pula


INSERT INTO menu (menuid, nama_menu, harga_menu, kategori_menu) VALUES
(uuid_generate_v4(), 'Nasi Lemak', 3.25, 'Malaysian'),
(uuid_generate_v4(), 'Roti Canai', 1.50, 'Malaysian'),
(uuid_generate_v4(), 'Satay', 0.90, 'Malaysian'),
(uuid_generate_v4(), 'Char Kway Teow', 2.50, 'Malaysian'),
(uuid_generate_v4(), 'Laksa', 3.00, 'Malaysian'),
(uuid_generate_v4(), 'Chicken Rice', 4.00, 'Malaysian'),
(uuid_generate_v4(), 'Hainanese Chicken Rice', 4.50, 'Malaysian'),
(uuid_generate_v4(), 'Beef Rendang', 5.00, 'Malaysian'),
(uuid_generate_v4(), 'Nasi Goreng', 3.75, 'Malaysian'),
(uuid_generate_v4(), 'Mee Goreng', 2.75, 'Malaysian'),
(uuid_generate_v4(), 'Tom Yum', 4.25, 'Thai'),
(uuid_generate_v4(), 'Pad Thai', 3.75, 'Thai'),
(uuid_generate_v4(), 'Green Curry', 5.50, 'Thai'),
(uuid_generate_v4(), 'Red Curry', 5.50, 'Thai'),
(uuid_generate_v4(), 'Massaman Curry', 6.00, 'Thai'),
(uuid_generate_v4(), 'Som Tum', 2.50, 'Thai'),
(uuid_generate_v4(), 'Spring Rolls', 1.25, 'Thai'),
(uuid_generate_v4(), 'Pho', 4.50, 'Vietnamese'),
(uuid_generate_v4(), 'Banh Mi', 3.00, 'Vietnamese'),
(uuid_generate_v4(), 'Bun Cha', 4.00, 'Vietnamese'),
(uuid_generate_v4(), 'Goi Cuon', 2.00, 'Vietnamese'),
(uuid_generate_v4(), 'Cao Lau', 4.25, 'Vietnamese'),
(uuid_generate_v4(), 'Banh Xeo', 3.75, 'Vietnamese'),
(uuid_generate_v4(), 'Bibimbap', 5.00, 'Korean'),
(uuid_generate_v4(), 'Kimchi', 1.50, 'Korean'),
(uuid_generate_v4(), 'Bulgogi', 6.00, 'Korean'),
(uuid_generate_v4(), 'Japchae', 4.00, 'Korean'),
(uuid_generate_v4(), 'Tteokbokki', 3.00, 'Korean'),
(uuid_generate_v4(), 'Samgyeopsal', 7.00, 'Korean'),
(uuid_generate_v4(), 'Kimchi Stew', 3.50, 'Korean'),
(uuid_generate_v4(), 'Sushi', 2.75, 'Japanese'),
(uuid_generate_v4(), 'Ramen', 5.50, 'Japanese'),
(uuid_generate_v4(), 'Tempura', 4.00, 'Japanese'),
(uuid_generate_v4(), 'Udon', 3.75, 'Japanese'),
(uuid_generate_v4(), 'Sashimi', 5.25, 'Japanese'),
(uuid_generate_v4(), 'Yakitori', 3.00, 'Japanese'),
(uuid_generate_v4(), 'Tonkatsu', 4.50, 'Japanese'),
(uuid_generate_v4(), 'Okonomiyaki', 3.75, 'Japanese'),
(uuid_generate_v4(), 'Takoyaki', 3.25, 'Japanese'),
(uuid_generate_v4(), 'Miso Soup', 1.50, 'Japanese'),
(uuid_generate_v4(), 'Pasta Carbonara', 5.00, 'Italian'),
(uuid_generate_v4(), 'Margherita Pizza', 4.75, 'Italian'),
(uuid_generate_v4(), 'Lasagna', 5.25, 'Italian'),
(uuid_generate_v4(), 'Risotto', 6.00, 'Italian'),
(uuid_generate_v4(), 'Tiramisu', 3.50, 'Italian'),
(uuid_generate_v4(), 'Bruschetta', 2.50, 'Italian'),
(uuid_generate_v4(), 'Gelato', 2.75, 'Italian'),
(uuid_generate_v4(), 'Panini', 3.50, 'Italian'),
(uuid_generate_v4(), 'Gnocchi', 4.00, 'Italian'),
(uuid_generate_v4(), 'Focaccia', 2.00, 'Italian'),
(uuid_generate_v4(), 'Fish and Chips', 4.00, 'British'),
(uuid_generate_v4(), 'Bangers and Mash', 4.50, 'British'),
(uuid_generate_v4(), 'Yorkshire Pudding', 2.50, 'British'),
(uuid_generate_v4(), 'Beef Wellington', 6.75, 'British'),
(uuid_generate_v4(), 'Cornish Pasty', 3.25, 'British'),
(uuid_generate_v4(), 'Full English Breakfast', 7.00, 'British'),
(uuid_generate_v4(), 'Scotch Egg', 2.75, 'British'),
(uuid_generate_v4(), 'Chicken Tikka Masala', 5.00, 'Indian'),
(uuid_generate_v4(), 'Butter Chicken', 4.50, 'Indian'),
(uuid_generate_v4(), 'Rogan Josh', 5.50, 'Indian'),
(uuid_generate_v4(), 'Palak Paneer', 4.00, 'Indian'),
(uuid_generate_v4(), 'Chole Bhature', 3.25, 'Indian'),
(uuid_generate_v4(), 'Biryani', 5.00, 'Indian'),
(uuid_generate_v4(), 'Samosa', 1.25, 'Indian'),
(uuid_generate_v4(), 'Aloo Gobi', 3.50, 'Indian'),
(uuid_generate_v4(), 'Paneer Tikka', 4.00, 'Indian'),
(uuid_generate_v4(), 'Naan', 1.75, 'Indian'),
(uuid_generate_v4(), 'Kebab', 3.50, 'Middle Eastern'),
(uuid_generate_v4(), 'Falafel', 2.75, 'Middle Eastern'),
(uuid_generate_v4(), 'Hummus', 2.50, 'Middle Eastern'),
(uuid_generate_v4(), 'Shawarma', 4.50, 'Middle Eastern'),
(uuid_generate_v4(), 'Tabbouleh', 2.00, 'Middle Eastern'),
(uuid_generate_v4(), 'Baba Ganoush', 2.50, 'Middle Eastern'),
(uuid_generate_v4(), 'Baklava', 3.00, 'Middle Eastern'),
(uuid_generate_v4(), 'Fattoush', 2.75, 'Middle Eastern'),
(uuid_generate_v4(), 'Pita Bread', 1.25, 'Middle Eastern'),
(uuid_generate_v4(), 'Shakshuka', 3.75, 'Middle Eastern'),
(uuid_generate_v4(), 'Empanada', 2.00, 'Latin American'),
(uuid_generate_v4(), 'Taco', 2.50, 'Latin American'),
(uuid_generate_v4(), 'Burrito', 4.00, 'Latin American'),
(uuid_generate_v4(), 'Ceviche', 4.50, 'Latin American'),
(uuid_generate_v4(), 'Arepa', 3.00, 'Latin American'),
(uuid_generate_v4(), 'Feijoada', 5.00, 'Latin American'),
(uuid_generate_v4(), 'Tostada', 2.25, 'Latin American'),
(uuid_generate_v4(), 'Quesadilla', 3.75, 'Latin American'),
(uuid_generate_v4(), 'Tamale', 2.50, 'Latin American'),
(uuid_generate_v4(), 'Churro', 1.50, 'Latin American');

Jumlah menu adalah 87 Menu:

select kategori_menu, count(*)
from menu
group by kategori_menu;

Mula-mula kita dapatkan matlamat analisa 1: Mendapatkan Mendapatkan harga menu paling murah dan paling mahal dalam setiap kategori

Dengan menggunakan function FIRST_VALUE dan LAST_VALUE


SELECT
	nama_menu,
	kategori_menu,
	harga_menu,
	FIRST_VALUE (harga_menu) OVER (
		PARTITION BY kategori_menu
		ORDER BY
			harga_menu
	) AS paling_murah_dlm_kategory,
    	LAST_VALUE (harga_menu) OVER (
		PARTITION BY kategori_menu
		ORDER BY
			harga_menu RANGE BETWEEN UNBOUNDED PRECEDING
		AND UNBOUNDED FOLLOWING
	) AS paling_mahal_dlm_kategory
FROM
	menu;

Dan ini adalah sebahagian hasil SQL query:

Mari kita pecahkan SQL ini kepada beberapa bahagian penting

	FIRST_VALUE (harga_menu) OVER (
		PARTITION BY kategori_menu
		ORDER BY
			harga_menu
	) AS paling_murah_dlm_kategory,

Bahagian ini agak mudah, SQL ini mengumpulkan group berdasarkan kategori_menu dan row disusun berdasarkan harga menu. FIRST_VALUE akan mendapatkan baris pertama.

    	LAST_VALUE (harga_menu) OVER (
		PARTITION BY kategori_menu
		ORDER BY
			harga_menu RANGE BETWEEN UNBOUNDED PRECEDING
		AND UNBOUNDED FOLLOWING
	) AS paling_mahal_dlm_kategory

Bahagian ini agak sukar, kerana saya hanya menyangka dengan hanya menggantikan FIRST_VALUE dengan LAST_VALUE, ternyata jangkaan saya meleset. Kerana hasilnya akan seperti berikut

    	LAST_VALUE (harga_menu) OVER (
		PARTITION BY kategori_menu
		ORDER BY
			harga_menu
	) AS paling_mahal_dlm_kategory

Hasilnya:

Ia akan memulangkan current row as row terakhir. Kurang tepatkan? Ini kerana secara default data frame akan menggunakan : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (rujuk)

Dengan menambah keyword: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SQL tersebut akan mendapatkan row yang sebelum dan selepas current row.

Matlamat 2: Bandingkan harga menu dengan menu paling mahal dan murah

Saya menggunakan query ini

WITH CTE_MENU_MIN_MAX AS (SELECT nama_menu,
                                 kategori_menu,
                                 harga_menu,
                                 FIRST_VALUE(harga_menu) OVER (
                                     PARTITION BY kategori_menu
                                     ORDER BY
                                         harga_menu
                                     ) AS paling_murah_dlm_kategory,
                                 LAST_VALUE(harga_menu) OVER (
                                     PARTITION BY kategori_menu
                                     ORDER BY
                                         harga_menu RANGE BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING
                                     ) AS paling_mahal_dlm_kategory
                          FROM menu)
SELECT nama_menu,
       kategori_menu,
       harga_menu,
       paling_murah_dlm_kategory,
       paling_mahal_dlm_kategory,
       (harga_menu - paling_murah_dlm_kategory) AS beza_harga_vs_murah,
       (harga_menu - paling_mahal_dlm_kategory) As beza_harga_vs_mahal
FROM CTE_MENU_MIN_MAX;

Dan hasilnya:

Dengan mendapatkan perbezaan ini ada dapat mencari perbezaan dari segi peratusan (kongsikan SQL anda. Saya biarkan anda sambung analisa ini, hehe)

Selamat mencuba!!!

nota kecil: Saya pun tidak pasti betulkah kategori menu ini sebab saya minta ChatGPT untuk menjana data-data ini (mohon jangan kecam =))

Rujukan:

https://www.postgresqltutorial.com/postgresql-window-function/

https://dba.stackexchange.com/questions/122623/default-value-for-uuid-column-in-postgres

https://www.geeksforgeeks.org/sql-rows-between/

Hasbullah Abu Bakar

Postgres Window Function: FIRST_VALUE vs LAST_VALUE

Bandingkan harga menu antara yang paling murah dan paling mahal

Discussion about this post