I have a MySQL requirement to select data from a table based on a start date and end date and group it by weekly also selecting the data in reverse order by date. Assume that, I have chosen the start date as 1st November and the end date as 04 December. Now, I would like to fetch the data as 04 December to 28 November, 27 November to 20 November, 19 November to 12 November and so on and sum the value count for that week.

Given an example table,

Now the result should be like 2021-12-04 to 2021-11-28 as one week, following the same in reverse order and summing the column value data for that week. I have tried in the query to add the interval of 7 days after the end date but it didn't work.

SELECT count(value) AS total, MIN(R.created_at) 
FROM data_table AS D 
WHERE D.created_at BETWEEN '2021-11-01' AND '2021-12-04'  - INTERVAL 7 DAY  ORDER BY D.created_at;

And it's also possible to have the last week may have lesser than 7 days.

Expected output:

Note that the last week is only 5 days depending upon the selected from and end dates.

CodePudding user response：

One option to address this problem is to

• generate a calendar of all your intervals, beginning from last date till first date, with a split of your choice, using a recursive query
• joining back the calendar with the original table
• capping start_interval at your start_date value
• aggregating values for each interval

You can have three variables to be set, to customize your date intervals and position:

SET @start_date = DATE('2021-10-25');
SET @end_date = DATE('2021-12-04');
SET @interval_days = 7;

Then use the following query, as already described:

WITH RECURSIVE cte AS (
    SELECT @end_date AS end_interval,
           DATE_SUB(@end_date, INTERVAL @interval_days DAY) AS start_interval
    UNION ALL
    SELECT start_interval AS end_interval, 
           GREATEST(DATE(@start_date), DATE_SUB(start_interval, INTERVAL @interval_days DAY)) AS start_interval
    FROM cte
    WHERE start_interval > @start_date
)
SELECT end_interval, start_interval, SUM(_value) AS total
FROM      cte
LEFT JOIN tab
       ON tab.created_at BETWEEN start_interval AND end_interval
GROUP BY end_interval, start_interval

Check the demo here.