Hasil yang salah apabila menggunakan last_value()

Question

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.

Answer 1

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;