ORACLE SELECT GROUP BY +我不知道的东西 [英] ORACLE SELECT GROUP BY + something that I don't know

问题描述

我在ORACLE数据库中有一张表，详情如下：

  --- ----------------------------------------- 
 |水果| 
 -------------------------------------------- 
 | FRUIT_NAME | GROWTH_TIME | GROWTH_PLACE | 
 -------------------------------------------- 
 |甜瓜| 0600 | shelf1 | 
 |甜瓜| 0630 | shelf1 | 
 |甜瓜| 0700 | shelf1 | 
 |甜瓜| 0730 | shelf1 | 
 |甜瓜| 0800 | shelf1 | 
 
 |橙色| 0600 | shelf5 | 
 |橙色| 0630 | shelf5 | 
 |橙色| 0700 | shelf5 | 
 |橙色| 0730 | shelf5 | 
 |橙色| 0800 | shelf5 | 
 |橙色| 0830 | shelf5 | 
 |橙色| 0900 | shelf5 | 
 |橙色| 0930 | shelf5 | 
 |橙色| 1000 | shelf5 | 
 
 |橙色| 1200 | shelf5 | 
 |橙色| 1230 | shelf5 | 
 |橙色| 1300 | shelf5 | 
 |橙色| 1330 | shelf5 | 
 |橙色| 1400 | shelf5 | 
 
 |苹果| 0600 | shelf3 | 
 |苹果| 0630 | shelf3 | 
 |苹果| 0700 | shelf3 | 
 |苹果| 0730 | shelf3 | 
 |苹果| 0800 | shelf3 | 
 --------------------------------------------  

我想得到如下结果：

  ---------------------------------- ---------- 
 | FRUIT_NAME | GROWTH_TIME | GROWTH_PLACE | 
 -------------------------------------------- 
 |甜瓜| 0600-0800 | shelf1 | 
 
 |橙色| 0600-1000 | shelf5 | 
 |橙色| 1200-1400 | shelf5 | 
 
 |苹果| 0600-0800 | shelf3 |  

或如下所示：

  ----------------------------- -------------------------------------- 
 | FRUIT_NAME | GROWTH_START_TIME | GROWTH_END_TIME | GROWTH_PLACE | 
 ---------------------------------------------- --------------------- 
 |甜瓜| 0600 | 0800 | shelf1 | 
 
 |橙色| 0600 | 1000 | shelf5 | 
 |橙色| 1200 | 1400 | shelf5 | 
 
 |苹果| 0600 | 0800 | shelf3 |  

ORANGE情况（在1000和1400之间）这仍然是一个架子，但时间上有一点小差距。它发生了，但我不知道如何解决这个问题。 解决方案

您可以通过分析解决此问题：

  SQL>选择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（由fruit_name分区，grow_place order by growth_time）grp 
 4 from（select fruit_name，growth_time，growth_place，
 5个案例
当to_date（lag（growth_time，1）
 7（通过fruit_name划分，growth_place order by growth_time），'hh24mi'）
 8  9 then 
 10 row_number（）over（由fruit_name分区，growth_place order by growth_time）
 11 row_number （）over（由fruit_name划分，growth_place order by growth_time）= 1 
 12 then 
 13 1 
 14 ）
 16 group by fruit_name，growth_place，grp 
 17 order by fruit_name，growth_time 
 18 / 
 
 FRUIT_ GROWTH_TIME GROWTH 
 ------ ---------------------------------------- ------ 
 apple 0600-0800 shelf3 
甜瓜0600-0800 shelf1 
橙色0600-1000 shelf5 
橙色1200-1400 shelf5 
  

即首先我们将结果集分成组，其中一个组被定义为特定fruut / shelf的连续日期。

我们通过检查之前的日期并查看它的<当前行的日期为 - 30分钟，含有

  lag（growth_time，1）over（由fruit_name分区，growth_place order by growth_time）
 $ / code> 

从这里我们可以得出其中前一行比这行更早30分钟以上的组：

  SQL>选择fruit_name，growth_time，growth_place，
 2 case $ b $ 3当to_date（lag（growth_time，1）
 4 over（分区由fruit_name，growth_place order by growth_time），'hh24mi'）
 5< to_date（growth_time，'hh24mi'） - （30/1440）
 6 then 
 7 row_number（）over（由fruit_name分区，growth_place order by growth_time）
当row_number（）超过由fruit_name划分，growth_place order by growth_time）= 1 
 9然后
 10 1 
 11结束grp 
 12 from水果; 
 
 FRUIT_ GROW GROWTH GRP 
 ------ ---- ------ ---------- 
 apple 0600 shelf3 1 
苹果0630 shelf3 
苹果0700 shelf3 
苹果0730 shelf3 
苹果0800 shelf3 
甜瓜0600 shelf1 1 
甜瓜0630 shelf1 
甜瓜0700 shelf1 
甜瓜0730 shelf1 
甜瓜0800 shelf1 
橙色0600 shelf5 1 
橙色0630 shelf5 
橙色0700 shelf5 
橙色0730 shelf5 
橙色0800 shelf5 
橙色0830货架5 
橙色0900货架5 
橙色0930货架5 
橙色1000货架5 
橙色1200货架5 10 
橙色1230货架5 
橙色1300货架5 
橙色1330 shelf5 
橙色1400 shelf5 
  

现在我们只需将组分配给每行都有一个max（）分析：



  SQL>请选择fruit_name，growth_place，growth_time，
 2 max（grp）（根据fruit_name分区，grow_place order by growth_time）grp 
 3 from（select fruit_name，growth_time，growth_place，
 4 case 
 5当to_date（lag（growth_time，1）
 6 over（由fruit_name划分，growth_place order by growth_time），'hh24mi'）
 7  8然后
 9 row_number（）over（由fruit_name划分，growth_place order by growth_time）
 10 row_number（）over（由fruit_name划分的分区，growth_place按growth_time划分）= 1 
 11然后
 12 1 
 13结束grp 
 14从水果）; 
 
 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 
甜瓜货架1 0700 1 
甜瓜货架1 0730 1 
甜瓜货架1 0800 1 
橙色货架5 0600 1 
橙色货架5 0630 1 
橙色货架5 0700 1 
橙色货架5 0730 1 
橙色货架5 0800 1 
橙色货架5 0830 1 
橙色货架5 0900 1 
橙色货架5 0930 1 
橙色货架5 1000 1 
橙色货架5 1200 10 
橙色货架5 1230 10 
橙色货架5 1300 10 
橙色货架5 1330 10 
橙色货架5 1400 10 
   GRP 以获得最终答案。
 
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.
解决方案
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.

                        
这篇关于ORACLE SELECT GROUP BY +我不知道的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！