Window

LAG

PostgreSQL

Mengakses nilai dari baris sebelumnya dalam result set berdasarkan offset tertentu. Sangat berguna untuk perbandingan dengan periode sebelumnya.

Tipe hasil: same as expression

Syntax

SQL

LAG(expression [, offset [, default]]) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

Parameter

expressionanywajib

Kolom atau ekspresi yang nilainya akan diambil

ORDER BYclausewajib

Menentukan urutan baris untuk perhitungan LAG

offsetintegeropsional

Berapa baris ke belakang (default: 1)

Default: 1

defaultanyopsional

Nilai default jika tidak ada baris sebelumnya

PARTITION BYclauseopsional

Opsional. Membagi hasil ke dalam partition terpisah

Contoh Penggunaan

Perbandingan dengan Bulan Sebelumnya

SQL

1 SELECT
2   month,
3   revenue,
4   LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
5   revenue - LAG(revenue) OVER (ORDER BY month) as revenue_change
6 FROM monthly_sales;

Menampilkan revenue bulan ini vs bulan sebelumnya dan selisihnya.

Hasil

month	revenue	prev_month	change
2024-01	100000	NULL	NULL
2024-02	120000	100000	20000
2024-03	115000	120000	-5000

LAG dengan Default Value

SQL

1 SELECT
2   date,
3   price,
4   LAG(price, 1, price) OVER (ORDER BY date) as prev_price,
5   ROUND((price - LAG(price, 1, price) OVER (ORDER BY date)) /
6         LAG(price, 1, price) OVER (ORDER BY date) * 100, 2) as pct_change
7 FROM stock_prices
8 WHERE symbol = 'AAPL';

Menghitung persentase perubahan harga saham. Default value mencegah division by zero di baris pertama.

LAG per Customer (PARTITION BY)

SQL

1 SELECT
2   customer_id,
3   order_date,
4   order_total,
5   LAG(order_date) OVER (
6     PARTITION BY customer_id
7     ORDER BY order_date
8   ) as prev_order_date,
9   order_date - LAG(order_date) OVER (
10     PARTITION BY customer_id
11     ORDER BY order_date
12   ) as days_between_orders
13 FROM orders;

Menghitung jarak hari antar order untuk setiap customer.

Hasil

customer_id	order_date	order_total	prev_order_date	days_between
1	2024-01-15	50000	NULL	NULL
1	2024-02-20	75000	2024-01-15	36
1	2024-03-10	60000	2024-02-20	19
2	2024-01-20	40000	NULL	NULL
... 1 baris lainnya

Fungsi Equivalen di Database Lain

LAG

MySQL

LAG

Snowflake

LAG

BigQuery

LAG

DuckDB

Fungsi Terkait

LEADFIRST_VALUELAST_VALUENTH_VALUE

Kembali ke PostgreSQL Functions

1	SELECT
2	month,
3	revenue,
4	LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
5	revenue - LAG(revenue) OVER (ORDER BY month) as revenue_change
6	FROM monthly_sales;

1	SELECT
2	date,
3	price,
4	LAG(price, 1, price) OVER (ORDER BY date) as prev_price,
5	ROUND((price - LAG(price, 1, price) OVER (ORDER BY date)) /
6	LAG(price, 1, price) OVER (ORDER BY date) * 100, 2) as pct_change
7	FROM stock_prices
8	WHERE symbol = 'AAPL';

1	SELECT
2	customer_id,
3	order_date,
4	order_total,
5	LAG(order_date) OVER (
6	PARTITION BY customer_id
7	ORDER BY order_date
8	) as prev_order_date,
9	order_date - LAG(order_date) OVER (
10	PARTITION BY customer_id
11	ORDER BY order_date
12	) as days_between_orders
13	FROM orders;