Ygthkb

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.

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.

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.

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>