데이터베이스 퀴즈 및 튜닝 문제
'ㅁ'초등학교 6학년의 2학기기말성적 데이터를 가지고 반(class)최고득점자와 학년전체 최고득점자를 출력하는 문제입니다.
- 2학기기말 성적데이터
with t as
(
select '1' class,'a' name, 80 kor, 75 eng, 80 math from dual union all
select '1' class,'b' name, 90 kor, 80 eng, 85 math from dual union all
select '1' class,'c' name, 80 kor, 95 eng, 70 math from dual union all
select '2' class,'d' name, 70 kor, 85 eng, 75 math from dual union all
select '2' class,'e' name, 95 kor, 80 eng, 90 math from dual union all
select '2' class,'f' name, 60 kor, 70 eng, 70 math from dual
)
select *
from t
- 결과데이터
반최고득점자 : 각 과목별로 class내 최고득점자name
최고득점자 : 각 과목별로 학년내 최고득점자name
<<
select
class,
case grouping(name)
when 0 then name
else case grouping(class)
when 0 then '반최고득점자'
else '최고득점자'
end
end name,
decode(grouping(name),1,max(name) keep (dense_rank last order by kor),
max(kor)) kor,
decode(grouping(name),
1,max(name) keep (dense_rank last order by eng),
max(eng)) eng,
decode(grouping(name),
1,max(name) keep (dense_rank last order by math),
max(math)) math
from t
group by grouping sets
(
(class,name),
(class),
()
)
- 2학기기말 성적데이터
with t as
(
select '1' class,'a' name, 80 kor, 75 eng, 80 math from dual union all
select '1' class,'b' name, 90 kor, 80 eng, 85 math from dual union all
select '1' class,'c' name, 80 kor, 95 eng, 70 math from dual union all
select '2' class,'d' name, 70 kor, 85 eng, 75 math from dual union all
select '2' class,'e' name, 95 kor, 80 eng, 90 math from dual union all
select '2' class,'f' name, 60 kor, 70 eng, 70 math from dual
)
select *
from t
| CLASS | NAME | KOR | ENG | MATH |
| 1 | a | 80 | 75 | 80 |
| 1 | b | 90 | 80 | 85 |
| 1 | c | 80 | 95 | 70 |
| 2 | d | 70 | 85 | 75 |
| 2 | e | 95 | 80 | 90 |
| 2 | f | 60 | 70 | 70 |
- 결과데이터
| CLASS | NAME | KOR | ENG | MATH |
| 1 | a | 80 | 75 | 80 |
| 1 | b | 90 | 80 | 85 |
| 1 | c | 80 | 95 | 70 |
| 1 | 반최고득점자 | b | c | b |
| 2 | d | 70 | 85 | 75 |
| 2 | e | 95 | 80 | 90 |
| 2 | f | 60 | 70 | 70 |
| 2 | 반최고득점자 | e | d | e |
| 최고득점자 | e | c | e |
반최고득점자 : 각 과목별로 class내 최고득점자name
최고득점자 : 각 과목별로 학년내 최고득점자name
<<
select
class,
case grouping(name)
when 0 then name
else case grouping(class)
when 0 then '반최고득점자'
else '최고득점자'
end
end name,
decode(grouping(name),1,max(name) keep (dense_rank last order by kor),
max(kor)) kor,
decode(grouping(name),
1,max(name) keep (dense_rank last order by eng),
max(eng)) eng,
decode(grouping(name),
1,max(name) keep (dense_rank last order by math),
max(math)) math
from t
group by grouping sets
(
(class,name),
(class),
()
)
by v상이v
[2010.11.26 15:12:27]
WITH T AS
(
SELECT '1' CLASS,'A' NAME, 80 KOR, 75 ENG, 80 MATH FROM DUAL UNION ALL
SELECT '1' CLASS,'B' NAME, 90 KOR, 80 ENG, 85 MATH FROM DUAL UNION ALL
SELECT '1' CLASS,'C' NAME, 80 KOR, 95 ENG, 70 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'D' NAME, 70 KOR, 85 ENG, 75 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'E' NAME, 95 KOR, 80 ENG, 90 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'F' NAME, 60 KOR, 70 ENG, 70 MATH FROM DUAL
)
SELECT CLASS
,DECODE(GROUPING(CLASS),0,NVL(NAME,'반최고득점자'),'최고득점자') AS NAME
,DECODE(GROUPING(NAME),0,TO_CHAR(KOR),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY KOR)) AS KOR
,DECODE(GROUPING(NAME),0,TO_CHAR(ENG),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY ENG)) AS ENG
,DECODE(GROUPING(NAME),0,TO_CHAR(MATH),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY MATH)) AS MATH
FROM T
GROUP BY ROLLUP(CLASS,(NAME,KOR,ENG,MATH))
ORDER BY 1,2
(
SELECT '1' CLASS,'A' NAME, 80 KOR, 75 ENG, 80 MATH FROM DUAL UNION ALL
SELECT '1' CLASS,'B' NAME, 90 KOR, 80 ENG, 85 MATH FROM DUAL UNION ALL
SELECT '1' CLASS,'C' NAME, 80 KOR, 95 ENG, 70 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'D' NAME, 70 KOR, 85 ENG, 75 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'E' NAME, 95 KOR, 80 ENG, 90 MATH FROM DUAL UNION ALL
SELECT '2' CLASS,'F' NAME, 60 KOR, 70 ENG, 70 MATH FROM DUAL
)
SELECT CLASS
,DECODE(GROUPING(CLASS),0,NVL(NAME,'반최고득점자'),'최고득점자') AS NAME
,DECODE(GROUPING(NAME),0,TO_CHAR(KOR),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY KOR)) AS KOR
,DECODE(GROUPING(NAME),0,TO_CHAR(ENG),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY ENG)) AS ENG
,DECODE(GROUPING(NAME),0,TO_CHAR(MATH),1,MAX(NAME) KEEP(DENSE_RANK LAST ORDER BY MATH)) AS MATH
FROM T
GROUP BY ROLLUP(CLASS,(NAME,KOR,ENG,MATH))
ORDER BY 1,2
by .
[2010.11.26 15:18:06]
상이님 정답입니다.^^
by 초보의한계
[2010.11.26 15:25:20]
SELECT A.CLASS
, A.NAME
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.KOR = X.KOR)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.KOR = A.KOR)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.KOR = A.KOR)
ELSE TO_CHAR(A.KOR)
END KOR
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.ENG = X.ENG)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.ENG = A.ENG)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.ENG = A.ENG)
ELSE TO_CHAR(A.ENG)
END ENG
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.MATH = X.MATH)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.MATH = A.MATH)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.MATH = A.MATH)
ELSE TO_CHAR(A.MATH)
END MATH
FROM (
SELECT CLASS
, DECODE(GROUPING_ID(CLASS, NAME), 1, '반 최고득점자'
, 3, '최고득점자'
, NAME) NAME
, MAX(KOR) KOR
, MAX(ENG) ENG
, MAX(MATH) MATH
, GROUPING_ID(CLASS, NAME) GRP_ID
FROM T
GROUP BY ROLLUP(CLASS, NAME)
) A
ORDER BY A.CLASS, A.NAME
, A.NAME
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.KOR = X.KOR)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.KOR = A.KOR)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.KOR = A.KOR)
ELSE TO_CHAR(A.KOR)
END KOR
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.ENG = X.ENG)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.ENG = A.ENG)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.ENG = A.ENG)
ELSE TO_CHAR(A.ENG)
END ENG
, CASE WHEN A.GRP_ID = 1 AND 0 < (SELECT COUNT(*) FROM T X WHERE A.CLASS = X.CLASS AND A.MATH = X.MATH)
THEN (SELECT T.NAME FROM T T WHERE T.CLASS = A.CLASS AND T.MATH = A.MATH)
WHEN A.GRP_ID = 3 THEN (SELECT NAME FROM T Y WHERE Y.MATH = A.MATH)
ELSE TO_CHAR(A.MATH)
END MATH
FROM (
SELECT CLASS
, DECODE(GROUPING_ID(CLASS, NAME), 1, '반 최고득점자'
, 3, '최고득점자'
, NAME) NAME
, MAX(KOR) KOR
, MAX(ENG) ENG
, MAX(MATH) MATH
, GROUPING_ID(CLASS, NAME) GRP_ID
FROM T
GROUP BY ROLLUP(CLASS, NAME)
) A
ORDER BY A.CLASS, A.NAME
by v상이v
[2010.11.26 15:27:31]
그러고 보니...이거
예전에 글쓴분께서 내주셨던 문제의 응용...이네요...^^;;
늘 감사히 풀고 있습니다~~
예전에 글쓴분께서 내주셨던 문제의 응용...이네요...^^;;
늘 감사히 풀고 있습니다~~
by .
[2010.11.26 15:30:06]
어쩐지...
문제 내면서도 익숙하다 했는데 ㅡ.ㅡ
치매가 오려나봐요ㅜ,ㅜ
문제 내면서도 익숙하다 했는데 ㅡ.ㅡ
치매가 오려나봐요ㅜ,ㅜ
by .
[2010.11.26 15:38:08]
초보의한계님.
keep없이 푸시다니 대단합니다..
정답입니다.^^
keep없이 푸시다니 대단합니다..
정답입니다.^^
by 마농
[2010.11.26 15:39:27]
-- 조금 색다르게~
SELECT class
, DECODE(GROUPING(class), 0, NVL(name, '반최고득점자'), '최고득점자') name
, LTRIM(SUBSTR(MAX(LPAD(kor , 3, '0')||name), GROUPING(name)*3+1, 3), '0') kor
, LTRIM(SUBSTR(MAX(LPAD(eng , 3, '0')||name), GROUPING(name)*3+1, 3), '0') eng
, LTRIM(SUBSTR(MAX(LPAD(math, 3, '0')||name), GROUPING(name)*3+1, 3), '0') math
FROM t
GROUP BY ROLLUP(class, name)
;
SELECT class
, DECODE(GROUPING(class), 0, NVL(name, '반최고득점자'), '최고득점자') name
, LTRIM(SUBSTR(MAX(LPAD(kor , 3, '0')||name), GROUPING(name)*3+1, 3), '0') kor
, LTRIM(SUBSTR(MAX(LPAD(eng , 3, '0')||name), GROUPING(name)*3+1, 3), '0') eng
, LTRIM(SUBSTR(MAX(LPAD(math, 3, '0')||name), GROUPING(name)*3+1, 3), '0') math
FROM t
GROUP BY ROLLUP(class, name)
;
by 마농
[2010.11.26 15:42:01]
-- 최고득점이 동순위 일경우까지 감안한다면...
SELECT class
, DECODE(GROUPING(class), 0, NVL(name, '반최고득점자'), '최고득점자') name
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY kor ), kor ) kor
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY eng ), eng ) eng
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY math), math) math
FROM t
GROUP BY ROLLUP(class, (name, kor, eng, math))
;
SELECT class
, DECODE(GROUPING(class), 0, NVL(name, '반최고득점자'), '최고득점자') name
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY kor ), kor ) kor
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY eng ), eng ) eng
, DECODE(GROUPING(name), 1, wm_concat(name) KEEP(DENSE_RANK LAST ORDER BY math), math) math
FROM t
GROUP BY ROLLUP(class, (name, kor, eng, math))
;
by .
[2010.11.26 15:53:45]
점수에 이름을 붙여서 max를 구하시다니... 새로운 시도네요.
마농님 정답입니다^^
마농님 정답입니다^^
by .
[2010.11.26 16:14:34]
초보의한계님 쿼리를 다시 살펴보니 실수하신게 있네요.
서브쿼리 (SELECT NAME FROM T Y WHERE Y.KOR = A.KOR)에서 다른class에 동일한 이름을 가진 사람이 있으면 두건이상이 리턴되네요.조건절에 class도 추가하시면 되구요. 그리고 최고득점자를 구할때 서브쿼리를 사용하시면 인원이 증가할때 성능도 떨어진다는걸 알아주셨으면 합니다.상이님 방법이나 마농님쿼리를 참고해주세요.
서브쿼리 (SELECT NAME FROM T Y WHERE Y.KOR = A.KOR)에서 다른class에 동일한 이름을 가진 사람이 있으면 두건이상이 리턴되네요.조건절에 class도 추가하시면 되구요. 그리고 최고득점자를 구할때 서브쿼리를 사용하시면 인원이 증가할때 성능도 떨어진다는걸 알아주셨으면 합니다.상이님 방법이나 마농님쿼리를 참고해주세요.
by 초보의한계
[2010.11.26 18:27:43]
많이 배우고갑니다~ ^^
제가 허접하지만 다시한번 해봤는데요... 평가좀 부탁드립니다...
SELECT A.CLASS
, A.NAME
, WM_CONCAT(DISTINCT A.KOR) KOR
, WM_CONCAT(DISTINCT A.ENG) ENG
, WM_CONCAT(DISTINCT A.MATH) MATH
FROM (
SELECT A.CLASS
, CASE WHEN A.GRP_ID = 1 THEN '반 최고득점자' WHEN A.GRP_ID = 3 THEN '최고득점자' ELSE A.NAME END NAME
, CASE WHEN A.GRP_ID = 1 THEN B.NAME WHEN A.GRP_ID = 3 THEN B1.NAME ELSE TO_CHAR(A.KOR) END KOR
, CASE WHEN A.GRP_ID = 1 THEN C.NAME WHEN A.GRP_ID = 3 THEN C1.NAME ELSE TO_CHAR(A.ENG) END ENG
, CASE WHEN A.GRP_ID = 1 THEN D.NAME WHEN A.GRP_ID = 3 THEN D1.NAME ELSE TO_CHAR(A.MATH) END MATH
FROM (
SELECT CLASS
, DECODE(GROUPING_ID(CLASS, NAME), 1, '반 최고득점자'
, 3, '최고득점자'
, NAME) NAME
, MAX(KOR) KOR
, MAX(ENG) ENG
, MAX(MATH) MATH
, GROUPING_ID(CLASS, NAME) GRP_ID
FROM T
GROUP BY ROLLUP(CLASS, NAME)
) A
LEFT OUTER JOIN T B
ON A.CLASS = B.CLASS
AND A.KOR = B.KOR
LEFT OUTER JOIN T C
ON A.CLASS = C.CLASS
AND A.ENG = C.ENG
LEFT OUTER JOIN T D
ON A.CLASS = D.CLASS
AND A.MATH = D.MATH
LEFT OUTER JOIN T B1
ON A.KOR = B1.KOR
LEFT OUTER JOIN T C1
ON A.ENG = C1.ENG
LEFT OUTER JOIN T D1
ON A.MATH = D1.MATH
ORDER BY A.CLASS, A.NAME
) A
GROUP BY A.CLASS, A.NAME
ORDER BY A.CLASS, A.NAME
제가 허접하지만 다시한번 해봤는데요... 평가좀 부탁드립니다...
SELECT A.CLASS
, A.NAME
, WM_CONCAT(DISTINCT A.KOR) KOR
, WM_CONCAT(DISTINCT A.ENG) ENG
, WM_CONCAT(DISTINCT A.MATH) MATH
FROM (
SELECT A.CLASS
, CASE WHEN A.GRP_ID = 1 THEN '반 최고득점자' WHEN A.GRP_ID = 3 THEN '최고득점자' ELSE A.NAME END NAME
, CASE WHEN A.GRP_ID = 1 THEN B.NAME WHEN A.GRP_ID = 3 THEN B1.NAME ELSE TO_CHAR(A.KOR) END KOR
, CASE WHEN A.GRP_ID = 1 THEN C.NAME WHEN A.GRP_ID = 3 THEN C1.NAME ELSE TO_CHAR(A.ENG) END ENG
, CASE WHEN A.GRP_ID = 1 THEN D.NAME WHEN A.GRP_ID = 3 THEN D1.NAME ELSE TO_CHAR(A.MATH) END MATH
FROM (
SELECT CLASS
, DECODE(GROUPING_ID(CLASS, NAME), 1, '반 최고득점자'
, 3, '최고득점자'
, NAME) NAME
, MAX(KOR) KOR
, MAX(ENG) ENG
, MAX(MATH) MATH
, GROUPING_ID(CLASS, NAME) GRP_ID
FROM T
GROUP BY ROLLUP(CLASS, NAME)
) A
LEFT OUTER JOIN T B
ON A.CLASS = B.CLASS
AND A.KOR = B.KOR
LEFT OUTER JOIN T C
ON A.CLASS = C.CLASS
AND A.ENG = C.ENG
LEFT OUTER JOIN T D
ON A.CLASS = D.CLASS
AND A.MATH = D.MATH
LEFT OUTER JOIN T B1
ON A.KOR = B1.KOR
LEFT OUTER JOIN T C1
ON A.ENG = C1.ENG
LEFT OUTER JOIN T D1
ON A.MATH = D1.MATH
ORDER BY A.CLASS, A.NAME
) A
GROUP BY A.CLASS, A.NAME
ORDER BY A.CLASS, A.NAME
by .
[2010.11.26 23:42:15]
반최고득점자와 최고득점자를 구하기 위해서 과목수x2만큼 조인을 하셨네요
과목수가 늘어나는만큼 두배씩 조인갯수가 늘어나겠군요..
실상황이라면 사용하지 못하는 쿼리지만 서브쿼리를 사용하지 않고 새로운 방법으로 푸셨네요^^
두 방법다 좋은 방법은 아닙니다만. 여러가지 방법을 알아두는건 좋습니다.
과목수가 늘어나는만큼 두배씩 조인갯수가 늘어나겠군요..
실상황이라면 사용하지 못하는 쿼리지만 서브쿼리를 사용하지 않고 새로운 방법으로 푸셨네요^^
두 방법다 좋은 방법은 아닙니다만. 여러가지 방법을 알아두는건 좋습니다.
by 호야
[2010.12.01 14:38:56]
-0-;; 아닌가..
select CLASS,DECODE(GROUPING(CLASS),1,'최고득점자',NVL(NAME,'반최고득점자')) name
,MAX(name) KEEP (DENSE_RANK LAst ORDER by kor ) kor
,MAX(name) KEEP (DENSE_RANK LAst ORDER by eng ) eng
,MAX(name) KEEP (DENSE_RANK LAst ORDER by math ) math
from t
GROUP BY ROLLUP(CLASS,name)
select CLASS,DECODE(GROUPING(CLASS),1,'최고득점자',NVL(NAME,'반최고득점자')) name
,MAX(name) KEEP (DENSE_RANK LAst ORDER by kor ) kor
,MAX(name) KEEP (DENSE_RANK LAst ORDER by eng ) eng
,MAX(name) KEEP (DENSE_RANK LAst ORDER by math ) math
from t
GROUP BY ROLLUP(CLASS,name)
by 호야
[2010.12.01 14:40:46]
중간에 grouping 이 들어 가야 되는군용..;;; 어려워..ㅋㅋㅋㅋ
by 쫑
[2012.02.01 12:00:16]
호야님 처럼 풀면 점수가 안나오던데요ㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.