데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 시험실 좌석배치도 V1 V2 V3 V4 V5 V1 V2 V3 V4 V5 V1 V2 V3 V4 V5 1 6 11 16 21 1 6 10 14 18 1 7 13 18 23 2 7 12 17 22 2 7 11 15 19 2 8 14 19 24 3 8 13 18 23 3 8 12 16 20 3 9 15 20 25 4 9 14 19 24 4 9 13 17 21 4 10 16 21 26 5 10 15 20 25 5 5 11 17 22 27 6 12
시험장은 25명씩 입장하도록 되어 있습니다.
좌석배치의 원리는 왼쪽 좌석부터 앞에서 뒤로 채우며 5줄의 좌석을 채웁니다.
25명이 시험을 치를 경우 다음과 같은 좌석배치도가 나오게 됩니다.
그러나 25명씩 시험실에 배치하다 보면 마지막 시험장의 인원은 25명이 정확하게 떨어지질 않습니다.
또한 마지막 인원이 5명 이하인 경우엔 이 인원을 전 교실에서 같이 시험을 치루도록 합니다.
이 경우 인원은 최대 30명까지가 되겠네요.
[문제] 시험실 배정인원(6 ~ 30)을 입력받아 좌석배치도를 출력하는 쿼리를 작성하세요.
[결과]
[답안1-분석함수버전] <== 트리플클릭
SELECT MIN(DECODE(x,1,v)) v1
, MIN(DECODE(x,2,v)) v2
, MIN(DECODE(x,3,v)) v3
, MIN(DECODE(x,4,v)) v4
, MIN(DECODE(x,5,v)) v5
FROM (SELECT v, x
, ROW_NUMBER() OVER(PARTITION BY x ORDER BY v) y
FROM (SELECT v
, NTILE(5) OVER(ORDER BY v) x
FROM (SELECT LEVEL v FROM dual CONNECT BY LEVEL <= :Inwon)
)
)
GROUP BY y
ORDER BY 1
;
[답안2-일반버전] <== 트리플클릭
SELECT MIN(DECODE(x,1,v)) v1
, MIN(DECODE(x,2,v)) v2
, MIN(DECODE(x,3,v)) v3
, MIN(DECODE(x,4,v)) v4
, MIN(DECODE(x,5,v)) v5
FROM (SELECT v
, CASE WHEN v <= v_m * v_c
THEN CEIL(v / v_c)
ELSE CEIL((v - v_m) / v_f)
END x
, CASE WHEN v <= v_m * v_c
THEN REPLACE(MOD(v , v_c), 0, v_c)
ELSE REPLACE(MOD((v - v_m) , v_f), 0, v_f)
END y
FROM (SELECT LEVEL v
, MOD(:Inwon, 5) v_m
, CEIL(:Inwon / 5) v_c
, FLOOR(:Inwon / 5) v_f
FROM dual
CONNECT BY LEVEL <= :Inwon
)
)
GROUP BY y
ORDER BY y
;
FROM (
SELECT CASE WHEN NUM <= V1 THEN NUM END AS NUM1,
LEAD(CASE WHEN NUM <= V2 THEN NUM END, V1) OVER (ORDER BY NUM) AS NUM2,
LEAD(CASE WHEN NUM <= V3 THEN NUM END, V2) OVER (ORDER BY NUM) AS NUM3,
LEAD(CASE WHEN NUM <= V4 THEN NUM END, V3) OVER (ORDER BY NUM) AS NUM4,
LEAD(CASE WHEN NUM <= V5 THEN NUM END, V4) OVER (ORDER BY NUM) AS NUM5
FROM (
SELECT LEVEL NUM,
FLOOR(:NUM / 5) H,
MOD(:NUM, 5) W
FROM DUAL
CONNECT BY LEVEL <= :NUM
) X,
(
SELECT MAX(DECODE(LVL, 1, VAL)) V1,
MAX(DECODE(LVL, 2, VAL)) V2,
MAX(DECODE(LVL, 3, VAL)) V3,
MAX(DECODE(LVL, 4, VAL)) V4,
MAX(DECODE(LVL, 5, VAL)) V5
FROM (
SELECT LEVEL LVL,
CEIL(:NUM / 5) * LEAST(LEVEL, MOD(:NUM, 5)) +
FLOOR(:NUM / 5) * GREATEST(0, SIGN(LEVEL-MOD(:NUM, 5))) * (LEVEL-MOD(:NUM, 5)) VAL
FROM DUAL
CONNECT BY LEVEL <= 5
)
) Y
)
WHERE NUM1 IS NOT NULL;
ntile을 안쓰고 하려니 어렵네요...
분석함수 사용 답안 외에 일반함수 사용 답안 추가했습니다.
처음엔 Mod, Ceil, Floor등을 이용해 문제를 만들어 보려고 시도하다가
스스로도 문제가 잘 풀리지 않아 고민하던중 불현듯 Ntile이 떠오르는 바람에
문제 제출 도중에 답안이 급수정되었더랬습니다.
madcat님 답안을 보고 저도 다시 한번 도전해봤습니다. ㅎㅎ
마농님께서 산뜻하게 정리해주시네요^^
문제는 왼쪽좌석부터 앞에서 뒤로 채워진다고 하는데..
그림상에 먼저 채워지는 순서는 앞줄이 먼저지 왼쪽좌석이 먼저가 아닌듯한데..
V1이 V2보다 먼저 채워지니까 왼쪽좌석부터 채워진다는 표현을 썼구요
V1내에서는 1행부터 차례로 채워지므로 앞에서 뒤로라는 표현을 썼습니다.
7명이라면
1 3 5 6 7
2 4
11명이면
1 4 6 8 10
2 5 7 9 11
3
이게 맞나요? ^^
MIN(DECODE(cl,1, lv)) c1
,MIN(DECODE(cl,2, lv)) c2
,MIN(DECODE(cl,3, lv)) c3
,MIN(DECODE(cl,4, lv)) c4
,MIN(DECODE(cl,5, lv)) c5
FROM(
SELECT
level lv
,TRUNC(CEIL(level / 5) ) cl
,ROW_NUMBER() OVER(PARTITION BY TRUNC( (level - 1) / 5 ) ORDER BY level) rn
FROM dual
CONNECT BY level <= 25
)
GROUP BY rn
ORDER BY rn;
25명에 대해서만 도전해 봤습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.