计算不同分组下，累加时长。

[kyle315]

有一表t,结构如下：
id group1 date_time obj_value
1 g1 2011-2-1 0:00:00 1
2 g2 2011-2-1 0:00:00 0
3 g3 2011-2-1 0:00:00 0
4 g2 2011-2-1 10:00:00 1
5 g2 2011-2-1 12:00:00 0
6 g1 2011-2-1 18:00:00 0
7 g1 2011-2-1 23:59:59 0
8 g2 2011-2-1 23:59:59 0
9 g3 2011-2-1 23:59:59 0

想按group1分组，计算每天在obj_value字段不同状态下(0、1)的累加时间长度，精确的分钟。
统计后结果为：
group1 date_time time_len obj_value
g1 2011-2-1 1080 1
g1 2011-2-1 360 0
g2 2011-2-1 120 1
g2 2011-2-1 1320 0
g3 2011-2-1 0 1
g3 2011-2-1 1440 0

以上只是列出了1天的数据，其它天的和此类似，每天会有00:00:00和23:59:59时间点的obj_value字段状态，方便统计。

[kyle315]

多谢狼兄，太厉害了。还有一个计算时间差的，还麻烦狼兄看下：
http://topic.csdn.net/u/20110124/13/fee37554-06aa-42c0-9ca5-69e5e5f2d556.html

[ACMAIN_CHM]

mysql> select * from t;
+----+--------+---------------------+-----------+
| id | group1 | date_time           | obj_value |
+----+--------+---------------------+-----------+
|  1 | g1     | 2011-02-01 00:00:00 |         1 |
|  2 | g2     | 2011-02-01 00:00:00 |         0 |
|  3 | g3     | 2011-02-01 00:00:00 |         0 |
|  4 | g2     | 2011-02-01 10:00:00 |         1 |
|  5 | g2     | 2011-02-01 12:00:00 |         0 |
|  6 | g1     | 2011-02-01 18:00:00 |         0 |
|  7 | g1     | 2011-02-01 23:59:59 |         0 |
|  8 | g2     | 2011-02-01 23:59:59 |         0 |
|  9 | g3     | 2011-02-01 23:59:59 |         0 |
+----+--------+---------------------+-----------+
9 rows in set (0.02 sec)

mysql> Select group1,date(date_time) as date_time,obj_value,
    ->  CEILING(sum(UNIX_TIMESTAMP(next_date_time)-UNIX_TIMESTAMP(date_time))/60) as time_len
    -> from(
    -> select *,
    ->  (Select MIN(date_time) from t Where group1=a.group1 And date(date_time)=date(a.date_time) and date_time>a.date_time)  as next_date_time
    -> from t a
    -> ) u
    -> Group by group1,date(date_time),obj_value;
+--------+------------+-----------+----------+
| group1 | date_time  | obj_value | time_len |
+--------+------------+-----------+----------+
| g1     | 2011-02-01 |         0 |      360 |
| g1     | 2011-02-01 |         1 |     1080 |
| g2     | 2011-02-01 |         0 |     1320 |
| g2     | 2011-02-01 |         1 |      120 |
| g3     | 2011-02-01 |         0 |     1440 |
+--------+------------+-----------+----------+
5 rows in set (0.02 sec)

mysql>

[小小小小周]

[Quote=引用 1 楼 acmain_chm 的回复:]

SQL code
mysql> select * from t;
+----+--------+---------------------+-----------+
| id | group1 | date_time | obj_value |
+----+--------+---------------------+-----------+
| 1 | g1 |……
[/Quote]
向狼头哥学习;

[小小小小周]

select group1,date(date_time), ceil((UNIX_TIMESTAMP(date_time1)-UNIX_TIMESTAMP(date_time))/60) as time_len ,obj_value from (

select tt.*,t.date_time1 from t tt ,(select group1,date_time as date_time1 from t) t
where tt.group1=t.group1 and tt.date_time<>t.date_time1 and tt.date_time<t.date_time1
) asd
group by 
group1,date(date_time),obj_value