Extracting date parts
There is some concern about whether an anemometer measuring wind speed was properly repaired at the Coffee County weather station. It was suggested that the number of windy readings in each month be compared to see if it was repaired by October 2023.
Diese Übung ist Teil des Kurses
Introduction to Redshift
Anleitung zur Übung
- Get the year from the
datecolumn, aliasing asyear. - Get the month from the
datecolumn, aliasing asmonth. - Only count them when the
hourlywindspeedis not'0'by returning the number 1'. - Group and order by the year and month, in that order.
Interaktive Übung
Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.
-- Get the year from date as year
SELECT ___(___, date) as year,
-- Get the month from date as month
___(___, ___) as ___,
-- Count readings when the hourlywindspeed isn't '0'
COUNT(
CASE WHEN ___ != '0' THEN ___
ELSE NULL
END
)
FROM public_intro_redshift.coffee_county_weather
-- Group and order by the dateparts you extracted
GROUP BY ___, ___
ORDER BY ___ DESC, ___ DESC;