Home » SQL & PL/SQL » SQL & PL/SQL » Query to group data (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)
Query to group data [message #671478] |
Fri, 31 August 2018 05:24 |
|
fixxxer
Messages: 46 Registered: August 2014
|
Member |
|
|
I need help with some aggregate/analytics functions!
I have a query which produces the below dataset:
SELECT box_id,
link_id,
sub_id,
iden_id,
ext_id
FROM box
WHERE ext_id = 1;
BOX_ID LINK_ID SUB_ID IDEN_ID EXT_ID
1 A S1 I1 1
1 A S2 I2 1
1 B S3 I3 1
2 C S4 I4 1
2 C S5 I5 1
I need the data grouped by BOX_ID, and the other ID columns cast (multiset?) into a nested table (not a list), so it would look something like this (do not need EXT_ID included in final result set):
BOX_ID LINK_ID SUB_ID IDEN_ID
1 [A,B] [S1,S2,S3] [I1,I2,I3]
2 [C] [S4,S5] [I4,I5]
I am wanting to avoid CAST COLLECT if possible, as it generates SYS type objects. Any help at all would be massively appreciated!
FIXXXER
|
|
|
Re: Query to group data [message #671479 is a reply to message #671478] |
Fri, 31 August 2018 05:31 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This example (from here, https://oracle-base.com/articles/misc/string-aggregation-techniques) should give you a start:orclx>
orclx> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
2 FROM emp
3 GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
orclx>
|
|
|
|
Re: Query to group data [message #671483 is a reply to message #671480] |
Fri, 31 August 2018 06:22 |
|
fixxxer
Messages: 46 Registered: August 2014
|
Member |
|
|
/***************
* Create table *
***************/
CREATE TABLE box
(
box_id NUMBER,
link_id VARCHAR2(10 CHAR),
sub_id VARCHAR2(10 CHAR),
iden_id VARCHAR2(10 CHAR),
ext_id VARCHAR2(10 CHAR)
);
/*****************
* Populate table *
*****************/
DECLARE
v_box_id NUMBER := 1;
BEGIN
INSERT
INTO box VALUES
(
v_box_id,
'iden1',
'inst1',
'cust1',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden1',
'inst2',
'cust2',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden1',
'inst3',
'cust3',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden1',
'inst4',
'cust4',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden1',
'inst5',
'cust5',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden2',
'inst6',
'cust6',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden2',
'inst7',
'cust7',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden2',
'inst8',
'cust8',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden3',
'inst9',
'cust9',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden4',
'inst10',
'cust10',
'1'
);
v_box_id := 2;
INSERT
INTO box VALUES
(
v_box_id,
'iden5',
'inst11',
'cust11',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden5',
'inst12',
'cust12',
'1'
);
INSERT
INTO box VALUES
(
v_box_id,
'iden6',
'inst13',
'cust13',
'1'
);
COMMIT;
END;
/**********
* LISTAGG *
**********/
SELECT box_id,
LISTAGG(link_id,',') WITHIN GROUP (ORDER BY link_id),
LISTAGG(sub_id,',') WITHIN GROUP (ORDER BY sub_id),
LISTAGG(iden_id,',') WITHIN GROUP (ORDER BY iden_id)
FROM (
SELECT box_id
,DECODE(ROW_NUMBER () OVER (PARTITION BY link_id order by link_id),1,link_id) link_id -- To remove duplicate values
,sub_id -- no need to remove duplicates, unique
,iden_id -- no need to remove duplicates, unique
FROM box
WHERE ext_id = 1
)
GROUP BY box_id;
The LISTAGG query displays the sort of output I want, but LISTAGG is limited by string concatenation, so that is why I wanted to aggregate the data into nested tables.
|
|
|
Re: Query to group data [message #671484 is a reply to message #671483] |
Fri, 31 August 2018 08:00 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here's a LISTAGG-like function that returns a CLOB and so is not limited as VARCHAR2:
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=78622#216019
SQL> set lines 130
SQL> set numwidth 5
SQL> col res format a40
SQL> SELECT box_id,
2 stragg4(link_id) res,
3 stragg4(sub_id) res,
4 stragg4(iden_id) res
5 FROM (
6 SELECT box_id
7 ,DECODE(ROW_NUMBER () OVER (PARTITION BY link_id order by link_id),1,link_id) link_id
8 ,sub_id
9 ,iden_id
10 FROM box
11 WHERE ext_id = 1
12 )
13 GROUP BY box_id
14 /
BOX_ID RES RES RES
------ ---------------------------------------- ---------------------------------------- ----------------------------------------
1 iden1,iden4,iden3,iden2 inst1,inst10,inst9,inst8,inst7,inst6,ins cust1,cust10,cust9,cust8,cust7,cust6,cus
t5,inst4,inst3,inst2 t5,cust4,cust3,cust2
2 iden5,iden6 inst11,inst13,inst12 cust11,cust13,cust12
2 rows selected.
|
|
|
Re: Query to group data [message #671485 is a reply to message #671483] |
Fri, 31 August 2018 08:12 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with t as (
select box_id,
case row_number() over(partition by box_id,link_id order by 1)
when 1 then link_id
end link_id,
sub_id,
iden_id
from box
)
select box_id,
cast(collect(link_id) as sys.OdciVarchar2List) link_id,
cast(collect(sub_id) as sys.OdciVarchar2List) sub_id,
cast(collect(iden_id) as sys.OdciVarchar2List) iden_id
from t
group by box_id
order by box_id
/
SY.
|
|
|
Re: Query to group data [message #671486 is a reply to message #671485] |
Fri, 31 August 2018 08:15 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Actually, COLLECT supports DISTINCT so all you need is:
select box_id,
cast(collect(distinct link_id) as sys.OdciVarchar2List) link_id,
cast(collect(sub_id) as sys.OdciVarchar2List) sub_id,
cast(collect(iden_id) as sys.OdciVarchar2List) iden_id
from box
group by box_id
order by box_id
/
SY.
|
|
|
Re: Query to group data [message #671487 is a reply to message #671485] |
Fri, 31 August 2018 08:53 |
|
fixxxer
Messages: 46 Registered: August 2014
|
Member |
|
|
Solomon Yakobson wrote on Fri, 31 August 2018 14:12
with t as (
select box_id,
case row_number() over(partition by box_id,link_id order by 1)
when 1 then link_id
end link_id,
sub_id,
iden_id
from box
)
select box_id,
cast(collect(link_id) as sys.OdciVarchar2List) link_id,
cast(collect(sub_id) as sys.OdciVarchar2List) sub_id,
cast(collect(iden_id) as sys.OdciVarchar2List) iden_id
from t
group by box_id
order by box_id
/
SY.
Hi Sy,
I went with something similar:
CREATE OR REPLACE TYPE t_id AS OBJECT (id VARCHAR2(10))
/
CREATE OR REPLACE TYPE tab_id AS TABLE OF t_id
/
SELECT box_id,
CAST(COLLECT(CASE WHEN link_id IS NOT NULL THEN t_id(link_id) END) AS tab_id) tab_link_id,
CAST(COLLECT(t_id(sub_id)) AS tab_id) tab_sub_id,
CAST(COLLECT(t_id(iden_id)) AS tab_id) tab_iden_id
FROM (
SELECT box_id
,DECODE(ROW_NUMBER () OVER (PARTITION BY link_id order by link_id),1,link_id) link_id -- To remove duplicate values
,sub_id -- no need to remove duplicates, unique
,iden_id -- no need to remove duplicates, unique
FROM box
WHERE ext_id = 1
)
GROUP BY box_id;
Added the check below to avoid NULL elements in the list:
CASE WHEN link_id IS NOT NULL THEN t_id(link_id) END
Again, the only thing is Oracle creates internal TYPEs when you CAST COLLECT. If I use this in production, will it generate a new type every time this SQL Cursor is removed from the shared pool, or will it only generate the type once and use it from there on in?
|
|
|
Re: Query to group data [message #671489 is a reply to message #671487] |
Fri, 31 August 2018 10:22 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
What NULL issue? COLLECT is aggregate function and as any aggregation it skips NULLs:
SQL> select comm from emp;
COMM
----------
0
300
500
1400
COMM
----------
14 rows selected.
SQL> select collect(comm) from emp;
COLLECT(COMM)
-------------------------------------------------
ST00001MD8ojCIQrC9VkPGfOHxlA=(0, 300, 500, 1400)
SQL>
SY.
|
|
|
|
Re: Query to group data [message #671491 is a reply to message #671489] |
Fri, 31 August 2018 10:30 |
|
fixxxer
Messages: 46 Registered: August 2014
|
Member |
|
|
If you use user defined types, like:
CREATE OR REPLACE TYPE t_id AS OBJECT (id VARCHAR2(10))
/
CREATE OR REPLACE TYPE tab_id AS TABLE OF t_id
/
Then COLLECT will not inspect the record object to see if the element in the record is NULL, so you end up with a record of "T_ID(NULL)", since it is the element within the record that is NULL, the record itself is not NULL, so COLLECT includes it, so that is why I put that CASE statement, to avoid generating the record with NULL value - I hope that makes sense!
Yes, I seen the other reply, thanks a lot!
|
|
|
|
|
Re: Query to group data [message #671494 is a reply to message #671491] |
Fri, 31 August 2018 11:21 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
fixxxer wrote on Fri, 31 August 2018 11:30If you use user defined types, like:
Sure, since now you have table of objects, not table of scalars so you are aggregating not NULL but T_ID(NULL). There is absolutely no need to use table of objects. You want own type - use table of VARCHAR2s:
SQL> create or replace
2 type string_list
3 as table of varchar2(4000)
4 /
Type created.
SQL> select cast(collect(to_char(comm)) as string_list) from emp;
CAST(COLLECT(TO_CHAR(COMM))ASSTRING_LIST)
-------------------------------------------------------------------
STRING_LIST('0', '300', '500', '1400')
SQL>
SY.
[Updated on: Fri, 31 August 2018 11:22] Report message to a moderator
|
|
|
Re: Query to group data [message #671495 is a reply to message #671494] |
Fri, 31 August 2018 11:36 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And there is no need to add
CASE WHEN link_id IS NOT NULL THEN t_id(link_id) END
if you need to use table of objects. Just use t_id(link_id) in decode:
SELECT box_id,
CAST(COLLECT(link_id) AS tab_id) tab_link_id
FROM (
SELECT box_id
,DECODE(ROW_NUMBER () OVER (PARTITION BY link_id order by link_id),1,t_id(link_id)) link_id -- create object only when needed
FROM box
WHERE ext_id = 1
)
GROUP BY box_id
/
BOX_ID TAB_LINK_ID(ID)
---------- ------------------------------------------------------------------
1 TAB_ID(T_ID('iden1'), T_ID('iden4'), T_ID('iden3'), T_ID('iden2'))
2 TAB_ID(T_ID('iden5'), T_ID('iden6'))
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:49:29 CDT 2024
|