ORACLE SELECT GROUP BY + something that I don't know

Question

I have a table in the ORACLE database, details below:

--------------------------------------------
|                  FRUITS                  |
--------------------------------------------
| FRUIT_NAME | GROWTH_TIME | GROWTH_PLACE  |
--------------------------------------------
|      melon |        0600 |        shelf1 |
|      melon |        0630 |        shelf1 |
|      melon |        0700 |        shelf1 |
|      melon |        0730 |        shelf1 |
|      melon |        0800 |        shelf1 |

|     orange |        0600 |        shelf5 |
|     orange |        0630 |        shelf5 |
|     orange |        0700 |        shelf5 |
|     orange |        0730 |        shelf5 |
|     orange |        0800 |        shelf5 |
|     orange |        0830 |        shelf5 |
|     orange |        0900 |        shelf5 |
|     orange |        0930 |        shelf5 |
|     orange |        1000 |        shelf5 |

|     orange |        1200 |        shelf5 |
|     orange |        1230 |        shelf5 |
|     orange |        1300 |        shelf5 |
|     orange |        1330 |        shelf5 |
|     orange |        1400 |        shelf5 |

|      apple |        0600 |        shelf3 |
|      apple |        0630 |        shelf3 |
|      apple |        0700 |        shelf3 |
|      apple |        0730 |        shelf3 |
|      apple |        0800 |        shelf3 |
--------------------------------------------

and I would like to get results like these below:

--------------------------------------------
| FRUIT_NAME | GROWTH_TIME | GROWTH_PLACE  |
--------------------------------------------
|      melon |   0600-0800 |        shelf1 |

|     orange |   0600-1000 |        shelf5 |
|     orange |   1200-1400 |        shelf5 |

|      apple |   0600-0800 |        shelf3 |

or like these:

-------------------------------------------------------------------
| FRUIT_NAME | GROWTH_START_TIME | GROWTH_END_TIME | GROWTH_PLACE |
-------------------------------------------------------------------
|      melon |              0600 |            0800 |       shelf1 |

|     orange |              0600 |            1000 |       shelf5 |
|     orange |              1200 |            1400 |       shelf5 |

|      apple |              0600 |            0800 |       shelf3 |

There is a small gap in the ORANGE case (between 1000 and 1400) and this is still the same shelf but with a small gap in time. It happens but I don't know how to solve this problem.

Answer 1

You can use the LEAD function, using the partition by FRUIT_NAME ordered by GROWTH_TIME to get the GROWTH_TIME of the next record, comparing to the current one you can know if there is a gap.

SELECT FRUIT_NAME , MIN(GROWTH_TIME ) || '-' || MAX(GROWTH_TIME ), GROWTH FROM (
    SELECT FRUIT_NAME , GROWTH_TIME , 
    NVL(lead (GROWTH_TIME ) over (partition by FRUIT_NAME  order by GROWTH_TIME ) -   GROWTH_TIME , 0) as gap
    FROM FRUITS
  )
GROUP BY FRUIT_NAME, gap
HAVING (gap <= 70)

Answer 2

you can solve this by analytics:

SQL> select fruit_name, min(growth_time) || '-' || max(growth_time) growth_time, growth_place
  2    from (select fruit_name, growth_place, growth_time,
  3                 max(grp) over(partition by fruit_name, growth_place order by growth_time) grp
  4             from (select fruit_name, growth_time, growth_place,
  5                          case
  6                            when to_date(lag(growth_time, 1)
  7                                   over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  8                                 < to_date(growth_time, 'hh24mi') - (30/1440)
  9                            then
 10                              row_number() over(partition by fruit_name, growth_place order by growth_time)
 11                            when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
 12                            then
 13                              1
 14                          end grp
 15                      from fruits))
 16   group by fruit_name, growth_place, grp
 17   order by fruit_name, growth_time
 18  /

FRUIT_ GROWTH_TIME                              GROWTH
------ ---------------------------------------- ------
apple  0600-0800                                shelf3
melon  0600-0800                                shelf1
orange 0600-1000                                shelf5
orange 1200-1400                                shelf5

i.e. first we break the result set into groups where a group is defined as contigious dates for a given fruut/shelf.

We do this by checking the prior date and seeing if its < the current rows date - 30 minutes with

lag(growth_time, 1) over (partition by fruit_name, growth_place order by growth_time)

from this we can derive groups where the prior row was over 30 minutes older that this row:

SQL> select fruit_name, growth_time, growth_place,
  2         case
  3           when to_date(lag(growth_time, 1)
  4                over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  5                < to_date(growth_time, 'hh24mi') - (30/1440)
  6           then
  7             row_number() over(partition by fruit_name, growth_place order by growth_time)
  8           when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
  9           then
 10             1
 11         end grp
 12    from fruits;

FRUIT_ GROW GROWTH        GRP
------ ---- ------ ----------
apple  0600 shelf3          1
apple  0630 shelf3
apple  0700 shelf3
apple  0730 shelf3
apple  0800 shelf3
melon  0600 shelf1          1
melon  0630 shelf1
melon  0700 shelf1
melon  0730 shelf1
melon  0800 shelf1
orange 0600 shelf5          1
orange 0630 shelf5
orange 0700 shelf5
orange 0730 shelf5
orange 0800 shelf5
orange 0830 shelf5
orange 0900 shelf5
orange 0930 shelf5
orange 1000 shelf5
orange 1200 shelf5         10
orange 1230 shelf5
orange 1300 shelf5
orange 1330 shelf5
orange 1400 shelf5

now we just assign the group to each row with a max() analytic:

SQL> select fruit_name, growth_place, growth_time,
  2         max(grp) over(partition by fruit_name, growth_place order by growth_time) grp
  3    from (select fruit_name, growth_time, growth_place,
  4                 case
  5                 when to_date(lag(growth_time, 1)
  6                        over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  7                      < to_date(growth_time, 'hh24mi') - (30/1440)
  8                 then
  9                   row_number() over(partition by fruit_name, growth_place order by growth_time)
 10                 when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
 11                 then
 12                   1
 13               end grp
 14          from fruits);

FRUIT_ GROWTH GROW        GRP
------ ------ ---- ----------
apple  shelf3 0600          1
apple  shelf3 0630          1
apple  shelf3 0700          1
apple  shelf3 0730          1
apple  shelf3 0800          1
melon  shelf1 0600          1
melon  shelf1 0630          1
melon  shelf1 0700          1
melon  shelf1 0730          1
melon  shelf1 0800          1
orange shelf5 0600          1
orange shelf5 0630          1
orange shelf5 0700          1
orange shelf5 0730          1
orange shelf5 0800          1
orange shelf5 0830          1
orange shelf5 0900          1
orange shelf5 0930          1
orange shelf5 1000          1
orange shelf5 1200         10
orange shelf5 1230         10
orange shelf5 1300         10
orange shelf5 1330         10
orange shelf5 1400         10

now all that's left was the final group by on the GRP to get the final answer.