You can solve this problem following these steps:
- convert the format of your "date" field from
VARCHAR to DATE
- generate your partitions on your dates for each 7 days
- aggregate for each partition, by getting the maximum date and the average temperature
In order to generate your partition, you use:
- the
ROW_NUMBER window function, if your date values are always consecutives (every day a new temperature will appear)
- the
UNIX_TIMESTAMP function, to normalize the date to actual weeks, if your date values are not always consecutives (you forget to record temperature values some days)
First Solution (fiddle):
WITH cte AS (
SELECT STR_TO_DATE(date_, '%m-%d-%Y') AS date_as_date,
temperature
FROM tab
), partitioned_dates AS (
SELECT *, (ROW_NUMBER() OVER(ORDER BY date_as_date DESC) -1) DIV 7 AS rn
FROM cte
)
SELECT MAX(date_as_date) AS week_ending_in_date,
AVG(temperature) AS avg_temperature
FROM partitioned_dates
GROUP BY rn
Second Solution (fiddle):
WITH cte AS (
SELECT STR_TO_DATE(date_, '%m-%d-%Y') AS date_as_date,
UNIX_TIMESTAMP(STR_TO_DATE(date_, '%m-%d-%Y')) AS integer_date,
temperature
FROM tab
), partitioned_dates AS (
SELECT *, (MAX(integer_date) OVER(ORDER BY integer_date DESC) - integer_date) DIV (60*60*24*7) AS rn
FROM cte
)
SELECT MAX(date_as_date) AS week_ending_in_date,
AVG(temperature) AS avg_temperature
FROM partitioned_dates
GROUP BY rn
Extra tips:
- Use MySQL standard for dates: you're storing in the
%mm-%dd-%yyyy format, MySQL standard for dates is %dd-%mm-%yyyy.
- Don't store dates as
VARCHAR. MySQL has the DATE datetype, that allows you to use a wide toolkit of functions at your disposal.
- Don't use names that match reserved MySQL keywords to name your tables and fields. Specifically, the "date" field name can conflict with the
DATE reserved word for its homonimous datatype, you may run into syntax errors.
Following MySQL rules will considerably help you with manipulation and save yourself a lot of time.
