|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 15th November 2006 Oracle version(s): 10.2 |
|
How do I exclude duplicated elements from the “STRAGG” in a SQL Statement? |
|
The following SQL Model Clause pattern can be used to exclude duplicates elements from the “STRAGG”. The requirements for this pattern are as following:
1) Comma separated sorted id
2) No duplicate “item_str” are allowed within the same group.
3) Current row’s “num_shift” is between previous row’s “num_shift” - 1 and previous row’s “num_shift” + 1
4) Calculate both the sum/average “num_shift” for the “STRAGG” group.
create table t1 (item_name varchar2(3), num_shift number(5,2), id number(3), item_str varchar2(3) )
INSERT INTO t1 VALUES ('a1', 10, 1, 'A');
INSERT INTO t1 VALUES ('a1', 11, 2, 'B');
INSERT INTO t1 VALUES ('a1', 11.5, 3, 'C');
INSERT INTO t1 VALUES ('a1', 12, 4, 'A');
INSERT INTO t1 VALUES ('a1', 12.5, 5, 'B');
INSERT INTO t1 VALUES ('a1', 16, 6, 'C');
INSERT INTO t1 VALUES ('a1', 17 , 7, 'B');
INSERT INTO t1 VALUES ('a1', 23 , 8, 'B');
INSERT INTO t1 VALUES ('a1', 23 , 9, 'C');
INSERT INTO t1 VALUES ('b1', 5, 10, 'W');
INSERT INTO t1 VALUES ('b1', 6, 11, 'Z');
INSERT INTO t1 VALUES ('c1', 3, 12, 'X');
INSERT INTO t1 VALUES ('c1', 4 , 13, 'X');
INSERT INTO t1 VALUES ('c1', 5, 14, 'D');
INSERT INTO t1 VALUES ('c1', 6, 15, 'E');
INSERT INTO t1 VALUES ('c1', 7, 16, 'D');
SQL> select * from t1;
ITE NUM_SHIFT ID ITE
--- ---------- ---------- ---
a1 10 1 A
(1,2,3 A/B/C in the same
group)
a1 11 2 B
a1 11.5 3 C
a1 12 4 A
(4,5 A/B in the same group )
a1 12.5 5 B
a1 16 6 C
(6,7 C/B in the same group )
a1 17 7 B
a1 23 8 B
(8,9 B/C in the same group)
a1 23 9 C
b1 5 10 W (10,11 W/Z in the same group)
b1 6 11 Z
c1 3 12 X
(12 X )
c1 4 13 X
(13,14,15 X/D/E in the same
group)
c1 5 14 D
c1 6 15 E
c1 7 16 D
(16 D )
SQL> COLUMN item_name FORMAT A3
SQL> COLUMN id_Group FORMAT A12
SQL> COLUMN name_group FORMAT A8
SELECT item_name, id_Group, name_group, sum_num_shift,
round(sum_num_shift/(LENGTH(id_Group) -
LENGTH(REPLACE(id_Group, ',', '')) +1),2)
avg_num_shift
FROM
(SELECT item_name, id_Group, sum_num_shift, name_group
FROM
(SELECT item_name, id_Group, sum_num_shift, name_group
FROM
t1
MODEL
PARTITION BY (item_name)
DIMENSION BY
(row_number() OVER (PARTITION BY item_name
ORDER BY id, item_str)
as position
)
MEASURES
(
num_shift,
0 flag,
CAST(NULL AS NUMBER) sum_num_shift,
item_str item_str,
CAST(id
AS VARCHAR2(3)) ID,
CAST(NULL AS VARCHAR2(3255))
name_group,
CAST(NULL AS VARCHAR2(3255))
IdGroup_temp,
CAST(NULL AS VARCHAR2(3255)) id_Group
)
RULES
ITERATE(2)
(
id_Group[ ANY] ORDER BY position =
CASE WHEN flag[CV()]
= 1
THEN CASE WHEN
IdGroup_temp[CV()+1] IS NOT NULL
THEN CASE WHEN instr(IdGroup_temp[CV()+1],
IdGroup_temp[CV()]) < 1
THEN IdGroup_temp[CV()]
END
ELSE IdGroup_temp[CV()]
END
END,
name_group[ ANY] ORDER BY
position =
CASE WHEN flag[CV()]
= 0
THEN CASE WHEN abs( num_shift[CV()] -
nvl(num_shift[CV()-1], num_shift[CV()])) <= 1
AND instr(name_group[CV()-1], item_str[CV()]) <1
THEN name_group[CV()-1]|| ',' ||item_str[CV()]
ELSE item_str[CV()]
END
ELSE name_group[CV()]
END,
IdGroup_temp[ANY] ORDER BY position =
CASE WHEN flag[CV()] = 0
THEN CASE WHEN abs( num_shift[CV()] -
nvl(num_shift[CV()-1], num_shift[CV()]))<= 1
AND
instr (name_group[CV()-1], item_str[CV()])<1
THEN IdGroup_temp[CV()-1]|| ','|| ID [CV()]
ELSE ID[CV()]
END
END ,
sum_num_shift[ANY] ORDER BY position =
CASE WHEN flag[CV()]
= 0
THEN CASE WHEN abs( num_shift[CV()] -
nvl(num_shift[CV()-1], num_shift[CV()])) <=1
AND instr(name_group[CV()-1], item_str[CV()])<1
THEN
sum_num_shift[CV()-1] + num_shift[CV()]
ELSE
num_shift[CV()]
END
ELSE sum_num_shift[CV()]
END ,
flag[ ANY] = 1
)
)
)
WHERE
id_Group IS NOT NULL
ORDER BY item_name, id_Group ;
ITE ID_GROUP NAME_GRO
SUM_NUM_SHIFT AVG_NUM_SHIFT
--- ------------ -------- ------------- -------------
a1 1,2,3 A,B,C 32.5 10.83
a1 4,5 A,B 24.5 12.25
a1 6,7 C,B 33 16.5
a1 8,9 B,C 46 23
b1 10,11 W,Z 11 5.5
c1 12 X 3 3
c1 13,14,15 X,D,E 15 5
c1 16 D 7 7
8 rows selected.
SQL> spool off;