Home » SQL & PL/SQL » SQL & PL/SQL » Special grouping with SQL (Oracle 12C R2)
Special grouping with SQL [message #667843] Sat, 20 January 2018 01:00 Go to next message
OlafCologne
Messages: 11
Registered: September 2008
Location: Cologne
Junior Member
Dear Forum Members,

I have a specific grouping problem and hope that it can be solved via SQL.

Existing production data:

ROW / DATA
1 / A
2 / A
3 / A
4 / D
5 / D
6 / C
7 / E
8 / E
9 / A
10 / D
11 / D
12 / D

These should now be grouped in the following way

ROW / DATA / COUNT
1 / A / 3
2 / D / 2
3 / C / 1
4 / E / 2
5 / A / 1
6 / D / 3

A simple goup by is not the solution. Is this possible with SQL?

Thanks for your help.
Re: Special grouping with SQL [message #667846 is a reply to message #667843] Sat, 20 January 2018 02:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> with test (rowc, datac) as
  2  (select 1, 'a' from dual union
  3   select 2, 'a' from dual union
  4   select 3, 'a' from dual union
  5   select 4, 'd' from dual union
  6   select 5, 'd' from dual union
  7   select 6, 'c' from dual union
  8   select 7, 'e' from dual union
  9   select 8, 'e' from dual
 10  )
 11  select row_number() over (order by rowc) rn,
 12    datac,
 13    cnt
 14  from (select
 15         min(rowc) rowc,
 16         datac,
 17         count(*) cnt
 18        from test
 19        group by datac
 20       )
 21  order by rowc;

        RN D        CNT
---------- - ----------
         1 a          3
         2 d          2
         3 c          1
         4 e          2

SQL>
Re: Special grouping with SQL [message #667847 is a reply to message #667846] Sat, 20 January 2018 02:21 Go to previous messageGo to next message
OlafCologne
Messages: 11
Registered: September 2008
Location: Cologne
Junior Member
Thanks.

Need to see if the approach helps me.
The number of possible elements (A, B, C...) is dynamic and unknown.
Re: Special grouping with SQL [message #667848 is a reply to message #667846] Sat, 20 January 2018 06:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You only took first 8 rows from the sample avoiding groups A & D occurring twice:

SQL> with test (rowc, datac) as
  2    (select 1, 'A' from dual union
  3     select 2, 'A' from dual union
  4     select 3, 'A' from dual union
  5     select 4, 'D' from dual union
  6     select 5, 'D' from dual union
  7     select 6, 'C' from dual union
  8     select 7, 'E' from dual union
  9     select 8, 'E' from dual union
 10     select 9, 'A' from dual union
 11     select 10, 'D' from dual union
 12     select 11, 'D' from dual union
 13     select 12, 'D' from dual
 14    )
 15  select row_number() over (order by rowc) rn,
 16         datac,
 17         cnt
 18    from (select
 19           min(rowc) rowc,
 20           datac,
 21           count(*) cnt
 22          from test
 23          group by datac
 24         )
 25    order by rowc;

        RN D        CNT
---------- - ----------
         1 A          4 
         2 D          5
         3 C          1
         4 E          2

SQL> 

while it should return:


        RN D        CNT
---------- - ----------
         1 A          3 
         2 D          2
         3 C          1
         4 E          2
         5 A          1 
         6 D          3

This is typical start-of-group task. Assuming ROWC is consecutive:

with test (rowc, datac) as
  (select 1, 'A' from dual union
   select 2, 'A' from dual union
   select 3, 'A' from dual union
   select 4, 'D' from dual union
   select 5, 'D' from dual union
   select 6, 'C' from dual union
   select 7, 'E' from dual union
   select 8, 'E' from dual union
   select 9, 'A' from dual union
   select 10, 'D' from dual union
   select 11, 'D' from dual union
   select 12, 'D' from dual
  )
select  row_number() over(order by min(rowc)) rn,
        datac,
        count(*) cnt
  from  (
         select  rowc,
                 datac,
                 datac || (rowc - row_number() over(partition by datac order by rowc)) grp
           from  test
        )
  group by grp,
           datac
  order by min(rowc)
/

        RN D        CNT
---------- - ----------
         1 A          3
         2 D          2
         3 C          1
         4 E          2
         5 A          1
         6 D          3

6 rows selected.

SQL> 

And if rows are not consecutive:

with test (rowc, datac) as
  (select 1, 'A' from dual union
   select 12, 'A' from dual union
   select 23, 'A' from dual union
   select 24, 'D' from dual union
   select 25, 'D' from dual union
   select 26, 'C' from dual union
   select 37, 'E' from dual union
   select 38, 'E' from dual union
   select 49, 'A' from dual union
   select 110, 'D' from dual union
   select 111, 'D' from dual union
   select 212, 'D' from dual
  )
select  row_number() over(order by min(rowc)) rn,
        datac,
        count(*) cnt
  from  (
         select  rowc,
                 datac,
                 datac || (row_number() over(order by rowc) - row_number() over(partition by datac order by rowc)) grp
           from  test
        )
  group by grp,
           datac
  order by min(rowc)
/

        RN D        CNT
---------- - ----------
         1 A          3
         2 D          2
         3 C          1
         4 E          2
         5 A          1
         6 D          3

6 rows selected.

SQL> 

SY.

[Updated on: Sat, 20 January 2018 06:23]

Report message to a moderator

Re: Special grouping with SQL [message #667849 is a reply to message #667848] Sat, 20 January 2018 06:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And if OP is on 12C then MATCH_RECOGNIZE makes it way simpler:

with test (rowc, datac) as
  (select 1, 'A' from dual union
   select 12, 'A' from dual union
   select 23, 'A' from dual union
   select 24, 'D' from dual union
   select 25, 'D' from dual union
   select 26, 'C' from dual union
   select 37, 'E' from dual union
   select 38, 'E' from dual union
   select 49, 'A' from dual union
   select 110, 'D' from dual union
   select 111, 'D' from dual union
   select 212, 'D' from dual
  )
select  rownum rn,
        datac,
        cnt
  from  test
  match_recognize(
                  order by rowc
                  measures
                    datac datac,
                    count(*) cnt
                  pattern(p*)
                  define p as datac = first(datac)
                 )
/

        RN D        CNT
---------- - ----------
         1 A          3
         2 D          2
         3 C          1
         4 E          2
         5 A          1
         6 D          3

6 rows selected.

SQL> 

SY.

[Updated on: Sat, 20 January 2018 06:23]

Report message to a moderator

Re: Special grouping with SQL [message #667851 is a reply to message #667848] Sat, 20 January 2018 09:25 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Solomon Yakobson wrote on Sat, 20 January 2018 13:10

You only took first 8 rows from the sample avoiding groups A & D occurring twice:
Huh, right! I got bored typing test case and didn't even notice that ... my bad, sorry. Thank you, SY.
Previous Topic: Material view auto refresh DBMS_SCHEDULER
Next Topic: how can don't use column in group by
Goto Forum:
  


Current Time: Fri Mar 29 00:57:44 CDT 2024