Window

LEAD

PostgreSQL

Mengakses nilai dari baris selanjutnya dalam result set. Kebalikan dari LAG.

Tipe hasil: same as expression

Syntax

SQL

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

Parameter

expressionanywajib

Kolom atau ekspresi yang nilainya akan diambil

offsetintegeropsional

Berapa baris ke depan (default: 1)

Default: 1

defaultanyopsional

Nilai default jika tidak ada baris selanjutnya

Contoh Penggunaan

Melihat Task Selanjutnya

SQL

1 SELECT
2   task_name,
3   due_date,
4   LEAD(task_name) OVER (ORDER BY due_date) as next_task,
5   LEAD(due_date) OVER (ORDER BY due_date) as next_due_date
6 FROM tasks;

Menampilkan task saat ini dan task selanjutnya.

Hasil

task_name	due_date	next_task	next_due_date
Design	2024-03-10	Development	2024-03-20
Development	2024-03-20	Testing	2024-03-25
Testing	2024-03-25	NULL	NULL

Menghitung Durasi Event

SQL

1 SELECT
2   event_name,
3   start_time,
4   LEAD(start_time) OVER (ORDER BY start_time) - start_time as duration
5 FROM events;

Menghitung durasi setiap event (sampai event berikutnya dimulai).

Fungsi Equivalen di Database Lain

LEAD

MySQL

LEAD

Snowflake

LEAD

BigQuery

LEAD

DuckDB

Fungsi Terkait

LAGFIRST_VALUELAST_VALUENTH_VALUE

Kembali ke PostgreSQL Functions

1	SELECT
2	task_name,
3	due_date,
4	LEAD(task_name) OVER (ORDER BY due_date) as next_task,
5	LEAD(due_date) OVER (ORDER BY due_date) as next_due_date
6	FROM tasks;

1	SELECT
2	event_name,
3	start_time,
4	LEAD(start_time) OVER (ORDER BY start_time) - start_time as duration
5	FROM events;