꿈꾸는 개발자, DBA 커뮤니티 구루비

조건을 순차적으로 체크하고, 그에 따른 값을 넣으려는데 도움 부탁드립니다.

by 박상준 [SQL Query] [2022.11.21 15:44:45]

VALUE 를 정하는 조건 (조건 테이블) 이 있고, 조건에 따라 데이터 테이블의 VALUE가 정해집니다.

DEFAULT 가 존재하고, 조건 테이블의 컬럼들을 순차 체크합니다.

케이스를 예시로 보자면

1. 먼저 이름을 체크하고, 이름이 박__ 이 아닌 경우 DEFAULT 인 값 5 를 받습니다.

2. 위 (1) 을 제외한
박__ 인 경우 그 다음 순서인 성별을 체크하고, 성별이 남 이 아닌경우 값 10 을 받습니다.

3. 위 (1), (2) 에 포함되지 않은
박__ 인 경우 그 다음 순서인 나이, 키, 몸무게를 순차적으로 검증합니다.

데이터 TABLE 생성 후 조건에 따라 데이터들에 값을 UPDATE 해주려고 합니다.

저 테이블을 활용해서 순차적으로 처리하고 제외하는 방법을 어떤식으로 접근해야할지 생각해봐도 감이 안와서 도움 요청드립니다..

RULE 이 맞는 값이 DATA의 값 에 들어가야합니다.. 원하는 값은 들어가야 하는 값을 적어두었습니다..

RULE 테이블 ( DEFAULT 값이 있고, ADD 되는 룰에 따라 값이 정해집니다. '-' 는 ALL 입니다. )

이름	성별	나이	키	몸무게	값
___	-	-	-	-	5	DEFAULT
박__	-	-	-	-	10	ADD
박__	남	-	-	-	20	ADD
박__	남	-	150	-	30	ADD

데이터 테이블 ( 이 테이블에 VALUE 를 RULE 에 맞는 값으로 UPDATE 하는것이 목적입니다. )

이름	성별	나이	키	몸무게	VALUE	원하는 VALUE
박길동	남	10	150	50	0	30
박네모	남	10	160	50	0	20
박하나	여	10	150	50	0	10
정길동	남	10	150	50	0	5

체크 컬럼 순서 테이블

컬럼	순서
이름	1
성별	2
나이	3
키	4
몸무게	5

WITH RULE AS (
    SELECT '___' AS 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 5  값, 'DEFAULT' INIT FROM DUAL 
    UNION ALL
    SELECT '박__' AS 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 10 값 , 'CHECK' INIT FROM DUAL
    UNION ALL
    SELECT '박__' AS 이름, '남' 성별, '-' 나이, '-' 키, '-' 몸무게, 20 값 , 'CHECK' INIT FROM DUAL
    UNION ALL
    SELECT '박__' AS 이름, '남' 성별, '-' 나이, '150' 키, '-' 몸무게, 30 값 , 'CHECK' INIT FROM DUAL
),
DATA AS (
    SELECT '박길동' AS 이름, '남' 성별, '10' 나이, '150' 키, '50' 몸무게, 0 값, '30' 원하는값 FROM DUAL
    UNION ALL                                                                         
    SELECT '박네모' AS 이름, '남' 성별, '10' 나이, '160' 키, '50' 몸무게, 0 값, '20' 원하는값 FROM DUAL
    UNION ALL                                                                         
    SELECT '박하나' AS 이름, '여' 성별, '10' 나이, '150' 키, '50' 몸무게, 0 값, '10' 원하는값 FROM DUAL
    UNION ALL                                                                         
    SELECT '정길동' AS 이름, '남' 성별, '10' 나이, '150' 키, '50' 몸무게, 0 값, '5'  원하는값 FROM DUAL
),
SEQ AS (
    SELECT '이름' 컬럼, 1 순서 FROM DUAL 
    UNION ALL
    SELECT '성별' 컬럼, 2 순서 FROM DUAL
    UNION ALL
    SELECT '나이' 컬럼, 3 순서 FROM DUAL
    UNION ALL
    SELECT '키' 컬럼, 4 순서 FROM DUAL
    UNION ALL
    SELECT '몸무게' 컬럼, 5 순서 FROM DUAL
)    
SELECT * FROM SEQ

by 마농 [2022.11.21 16:10:43]

박__ 에서 언더바 2개가 2글자를 의미하나요?
이름이 "박군" 인 경우 조건에 맞지 않는 것인가요?
순서는 이미 rule 에서 정해진 거 아닌가요? seq 테이블이 필요한지?

by 박상준 [2022.11.21 16:17:25]

안녕하세요 마농님~

1. 네 2개의 글자를 의미합니다.

2. 세 글자의 이름만 가능합니다.

3. RULE 의 컬럼 순서와 SEQ 테이블에서 컬럼 순서가 다릅니다.. 임시 작성한걸 같게 해두었네요..

SEQ 테이블의 SEQ 순서를 활용해서 컬럼 체크하는것이 목적입니다.

SEQ가 1인 컬럼을 찾아 RULE 확인, 일치하는것이 없으면 DEFAULT, 일치하는것이 있다면 2번 컬럼 비교,, 쭉쭉 순차적으루요..

by 마농 [2022.11.21 16:26:36]

박씨에 남자이면서 150 인 사람과
박씨에 150이면서 남자인 사람이 점수가 동일한것 아닌가요?
어떤 조합인가가 중요하지? 순서는 무의미해 보입니다.

by 마농 [2022.11.21 16:33:37]

WITH rule AS
(
SELECT '___' 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 5 값, 'DEFAULT' init FROM dual
UNION ALL SELECT '박__', '-' , '-', '-'  , '-', 10, 'CHECK' FROM dual
UNION ALL SELECT '박__', '남', '-', '-'  , '-', 20, 'CHECK' FROM dual
UNION ALL SELECT '박__', '남', '-', '150', '-', 30, 'CHECK' FROM dual
)
, data AS
(
SELECT '박길동' 이름, '남' 성별, '10' 나이, '150' 키, '50' 몸무게 FROM dual
UNION ALL SELECT '박네모', '남', '10', '160', '50' FROM dual
UNION ALL SELECT '박하나', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '정길동', '남', '10', '150', '50' FROM dual
UNION ALL SELECT '박군'  , '남', '10', '150', '50' FROM dual
)
SELECT a.이름
     , a.성별
     , a.나이
     , a.키
     , a.몸무게
     , MAX(b.값) 값
  FROM data a
  LEFT OUTER JOIN rule b
    ON a.이름   LIKE b.이름
   AND a.성별   LIKE REPLACE(b.성별  , '-', '%')
   AND a.나이   LIKE REPLACE(b.나이  , '-', '%')
   AND a.키     LIKE REPLACE(b.키    , '-', '%')
   AND a.몸무게 LIKE REPLACE(b.몸무게, '-', '%')
 GROUP BY a.이름
     , a.성별
     , a.나이
     , a.키
     , a.몸무게
;

by 박상준 [2022.11.22 09:39:27]

직접 진행해보니 마농님 말씀대로 순서보단 조합이 중요하네요.. 감사합니다!

by 박상준 [2022.11.22 09:41:46]

WITH rule AS
(
SELECT '수학과' CLASS, '___' 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 5 값, 'DEFAULT' init FROM dual
UNION ALL SELECT '체육과' CLASS, '___' 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 99 값, 'DEFAULT' init FROM dual
UNION ALL SELECT '체육과' CLASS, '정__' 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 22 값, 'CHECK' init FROM dual
UNION ALL SELECT '수학과' CLASS, '박__', '-' , '-', '-'  , '-', 10, 'CHECK' FROM dual
UNION ALL SELECT '수학과' CLASS, '박하_', '-' , '-', '-'  , '-', 11, 'CHECK' FROM dual
UNION ALL SELECT '수학과' CLASS, '박__', '남', '-', '-'  , '-', 20, 'CHECK' FROM dual
UNION ALL SELECT '수학과' CLASS, '박__', '남', '-', '150', '-', 30, 'CHECK' FROM dual
UNION ALL SELECT '수학과' CLASS, '정__', '여', '-', '150', '-', 50, 'CHECK' FROM dual
UNION ALL SELECT '수학과' CLASS, '정__', '-', '-', '-', '20', 33, 'CHECK' FROM dual
)
, data AS
(
SELECT '수학과' CLASS, '박길동' 이름, '남' 성별, '10' 나이, '150' 키, '50' 몸무게 FROM dual
UNION ALL SELECT '수학과' CLASS , '박네모', '남', '10', '160', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박하나', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박미나', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '정길동', '남', '10', '150', '50' FROM dual
UNION ALL SELECT '체육과' CLASS , '정여자', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '체육과' CLASS , '남여자', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박군'  , '남', '10', '150', '50' FROM dual
)
SELECT a.CLASS
     , a.이름
     , a.성별
     , a.나이
     , a.키
     , a.몸무게
     , MAX(b.값) 값
  FROM data a
  LEFT OUTER JOIN rule b
    ON a.CLASS  LIKE B.CLASS
   AND A.이름   LIKE B.이름
   AND a.성별   LIKE REPLACE(b.성별  , '-', '%')
   AND a.나이   LIKE REPLACE(b.나이  , '-', '%')
   AND a.키     LIKE REPLACE(b.키    , '-', '%')
   AND a.몸무게 LIKE REPLACE(b.몸무게, '-', '%') 
 GROUP BY a.class
        , a.이름
        , a.성별
        , a.나이
        , a.키
        , a.몸무게
;

위 코드대로 실행하면

체육과의 남여자 는 99의 값.

체육과의 정여자 는 22의 값이

나와야 한다고 생각하는데 이름 에서 조건이 먹지않는것 같습니다. 컬럼 순서적인 문제일까요?

by 마농 [2022.11.22 10:00:17]

일치하는게 많을수록 점수가 높다는 가정하에 작성한 쿼리입니다.
예시에서는 이름이 일치하는게(22) 일치하지 않는 것(99)보다 점수가 낮네요?
우선순위 높은게 일치할수록, 일치하는게 많을 수록 점수가 더 높아야 하는 것 아닌지요?
예시자료의 점수가 잘못 책정되어 있는 것 같습니다.

by 박상준 [2022.11.28 10:54:50]

감사합니니다 마농님. 다른 업무로 너무 늦게 왔습니다..

점수라는 컬럼의 숫자는 일치 여부와 관련된 SCORE 가 아닌,

그 상황에 따른 고정된 값이 들어오는 것 입니다.

예를들어 우편번호 처럼요,,

이름이 일치하지 않는 것 이 RULE 에 99 라고 되어있다면 99 로 들어오고,

이름이 일치하는 것에 대한 RULE 이 1 이라고 되어있으면 이름이 일치하는것은 1 로 들어오도록 하는 것 입니다.

어제도 다시 고민하면서 시도해보는데 어디서 멈춰버린건지 해결이 되지 않네요..

그래서 우선순위 체크하는 테이블이 필요할것같다고 생각했던것인가 싶기도 하네요

by 마농 [2022.11.28 11:28:46]

항목을 두가지로 관리하셔야 할 듯 합니다.
우선 순위에 따른 점수항목과 조회항목 별도 관리.
그게 아니라면, 우선순이 테이블이 필요할 수 있는데. 쿼리는 훨씬 더 복잡해 질 것입니다.

by 쭈니 [2022.11.28 11:32:32]

우선순위 테이블을 활용해보라고 했으니 방법을 고민해봐야겠네요..

마농님 도와주셔서 감사합니다 !☺️☺️

by 마농 [2022.11.28 13:31:10]

WITH rule AS
(
SELECT '수학과' class, '___' 이름, '-' 성별, '-' 나이, '-' 키, '-' 몸무게, 5 값, 'DEFAULT' init FROM dual
UNION ALL SELECT '체육과' CLASS, '___'  , '-' , '-', '-'  , '-' , 99, 'DEFAULT' FROM dual
UNION ALL SELECT '체육과' CLASS, '정__' , '-' , '-', '-'  , '-' , 22, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '박__' , '-' , '-', '-'  , '-' , 10, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '박하_', '-' , '-', '-'  , '-' , 11, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '박__' , '남', '-', '-'  , '-' , 20, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '박__' , '남', '-', '150', '-' , 30, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '정__' , '여', '-', '150', '-' , 50, 'CHECK'   FROM dual
UNION ALL SELECT '수학과' CLASS, '정__' , '-' , '-', '-'  , '20', 33, 'CHECK'   FROM dual
)
, data AS
(
SELECT '수학과' class, '박길동' 이름, '남' 성별, '10' 나이, '150' 키, '50' 몸무게 FROM dual
UNION ALL SELECT '수학과' CLASS , '박네모', '남', '10', '160', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박하나', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박미나', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '정길동', '남', '10', '150', '50' FROM dual
UNION ALL SELECT '체육과' CLASS , '정여자', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '체육과' CLASS , '남여자', '여', '10', '150', '50' FROM dual
UNION ALL SELECT '수학과' CLASS , '박군'  , '남', '10', '150', '50' FROM dual
)
-- 우선순위에 따라 2의 제곱 형태로 점수를 부여하는 방법 --
SELECT a.class
     , a.이름
     , a.성별
     , a.나이
     , a.키
     , a.몸무게
     , MAX(b.값) KEEP(DENSE_RANK FIRST
       ORDER BY CASE WHEN a.이름 LIKE b.이름 THEN
                DECODE(REGEXP_COUNT(b.이름, '_'), 0, 64
                                                , 1, 32
                                                , 2, 16
                                                , 0) ELSE 0 END
              + DECODE(a.성별  , b.성별  , 8, 0)
              + DECODE(a.나이  , b.나이  , 4, 0)
              + DECODE(a.키    , b.키    , 2, 0)
              + DECODE(a.몸무게, b.몸무게, 1, 0)
                DESC) AS 값
  FROM data a
  LEFT OUTER JOIN rule b
    ON a.class  =    b.class
   AND a.이름   LIKE b.이름
   AND a.성별   LIKE REPLACE(b.성별  , '-', '%')
   AND a.나이   LIKE REPLACE(b.나이  , '-', '%')
   AND a.키     LIKE REPLACE(b.키    , '-', '%')
   AND a.몸무게 LIKE REPLACE(b.몸무게, '-', '%') 
 GROUP BY a.class
        , a.이름
        , a.성별
        , a.나이
        , a.키
        , a.몸무게
;

by 쭈니 [2022.11.28 21:40:51]

우선순위에 따른 점수를 2의 제곱 형태로 부여하는 특별한 이유가 있을까요??

by 마농 [2022.11.29 08:18:33]

예를 들면
1순위를 16점 부여하고
2,3,4,5 순위에 8,4,2,1 점을 부여하면
1순위가 일치하는 점수는 16점이 되고
1순위가 일치하지 않고 2,3,4,5 순위가 일치하는 점수는 8+4+2+1=15점이 됩니다.
즉, 다음 순위가 아무리 많이 일치해도 우선순위 높은 것이 점수가 더 많이 나오게 하기 위함입니다.
쿼리에서는 이름을 1순위로 보려고 했는데 이름 자체에서도 일치하는 개수에 따라 등급이 나뉘네요.
그래서 이름의 우선순위를 세분화해서 표현했습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변