Window

FIRST_VALUE

PostgreSQL

Mengembalikan nilai pertama dalam window frame. Berguna untuk mengambil nilai awal, baseline, atau referensi dalam perbandingan.

Tipe hasil: same as input

Syntax

SQL

FIRST_VALUE(expression) OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
  [frame_clause]
)

Parameter

expressionanywajib

Kolom atau ekspresi yang nilainya diambil

ORDER BYexpressionwajib

Menentukan urutan dalam window

PARTITION BYexpressionopsional

Membagi data menjadi grup terpisah

Contoh Penggunaan

Harga Pembukaan (Opening Price)

SQL

1 SELECT 
2   date,
3   stock_symbol,
4   price,
5   FIRST_VALUE(price) OVER (
6     PARTITION BY stock_symbol 
7     ORDER BY date
8   ) AS opening_price
9 FROM stock_prices;

Mengambil harga pertama (pembukaan) untuk setiap saham.

Hasil

date	stock_symbol	price	opening_price
2024-01-01	BBCA	10000	10000
2024-01-02	BBCA	10200	10000
2024-01-01	GOTO	500	500

Perbandingan dengan Nilai Awal

SQL

1 SELECT 
2   date,
3   revenue,
4   FIRST_VALUE(revenue) OVER (ORDER BY date) AS first_revenue,
5   revenue - FIRST_VALUE(revenue) OVER (ORDER BY date) AS diff_from_start
6 FROM monthly_sales;

Menghitung selisih revenue dari bulan pertama.

Baseline per Kategori

SQL

1 SELECT 
2   category,
3   product_name,
4   price,
5   price / FIRST_VALUE(price) OVER (
6     PARTITION BY category ORDER BY price
7   ) AS price_ratio
8 FROM products;

Rasio harga terhadap produk termurah di kategori.

Fungsi Equivalen di Database Lain

FIRST_VALUE

MySQL

FIRST_VALUE

Snowflake

FIRST_VALUE

BigQuery

FIRST_VALUE

DuckDB

Fungsi Terkait

LAGLEADLAST_VALUENTH_VALUE

Kembali ke PostgreSQL Functions

1	SELECT
2	date,
3	stock_symbol,
4	price,
5	FIRST_VALUE(price) OVER (
6	PARTITION BY stock_symbol
7	ORDER BY date
8	) AS opening_price
9	FROM stock_prices;

1	SELECT
2	date,
3	revenue,
4	FIRST_VALUE(revenue) OVER (ORDER BY date) AS first_revenue,
5	revenue - FIRST_VALUE(revenue) OVER (ORDER BY date) AS diff_from_start
6	FROM monthly_sales;

1	SELECT
2	category,
3	product_name,
4	price,
5	price / FIRST_VALUE(price) OVER (
6	PARTITION BY category ORDER BY price
7	) AS price_ratio
8	FROM products;