Search code examples
mysqlsqldatetimesumintervals

SQL Aggregating by 6 month windows

Is there a better way of using SQL (mysql) to aggregate by 6 month windows? This seems to perform okay gets the relevant data in the normal case, but is obviously limited.

SELECT SUM(...) some_aggg, 
   (CASE
       WHEN SOME_DATE > DATE_SUB(now(), INTERVAL 6 MONTH) THEN 3
       WHEN SOME_DATE > DATE_SUB(now(), INTERVAL 12 MONTH) THEN 2
       WHEN SOME_DATE > DATE_SUB(now(), INTERVAL 18 MONTH) THEN 1
     ELSE 0
    END) RECENCY
WHERE ...
GROUP BY RECENCY
ORDER BY RECENCY DESC
Solution

  • How about a little arithmetics?

    select sum(...) some_aggg, 
   floor(timestampdiff(month, some_date, now()) / 6) recency
where ...
group by recency
order by recency

    recency gives you an integer value that starts at 0 (the date is less than 6 months old) and increments by 1 for every 6 month period: 1 means between 6 months and 1 year old, and so on.