linux空间做网站,视频网站咋么做,南通网站排名外包,中冶东北建设网站这是一个复杂的过程.但是我有一个表,其中包含一个DATETIME字段,以及一些其他的int和float字段,需要对其求和并求平均值.我们希望基于时间戳在此表上进行求和和平均,最终旨在开发3个在某种意义上可以相互补充的查询.所以能力看起来像这样TIMESTAMP |subj_diff| SCR2 | SCR32011-…这是一个复杂的过程.但是我有一个表,其中包含一个DATETIME字段,以及一些其他的int和float字段,需要对其求和并求平均值.我们希望基于时间戳在此表上进行求和和平均,最终旨在开发3个在某种意义上可以相互补充的查询.所以能力看起来像这样TIMESTAMP |subj_diff| SCR2 | SCR32011-09-20 09:01:37 | 1 | 0.02 | 1.62011-09-20 09:04:18 | 3 | 0.09 | 1.82011-09-20 14:24:55 | 5 | 0.21 | 1.22011-09-21 18:50:47 | 8 | 0.08 | 0.92011-09-21 18:54:21 | 9 | 0.12 | 2.1我们要生成的三个查询是1.对以前的数据(包括当前选择的记录)中的所有前面的项进行汇总.还应该有一个总计列的另一列.例如,如果我们希望返回20号和21号之间的结果,则返回的表应如下所示TIMESTAMP |subj_diff| SCR2 | SCR3 | COUNT2011-09-20 09:01:37 | 1 | 0.02 | ... | 12011-09-20 09:04:18 | 4 | 0.11 | | 22011-09-20 14:24:55 | 9 | 0.32 | | 32011-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 | COUNT2011-09-20 09:05:00 | 4 | 0.11 | 3.4 | 22011-09-20 14:25:00 | 5 | 0.21 | 1.2 | 12011-09-21 18:55:00 | 17 | 0.20 | 3.0 | 23.在一天中的每5分钟间隔(即从00:05:00到24:00:00),对第二个查询的结果集在第1个查询中执行相同的操作.这是一个相当棘手的问题,我不知道如何开始.谁能编写SQL来解决这个问题这是一些使用游标和存储的proc的基本代码,但实际上并没有用.DROP PROCEDURE curdemo;DELIMITER $$CREATE PROCEDURE curdemo()BEGINDECLARE done INT DEFAULT 0;DECLARE a datetime;DECLARE b,c FLOAT;DECLARE cur1 CURSOR FORSELECT msgDate, subj_diff FROM classifier_resultsWHERE 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: LOOPFETCH cur1 INTO a, b;IF done THENLEAVE read_loop;END IF;INSERT temp_scores(d,acc)SELECT a, SUM(subj_diff) FROM classifier_resultsWHERE 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 querySET 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:cnt1 countFROM 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 querySELECTDATE(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(*) countFROM classifier_resultsGROUP 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 querySET 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:cnt1 countFROM (SELECTDATE(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(*) countFROM classifier_resultsGROUP 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
