Group data by Custom Period Ranges Using a Reference Date

Question

If for example, I have a table that looks like this:

+----+--------+---------------------+
| id | volume |      createdAt      |
+----+--------+---------------------+
|  1 |   0.11 | 2018-01-26 13:56:01 |
|  2 |   0.34 | 2018-01-28 14:22:12 |
|  3 |   0.22 | 2018-03-11 11:01:12 |
|  4 |   0.19 | 2018-04-13 12:12:12 |
|  5 |   0.12 | 2014-04-21 19:12:11 |
+----+--------+---------------------+

I want to perform a query that can accept starting point and then loop through a given number of days, and then group by that date range.

For instance, I'd like the result to look like this:

+------------+------------+--------+
| enddate    | startdate  | volume |
+------------+------------+--------+
| 2018-04-25 | 2018-04-12 |   0.31 |
| 2018-04-11 | 2018-03-29 |   0.00 |
| 2018-03-28 | 2018-03-15 |   0.00 |
| 2018-03-14 | 2018-03-01 |   0.22 |
| 2018-02-28 | 2018-02-15 |   0.00 |
| 2018-02-14 | 2018-02-01 |   0.00 |
| 2018-01-31 | 2018-01-18 |   0.45 |
| ...        | ...        |    ... |
+------------+------------+--------+

In essence, I want to be able to input a start_date e.g 2018-04-25, a time_interval e.g. 14, like in the illustration above and then the query will sum the volumes in that time range.

I know how to use INTERVAL with the DATE_SUB() and the DATE_ADD() functions but I cannot figure out how to perform the loop I think is necessary.

Please help.

Answer 1

I hope I understand this right and you can use this for at least a start.

SELECT x.startdate + INTERVAL (x.days + 1) DAY enddate,
       x.startdate,
       (SELECT sum(t.volumes)
               FROM tab t
               WHERE t.createdAt >= x.startdate
                     AND t.createdAt < x.startdate + INTERVAL (x.days + 1) DAY) volume
       FROM (SELECT '2018-04-12' startdate,
                    12 days
             UNION
             SELECT '2018-03-29' startdate,
                    12 days
             UNION
             SELECT '2018-03-15' startdate,
                    12 days
             UNION
             SELECT '2018-03-01' startdate,
                    12 days
             UNION
             SELECT '2018-02-15' startdate,
                    12 days
             UNION
             SELECT '2018-02-01' startdate,
                    12 days
             UNION
             SELECT '2018-01-18' startdate,
                    12 days) x;

I haven't quite gotten on how you'd like to specify the dates and intervals though. Maybe you can clarify that, than I can probably edit this in.

Answer 2

There are 3 ways to get the list of date ranges.

• Use a 'sequence table' that is available in MariaDB (but not in MySQL).

• Create a table with a bunch of dates or integers from which to derive the desired dates.

• Write a Stored Procedure with a WHILE loop that iterates through the desired dates.

Answer 3

mysql> CREATE TABLE volumes (id INT, volume DECIMAL(5,2), createdAt DATETIME);
Query OK, 0 rows affected (0.22 sec)

mysql>
mysql> INSERT INTO volumes
    -> SELECT 1, 0.11, '2018-01-26 13:56:01' UNION ALL
    -> SELECT 2, 0.34, '2018-01-28 14:22:12' UNION ALL
    -> SELECT 3, 0.22, '2018-03-11 11:01:12' UNION ALL
    -> SELECT 4, 0.19, '2018-04-13 12:12:12' UNION ALL
    -> SELECT 5, 0.12, '2018-04-21 19:12:11' ;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE intervals (enddate DATE, startdate DATE);
Query OK, 0 rows affected (0.30 sec)

mysql>
mysql> INSERT INTO intervals
    -> SELECT '2018-04-25', '2018-04-12' UNION ALL
    -> SELECT '2018-04-11', '2018-03-29' UNION ALL
    -> SELECT '2018-03-28', '2018-03-15' UNION ALL
    -> SELECT '2018-03-14', '2018-03-01' UNION ALL
    -> SELECT '2018-02-28', '2018-02-15' UNION ALL
    -> SELECT '2018-02-14', '2018-02-01' UNION ALL
    -> SELECT '2018-01-31', '2018-01-18' ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT i.enddate, i.startdate, COALESCE(SUM(v.volume), 0) volume
    -> FROM intervals i
    -> LEFT JOIN volumes v
    -> ON v.createdAt BETWEEN i.startdate AND i.enddate
    -> GROUP BY i.enddate DESC, i.startdate;
+------------+------------+--------+
| enddate    | startdate  | volume |
+------------+------------+--------+
| 2018-04-25 | 2018-04-12 |   0.31 |
| 2018-04-11 | 2018-03-29 |   0.00 |
| 2018-03-28 | 2018-03-15 |   0.00 |
| 2018-03-14 | 2018-03-01 |   0.22 |
| 2018-02-28 | 2018-02-15 |   0.00 |
| 2018-02-14 | 2018-02-01 |   0.00 |
| 2018-01-31 | 2018-01-18 |   0.45 |
+------------+------------+--------+
7 rows in set (0.00 sec)

mysql>