SQL query to get min, max rows

问题:

I have following sample data, I want to get min and max time of every consecutive status.

cat     subcat  status  logtime
fruits  apple   0   30-10-2017 06:00
fruits  apple   0   30-10-2017 06:03
fruits  apple   0   30-10-2017 06:06
fruits  apple   0   30-10-2017 06:09
fruits  apple   0   30-10-2017 06:12
fruits  apple   0   30-10-2017 06:15
fruits  apple   0   30-10-2017 06:18
fruits  apple   0   30-10-2017 06:21
fruits  apple   0   30-10-2017 06:24
fruits  apple   0   30-10-2017 06:27
fruits  apple   0   30-10-2017 06:30
fruits  apple   0   30-10-2017 06:33
fruits  apple   0   30-10-2017 06:36
fruits  apple   0   30-10-2017 06:39
fruits  apple   0   30-10-2017 06:42
fruits  apple   0   30-10-2017 06:45
fruits  apple   0   30-10-2017 06:48
fruits  apple   0   30-10-2017 06:51
fruits  apple   0   30-10-2017 06:54
fruits  apple   0   30-10-2017 06:57
fruits  strawberry  1   30-10-2017 06:00
fruits  strawberry  1   30-10-2017 06:03
fruits  strawberry  1   30-10-2017 06:06
fruits  strawberry  2   30-10-2017 06:09
fruits  strawberry  1   30-10-2017 06:12
fruits  strawberry  1   30-10-2017 06:15
fruits  strawberry  1   30-10-2017 06:18
fruits  strawberry  1   30-10-2017 06:21
fruits  strawberry  1   30-10-2017 06:24
fruits  strawberry  1   30-10-2017 06:27
fruits  strawberry  1   30-10-2017 06:30
fruits  strawberry  1   30-10-2017 06:33
fruits  strawberry  1   30-10-2017 06:36
fruits  strawberry  1   30-10-2017 06:39
fruits  strawberry  2   30-10-2017 06:42
fruits  strawberry  1   30-10-2017 06:45
fruits  strawberry  1   30-10-2017 06:48
fruits  strawberry  1   30-10-2017 06:51
fruits  strawberry  1   30-10-2017 06:54
fruits  strawberry  1   30-10-2017 06:57
fruits  blueberry   0   30-10-2017 06:00
fruits  blueberry   0   30-10-2017 06:03
fruits  blueberry   0   30-10-2017 06:06
fruits  blueberry   2   30-10-2017 06:09
fruits  blueberry   0   30-10-2017 06:12
fruits  blueberry   0   30-10-2017 06:15
fruits  blueberry   0   30-10-2017 06:18
fruits  blueberry   0   30-10-2017 06:21
fruits  blueberry   0   30-10-2017 06:24
fruits  blueberry   0   30-10-2017 06:27
fruits  blueberry   0   30-10-2017 06:30
fruits  blueberry   0   30-10-2017 06:33
fruits  blueberry   0   30-10-2017 06:36
fruits  blueberry   0   30-10-2017 06:39
fruits  blueberry   2   30-10-2017 06:42
fruits  blueberry   0   30-10-2017 06:45
fruits  blueberry   0   30-10-2017 06:48
fruits  blueberry   0   30-10-2017 06:51
fruits  blueberry   0   30-10-2017 06:54
fruits  blueberry   0   30-10-2017 06:57
fruits  guaua   1   30-10-2017 06:00
fruits  guaua   1   30-10-2017 06:03
fruits  guaua   1   30-10-2017 06:06
fruits  guaua   2   30-10-2017 06:09
fruits  guaua   1   30-10-2017 06:12
fruits  guaua   1   30-10-2017 06:15
fruits  guaua   1   30-10-2017 06:18
fruits  guaua   1   30-10-2017 06:21
fruits  guaua   1   30-10-2017 06:24
fruits  guaua   1   30-10-2017 06:27
fruits  guaua   1   30-10-2017 06:30
fruits  guaua   1   30-10-2017 06:33
fruits  guaua   1   30-10-2017 06:36
fruits  guaua   1   30-10-2017 06:39
fruits  guaua   2   30-10-2017 06:42
fruits  guaua   1   30-10-2017 06:45
fruits  guaua   1   30-10-2017 06:48
fruits  guaua   1   30-10-2017 06:51
fruits  guaua   1   30-10-2017 06:54
fruits  guaua   1   30-10-2017 06:57

I'm trying get min, max time of each status for cat and subcat status.

Output result should be following:

cat     subcat      status  mintime maxtime
fruits  apple       0   30-10-2017 06:00    30-10-2017 06:57
fruits  strawberry  1   30-10-2017 06:00    30-10-2017 06:06
fruits  strawberry  2   30-10-2017 06:09    30-10-2017 06:09
fruits  strawberry  1   30-10-2017 06:12    30-10-2017 06:39
fruits  strawberry  2   30-10-2017 06:42    30-10-2017 06:42
fruits  strawberry  1   30-10-2017 06:45    30-10-2017 06:57
fruits  blueberry   0   30-10-2017 06:00    30-10-2017 06:06
fruits  blueberry   2   30-10-2017 06:09    30-10-2017 06:09
fruits  blueberry   0   30-10-2017 06:12    30-10-2017 06:39
fruits  blueberry   2   30-10-2017 06:42    30-10-2017 06:42
fruits  blueberry   0   30-10-2017 06:45    30-10-2017 06:57
fruits  guaua       1   30-10-2017 06:00    30-10-2017 06:06
fruits  guaua       2   30-10-2017 06:09    30-10-2017 06:09
fruits  guaua       1   30-10-2017 06:12    30-10-2017 06:39
fruits  guaua       2   30-10-2017 06:42    30-10-2017 06:42
fruits  guaua       1   30-10-2017 06:45    30-10-2017 06:57

tried with following sql query but unable to get above output instead its giving min, max total hour.

SELECT cat,
        subcat,
        status,        
        min(logtime) as minlog,
        max(logtime) as maxlog
FROM fruits_log
WHERE 
    cat = 'fruits'
    AND date_format(logtime,'%Y-%m-%d %H) = '2017-10-30 06'
    AND status IN ('0','1','2')
GROUP BY cat,subcat,date_format(logtime,'%Y-%m-%d %H),status
ORDER BY cat,subcat,minlog ;

Please help what i'm missing in above query

回答1:

You may try something like this

select  t.cat, t.subcat, t.status, min(t.logdate), max(t.logdate)
from
(
    select t1.cat, t1.subcat, t1.status, t1.logdate, count(t2.cat) grpnum
    from data t1
    left join data t2 on t1.cat = t2.cat and t1.subcat = t2.subcat and 
                         t1.status != t2.status and 
                         t1.logdate < t2.logdate
    group by t1.cat, t1.subcat, t1.status, t1.logdate
) t
group by t.cat, t.subcat, t.status, t.grpnum

I have tested on just very small sample of your data, but it seems to be ok. The inner query use a self-join to isolate consecutive rows with the same cat, subcat and status values. Once this is ready, you can simply do a group by for each group of rows in the data.

回答2:

Select cat,subcat,minlog,maxlog from ( SELECT cat,
            subcat,
            status,        
            min(logtime) as minlog,
            max(logtime) as maxlog
    FROM fruits_log
    WHERE 
        cat = 'fruits'
        AND date_format(logtime,'%Y-%m-%d %H') = '2017-10-30 06'
        AND status IN ('0','1','2')
    GROUP BY cat,subcat,date_format(logtime,"%Y-%m-%d %H-%i"),status) t1 order by cat,subcat,status;

try this...