Reputation: 387
I have a SQL table in which I save a record that contains a DATE
field, in the yyyy-mm-dd
format. I'm trying to compute an average over partitions of the table defined by a months range, i.e. I want to group the rows by a window of dates between August and July of the next year, and then compute the average of a field in each partition.
How can I do this automatically, instead of using a
WHERE day BETWEEN ('1995-08-01' AND '1996-07-31' OR '1996-08-01' AND '1997-07-31')
etc..,
since the data can grow indefinitely?
Sample data from the table
div date hometeam awayteam fthg ftag
E0 2017-04-17 00:00:00.000 Middlesbrough Arsenal 1 2
E0 2017-04-16 00:00:00.000 Man United Chelsea 2 0
E0 2017-04-16 00:00:00.000 West Brom Liverpool 0 1
E0 2017-04-15 00:00:00.000 Crystal Palace Leicester 2 2
E0 2017-04-15 00:00:00.000 Everton Burnley 3 1
I want to group the matches by soccer season instead of the year.
Upvotes: 0
Views: 65
Reputation: 2365
You can shift the date to the new year, and then extract year from that date.
SELECT EXTRACT(year from DATE_ADD(CAST(dt AS date), interval 5 month)) as season
To understand it better, I prepared a step by step query
SELECT dt,
DATE_ADD(CAST(dt AS date), interval 5 month) as shifted_dt,
EXTRACT(year from DATE_ADD(CAST(dt AS date), interval 5 month)) as season
FROM UNNEST(['1995-08-01', '1996-07-31', '2017-04-16', '1996-08-01', '1997-07-31']) as dt
Upvotes: 1