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 Go to next message
fixxxer
Messages: 32
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 Go to previous messageGo to next message
John Watson
Messages: 7953
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 #671480 is a reply to message #671478] Fri, 31 August 2018 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Re: Query to group data [message #671483 is a reply to message #671480] Fri, 31 August 2018 06:22 Go to previous messageGo to next message
fixxxer
Messages: 32
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
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 Go to previous messageGo to next message
fixxxer
Messages: 32
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
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 #671490 is a reply to message #671489] Fri, 31 August 2018 10:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
Also, did you see my reply on collect supporting distinct? So no need to use WITH clause and ROW_NUMBER.

SY.
Re: Query to group data [message #671491 is a reply to message #671489] Fri, 31 August 2018 10:30 Go to previous messageGo to next message
fixxxer
Messages: 32
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 #671492 is a reply to message #671491] Fri, 31 August 2018 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And did you see the solution I provided?

Re: Query to group data [message #671493 is a reply to message #671492] Fri, 31 August 2018 10:46 Go to previous messageGo to next message
fixxxer
Messages: 32
Registered: August 2014
Member
Michel Cadot wrote on Fri, 31 August 2018 16:41

And did you see the solution I provided?


Yeah, Michel, thanks a lot. I'm going to go with CAST COLLECT, it will save installing the third party code.
Re: Query to group data [message #671494 is a reply to message #671491] Fri, 31 August 2018 11:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
fixxxer wrote on Fri, 31 August 2018 11:30
If 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 Go to previous message
Solomon Yakobson
Messages: 2863
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.
Previous Topic: Oracle regular expressions query
Next Topic: Oracle ad-hoc report issues
Goto Forum:
  


Current Time: Wed Jul 17 20:04:43 CDT 2019