순위 1,2,3위와 나머지 값들의 합계계산하는 쿼리

안녕하세요? 한번의 query로 도저히 될지 안될지 알수가 없어서 여러 님들께 질문 드립니당.. 데이터의 순위를 1,2,3위를 계산하고요.. 1,2,3위 이외의 나머지는 합계로 계산해야 하는 형식인데요.. 어떤식으로 처리 해야 할지요? 데이터의 예는  구분 부품명 업체 A01 AA A A01 AA B A01 AA B A01 AA B A01 BB A A01 BB A A01 BB A A01 BB A A01 CC A A01 CC B A01 CC C A01 CC A A01 DD A A01 DD C A01 DD C A01 DD C A01 DD C A01 DD D A01 EE D A01 EE D A01 EE D A01 EE D A01 EE D A01 EE C A01 EE C B01 AA A B01 AA A B01 AA B B01 AA B B01 AA A B01 AA A B01 BB C B01 BB D B01 BB D B01 BB D B01 BB D B01 BB D B01 CC A B01 CC A B01 CC B B01 DD B B01 DD C B01 DD D B01 DD A B01 DD A B01 DD B B01 DD B B01 DD C B01 DD D B01 EE D B01 EE A 이런 식이구요... 나와야 하는 값은.. 구분 No 부품명 업체 건수 비율 A01 1 EE D 5 20.00% A01 2 BB A 4 16.00% A01 2 DD C 4 16.00% A01 기타 　 　 12 48.00% 　 합계 　 　 25 100% B01 1 BB D 5 19.23% B01 2 AA A 4 15.38% B01 3 DD B 3 11.54% B01 기타 　 　 14 53.85% 　 합계 　 　 26 100% 이렇습니당... 어떤 식으로 접근해야 할까요? row_number() over (partition by  구분   order by  COUNT(*) DESC) row_num 이렇게 해서 row number구하고 count하고 했는데 잘 안되내요.. 고수님들 부탁 드려용~~

추 천 글 등 록

목록보기

댓글 목록

• 
  ET [2011년 06월 22일 13시]

  WITH T AS 
  ( 
  SELECT 'A01' CD, 'AA' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'AA' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'AA' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'AA' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'BB' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'BB' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'BB' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'BB' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'CC' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'CC' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'CC' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'CC' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'DD' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'A01' CD, 'EE' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'AA' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'BB' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'CC' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'CC' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'CC' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'A' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'B' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'C' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'DD' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'EE' A, 'D' B FROM DUAL UNION ALL 
  SELECT 'B01' CD, 'EE' A, 'A' B FROM DUAL 
  ) 
  SELECT CD, 
  CASE WHEN GROUPING(RN) !=0 THEN '합계' 
  ELSE CASE WHEN RN > 3 THEN '기타' 
  ELSE TO_CHAR(MAX(NO)) 
  END 
  END NO, 
  CASE WHEN GROUPING(RN) = 0 AND RN <= 3 
  THEN MAX(A) 
  END A, 
  CASE WHEN GROUPING(RN) = 0 AND RN <= 3 
  THEN MAX(B) 
  END B, 
  SUM(CNT) CNT, 
  TO_CHAR(SUM(RT)*100,'FM990.00')||'%' RT 
  FROM ( 
  SELECT CD, 
  A, 
  B, 
  COUNT(*) CNT, 
  RANK() OVER (PARTITION BY CD ORDER BY COUNT(A||B) DESC) NO, 
  CASE WHEN ROW_NUMBER() OVER (PARTITION BY CD ORDER BY COUNT(A||B) DESC,B) > 3 THEN 4
  ELSE ROW_NUMBER() OVER (PARTITION BY CD ORDER BY COUNT(A||B) DESC,B) END RN, 
  RATIO_TO_REPORT(COUNT(*)) OVER (PARTITION BY CD) RT 
  FROM T 
  GROUP BY CD,A,B 
  ) 
  GROUP BY CD,ROLLUP(RN)