mysql 10分钟一聚合_SQL-根据DateTime查询多个聚合-MySQL

这是一个复杂的过程.但是我有一个表,其中包含一个DATETIME字段,以及一些其他的int和float字段,需要对其求和并求平均值.我们希望基于时间戳在此表上进行求和和平均,最终旨在开发3个在某种意义上可以相互补充的查询.

所以能力看起来像这样

TIMESTAMP |subj_diff| SCR2 | SCR3

2011-09-20 09:01:37 | 1 | 0.02 | 1.6

2011-09-20 09:04:18 | 3 | 0.09 | 1.8

2011-09-20 14:24:55 | 5 | 0.21 | 1.2

2011-09-21 18:50:47 | 8 | 0.08 | 0.9

2011-09-21 18:54:21 | 9 | 0.12 | 2.1

我们要生成的三个查询是：

1.对以前的数据(包括当前选择的记录)中的所有前面的项进行汇总.还应该有一个总计列的另一列.例如,如果我们希望返回20号和21号之间的结果,则返回的表应如下所示：

TIMESTAMP |subj_diff| SCR2 | SCR3 | COUNT

2011-09-20 09:01:37 | 1 | 0.02 | ... | 1

2011-09-20 09:04:18 | 4 | 0.11 | | 2

2011-09-20 14:24:55 | 9 | 0.32 | | 3

2011-09-21 18:50:47 | 17 | ...

2011-09-21 18:54:21 | 26 |

2.以5分钟的时间间隔汇总结果-与上述类似,但是查询将返回3行作为第1行和第2行. 2和第4& 5行将以与上述相同的方式相加.在此查询中,如果每隔5分钟没有返回0,则返回0,计数为0.

TIMESTAMP |subj_diff| SCR2 | SCR3 | COUNT

2011-09-20 09:05:00 | 4 | 0.11 | 3.4 | 2

2011-09-20 14:25:00 | 5 | 0.21 | 1.2 | 1

2011-09-21 18:55:00 | 17 | 0.20 | 3.0 | 2

3.在一天中的每5分钟间隔(即从00:05:00到24:00:00),对第二个查询的结果集在第1个查询中执行相同的操作.

这是一个相当棘手的问题,我不知道如何开始.谁能编写SQL来解决这个问题？

这是一些使用游标和存储的proc的基本代码,但实际上并没有用.

DROP PROCEDURE curdemo;

DELIMITER $$

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE a datetime;

DECLARE b,c FLOAT;

DECLARE cur1 CURSOR FOR

SELECT msgDate, subj_diff FROM classifier_results

WHERE DATE(msgDate) >= DATE('2011-09-25');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_scores (d datetime, acc float);

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO a, b;

IF done THEN

LEAVE read_loop;

END IF;

INSERT temp_scores(d,acc)

SELECT a, SUM(subj_diff) FROM classifier_results

WHERE DATE(msgDate) >= DATE('2011-09-25')

AND msgDate <= a;

END LOOP;

CLOSE cur1;

SELECT * FROM temp_scores;

END;

干杯!

解决方法:

试试这个代码-

创建并填充表：

CREATE TABLE classifier_results(

`TIMESTAMP` DATETIME NOT NULL,

subj_diff INT(11) DEFAULT NULL,

scr2 FLOAT(10, 5) DEFAULT NULL,

scr3 FLOAT(10, 5) DEFAULT NULL

);

INSERT INTO classifier_results VALUES

('2011-09-20 09:01:37', 1, 0.02000, 1.60000),

('2011-09-20 09:04:18', 3, 0.09000, 1.80000),

('2011-09-20 14:24:55', 5, 0.21000, 1.20000),

('2011-09-21 18:50:47', 8, 0.08000, 0.90000),

('2011-09-21 18:54:21', 9, 0.12000, 2.10000);

并执行以下查询：

-- 1 query

SET @subj_diff = 0;

SET @scr2 = 0;

SET @scr3 = 0;

SET @cnt = 0;

SELECT timestamp,

@subj_diff:=IF(@subj_diff IS NULL, subj_diff, @subj_diff + subj_diff) subj_diff,

@scr2:=IF(@scr2 IS NULL, scr2, @scr2 + scr2) scr2,

@scr3:=IF(@scr3 IS NULL, scr3, @scr3 + scr3) scr3,

@cnt:=@cnt+1 count

FROM classifier_results;

+---------------------+-----------+---------+---------+-------+

| timestamp | subj_diff | scr2 | scr3 | count |

+---------------------+-----------+---------+---------+-------+

| 2011-09-20 09:01:37 | 1 | 0.02000 | 1.60000 | 1 |

| 2011-09-20 09:04:18 | 4 | 0.11000 | 3.40000 | 2 |

| 2011-09-20 14:24:55 | 9 | 0.32000 | 4.60000 | 3 |

| 2011-09-21 18:50:47 | 17 | 0.40000 | 5.50000 | 4 |

| 2011-09-21 18:54:21 | 26 | 0.52000 | 7.60000 | 5 |

+---------------------+-----------+---------+---------+-------+

-- 2 query

SELECT

DATE(timestamp) + INTERVAL 5 * (12 * HOUR(timestamp) + FLOOR(MINUTE(timestamp) / 5)) MINUTE new_timestamp,

SUM(subj_diff) subj_diff,

SUM(scr2) scr2,

SUM(scr3) scr3,

COUNT(*) count

FROM classifier_results

GROUP BY new_timestamp;

+---------------------+-----------+---------+---------+-------+

| new_timestamp | subj_diff | scr2 | scr3 | count |

+---------------------+-----------+---------+---------+-------+

| 2011-09-20 09:00:00 | 4 | 0.11000 | 3.40000 | 2 |

| 2011-09-20 14:20:00 | 5 | 0.21000 | 1.20000 | 1 |

| 2011-09-21 18:50:00 | 17 | 0.20000 | 3.00000 | 2 |

+---------------------+-----------+---------+---------+-------+

-- 3 query

SET @subj_diff = 0;

SET @scr2 = 0;

SET @scr3 = 0;

SET @cnt = 0;

SELECT new_timestamp timestamp,

@subj_diff:=IF(@subj_diff IS NULL, subj_diff, @subj_diff + subj_diff) subj_diff,

@scr2:=IF(@scr2 IS NULL, scr2, @scr2 + scr2) scr2,

@scr3:=IF(@scr3 IS NULL, scr3, @scr3 + scr3) scr3,

@cnt:=@cnt+1 count

FROM (

SELECT

DATE(timestamp) + INTERVAL 5 * (12 * HOUR(timestamp) + FLOOR(MINUTE(timestamp) / 5)) MINUTE new_timestamp,

SUM(subj_diff) subj_diff,

SUM(scr2) scr2,

SUM(scr3) scr3,

COUNT(*) count

FROM classifier_results

GROUP BY new_timestamp

) t;

+---------------------+-----------+---------+---------+-------+

| timestamp | subj_diff | scr2 | scr3 | count |

+---------------------+-----------+---------+---------+-------+

| 2011-09-20 09:00:00 | 4 | 0.11000 | 3.40000 | 1 |

| 2011-09-20 14:20:00 | 9 | 0.32000 | 4.60000 | 2 |

| 2011-09-21 18:50:00 | 26 | 0.52000 | 7.60000 | 3 |

+---------------------+-----------+---------+---------+-------+

祝好运！

标签：aggregation,sql,mysql

来源： https://codeday.me/bug/20191207/2087353.html