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.
이 연습은 강의의 일부입니다
Time Series Analysis in PostgreSQL
연습 안내
- Find the three highest monthly temperatures for each
station_idand correspondingstate, for the year of 2017.
실습형 인터랙티브 연습
이 예제를 이 샘플 코드를 완성하여 풀어보세요.
-- 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, ___;