Find the three highest values per partition
You work for a meteorology observatory and the station director has tasked you with identifying the three highest monthly temperatures for each weather station for the year of 2017. You know that in order to find this you will need to create a window function to number the rows.
Since you are looking for the highest monthly temperatures, you'll want to ensure that you are ordering by the t_monthly_max in descending order so that the highest temperatures are numbered first.
Two tables are available for you: temperatures_monthly and temperature_stations.
Bu egzersiz, kursun bir parçasıdır
Time Series Analysis in PostgreSQL
Egzersiz talimatları
- Find the three highest monthly temperatures for each
station_idand correspondingstate, for the year of 2017.
Uygulamalı etkileşimli egzersiz
Bu egzersizi bu örnek kodu tamamlayarak deneyin.
-- Three highest monthly temps per station for 2017
SELECT * FROM
( SELECT
ts.station_id,
ts.state,
tm.year_month,
tm.t_monthly_max,
___ AS rank
FROM temperatures_monthly AS tm
JOIN temperature_stations AS ts USING(station_id)
WHERE ___
) AS temp_2017
WHERE ___
ORDER BY station_id, ___;