统计不同分组下，每天的时间差值。

狼兄已帮我解决了累加时长的问题，在此表示感谢，现在还要统计每天的时间差，说明如下：有一表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  
10  g1     2011-2-2 0:00:00  0
11  g2     2011-2-2 0:00:00  0
12  g3     2011-2-2 0:00:00  0
13  g1     2011-2-2 8:00:00  1
14  g3     2011-2-2 14:00:00 1
15  g1     2011-2-2 23:59:59 1
16  g2     2011-2-2 23:59:59 0
17  g3     2011-2-2 23:59:59 1   
18  g1     2011-2-4 0:00:00  1
19  g2     2011-2-4 0:00:00  0
20  g3     2011-2-4 0:00:00  1
21  g2     2011-2-4 10:00:00 1
22  g1     2011-2-4 15:00:00 0
23  g1     2011-2-4 23:59:59 0
24  g2     2011-2-4 23:59:59 1
25  g3     2011-2-4 23:59:59 1 想按group1分组，计算每天在obj_value字段不同状态下(0、1)，每日时间差（当天的累加时长-上一天的累加时长），
，如果无上一天的数据，当天时间差可标记为0或者null ,精确的分钟。
统计后结果为：
group1 date_time time_len cha_value obj_value
g1     2011-2-1  1080     null      1
g1     2011-2-1  360      null      0
g2     2011-2-1  120      null      1  
g2     2011-2-1  1320     null      0
g3     2011-2-1  0        null      1
g3     2011-2-1  1440     null      0
g1     2011-2-2  960      -120      1
g1     2011-2-2  480      120       0
g2     2011-2-2  0        -120      1  
g2     2011-2-2  1440     120       0
g3     2011-2-2  600      600       1
g3     2011-2-2  840      -600      0
g1     2011-2-4  900      null      1
g1     2011-2-4  540      null      0
g2     2011-2-4  840      null      1  
g2     2011-2-4  600      null      0
g3     2011-2-4  1440     null      1
g3     2011-2-4  0        null      0说明，每天会有00:00:00和23:59:59时间点的obj_value字段状态，方便统计；
上述数据中因2011-2-1和2011-2-4的上一天没有数据，所以cha_value字段用null代替了

解决方案 »

  1.   

    楼主把表结构和insert 语句发出来吧.不然我黏贴复制要老半天..
      

  2.   

    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 |
    |   10 | g1     | 2011-02-02 00:00:00 |         0 |
    |   11 | g2     | 2011-02-02 00:00:00 |         0 |
    |   12 | g3     | 2011-02-02 00:00:00 |         0 |
    |   13 | g1     | 2011-02-02 08:00:00 |         1 |
    |   14 | g3     | 2011-02-02 14:00:00 |         1 |
    |   15 | g1     | 2011-02-02 23:59:59 |         1 |
    |   16 | g2     | 2011-02-02 23:59:59 |         0 |
    |   17 | g3     | 2011-02-02 23:59:59 |         1 |
    |   18 | g1     | 2011-02-04 00:00:00 |         1 |
    |   19 | g2     | 2011-02-04 00:00:00 |         0 |
    |   20 | g3     | 2011-02-04 00:00:00 |         1 |
    |   21 | g2     | 2011-02-04 10:00:00 |         1 |
    |   22 | g1     | 2011-02-04 15:00:00 |         0 |
    |   23 | g1     | 2011-02-04 23:59:59 |         0 |
    |   24 | g2     | 2011-02-04 23:59:59 |         1 |
    |   25 | g3     | 2011-02-04 23:59:59 |         1 |
    +------+--------+---------------------+-----------+
    25 rows in set (0.00 sec)mysql> select abcc.group1,abcc.date_time,abcc.time_len,(abcc.time_len-abdd.time_
    len) as cha_value ,abcc.obj_value from
        -> (
        -> 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)=d
    ate(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
        -> ) abcc left join
        -> (
        -> 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)=d
    ate(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
        -> ) abdd
        -> on  abcc.group1=abdd.group1 and abcc.obj_value=abdd.obj_value and  datedi
    ff(abdd.date_time,abcc.date_time)=-1
        -> group by abcc.group1,date(abcc.date_time),abcc.obj_value
        -> ;
    +--------+------------+----------+-----------+-----------+
    | group1 | date_time  | time_len | cha_value | obj_value |
    +--------+------------+----------+-----------+-----------+
    | g1     | 2011-02-01 |      360 |      NULL |         0 |
    | g1     | 2011-02-01 |     1080 |      NULL |         1 |
    | g1     | 2011-02-02 |      480 |       120 |         0 |
    | g1     | 2011-02-02 |      960 |      -120 |         1 |
    | g1     | 2011-02-04 |      540 |      NULL |         0 |
    | g1     | 2011-02-04 |      900 |      NULL |         1 |
    | g2     | 2011-02-01 |     1320 |      NULL |         0 |
    | g2     | 2011-02-01 |      120 |      NULL |         1 |
    | g2     | 2011-02-02 |     1440 |       120 |         0 |
    | g2     | 2011-02-04 |      600 |      NULL |         0 |
    | g2     | 2011-02-04 |      840 |      NULL |         1 |
    | g3     | 2011-02-01 |     1440 |      NULL |         0 |
    | g3     | 2011-02-02 |      840 |      -600 |         0 |
    | g3     | 2011-02-02 |      600 |      NULL |         1 |
    | g3     | 2011-02-04 |     1440 |      NULL |         1 |
    +--------+------------+----------+-----------+-----------+
    15 rows in set (0.00 sec)
    用狼头大哥的基础表;
      

  3.   

    用楼上的方法吧。建议下次提问时，提供你的 create table ,insert into 语句。
      

  4.   

    -- 基础数据表sqldrop table if exists test.tb;
    create table test.tb (id int, group1 varchar(20), date_time timestamp, obj_value int);
    insert into test.tb
    select 1,   'g1',     '2011-2-1 0:00:00',  '1' union all
    select 1,   'g2',     '2011-2-1 0:00:00',  '  0' union all
    select 3,   'g3',     '2011-2-1 0:00:00',  '  0' union all
    select 4,   'g2',     '2011-2-1 10:00:00',  ' 1' union all
    select 5,   'g2',    '2011-2-1 12:00:00',  '0' union all
    select 6,   '   g1',     '2011-2-1 18:00:00',  '0' union all
    select 7,   '   g1',     '2011-2-1 23:59:59',  '0' union all
    select 8,   '   g2',     '2011-2-1 23:59:59',  '0' union all
    select 9,   '   g3',     '2011-2-1 23:59:59',  '0  ' union all
    select 10,   '  g1',     '2011-2-2 0:00:00',  '0' union all
    select 11,   '  g2',     '2011-2-2 0:00:00',  '0' union all
    select 12,   '  g3',     '2011-2-2 0:00:00',  '0' union all
    select 13,   '  g1',     '2011-2-2 8:00:00',  '1' union all
    select 14,   '  g3',     '2011-2-2 14:00:00',  '1' union all
    select 15,   '  g1',     '2011-2-2 23:59:59',  '1' union all
    select 16,   '  g2',     '2011-2-2 23:59:59',  '0' union all
    select 17,   '  g3',     '2011-2-2 23:59:59',  '1' union all   
    select 18,   '  g1',     '2011-2-4 0:00:00',  '1' union all
    select 19,   '  g2',     '2011-2-4 0:00:00',  '0' union all
    select 20,   '  g3',     '2011-2-4 0:00:00',  '1' union all
    select 21,   '  g2',     '2011-2-4 10:00:00',  '1' union all
    select 22,   '  g1',     '2011-2-4 15:00:00',  '0' union all
    select 23,   '  g1',     '2011-2-4 23:59:59',  '0' union all
    select 24 ,   ' g2',     '2011-2-4 23:59:59',  '1' union all
    select 25,   '  g3',     '2011-2-4 23:59:59',  '1';
    update test.tb set group1=trim(group1);