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