Window
PostgreSQL
NTH_VALUE
PostgreSQLMengembalikan nilai pada posisi ke-n dalam window frame. Mengembalikan NULL jika tidak ada cukup baris. n dimulai dari 1.
Tipe hasil:
same as inputSyntax
SQL
NTH_VALUE(expression, n) OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression
[frame_clause]
)Parameter
expressionanywajib
Kolom atau ekspresi yang nilainya diambil
nintegerwajib
Posisi nilai yang diambil (1-based)
ORDER BYexpressionwajib
Menentukan urutan dalam window
PARTITION BYexpressionopsional
Membagi data menjadi grup terpisah
Contoh Penggunaan
Ambil Nilai ke-2
SQL
1 SELECT 2 category, 3 product_name, 4 sales, 5 NTH_VALUE(product_name, 2) OVER ( 6 PARTITION BY category 7 ORDER BY sales DESC 8 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 9 ) AS second_best 10 FROM products;
Mengambil produk dengan penjualan kedua tertinggi per kategori.
Hasil
| category | product_name | sales | second_best |
|---|---|---|---|
| Electronics | Laptop | 1000 | Phone |
| Electronics | Phone | 800 | Phone |
| Electronics | Tablet | 600 | Phone |
Top 3 Values
SQL
1 SELECT DISTINCT 2 category, 3 NTH_VALUE(product_name, 1) OVER w AS top_1, 4 NTH_VALUE(product_name, 2) OVER w AS top_2, 5 NTH_VALUE(product_name, 3) OVER w AS top_3 6 FROM products 7 WINDOW w AS ( 8 PARTITION BY category 9 ORDER BY sales DESC 10 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 11 );
Mengambil top 3 produk per kategori dalam satu row.
Nilai Median (Posisi Tengah)
SQL
1 SELECT 2 NTH_VALUE(score, (COUNT(*) OVER () + 1) / 2) OVER ( 3 ORDER BY score 4 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 5 ) AS median_ish 6 FROM test_scores;
Pendekatan alternatif untuk mencari median.
