데이터베이스 질문과 답변

쿼리문 질문드립니다.(특정값 이상일때 연산하기) 1 6 1,024

by 성공한다 [Oracle 기초] [2018.11.13 16:44:30]

안녕하세요 .

성적합산 프로그램을 만들고 싶은데..

특정 인원(심사위원)수 이상이면 연산하는것을 넣고 싶습니다.

select no,name,simsa_name,q_1,q_2,q_3

from simsa

no(응시번호),name(지원자성명),simsa_name(심사위원성명),hard_q_1(창의성),hard_q_2(정직성),hard_q_3(체계성)

조회하면 이렇게 출력이 됩니다.

응시번호 지원자성명 심사위원 창의성 정직성 체계성
1 홍길동 쯔위 7 7 7
1 홍길동 채영 7 9 8
1 홍길동 다현 7 7 7
1 홍길동 지효 7 8 7
1 홍길동 나연 9 7 8

응시번호와 지원자 성명 중복을제거하고 한줄로 출력을하게되면

SELECT  no
               , name
               , MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
               , MAX(CASE WHEN RN = 1 THEN hard_q_1 END ) hard_q_1
               , MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
               , MAX(CASE WHEN RN = 1 THEN hard_q_2 END ) hard_q_2
               , MAX(CASE WHEN RN = 1 THEN simsa_name END )simsa_name
               , MAX(CASE WHEN RN = 1 THEN hard_q_3 END ) hard_q_3
               , MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 2 THEN hard_q_1 END ) hard_q_11
               , MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 2 THEN hard_q_2 END ) hard_q_21
               , MAX(CASE WHEN RN = 2 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 2 THEN hard_q_3 END ) hard_q_31  
               , MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 3 THEN hard_q_1 END  ) hard_q_12   
               , MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 3 THEN hard_q_2 END  ) hard_q_22
               , MAX(CASE WHEN RN = 3 THEN simsa_name END ) simsa_name
               , MAX(CASE WHEN RN = 3 THEN hard_q_3 END  ) hard_q_32

           FROM (SELECT no,name , hard_q_1 ,hard_q_2,hard_q_3, ROW_NUMBER() OVER(PARTITION BY no ORDER BY ROWNUM) RN
           FROM simsa 
       
         )
       GROUP BY no, kname

;

출력물이 이렇게 나옵니다.

응시번호 지원자성명 심사위원 창의성 정직성 체계성 심사위원 창의성 정직성 체계성 심사위원 창의성 정직성 체계성 심사위원 창의성 정직성 체계성 심사위원 창의성 정직성 체계성
1 홍길동 쯔위 7 7 7 채영 7 9 8 다현 7 7 7 지효 7 8 7 나연 9 7 8

여기서 심사위원 수에 따라서 계,평균을 구하고싶은데

응시번호 지원자성명 항목 심사위원판정 계 평균 최종평균합계
쯔위 채영 다현 지효 나연
     1     홍길동 창의성 7점 7점 7점 7점 9점 21점 7점         7.3점
정직성 7점 9점 7점 8점 7점 23점 7.6점
체계성 7점 8점 7점 7점 8점 22점 7.3점

이렇게 심사위원수가 5명이상일경우(쯔위,채영,다현,지효,나연)에는 항목별로 최고점과 최하점을 제외하고 계(합계), 평균   --> 창의성 : 7점,7점,7점,7점,9점 이기때문에 7점(최하점),9점(최고점)제외

창의성 : 계(7+7+7)=21 평균(7+7+7)/3= 7

정직성 : 7점,9점,7점,8점,7점이기때문에 7점(최하점), 9점(최고점)제외

정직성 : 계(7+7+8)=23 평균(7+7+8)/3 = 7.6

 

 

그러나 만약 5명 미만일경우 계,평균은 최고점과,최하점을 제외하지 않고 심사위원명단에 따라서 합계와 평균

응시번호 지원자성명 항목 심사위원판정 계 평균 최종평균합계
쯔위 채영 다현
     1   홍길동 창의성 7점 7점 7점 21점 7점         7.3점
정직성 7점 9점 7점 23점 7.6점
체계성 7점 8점 7점 22점 7.3점

심사위원수가 쯔위,채영,다현(3명) 5명미만이기때문에 최고점과,최하점을 제외하지않고

창의성 : 7+9+7 = 23(계) , 21/3 = 7(평균)

 

 

감사합니다.

by 마농 [2018.11.13 17:56:07] 
WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS
(
          SELECT 1, '미나', '쯔위', 7, 7, 7 FROM dual
UNION ALL SELECT 1, '미나', '채영', 7, 9, 8 FROM dual
UNION ALL SELECT 1, '미나', '다현', 7, 7, 7 FROM dual
UNION ALL SELECT 1, '미나', '지효', 7, 8, 7 FROM dual
UNION ALL SELECT 1, '미나', '나연', 9, 7, 8 FROM dual
--UNION ALL SELECT 1, '미나', '정연', 9, 7, 8 FROM dual
--UNION ALL SELECT 1, '미나', '사나', 9, 7, 8 FROM dual
)
SELECT no
     , name
     , DECODE(gb, 'Q1', '창의성', 'Q2', '정직성', 'Q3', '체계성') gb
     , NVL(q1||'', nm1) q1
     , NVL(q2||'', nm2) q2
     , NVL(q3||'', nm3) q3
     , NVL(q4||'', nm4) q4
     , NVL(q5||'', nm5) q5
     , NVL(q6||'', nm6) q6
     , NVL(q7||'', nm7) q7
     , SUM(s) s
     , ROUND(SUM(s) / SUM(cnt), 2) a
  FROM (SELECT no, name, gb
             , MIN(DECODE(rn, 1, nm)) nm1, MIN(DECODE(rn, 1, q)) q1
             , MIN(DECODE(rn, 2, nm)) nm2, MIN(DECODE(rn, 2, q)) q2
             , MIN(DECODE(rn, 3, nm)) nm3, MIN(DECODE(rn, 3, q)) q3
             , MIN(DECODE(rn, 4, nm)) nm4, MIN(DECODE(rn, 4, q)) q4
             , MIN(DECODE(rn, 5, nm)) nm5, MIN(DECODE(rn, 5, q)) q5
             , MIN(DECODE(rn, 6, nm)) nm6, MIN(DECODE(rn, 6, q)) q6
             , MIN(DECODE(rn, 7, nm)) nm7, MIN(DECODE(rn, 7, q)) q7
             , SUM(q)   - CASE WHEN COUNT(*) >= 5 THEN MIN(q) + MAX(q) ELSE 0 END s
             , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN               2 ELSE 0 END cnt
          FROM (SELECT no
                     , name
                     , simsa_name nm
                     , hard_q_1 q1
                     , hard_q_2 q2
                     , hard_q_3 q3
                     , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn
                  FROM simsa
                )
         UNPIVOT (q FOR gb IN (q1, q2, q3))
         GROUP BY no, name, gb
        ) a
 GROUP BY no, name
     , nm1, nm2, nm3, nm4, nm5, nm6, nm7
     , ROLLUP((gb, q1, q2, q3, q4, q5, q6, q7))
 ORDER BY no, a.gb NULLS FIRST
;

 

by 성공한다 [2018.11.14 13:47:50]

답변 정말 정말 감사합니다..!

밑에처럼 한줄로 출력을 하고싶습니다.. 아 그리고 마지막 평균의합계는 평균의 평균이 아니고 항목별 평균의 합계입니당

응시번호 지원자성명 심사위원 창의성 심사위원 정직성 심사위원 체계성 심사위원 창의성 심사위원 정직성 심사위원 체계성 심사위원 창의성 심사위원 정직성 심사위원 체계성 심사위원 창의성 심사위원 정직성 심사위원 체계성 심사위원 창의성 심사위원 정직성 심사위원 체계성 창의성계 정직성계 체계성계 창의성평균 정직성 평균 체계성평균 평균의합계
1 미나 쯔위 7 쯔위 7 쯔위 7 채영 7 채영 9 채영 8 다현 7 다현 7 다현 7 지효 7 지효 8 지효 7 나연 9 나연 7 나연 8 21 22 22 7 7.33 7.33 21.66
                                                                             

감사합니다..!

by 마농 [2018.11.14 14:50:43]

심사위원 이름을 굳이 3번 표시할 필요가 없어 한번만 표시했습니다.

WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS
(
          SELECT 1, '미나', '쯔위', 7, 7, 7 FROM dual
UNION ALL SELECT 1, '미나', '채영', 7, 9, 8 FROM dual
UNION ALL SELECT 1, '미나', '다현', 7, 7, 7 FROM dual
UNION ALL SELECT 1, '미나', '지효', 7, 8, 7 FROM dual
UNION ALL SELECT 1, '미나', '나연', 9, 7, 8 FROM dual
--UNION ALL SELECT 1, '미나', '정연', 9, 7, 8 FROM dual
--UNION ALL SELECT 1, '미나', '사나', 9, 7, 8 FROM dual
)
SELECT no, name
     , nm_1, q1_1, q2_1, q3_1
     , nm_2, q1_2, q2_2, q3_2
     , nm_3, q1_3, q2_3, q3_3
     , nm_4, q1_4, q2_4, q3_4
     , nm_5, q1_5, q2_5, q3_5
     , nm_6, q1_6, q2_6, q3_6
     , nm_7, q1_7, q2_7, q3_7
     , sum_q1
     , sum_q2
     , sum_q3
     , ROUND(sum_q1 / cnt, 2) avg_q1
     , ROUND(sum_q2 / cnt, 2) avg_q2
     , ROUND(sum_q3 / cnt, 2) avg_q3
     , ROUND(sum_q1 / cnt, 2) +
       ROUND(sum_q2 / cnt, 2) +
       ROUND(sum_q3 / cnt, 2) avg_sum
  FROM (SELECT no, name
             , MIN(DECODE(rn, 1, nm)) nm_1
             , MIN(DECODE(rn, 1, q1)) q1_1
             , MIN(DECODE(rn, 1, q2)) q2_1
             , MIN(DECODE(rn, 1, q3)) q3_1
             , MIN(DECODE(rn, 2, nm)) nm_2
             , MIN(DECODE(rn, 2, q1)) q1_2
             , MIN(DECODE(rn, 2, q2)) q2_2
             , MIN(DECODE(rn, 2, q3)) q3_2
             , MIN(DECODE(rn, 3, nm)) nm_3
             , MIN(DECODE(rn, 3, q1)) q1_3
             , MIN(DECODE(rn, 3, q2)) q2_3
             , MIN(DECODE(rn, 3, q3)) q3_3
             , MIN(DECODE(rn, 4, nm)) nm_4
             , MIN(DECODE(rn, 4, q1)) q1_4
             , MIN(DECODE(rn, 4, q2)) q2_4
             , MIN(DECODE(rn, 4, q3)) q3_4
             , MIN(DECODE(rn, 5, nm)) nm_5
             , MIN(DECODE(rn, 5, q1)) q1_5
             , MIN(DECODE(rn, 5, q2)) q2_5
             , MIN(DECODE(rn, 5, q3)) q3_5
             , MIN(DECODE(rn, 6, nm)) nm_6
             , MIN(DECODE(rn, 6, q1)) q1_6
             , MIN(DECODE(rn, 6, q2)) q2_6
             , MIN(DECODE(rn, 6, q3)) q3_6
             , MIN(DECODE(rn, 7, nm)) nm_7
             , MIN(DECODE(rn, 7, q1)) q1_7
             , MIN(DECODE(rn, 7, q2)) q2_7
             , MIN(DECODE(rn, 7, q3)) q3_7
             , SUM(q1)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q1) + MAX(q1) ELSE 0 END sum_q1
             , SUM(q2)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q2) + MAX(q2) ELSE 0 END sum_q2
             , SUM(q3)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q3) + MAX(q3) ELSE 0 END sum_q3
             , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN 2 ELSE 0 END cnt
          FROM (SELECT no
                     , name
                     , simsa_name nm
                     , hard_q_1 q1
                     , hard_q_2 q2
                     , hard_q_3 q3
                     , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn
                  FROM simsa
                )
         GROUP BY no, name
        )
;

 

by 성공한다 [2018.11.16 09:33:27]

답변감사합니다,.,..!

제 테이블에 적용해보니 10이상일때의 점수가 있을때는 계산이 잘 안되는거 같은데 ..

데이터타입이..제가 vachar2로 해서 연산이 안되는걸까요..? number타입을 해야하는데.. 아무생각없이 vachar2로 했네요ㅠ

by 마농 [2018.11.16 15:04:42]

숫자는 9 보다 10 이 크지만, 문자는 '9' 가 '10' 보다 더 큽니다. '2' 도 '10' 보다 크죠.
컬럼형을 바꿀 수 있으면 지금이라도 바꾸세요.
그냥 하려면 to_number 로 형변환 시켜 사용하세요.

WITH simsa(no, name, simsa_name, hard_q_1, hard_q_2, hard_q_3) AS
(
          SELECT 1, '미나', '쯔위', '7', '7', '7' FROM dual
UNION ALL SELECT 1, '미나', '채영', '7', '9', '8' FROM dual
UNION ALL SELECT 1, '미나', '다현', '7', '7', '7' FROM dual
UNION ALL SELECT 1, '미나', '지효', '7', '8', '7' FROM dual
UNION ALL SELECT 1, '미나', '나연', '9', '7', '8' FROM dual
--UNION ALL SELECT 1, '미나', '정연', '9', '7', '8' FROM dual
--UNION ALL SELECT 1, '미나', '사나', '9', '7', '8' FROM dual
)
SELECT no, name
     , nm_1, q1_1, q2_1, q3_1
     , nm_2, q1_2, q2_2, q3_2
     , nm_3, q1_3, q2_3, q3_3
     , nm_4, q1_4, q2_4, q3_4
     , nm_5, q1_5, q2_5, q3_5
     , nm_6, q1_6, q2_6, q3_6
     , nm_7, q1_7, q2_7, q3_7
     , sum_q1
     , sum_q2
     , sum_q3
     , ROUND(sum_q1 / cnt, 2) avg_q1
     , ROUND(sum_q2 / cnt, 2) avg_q2
     , ROUND(sum_q3 / cnt, 2) avg_q3
     , ROUND(sum_q1 / cnt, 2) +
       ROUND(sum_q2 / cnt, 2) +
       ROUND(sum_q3 / cnt, 2) avg_sum
  FROM (SELECT no, name
             , MIN(DECODE(rn, 1, nm)) nm_1
             , MIN(DECODE(rn, 1, q1)) q1_1
             , MIN(DECODE(rn, 1, q2)) q2_1
             , MIN(DECODE(rn, 1, q3)) q3_1
             , MIN(DECODE(rn, 2, nm)) nm_2
             , MIN(DECODE(rn, 2, q1)) q1_2
             , MIN(DECODE(rn, 2, q2)) q2_2
             , MIN(DECODE(rn, 2, q3)) q3_2
             , MIN(DECODE(rn, 3, nm)) nm_3
             , MIN(DECODE(rn, 3, q1)) q1_3
             , MIN(DECODE(rn, 3, q2)) q2_3
             , MIN(DECODE(rn, 3, q3)) q3_3
             , MIN(DECODE(rn, 4, nm)) nm_4
             , MIN(DECODE(rn, 4, q1)) q1_4
             , MIN(DECODE(rn, 4, q2)) q2_4
             , MIN(DECODE(rn, 4, q3)) q3_4
             , MIN(DECODE(rn, 5, nm)) nm_5
             , MIN(DECODE(rn, 5, q1)) q1_5
             , MIN(DECODE(rn, 5, q2)) q2_5
             , MIN(DECODE(rn, 5, q3)) q3_5
             , MIN(DECODE(rn, 6, nm)) nm_6
             , MIN(DECODE(rn, 6, q1)) q1_6
             , MIN(DECODE(rn, 6, q2)) q2_6
             , MIN(DECODE(rn, 6, q3)) q3_6
             , MIN(DECODE(rn, 7, nm)) nm_7
             , MIN(DECODE(rn, 7, q1)) q1_7
             , MIN(DECODE(rn, 7, q2)) q2_7
             , MIN(DECODE(rn, 7, q3)) q3_7
             , SUM(q1)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q1) + MAX(q1) ELSE 0 END sum_q1
             , SUM(q2)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q2) + MAX(q2) ELSE 0 END sum_q2
             , SUM(q3)  - CASE WHEN COUNT(*) >= 5 THEN MIN(q3) + MAX(q3) ELSE 0 END sum_q3
             , COUNT(*) - CASE WHEN COUNT(*) >= 5 THEN 2 ELSE 0 END cnt
          FROM (SELECT no
                     , name
                     , simsa_name nm
                     , TO_NUMBER(hard_q_1) q1    -- 요기
                     , TO_NUMBER(hard_q_2) q2    -- 요기
                     , TO_NUMBER(hard_q_3) q3    -- 요기
                     , ROW_NUMBER() OVER(PARTITION BY no ORDER BY 1) rn
                  FROM simsa
                )
         GROUP BY no, name
        )
;

 

by 성공한다 [2018.11.22 09:44:01]

감사합니다. 데이터타입을 number로 바꿔야겠네요 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입