sql - Oracle SQL进行连续分组

Question

我需要从具有以下结构和数据的表中生成报告。

票务表具有如下数据。

ID         Assigned_To
100       raju
101       raju
102       raju
103       anil
104       anil
105       sam
106       raju
107       raju
108       anil

Oracle SELECT应该生成以下报告

From_Id            To_Id    Assigned_To
100                  102      raju
103                  104      anil
105                  105      sam
106                  107      raju
108                  108      anil

有人可以帮我建立一个查询..吗？

提前致谢，
马修。

Answer 1

SQL> create table ticket (id,assigned_to)
  2  as
  3  select 100, 'raju' from dual union all
  4  select 101, 'raju' from dual union all
  5  select 102, 'raju' from dual union all
  6  select 103, 'anil' from dual union all
  7  select 104, 'anil' from dual union all
  8  select 105, 'sam'  from dual union all
  9  select 106, 'raju' from dual union all
 10  select 107, 'raju' from dual union all
 11  select 108, 'anil' from dual
 12  /

Tabel is aangemaakt.

SQL> select min(id) from_id
  2       , max(id) to_id
  3       , assigned_to
  4    from ( select id
  5                , assigned_to
  6                , id - row_number() over (partition by assigned_to order by id) grp
  7             from ticket
  8         )
  9   group by assigned_to
 10       , grp
 11   order by from_id
 12  /

   FROM_ID      TO_ID ASSIGNED_TO
---------- ---------- -----------
       100        102 raju
       103        104 anil
       105        105 sam
       106        107 raju
       108        108 anil

5 rijen zijn geselecteerd.

**更新与tuinstoel解决方案的性能比较结果:

在11.1.0.7上:

SQL> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

SQL> set termout off
SQL> select min(id) from_id
  2       , max(id) to_id
  3       , assigned_to
  4    from ( select id
  5                , assigned_to
  6                , id - row_number() over (partition by assigned_to order by id) grp
  7             from ticket
  8         )
  9   group by assigned_to
 10       , grp
 11   order by from_id
 12  /

   FROM_ID      TO_ID ASSI
---------- ---------- ----
       100        102 raju
       103        104 anil
       105        105 sam
       106        107 raju
       108        108 anil
       109        111 raju
<snip>
    589921     589922 raju
    589923     589923 anil

327680 rows selected.

SQL> set termout on
SQL> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

SQL> set termout off
SQL> select * from table(testpl.pltest)
  2  /

   FROM_ID      TO_ID ASSI
---------- ---------- ----
       100        102 raju
       103        104 anil
       105        105 sam
       106        107 raju
       108        108 anil
       109        111 raju
<snip>
    589921     589922 raju
    589923     589923 anil

327680 rows selected.

SQL> set termout on

结果:

SQL> exec runstats_pkg.rs_stop(100)
Run1 draaide in 547 hsecs
Run2 draaide in 549 hsecs
Run1 draaide in 99.64% van de tijd

Naam                                                      Run1        Run2    Verschil
STAT.recursive cpu usage                                     2         106         104
LATCH.row cache objects                                     91         217         126
STAT.bytes received via SQL*Net from client             37,496      37,256        -240
STAT.recursive calls                                         7       5,914       5,907
STAT.table scan rows gotten                            615,235     589,824     -25,411
STAT.sorts (rows)                                      917,504     589,824    -327,680

Run1 latches totaal versus run2 -- verschil en percentage
Run1      Run2  Verschil     Pct
10,255    10,471       216  97.94%

PL/SQL procedure successfully completed.

问候，
抢。