统计所有时间段，并按某字段分组，进行相关计算

Question

之前发过相关的帖子问过。但因为当时理解错误了领导的意思，把需求弄错了。汗表结构CREATE DATABASE north_db;
USE north_db;
 CREATE TABLE `rncfunction_15` (
  `start_time` datetime NOT NULL,
  `stop_time` datetime NOT NULL,
  `moi` varchar(256) NOT NULL ,
  `RAB.SuccRelCS.Conv` float ,
  `IUCSOCT.UPInCs.Conv` float ,
  `RLC.CSDlOct.sum` float ,
  `IRATHO.FailOutCS.sum` float ,
  PRIMARY KEY (`start_time`,`stop_time`,`moi`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO rncfunction_15(`start_time`,`stop_time`,`moi`,
`RAB.SuccRelCS.Conv`,`IUCSOCT.UPInCs.Conv`,
`RLC.CSDlOct.sum`,`IRATHO.FailOutCS.sum`)
VALUES('2010-11-25 09:30','2010-11-25 09:45','moi1',1,1,1,1),
('2010-11-25 10:15','2010-11-25 10:30','moi2',1,1,1,1),
('2010-12-15 16:00','2010-12-15 16:15','moi3',1,1,1,1),
('2010-12-15 16:30','2010-12-15 16:45','moi3',1,1,1,1);CREATE TABLE rncfunction_30 select * from rncfunction_15 WHERE 0;
CREATE TABLE rncfunction_60 select * from rncfunction_15 WHERE 0;其中start_time和stop_time格式是"2010-12-15 16:45" 这样子。
start_time分钟可能是0,15,30,45，stop_time和start_time间隔是15分钟。
每条数据之间，时间可以不是连续的。要求按30分钟和60分钟统计，并根据moi分组。
当几条数据在一个时间段内时，如果moi相等，将其它float字段进行sum操作；
如果moi不相等，将每条数据都统计进去(此时，因是相同的时间段，故这几条数据start_time、stop_time是相等的)。将统计出来的30分钟和60分钟的数据分别insert进rncfunction_30和rncfunction_60表。比如，有数据如下：
start_time                          stop_time                    moi          RAB.SuccRelCS.Conv
2010-11-25 09:30               2010-11-25 09:45                  moi1           1
2010-11-25 10:15               2010-11-25 10:30                  moi2           1
2010-12-15 16:00               2010-12-15 16:15                  moi3           1
2010-12-15 16:30               2010-12-15 16:45                  moi3           1统计30分钟的，结果如下：
start_time                          stop_time                    moi           RAB.SuccRelCS.Conv
2010-11-25 09:30               2010-11-25 10:00                  moi1            1
2010-11-25 10:00               2010-11-25 10:30                  moi2            1
2010-12-15 16:00               2010-12-15 16:30                  moi3            1
2010-12-15 16:30               2010-12-15 17:00                  moi3            1统计60分钟的，结果如下：
start_time                          stop_time                    moi          RAB.SuccRelCS.Conv     
2010-11-25 09:00               2010-11-25 10:00                  moi1             1
2010-11-25 10:00               2010-11-25 11:00                  moi2             1
2010-12-15 16:00               2010-12-15 17:00                  moi3             2注意看60分钟时，2010-12-15 16:00 到 2010-12-15 17:00时，有俩条记录moi都等于moi3,
于是后面的float字段进行了sum操作目前我用的方法很笨拙，在存储过程中，使用游标循环表的每条记录，计算该记录落在哪个时间段。
然后在游标中，再定义一个游标，按moi分组，当start_time和stop_time在那个时间段时，
取出moi值和对其它float字段值作sum计算的值。
然后在insert进rncfunction_30和rncfunction_60表数据的时候，进行ON DUPLICATE KEY UPDATE操作。
因为当时间段相同，而且moi值相同时，在循环rncfunction_15游标的过程中，往30和60插入数据，会发生主键重复的情况。
想了想，我这个办法实在是很笨重。不知道用SQL可否实现我的这个需求呢？
或者简单点的存储过程。
最近整数据库较多，颈椎疼。%>_<%
大家给给意见和建议吧。3Q。

Answer 1

上次好像回答过一次了。mysql> select * from rncfunction_15;
+---------------------+---------------------+------+--------------------+---------------------+-----------------+----------------------+
| start_time          | stop_time           | moi  | RAB.SuccRelCS.Conv | IUCSOCT.UPInCs.Conv | RLC.CSDlOct.sum | IRATHO.FailOutCS.sum |
+---------------------+---------------------+------+--------------------+---------------------+-----------------+----------------------+
| 2010-11-25 09:30:00 | 2010-11-25 09:45:00 | moi1 |                  1 |                   1 |               1 |                    1 |
| 2010-11-25 10:15:00 | 2010-11-25 10:30:00 | moi2 |                  1 |                   1 |               1 |                    1 |
| 2010-12-15 16:00:00 | 2010-12-15 16:15:00 | moi3 |                  1 |                   1 |               1 |                    1 |
| 2010-12-15 16:30:00 | 2010-12-15 16:45:00 | moi3 |                  1 |                   1 |               1 |                    1 |
+---------------------+---------------------+------+--------------------+---------------------+-----------------+----------------------+
4 rows in set (0.03 sec)mysql> select start_time-interval minute(start_time)%30 minute as start_time,
    ->  start_time-interval minute(start_time)%30-30 minute as start_time,
    ->  moi,sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`
    -> from rncfunction_15
    -> group by start_time-interval minute(start_time)%30 minute;
+---------------------+---------------------+------+--------------------+
| start_time          | start_time          | moi  | RAB.SuccRelCS.Conv |
+---------------------+---------------------+------+--------------------+
| 2010-11-25 09:30:00 | 2010-11-25 10:00:00 | moi1 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 10:30:00 | moi2 |                  1 |
| 2010-12-15 16:00:00 | 2010-12-15 16:30:00 | moi3 |                  1 |
| 2010-12-15 16:30:00 | 2010-12-15 17:00:00 | moi3 |                  1 |
+---------------------+---------------------+------+--------------------+
4 rows in set (0.01 sec)mysql>
mysql> select start_time-interval minute(start_time)%60 minute as start_time,
    ->  start_time-interval minute(start_time)%60-60 minute as start_time,
    ->  moi,sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`
    -> from rncfunction_15
    -> group by start_time-interval minute(start_time)%60 minute;
+---------------------+---------------------+------+--------------------+
| start_time          | start_time          | moi  | RAB.SuccRelCS.Conv |
+---------------------+---------------------+------+--------------------+
| 2010-11-25 09:00:00 | 2010-11-25 10:00:00 | moi1 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 11:00:00 | moi2 |                  1 |
| 2010-12-15 16:00:00 | 2010-12-15 17:00:00 | moi3 |                  2 |
+---------------------+---------------------+------+--------------------+
3 rows in set (0.00 sec)mysql>

Answer 2

60:
SELECT newtime,stoptime,SUM(`RAB.SuccRelCS.Conv`) FROM (
 SELECT *,
CAST( CONCAT( DATE_FORMAT(start_time,'%Y-%m-%d'),' ',HOUR(start_time),':',
IF( DATE_FORMAT(start_time,'%i')=0,DATE_FORMAT(start_time,'%i'),0),':') AS DATETIME) AS newtime,
DATE_ADD(
CAST( CONCAT( DATE_FORMAT(start_time,'%Y-%m-%d'),' ',HOUR(start_time),':',
IF( DATE_FORMAT(start_time,'%i')=0,DATE_FORMAT(start_time,'%i'),0),':') AS DATETIME),INTERVAL 60 MINUTE) AS stoptime
 FROM rncfunction_15) a
 GROUP BY newtime,stoptime;30:SELECT newtime,stoptime,SUM(`RAB.SuccRelCS.Conv`) FROM (
 SELECT *,CAST( CONCAT( DATE_FORMAT(start_time,'%Y-%m-%d'),' ',HOUR(start_time),':',
IF( DATE_FORMAT(start_time,'%i') IN(0,30),DATE_FORMAT(start_time,'%i'),0),':') AS DATETIME) AS newtime,
DATE_ADD(
CAST( CONCAT( DATE_FORMAT(start_time,'%Y-%m-%d'),' ',HOUR(start_time),':',
IF( DATE_FORMAT(start_time,'%i') IN(0,30),DATE_FORMAT(start_time,'%i'),0),':') AS DATETIME),INTERVAL 30 MINUTE) AS stoptime
 FROM rncfunction_15) a
  GROUP BY newtime,stoptime;

Answer 3

狼头哥，当时间段相同，moi不相同时，统计的结果不正确。我加了按moi分组30.sql
select start_time-interval minute(start_time)%30 minute as start_time,
start_time-interval minute(start_time)%30-30 minute as stop_time,
moi,sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`
from rncfunction_15
group by start_time-interval minute(start_time)%30 minute,moi;60.sql
select start_time-interval minute(start_time)%60 minute as start_time,
start_time-interval minute(start_time)%60-60 minute as stop_time,
moi,sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`
from rncfunction_15
group by start_time-interval minute(start_time)%60 minute,moi;mysql> select start_time,stop_time,moi,`RAB.SuccRelCS.Conv` from rncfunction_15
order by start_time;
+---------------------+---------------------+------+--------------------+
| start_time          | stop_time           | moi  | RAB.SuccRelCS.Conv |
+---------------------+---------------------+------+--------------------+
| 2010-11-25 09:30:00 | 2010-11-25 09:45:00 | moi1 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 10:15:00 | moi2 |                  1 |
| 2010-11-25 10:15:00 | 2010-11-25 10:30:00 | moi3 |                  1 |
| 2010-11-25 10:45:00 | 2010-11-25 11:00:00 | moi2 |                  1 |
| 2010-12-15 16:00:00 | 2010-12-15 16:15:00 | moi3 |                  1 |
| 2010-12-15 16:30:00 | 2010-12-15 16:45:00 | moi3 |                  1 |
+---------------------+---------------------+------+--------------------+mysql> source 30.sql
+---------------------+---------------------+------+--------------------+
| start_time          | stop_time           | moi  | RAB.SuccRelCS.Conv |
+---------------------+---------------------+------+--------------------+
| 2010-11-25 09:30:00 | 2010-11-25 10:00:00 | moi1 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 10:30:00 | moi2 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 10:30:00 | moi3 |                  1 |
| 2010-11-25 10:30:00 | 2010-11-25 11:00:00 | moi2 |                  1 |
| 2010-12-15 16:00:00 | 2010-12-15 16:30:00 | moi3 |                  1 |
| 2010-12-15 16:30:00 | 2010-12-15 17:00:00 | moi3 |                  1 |
+---------------------+---------------------+------+--------------------+mysql> source 60.sql
+---------------------+---------------------+------+--------------------+
| start_time          | stop_time           | moi  | RAB.SuccRelCS.Conv |
+---------------------+---------------------+------+--------------------+
| 2010-11-25 09:00:00 | 2010-11-25 10:00:00 | moi1 |                  1 |
| 2010-11-25 10:00:00 | 2010-11-25 11:00:00 | moi2 |                  2 |
| 2010-11-25 10:00:00 | 2010-11-25 11:00:00 | moi3 |                  1 |
| 2010-12-15 16:00:00 | 2010-12-15 17:00:00 | moi3 |                  2 |
+---------------------+---------------------+------+--------------------+

Answer 4

当时间段相同，moi不相同时，统计的结果不正确。我加了按moi分组还要按moi分组，详细说明

Answer 5

截取问题的一部分：要求按30分钟和60分钟统计，并根据moi分组。当几条数据在一个时间段内时，如果moi相等，将其它float字段进行sum操作；
如果moi不相等，将每条数据都统计进去(此时，因是相同的时间段，故这几条数据start_time、stop_time是相等的)。将统计出来的30分钟和60分钟的数据分别insert进rncfunction_30和rncfunction_60表。

Answer 6

我最后给出的就是完整的结果。我当时给出的测试数据，没有给出在一个时间段，但moi不相等的数据

Answer 7

答案已知晓。
感谢狼头哥还有WWWA弟。
过几天再结贴

Answer 8

狼头哥，在插入数据的时候需要对主键重复时作处理。我下面的代码有问题。
INSERT INTO north_db.rncfunction_30(
`start_time`,
`stop_time`,
`moi`,
`RAB.SuccRelCS.Conv`,
`IUCSOCT.UPInCs.Conv`,
`RLC.CSDlOct.sum`,
`IRATHO.FailOutCS.sum`)
SELECT start_time-interval minute(start_time)%30 minute as start_time,
start_time-interval minute(start_time)%30-30 minute as stop_time,
moi,
sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`,
sum(`IUCSOCT.UPInCs.Conv`) as `IUCSOCT.UPInCs.Conv`,
sum(`RLC.CSDlOct.sum`) as `RLC.CSDlOct.sum`,
sum(`IRATHO.FailOutCS.sum`) as `IRATHO.FailOutCS.sum`
FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi
ON DUPLICATE KEY UPDATE
`RAB.SuccRelCS.Conv`=(SELECT sum(`RAB.SuccRelCS.Conv`) FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi),
`IUCSOCT.UPInCs.Conv`=(SELECT max(`IUCSOCT.UPInCs.Conv`) FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi),
`RLC.CSDlOct.sum`=(SELECT min(`RLC.CSDlOct.sum`) FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi),
`IRATHO.FailOutCS.sum`=(SELECT avg(`IRATHO.FailOutCS.sum`) FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi);

Answer 9

java.sql.SQLException: Subquery returns more than 1 rowON DUPLICATE KEY UPDATE 后面的有问题我写的方法会被别人调用，重复调用执行insert操作，因此必须进行ON DUPLICATE KEY UPDATE操作

Answer 10

上面代码中，在ON DUPLICATE KEY UPDATE后面子查询中，聚合函数都是sum.这里拷贝的是程序生成的代码。只改了前面的后面的忘改了:)我试着用嵌套的子查询试试

Answer 11

主键重复,有两种解决办法1.使用REPLACE INTO。它是先删除重复的，再重新插入新的。
2.使用INSERT INTO ON DUPLICATE KEY UPDATE。它是更新值。此时使用values()函数。
INSERT INTO north_db.rncfunction_30(
`start_time`,
`stop_time`,
`moi`,
`RAB.SuccRelCS.Conv`,
`IUCSOCT.UPInCs.Conv`,
`RLC.CSDlOct.sum`,
`IRATHO.FailOutCS.sum`)
SELECT start_time-interval minute(start_time)%30 minute as start_time,
start_time-interval minute(start_time)%30-30 minute as stop_time,
moi,
sum(`RAB.SuccRelCS.Conv`) as `RAB.SuccRelCS.Conv`,
sum(`IUCSOCT.UPInCs.Conv`) as `IUCSOCT.UPInCs.Conv`,
sum(`RLC.CSDlOct.sum`) as `RLC.CSDlOct.sum`,
sum(`IRATHO.FailOutCS.sum`) as `IRATHO.FailOutCS.sum`
FROM north_db.rncfunction_15
GROUP BY start_time-interval minute(start_time)%30 minute,moi
ON DUPLICATE KEY UPDATE
`RAB.SuccRelCS.Conv`=values(`RAB.SuccRelCS.Conv`),
`IUCSOCT.UPInCs.Conv`=values(`IUCSOCT.UPInCs.Conv`),
`RLC.CSDlOct.sum`=values(`RLC.CSDlOct.sum`) ,
`IRATHO.FailOutCS.sum`=values(`IRATHO.FailOutCS.sum`) ;