Oracle SQL进行连续分组 [英] Oracle SQL for continuous grouping
本文介绍了Oracle SQL进行连续分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要从具有以下结构和数据的表中生成报告.
I need to generate a report from a table with the structure and data as given below.
Table Ticket具有下面给出的数据.
Table Ticket has data as given below.
ID Assigned_To
100 raju
101 raju
102 raju
103 anil
104 anil
105 sam
106 raju
107 raju
108 anil
Oracle SELECT
应该生成以下报告
The Oracle SELECT
should generate the below report
From_Id To_Id Assigned_To
100 102 raju
103 104 anil
105 105 sam
106 107 raju
108 108 anil
有人可以帮我建立一个查询吗?.
Can someone please help me with building a query..?
预先感谢, 马修.
推荐答案
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解决方案的性能比较的结果:
**UPDATE with the results of a performance comparison with tuinstoel's solution:
在11.1.0.7上:
On 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.
关于, 罗布.
这篇关于Oracle SQL进行连续分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文