id location percentage comments
1 loc 1,2 20% that mean point 1 and 2 close to each other by 20%
2 loc 1,3 40% that mean point 1 and 3 close to each other byy 40%
3 Loc 8,6 25% that mean point 8 and 6 close to each other by 25%
4 Loc 6,10 20%
5 LOC 11,10 10 %
6 LOC 15,14 0%
Also , we can see the relation between these points as follwoingdrop table temp_value;
create table temp_value(id number(10),location varchar2(20), percentage number(9));
insert into temp_value values (1,'LOC 1,2',10);
insert into temp_value values (2,'LOC 1,3',0);
insert into temp_value values (3,'LOC 1,4',0);
insert into temp_value values (4,'LOC 1,5',0);
insert into temp_value values (5,'LOC 1,6',0);
insert into temp_value values (6,'LOC 2,3',0);
insert into temp_value values(7,'LOC 2,4',0);
insert into temp_value values (8,'LOC 2,5',30);
insert into temp_value values (9,'LOC 2,6',0);
insert into temp_value values (10,'LOC 3,4',0);
insert into temp_value values (11,'LOC 3,5',0);
insert into temp_value values (12,'LOC 4,5',40);
insert into temp_value values (13,'LOC 4,6',0);
insert into temp_value values (14,'LOC 6,7',40);
insert into temp_value values (15,'LOC 7,2',0);
insert into temp_value values (16,'LOC 8,2',60);
insert into temp_value values (17,'LOC 8,3',0);
insert into temp_value values (18,'LOC 3,1',0);
insert into temp_value values (19,'LOC 9,6',30);
insert into temp_value values (20,'LOC 11,2',0);
insert into temp_value values (22,'LOC 12,3',10);
insert into temp_value values (23,'LOC 19,3',0);
insert into temp_value values (24,'LOC 17,3',0);
insert into temp_value values (24,'LOC 20,3',0);
when i used this query , the results is not correct with t as
(select percentage,loc1,loc2,sum(case when percentage = 0 then 1
when loc1 in (l1,l2) then 0
when loc2 in (l1,l2) then 0
when l1 is null and l2 is null then 0
else 1
end) over(order by rn) sm
from ( select id,location,percentage,
regexp_substr(location,'\d+',1,1) LOC1,
regexp_substr(location,'\d+',1,2) LOC2,
lag(regexp_substr(location,'\d+',1,1))
over(order by percentage desc) l1,
lag(regexp_substr(location,'\d+',1,2))
over(order by percentage desc) l2,
row_number() over(order by percentage desc) rn
from temp_value
order by percentage desc
)
)
select loc,min(sm)+1 grp
from(
select loc,rownum rn,sm
from(
select percentage,decode(rn,1,loc1,loc2) loc,sm
from t a,
(select 1 rn from dual union all
select 2 from dual ) b
order by percentage desc,decode(rn,1,loc1,loc2) asc
)
)
group by loc
order by min(sm),min(rn);
the results LOC GRP
-------------------- ----------
2 1
8 1
6 2
7 2
4 3
5 3
9 4
1 5
12 6
3 6
11 13
LOC GRP
-------------------- ----------
19 14
17 15
20 22
14 rows selected.
SQL>Location group No
2 1
8 1
4 1
5 1
1 1
6 2
7 2
9 2
12 3
3 3
19 4
17 5
20 6
many thanks in advance.with connects as (
select distinct
loc1
,loc2
,dense_rank() over (order by connect_by_root(loc1)) grp
from
temp_value
start with
percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
)
select
loc
,dense_rank() over (order by grp) grp
from (
select
loc
,max(grp) keep (dense_rank first order by grp) grp
from (
select
loc1 loc
,grp
from connects
union
select
loc2
,grp
from connects
)
group by
loc
)
order by 2,1
LOC GRP
1 1
2 1
4 1
5 1
8 1
12 2
3 2
6 3
7 3
9 3
Edited by: chris227 on 30.11.2012 10:34 Loc Grp
2 1
8 1
4 1
5 1
1 1
12 2
3 2
6 3
7 3
9 3
regards Ayham wrote:You can use the analytic COUNT function for that. Since you want to PARTITION BY grp, and grp is itself the results of an analytic function, then you have to compute grp in a sub-query, such as got_grp below:
yes, many thanks but how can i sort it based on percentage Descending
with connects as (
select distinct
loc1
,loc2
,dense_rank() over (order by connect_by_root(loc1)) grp
from temp_value
start with
percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
)
, got_grp AS
(
select
loc
,dense_rank() over (order by grp) grp
from (
select
loc
,max(grp) keep (dense_rank first order by grp) grp
from (
select
loc1 loc
,grp
from connects
union
select
loc2
,grp
from connects
)
group by
loc
)
)
SELECT loc
, grp
FROM got_grp
ORDER BY COUNT (*) OVER (PARTITION BY grp) DESC
, grp
, loc
;
SQL> with connects as (
2 select distinct
3 loc1
4 ,loc2
5 ,dense_rank() over (order by connect_by_root(loc1)) grp
6 from temp_value
7 start with
8 percentage != 0
9 connect by nocycle
10 (prior loc2 = loc1
11 or
12 prior loc1 = loc2
13 or
14 prior loc1 = loc1
15 or
16 prior loc2 = loc2)
17 and
18 percentage != 0
19 )
20 , got_grp AS
21 (
22 select
23 loc
24 ,dense_rank() over (order by grp) grp
25 from (
26 select
27 loc
28 ,max(grp) keep (dense_rank first order by grp) grp
29 from (
30 select
31 loc1 loc
32 ,grp
33 from connects
34 union
35 select
36 loc2
37 ,grp
38 from connects
39 )
40 group by
41 loc
42 )
43 )
44 SELECT loc
45 , grp
46 FROM got_grp
47 ORDER BY COUNT (*) OVER (PARTITION BY grp) DESC
48 , grp
49 , loc
50 ;
The output is
LOC GRP
-------------------- ----------
1 1
2 1
4 1
5 1
8 1
6 3
7 3
9 3
12 2
3 2
10 rows selected.
but i want it like this
Loc Grp
2 1
8 1
4 1
5 1
1 1
12 2
3 2
6 3
7 3
9 3
So , the sorting for each group Separate based on the percentage column.Ayham wrote:Sorry, I don't understand.
... The output isbut i want it like thisLOC GRP -------------------- ---------- 1 1 2 1 4 1 5 1 8 1 6 3 7 3 9 3 12 2 3 2 10 rows selected.
Loc Grp 2 1 8 1 4 1 5 1 1 1 12 2 3 2 6 3 7 3 9 3
Ayham wrote:Easy with the blueprint given, study and understand it ;-) Just add percentage to the projection.
yes, many thanks but how can i sort it based on percentage Descending
like this
with connects as (
select distinct
loc1
,loc2
,dense_rank() over (order by connect_by_root(loc1)) grp
,percentage per
from
temp_value
start with
percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
)
select
loc
,dense_rank() over (order by grp) grp
from (
select
loc
,max(grp) keep (dense_rank first order by grp) grp
,max(per) keep (dense_rank last order by per nulls first) per
from (
select
loc1 loc
,grp
,per
from connects
union
select
loc2
,grp
,per
from connects
)
group by
loc
)
order by 2,per desc,1
LOC GRP
2 1
8 1
4 1
5 1
1 1
12 2
3 2
6 3
7 3
9 3
Edited by: chris227 on 30.11.2012 10:33 with connects as (
select distinct
loc1
,loc2
,connect_by_root(loc1) grp
,percentage per
from
temp_value
--start with
--percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
and
prior percentage != 0
)
select
loc
,dense_rank() over (order by decode(per,0,1,0), grp) grp
from (
select
loc
,max(grp) keep (dense_rank first order by per desc, grp) grp
,max(per) keep (dense_rank last order by per nulls first) per
from (
select
loc1 loc
,grp
,per
from connects
union
select
loc2
,grp
,per
from connects
)
group by
loc )
order by 2,per desc,1
LOC GRP
2 1
8 1
4 1
5 1
1 1
12 2
3 2
6 3
7 3
9 3
11 4
17 5
19 6
20 7
Think we are done now;-)WITH ln1 AS (
SELECT Regexp_Substr (location, '\d+', 1, 1) lfr,
Regexp_Substr (location, '\d+', 1, 2) lto,
id,
percentage
FROM temp_value
), lnk AS (
SELECT lfr, lto, id, percentage
FROM ln1
UNION
SELECT lto, lfr, id, percentage
FROM ln1
), nod AS (
SELECT DISTINCT lfr n
FROM lnk
), rsf (n, id, n_root) AS (
SELECT n, 0 id, n n_root
FROM nod
UNION ALL
SELECT l.lto, l.id, r.n_root
FROM rsf r
JOIN lnk l
ON l.lfr = r.n
AND l.id != r.id
AND l.percentage > 0
) CYCLE n SET is_cycle TO '1' DEFAULT '0'
SELECT Dense_Rank () OVER (ORDER BY Min (n)) grp, n_root loc
FROM rsf
GROUP BY n_root
ORDER BY 1, 2
/
Solution
GRP LOC
---------- ----
1 1
1 2
1 4
1 5
1 8
2 3
2 12
3 6
3 7
3 9
4 11
5 17
6 19
7 20
14 rows selected.