Windowing to the max
The Idaho field staff has been reporting back sample data for a variety of measures in their water system monitoring system. They'd like you to compare the Alkalinity samples to the maximum for that year to help them analyze trends towards the maximum. The data is kinda messy and you'll have to explicitly convert any dates.
Bu egzersiz
Introduction to Redshift
kursunun bir parçasıdırEgzersiz talimatları
- Explicitly convert the
sample_dateto a date aliased asdate_sampledand order by it. - Use a window function to calculate the max Alkalinity reading,
characteristic_measurementvalue, for the year.
Uygulamalı interaktif egzersiz
Bu örnek kodu tamamlayarak bu egzersizi bitirin.
SELECT fk_monitoringlocation,
-- Explicitly convert sample_date to a DATE
___(sample_date AS ___) as date_sampled,
characteristic_measurementvalue,
-- Find the max measurement
___(___) ___ (
-- For each year window based on the sample date
___ BY DATE_PART(___, ___(___ AS DATE))
) as yearly_max
FROM public_intro_redshift.idaho_samples
WHERE characteristic_name = 'Alkalinity'
ORDER BY date_sampled DESC,
fk_monitoringlocation;