Ini borangnya:
| id | Wilayah | Pelbagai | Harga |
|---|---|---|---|
| 1 | Alexander Valley | Cabernet Sauvignon | 35 |
| 2 | Alexander Valley | Cabernet Sauvignon | 45 |
| 3 | Alexander Valley | Merlot | 19 |
| 4 | California | Sauvignon Blanc | 8 |
| 5 | California | Pinot Noir | 17 |
Saya ingin mengetahui jenis yang paling murah dan paling mahal di setiap rantau, jadi outputnya hendaklah:
| Wilayah | Mahal | Murah |
|---|---|---|
| Alexander Valley | Cabernet Sauvignon | Merlot |
| California | Pinot Noir | Sauvignon Blanc |
Saya dapat hasil yang betul menggunakan kedua-duanya first_value()
SELECT DISTINCT region, FIRST_VALUE(variety) OVER (PARTITION BY region ORDER BY price DESC) AS expensive, FIRST_VALUE(variety) OVER (PARTITION BY region ORDER BY price) AS cheapest FROM wine_list
Saya rasa ia setara dengan pertanyaan berikut
SELECT DISTINCT region, FIRST_VALUE(variety) OVER (PARTITION BY region ORDER BY price DESC) AS expensive, LAST_VALUE(variety) OVER (PARTITION BY region ORDER BY price DESC) AS cheapest FROM wine_list
Tetapi sekarang output saya ialah:
| Wilayah | Mahal | Murah |
|---|---|---|
| Alexander Valley | Cabernet Sauvignon | Cabernet Sauvignon |
| Alexander Valley | Cabernet Sauvignon | Merlot |
| California | Pinot Noir | Pinot Noir |
| California | Pinot Noir | Sauvignon Blanc |
Mengapa keluaran saya salah? Saya keliru.
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
FIRST_VALUE和LAST_VALUE的默认窗口是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. iaitu. Ini adalah respons pertama. Nilai terakhir ialah "setakat ini".Walau bagaimanapun, anda mahu ia digunakan pada keseluruhan set data, jadi anda perlu menerangkan julat tetingkap dengan jelas:
SELECT DISTINCT region, FIRST_VALUE(variety) OVER (PARTITION BY region ORDER BY price DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS expensive, LAST_VALUE(variety) OVER (PARTITION BY region ORDER BY price DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cheapest FROM wine_list;