请教下Oracle分组统计问题

[「已注销」]

如图，左边是源数据，右边是想统计的结果，不太会Oracle分组函数，应该怎么写呢？

[chengccy]

with tmp as
(select 'a' col1, 'r1' as col2 from dual union all 
select 'a' col1, 'r4' as col2 from dual union all 
select 'a' col1, 'r1' as col2 from dual union all 
select 'a' col1, 'r2' as col2 from dual union all 
select 'b' col1, 'r1' as col2 from dual union all 
select 'b' col1, 'r5' as col2 from dual union all 
select 'c' col1, 'r3' as col2 from dual union all 
select 'c' col1, 'r1' as col2 from dual union all 
select 'c' col1, 'r3' as col2 from dual)
SELECT *
FROM   (SELECT COL1, C_COL1, COL2, C_COL2,
                ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY C_COL2 DESC, COL2 ASC) AS RN
         FROM   (SELECT COUNT(1) OVER(PARTITION BY COL1) AS C_COL1,
                         COUNT(1) OVER(PARTITION BY COL1, COL2) AS C_COL2, T.*
                  FROM   TMP T))
WHERE  RN = 1

[chengccy]

或者这样

with tmp as
(select 'a' col1, 'r1' as col2 from dual union all 
select 'a' col1, 'r4' as col2 from dual union all 
select 'a' col1, 'r1' as col2 from dual union all 
select 'a' col1, 'r2' as col2 from dual union all 
select 'b' col1, 'r1' as col2 from dual union all 
select 'b' col1, 'r5' as col2 from dual union all 
select 'c' col1, 'r3' as col2 from dual union all 
select 'c' col1, 'r1' as col2 from dual union all 
select 'c' col1, 'r3' as col2 from dual) 
SELECT COL1, C_COL1, COL2, C_COL2
FROM   (SELECT COL1, COL2, COUNT(1) AS C_COL2,
                SUM(COUNT(1)) OVER(PARTITION BY COL1) AS C_COL1,
                ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COUNT(1) DESC, COL2 ASC) AS RN
         FROM   TMP
         GROUP  BY COL1, COL2)
WHERE  RN = 1