mysql - Iterative SQL query for datebased analyses

Question

I have a database (working on mysql 8.0) which is recording data for more than a year now. The entries into this database are just random. Now, I need to make a usage-profile to see in which timeframes how many data where applied into the db.

At the End, I would like to generate a chart showing the timeframes (i.e. 10:00, 10:05, 10:10 and so on) on the x-Axis and the sum of the values val which are in one given timeframe on the y-Axis.

The chart will be done with Excel, so I would like to generate one table with two rows, sum_val and timeframe containing the data.

+---------+-----------+
| sum_val | timeframe |
+---------+-----------+
|      43 | 10:00:00  |
|      22 | 10:05:00  |
|      09 | 10:10:00  |
|      25 | 10:15:00  |
|      05 | 10:20:00  |
+---------+-----------+

First of all, I made a very small test table:

CREATE TABLE test(id INT, val INT, time DATETIME);

INSERT INTO test 
VALUES (1, 5, '2021-04-02 10:00:00'),
       (2, 10, '2021-04-02 10:00:12'),
       (3, 5, '2021-04-02 10:01:13'),
       (4, 10, '2021-04-02 10:01:13'),
       (5, 8, '2021-04-02 10:02:12'),
       (6, 5, '2021-04-02 10:04:55'),
       (7, 6, '2021-04-02 10:05:00'),
       (8, 8, '2021-04-02 10:06:17'),
       (9, 3, '2021-04-02 10:06:18'),
       (10, 1, '2021-04-02 10:08:45'),
       (11, 4, '2021-04-02 10:09:23'),
       (12, 9, '2021-04-02 10:10:49'),
       (13, 0, '2021-04-02 10:12:34'),
       (14, 9, '2021-04-02 10:15:00'),
       (15, 9, '2021-04-02 10:15:01'),
       (16, 0, '2021-04-02 10:13:59'),
       (17, 2, '2021-04-02 10:16:35'),
       (18, 4, '2021-04-02 10:18:18'),
       (19, 1, '2021-04-02 10:19:03'),
       (20, 5, '2021-04-02 10:23:56');

Then, I had the idea to define the time-window which has to be analyzed. This time-window shall be iterated in a while loop until the time-window has been reached. Then, the time-window shall be extended for the next interval and the next table shall be generated for the output. At the end, I should receive one table per interval containing the sum of all val in the given timeframe and the starttime of the interval, so I can extract these data into Excel to draw a diagram.

But I'm thinking to tight to C programming than to SQL, so I'm struggling finding the right approach to my solution. I would be really thankful If someone can give me a hint how to structure my SQL to solve the problem. (The while loop is not implemented yet).

SET @interval = 5, 
    @starttime = ('2021-04-02 10:00:00'), 
    @actualtime = ('2021-04-02 10:00:00');

SELECT 
    time,
    @interval,
    @starttime,
    @actualtime,
    CASE 
        WHEN TIMESTAMPDIFF(MINUTE, @actualtime, @starttime) < @interval 
           THEN '1' AND @actualtime = SELECT time from test LIMIT 2 DESC,         
           ELSE '-1' 
    END timeinterval
FROM test;

I'm not sure If I'm allowed to run some select or calculate some variables within a CASE-WHEN statement? Do I have to place the statement

@actualtime = SELECT time FROM test LIMIT 2 DESC,

into a stored procedure? Could I call this procedure then inside the CASE-WHEN THEN Construct? Do I have to write another stored procedure to write the temporary table, too?

Kind regards, dosu

question from:https://stackoverflow.com/questions/66065723/iterative-sql-query-for-datebased-analyses

Answer 1

Waitting for answers