有一表t
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`f_id` varchar(45) NOT NULL,
`f_time` datetime NOT NULL,
`f_value` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbkinsert into t (f_id,f_time,f_value) values
('A','2011-03-10 00:00:00',1),
('A','2011-03-10 11:00:00',0),
('A','2011-03-10 15:00:00',1),
('A','2011-03-10 16:00:00',0),
('A','2011-03-10 17:00:00',1),
('A','2011-03-10 23:59:59',1),
('B','2011-03-10 00:00:00',0),
('B','2011-03-10 8:00:00',1),
('B','2011-03-10 23:59:59',1),
('C','2011-03-10 00:00:00',1),
('C','2011-03-10 10:00:00',0),
('C','2011-03-10 23:59:59',0),
('D','2011-03-10 00:00:00',0),
('D','2011-03-10 23:59:59',0),
('E','2011-03-10 00:00:00',1),
('E','2011-03-10 23:59:59',1)如何统计按f_id分组下,每天“1”状态(f_value字段的值)的累计时间,单位为分钟.
“1”状态时间是指f_value字段的值由“1”改变为“0”中间所经历的时间,
例如:
('A','2011-03-10 00:00:00',1),
('A','2011-03-10 11:00:00',0) 中间经历了11个小时,则1状态时间为11*60=660
或“1”改变为“1”时,中间所经历的时间(一种特殊情况),
例如:
('A','2011-03-10 17:00:00',1),
('A','2011-03-10 23:59:59',1) 中间经历了7个小时,则1状态时间为7*60=420
统计后结果如下:
f_id f_time f_value
A 2011-03-10 1140
B 2011-03-10 960
C 2011-03-10 840
D 2011-03-10 0
E 2011-03-10 1440
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`f_id` varchar(45) NOT NULL,
`f_time` datetime NOT NULL,
`f_value` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbkinsert into t (f_id,f_time,f_value) values
('A','2011-03-10 00:00:00',1),
('A','2011-03-10 11:00:00',0),
('A','2011-03-10 15:00:00',1),
('A','2011-03-10 16:00:00',0),
('A','2011-03-10 17:00:00',1),
('A','2011-03-10 23:59:59',1),
('B','2011-03-10 00:00:00',0),
('B','2011-03-10 8:00:00',1),
('B','2011-03-10 23:59:59',1),
('C','2011-03-10 00:00:00',1),
('C','2011-03-10 10:00:00',0),
('C','2011-03-10 23:59:59',0),
('D','2011-03-10 00:00:00',0),
('D','2011-03-10 23:59:59',0),
('E','2011-03-10 00:00:00',1),
('E','2011-03-10 23:59:59',1)如何统计按f_id分组下,每天“1”状态(f_value字段的值)的累计时间,单位为分钟.
“1”状态时间是指f_value字段的值由“1”改变为“0”中间所经历的时间,
例如:
('A','2011-03-10 00:00:00',1),
('A','2011-03-10 11:00:00',0) 中间经历了11个小时,则1状态时间为11*60=660
或“1”改变为“1”时,中间所经历的时间(一种特殊情况),
例如:
('A','2011-03-10 17:00:00',1),
('A','2011-03-10 23:59:59',1) 中间经历了7个小时,则1状态时间为7*60=420
统计后结果如下:
f_id f_time f_value
A 2011-03-10 1140
B 2011-03-10 960
C 2011-03-10 840
D 2011-03-10 0
E 2011-03-10 1440
这个没有7个小时,比7个小时少1秒。
select f_id,date(f_time),sum(UNIX_TIMESTAMP(k)-UNIX_TIMESTAMP(f_time))/60 as m
from (
select f_id,f_time,
(select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
from t a
where f_value=1
) b
where k is not null
group by f_id,date(f_time)
select @t1,@t2,TIME_TO_SEC(TIMEDIFF(@t1,@t2));
select f_id,date(t1.f_time),(UNIX_TIMESTAMP(t1.f_time)-UNIX_TIMESTAMP(t2.f_time))/60
from t t1,t t2
where t1.id=t2.id-1
and t1.f_value=1 and t2.f_value=0
-> from (
-> select f_id,f_time,
-> (select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
-> from t a
-> where f_value=1
-> ) b
-> where k is not null
-> group by f_id,date(f_time);
+------+--------------+-----------+
| f_id | date(f_time) | m |
+------+--------------+-----------+
| A | 2011-03-10 | 1139.9833 |
| B | 2011-03-10 | 959.9833 |
| C | 2011-03-10 | 600.0000 |
| E | 2011-03-10 | 1439.9833 |
+------+--------------+-----------+
4 rows in set (0.08 sec)mysql>
-> from (
-> select f_id,f_time,
-> (select Min(f_time) From t Where f_id=a.f_id And f_time>a.f_time And DATE(f_time)=date(t.f_time)) as k
-> from t a
-> where f_value=1
-> ) b
-> where k is not null
-> group by f_id,date(f_time);
+------+--------------+-----------+
| f_id | date(f_time) | m |
+------+--------------+-----------+
| A | 2011-03-10 | 1140.0000 |
| B | 2011-03-10 | 960.0000 |
| C | 2011-03-10 | 600.0000 |
| E | 2011-03-10 | 1440.0000 |
+------+--------------+-----------+
4 rows in set (0.06 sec)mysql>
(SELECT A.f_id,DATE(A.f_time),SUM(UNIX_TIMESTAMP(FF)-UNIX_TIMESTAMP(f_time))/60 AS FF FROM (
SELECT A.f_id,A.f_time,A.f_value,A.id,MIN(B.f_time) AS FF
FROM T A LEFT JOIN T B ON A.f_id=B.f_id AND B.f_time>A.f_time
WHERE A.f_value=1 AND B.f_id IS NOT NULL
GROUP BY A.f_id,A.f_time,A.f_value,A.id ) A
GROUP BY A.f_id,DATE(A.f_time)) A1
ON A.f_id=A1.F_ID