데이터베이스 퀴즈 및 튜닝 문제

쿼리퀴즈 질문드려요 0 15 5,756

by 냐옹냐옹 [2013.12.09 18:50:59]

WITH TE AS ( 
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL

)
SELECT  *
FROM    TE

1. 각 ROW에서 최고점과 최저점은 제외하고 총 점수,평균점수를 구하려고합니다. 
2. ROW는 가변입니다ㅜㅜ

어렵네요... 도와주세요~
by 아발란체 [2013.12.09 23:01:51] 
 
--난독증이 있어서... ㅋ 완전 다른 풀이를 올렸을 수도 있습니다.
WITH TE AS ( 
 SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
 UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
 UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
 UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
 UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
 UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
 UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
SELECT 
 SUM(A) AS A, AVG(A) AS A_AVG,
 SUM(B) AS B, AVG(B) AS B_AVG,
 SUM(C) AS C, AVG(C) AS C_AVG,
 SUM(D) AS D, AVG(D) AS D_AVG,
 SUM(E) AS E, AVG(E) AS E_AVG,
 SUM(F) AS F, AVG(F) AS F_AVG,
 SUM(G) AS G, AVG(G) AS G_AVG,
 SUM(H) AS H, AVG(H) AS H_AVG
FROM (
 SELECT
 DECODE(A, MAX(A) OVER(), 0, A) AS A,
 DECODE(B, MAX(B) OVER(), 0, B) AS B,
 DECODE(C, MAX(C) OVER(), 0, C) AS C,
 DECODE(D, MAX(D) OVER(), 0, D) AS D,
 DECODE(E, MAX(E) OVER(), 0, E) AS E,
 DECODE(F, MAX(F) OVER(), 0, F) AS F,
 DECODE(G, MAX(G) OVER(), 0, G) AS G,
 DECODE(H, MAX(H) OVER(), 0, H) AS H
 FROM
 TE
)
by 냐옹냐옹 [2013.12.10 12:36:22]
제가 애매하게 올렸네요 감사합니다^^
by 마농 [2013.12.09 23:43:49] 
WITH te AS
( 
SELECT 9 a, 7 b, 1 c, 5 d, 4 e, 6 f, 7 g, 4 h FROM dual
UNION ALL SELECT 1, 7, 8, 7, 4, 3, 9, 2 FROM dual
UNION ALL SELECT 5, 9, 5, 6, 6, 6, 5, 5 FROM dual
UNION ALL SELECT 9, 7, 4, 2, 2, 7, 3, 3 FROM dual
UNION ALL SELECT 1, 8, 3, 6, 9, 5, 7, 5 FROM dual
UNION ALL SELECT 9, 7, 4, 5, 3, 4, 8, 2 FROM dual
UNION ALL SELECT 8, 9, 4, 5, 3, 2, 4, 5 FROM dual
)
SELECT a, b, c, d, e, f, g, h
     , ( a + b + c + d + e + f + g + h
       - GREATEST(a, b, c, d, e, f, g, h) -- 최대값 1건 제외
       - LEAST   (a, b, c, d, e, f, g, h) -- 최소값 1건 제외
       ) / 6 AS avg
 FROM te
;
by Oracler [2013.12.09 23:48:04]
6으로 나눠야 할 것 같습니다.
by 마농 [2013.12.10 00:29:33]
아하~ㅋ
수정 완료.^^
by 냐옹냐옹 [2013.12.10 12:37:11]
greatest , least 이런 아이들이 있었군요 감사합니다^^
by Oracler [2013.12.09 23:47:25]
row가 가변이란 뜻이 컬럼의 갯수가 달라질 수 있단 말씀인가요?
by 냐옹냐옹 [2013.12.10 12:39:08]
아녀 컬럼은 고정이구요 저 위 예에서는 union 하는 수가 달라질 수 있다는 애기요^^;
좀 확실하게 적었어야는데... 국어도 어렵네요;;
by 우리집아찌 [2013.12.10 13:10:09]

최대값이나 최소값이 여러개면요..
by 하치 [2013.12.18 14:02:13] 
 
-- 연습하고 갑니다~
WITH TE AS ( 
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
SELECT A, B, C, D, E, F, G, H,
    (A+ B+ C+ D+ E+ F+ G+ H
     - GREATEST(A, B, C, D, E, F, G, H)
     - LEAST(A, B, C, D, E, F, G, H)) result_sum,
    (A+ B+ C+ D+ E+ F+ G+ H
     - GREATEST(A, B, C, D, E, F, G, H)
     - LEAST(A, B, C, D, E, F, G, H)) / 6 result_avg  
 FROM TE
by 허승호 [2013.12.27 16:07:08]
다른 방식으로 해봤어요 ^^ 
 
WITH TE AS ( 
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
, TR AS ( 
SELECT RN, LV ,DECODE(LV ,1,A,2,B,3,C,4,D,5,E,6,F,7,G,8,H) VALUE	 --A.* , B.LV
 FROM 
 (SELECT ROWNUM RN, A.* FROM TE A) A 
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 8) B
ORDER BY RN,LV
)
SELECT regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 1) as A
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 2) as B
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 3) as C
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 4) as D
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 5) as E
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 6) as F
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 7) as G
	 ,regexp_substr( SUBSTR(XMLAgg(XMLElement(x, ',', VALUE) ORDER BY LV).Extract('//text()'), 2) , '[^,]+' , 1, 8) as H
	 ,(SUM(VALUE) - MIN(VALUE) - MAX(VALUE)) AS SUM
	 ,ROUND((SUM(VALUE) - MIN(VALUE) - MAX(VALUE))/6,2) AS AVG
 FROM TR GROUP BY RN
 ORDER BY RN
by BJ [2014.01.02 22:58:11]
많은 분들이 올려 주신 것 같지만.. 저도 처음으로 올려 봅니다 ^^ 
도움이 되셨으면 하네요 
GREATEST, LEAST 사용하시면 편해요~ 

WITH TE AS ( 
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
SELECT   ROUND((A+B+C+D+E+F+G+H - GREATEST(A,B,C,D,E,F,G,H) - LEAST(A,B,C,D,E,F,G,H))/6) AS AVG
        ,GREATEST(A,B,C,D,E,F,G,H) AS MAX
        ,LEAST(A,B,C,D,E,F,G,H) AS MIN
        ,A+B+C+D+E+F+G+H AS TOTAL
        ,TE.*
FROM    TE;
by 랩소디 [2014.01.27 14:54:35]


Q)
1. 각 ROW에서 최고점과 최저점은 제외하고 총 점수,평균점수를 구하려고합니다.
2. ROW는 가변입니다.

A)
보다보니 몇가지 제약, 전제 사항들이 있을 것 같기도 하네요.
예를 들어,
MAX값이 N개인 경우인 경우의 조치라든지...
동일한 여러개의 최대값 중에 한개만 뺄것인지 최대값을 모두 빼서 계산할 것인지...
뺀다고 하면 분모에서도 최대값의 개수만큼 적어져야 하는지... 등등
아뭏튼 사전에 요건 정의가 좀 더 되었다면 SQL 작성하기가 더 수월할듯 합니다. ^^;


WITH te AS
( 
SELECT 9 a, 7 b, 1 c, 5 d, 4 e, 6 f, 7 g, 4 h FROM dual
UNION ALL SELECT 1, 7, 8, 7, 4, 3, 9, 2 FROM dual
UNION ALL SELECT 5, 9, 5, 6, 9, 6, 5, 5 FROM dual
UNION ALL SELECT 9, 7, 4, 2, 2, 7, 3, 3 FROM dual
UNION ALL SELECT 1, 8, 9, 6, 9, 5, 7, 5 FROM dual
UNION ALL SELECT 9, 7, 4, 5, 3, 4, 8, 2 FROM dual
UNION ALL SELECT 8, 9, 4, 5, 3, 2, 4, 0 FROM dual
--예시) 아래는 Data특성을 고려한 예외사항 부분 (현재 Skip)
--UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1 FROM dual 
--UNION ALL SELECT 0, 0, 0, 0, 0, 0, 0, 0 FROM dual  
)
SELECT  A, B, C, D, E, F, G, H 
   ,(ALL_SUMS - (GREATEST_NUM*GREATESTS_CNT) - (NVL(LEAST_NUM,0)*LEASTS_CNT)) REAL_SUMS
   ,ROUND((ALL_SUMS - (GREATEST_NUM*GREATESTS_CNT) - (NVL(LEAST_NUM,0)*LEASTS_CNT))/(8),0) REAL_AVGS
  FROM (
    SELECT
   A,B,C,D,E,F,G,H
  ,GREATEST(A,B,C,D,E,F,G,H) GREATEST_NUM
  ,REGEXP_COUNT(A||':'||B||':'||C||':'||D||':'||E||':'||F||':'||G||':'||H, GREATEST(A,B,C,D,E,F,G,H), 1, 'i') GREATESTS_CNT
  ,LEAST(A,B,C,D,E,F,G,H) LEAST_NUM
  ,REGEXP_COUNT(A||':'||B||':'||C||':'||D||':'||E||':'||F||':'||G||':'||H, LEAST(A,B,C,D,E,F,G,H), 1, 'i') LEASTS_CNT
  ,(A+B+C+D+E+F+G+H) ALL_SUMS
  FROM TE
    ) X
;

 

by 박군two [2017.06.29 17:58:10] 
WITH TE(A,B,C,D,E,F,G,H) AS ( 
SELECT '9', '7', '1', '5', '4', '6', '7', '4' FROM DUAL UNION ALL 
SELECT '1', '7', '8', '7', '4', '3', '9', '2' FROM DUAL UNION ALL 
SELECT '5', '9', '5', '6', '6', '6', '5', '5' FROM DUAL UNION ALL 
SELECT '9', '7', '4', '2', '2', '7', '3', '3' FROM DUAL UNION ALL 
SELECT '1', '8', '3', '6', '9', '5', '7', '5' FROM DUAL UNION ALL 
SELECT '9', '7', '4', '5', '3', '4', '8', '2' FROM DUAL UNION ALL 
SELECT '8', '9', '4', '5', '3', '2', '4', '5' FROM DUAL

)
SELECT  A,B,C,D,E,F,G,H,
        GREATEST(A,B,C,D,E,F,G,H) AS 최고점,
        LEAST(A,B,C,D,E,F,G,H)    AS 최저점,
        A+B+C+D+E+F+G+H - GREATEST(A,B,C,D,E,F,G,H) - LEAST(A,B,C,D,E,F,G,H) AS 총점,
        (A+B+C+D+E+F+G+H - GREATEST(A,B,C,D,E,F,G,H) - LEAST(A,B,C,D,E,F,G,H))/6 AS 평균
FROM    TE
;

 

by 기본충실 [2018.01.23 17:35:06]

와 GREATEST 와 LEAST  함수도 최대값 최소값이 군요

MAX랑 MIN로 하려고햇는데...ㅋ

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