100分:求统计每天不同状态下的累计运行时间

[kyle315]

有一表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=gbk

insert 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

[WWWWA]

SELECT DISTINCT A.F_ID,IFNULL(FF,0) FROM T A LEFT JOIN
(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

[kyle315]

狼头兄，第一个就满足俺的要求，俺没加注释说明，不好意思，多谢了。

[ACMAIN_CHM]

你的计算结果有问题
[Quote]例如：
('A','2011-03-10 17:00:00',1),
('A','2011-03-10 23:59:59',1) 中间经历了7个小时，则1状态时间为7*60=420[/Quote]
这个没有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)

[gungod]

直接求 最大和最小值的间隔怎么样？

select (select f_time from t where f_id='A' order by f_time desc limit 1 ) , (select f_time from t where f_id='A' order by f_time limit 1) into @t1,@t2;
select @t1,@t2,TIME_TO_SEC(TIMEDIFF(@t1,@t2));

[rucypli]

给表t加一列自增id列
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

[ACMAIN_CHM]

mysql> 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);
+------+--------------+-----------+
| 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>

[ACMAIN_CHM]

由于楼主的结果与他自己的说明并不一致，（可怜的中国语文教育机制）

如果以结果为准的话，则。

mysql> select f_id,date(f_time),sum(if(time(k)='23:59:59',1,0)+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);
+------+--------------+-----------+
| 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>