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 |
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 |
|
Littlefoot
Messages: 21809 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 #667848 is a reply to message #667846] |
Sat, 20 January 2018 06:10 |
Solomon Yakobson
Messages: 3273 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 |
Solomon Yakobson
Messages: 3273 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 |
|
Littlefoot
Messages: 21809 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.
|
|
|
Goto Forum:
Current Time: Sat May 11 05:41:21 CDT 2024
|