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

SQL 로직 도움 요청드립니다. ( 매칭되는 갯수에 따라 MAIN 설정 )

by 새싹 [SQL Query] [2024.10.14 22:16:39]

머릿속에서 구조는 정리가 되었는데 붙잡고있어도 로직으로 어떻게 구성해야할지 감이 안옵니다ㅠㅠ 도움 부탁드립니다.

매칭되는 컬럼 수량도 체크가 가능한가요??

0. 동일 ID 기준으로 2개의 데이터가 있을 때 어떤 데이터가 MAIN 이 되는 지 뽑기

1. 테이블 A 에서 ID 기준으로 MAIN이 되는 행을 추출해야합니다.

2. 테이블 B의 '*' 는 ALL 입니다.

3. 테이블 B에 있는 데이터를 기준으로, 많은 속성이 매칭되는 행이 MAIN_FLAG = 'Y' 가 됩니다. ( ID 기준으로 1개만 'Y' 이며, 매칭되는게 없다면 'N' 입니다.

4. 테이블 B의 SEQ는 어떤 기준으로 체크하였는지 확인하기 위함입니다.

5. 매칭되는 수량만 체크하고, 수량 기준으로 MAIN을 체크합니다.
아래 예시처럼 ( 1 or 4 ) 에 모두 매칭되더라도 갯수만 체크하여 main_flag 를 결정합니다.
(동일할 시 default 순서대로)

테이블 A

ID	COL1	COL2	COL3
A	가	다	라
A	가	나	라
B	다	나	마
B	라	나	다
C	나	다	라
C	나	다	마

테이블 B

COL1	COL2	COL3	SEQ
가	*	*	1
가	나	*	2
*	나	*	3
*	*	라	4
*	나	다	5

결과

ID	COL1	COL2	COL3	MAIN_FLAG	SEQ	매칭 컬럼 갯수
A	가	다	라	N	1 or 4	1개
A	가	나	라	Y	2	2개
B	다	나	마	N	3	1개
B	라	나	다	Y	5	2개
C	나	다	라	Y	4	1개
C	나	다	마	N		0개

WITH  A AS(
SELECT 'A' AS ID , '가' AS COL1, '다' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'A' AS ID , '가' AS COL1, '나' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'B' AS ID , '다' AS COL1, '나' AS COL2, '마' AS COL3 FROM DUAL UNION ALL
SELECT 'B' AS ID , '라' AS COL1, '나' AS COL2, '다' AS COL3 FROM DUAL UNION ALL
SELECT 'C' AS ID , '나' AS COL1, '다' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'C' AS ID , '나' AS COL1, '다' AS COL2, '마' AS COL3 FROM DUAL
),
B AS (
SELECT '가' AS COL1, '*' AS COL2, '*' AS COL3, '1' SEQ FROM DUAL UNION ALL
SELECT '가' AS COL1, '나' AS COL2, '*' AS COL3, '2' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '나' AS COL2, '*' AS COL3, '3' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '*' AS COL2, '라' AS COL3, '4' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '나' AS COL2, '다' AS COL3, '5' SEQ FROM DUAL
)

by 마농 [2024.10.15 08:33:08]

WITH a AS
(
SELECT 'A' id, '가' col1, '다' col2, '라' col3 FROM dual
UNION ALL SELECT 'A', '가', '나', '라' FROM dual
UNION ALL SELECT 'B', '다', '나', '마' FROM dual
UNION ALL SELECT 'B', '라', '나', '다' FROM dual
UNION ALL SELECT 'C', '나', '다', '라' FROM dual
UNION ALL SELECT 'C', '나', '다', '마' FROM dual
)
, b AS
(
SELECT '가' col1, '*' col2, '*' col3, 1 seq 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 id
     , col1, col2, col3
     , CASE WHEN MAX(cnt) > 1 THEN 'Y' ELSE 'N' END main_flag
     , LISTAGG(seq, ',') WITHIN GROUP(ORDER BY seq) seq
     , NVL(MAX(cnt), 0) match_cnt
  FROM (SELECT a.id
             , a.col1, a.col2, a.col3
             , b.seq, b.cnt
             , RANK() OVER(PARTITION BY a.id, a.col1, a.col2, a.col3 ORDER BY b.cnt DESC) rk
          FROM a
          LEFT OUTER JOIN
               (SELECT col1, col2, col3, seq
                     , NVL(LENGTH(REPLACE(col1 || col2 || col3, '*')), 0) cnt
                  FROM b
                ) b
            ON a.col1 = DECODE(b.col1, '*', a.col1, b.col1)
           AND a.col2 = DECODE(b.col2, '*', a.col2, b.col2)
           AND a.col3 = DECODE(b.col3, '*', a.col3, b.col3)
        )
 WHERE rk = 1
 GROUP BY id, col1, col2, col3
 ORDER BY id, col1, col2, col3
;

이 쿼리는 질문을 잘못 이해하고 만든 쿼리
- 내가 이해한것 : 매칭 카운트 2 이상인 경우 Y
- 원래 요구사항 : ID 별로 가장 많이 매치된 것 중 하나만 Y

by 우주민 [2024.10.15 09:05:03]

rank over 에서 동일 순위가 나오는 경우에는 각각 같은 rank값을 가지지 않나요??

by 마농 [2024.10.15 09:15:31]

네. rank 는 동순위가 나오죠.
공동 1등을 뽑기 위해 rank 사용했습니다.
공동1등 (1, 4)

by 마농 [2024.10.15 09:19:36]

아! 결과표가 뭔가 이상하다 했는데. 제가 잘못 이해한거군요.
저는 매치 카운트가 2 이상일 경우 Y 라고 생각했습니다.
ID 별로 1건만 Y 가 되는 거군요,

by 우주민 [2024.10.15 09:41:02]

아! 공동 순위 처리까지 만드신거군요//

by 우주민 [2024.10.15 09:02:27]

WITH  A AS(
SELECT 'A' AS ID , '가' AS COL1, '다' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'A' AS ID , '가' AS COL1, '나' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'B' AS ID , '다' AS COL1, '나' AS COL2, '마' AS COL3 FROM DUAL UNION ALL
SELECT 'B' AS ID , '라' AS COL1, '나' AS COL2, '다' AS COL3 FROM DUAL UNION ALL
SELECT 'C' AS ID , '나' AS COL1, '다' AS COL2, '라' AS COL3 FROM DUAL UNION ALL
SELECT 'C' AS ID , '나' AS COL1, '다' AS COL2, '마' AS COL3 FROM DUAL
),
B AS (
SELECT '가' AS COL1, '*' AS COL2, '*' AS COL3, '1' SEQ FROM DUAL UNION ALL
SELECT '가' AS COL1, '나' AS COL2, '*' AS COL3, '2' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '나' AS COL2, '*' AS COL3, '3' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '*' AS COL2, '라' AS COL3, '4' SEQ FROM DUAL UNION ALL
SELECT '*' AS COL1, '나' AS COL2, '다' AS COL3, '5' SEQ FROM DUAL
)
, C AS (
SELECT A.ID, A.COL1, A.COL2, A.COL3, B.SEQ
,CASE WHEN A.COL1 = B.COL1 THEN 1 ELSE 0 END
+CASE WHEN A.COL2 = B.COL2 THEN 1 ELSE 0 END
+CASE WHEN A.COL3 = B.COL3 THEN 1 ELSE 0 END AS MATCH_CNT
from A
LEFT OUTER JOIN B
ON A.COL1 = CASE WHEN B.COL1 = '*' THEN A.COL1 ELSE B.COL1 END
AND A.COL2 = CASE WHEN B.COL2 = '*' THEN A.COL2 ELSE B.COL2 END
AND A.COL3 = CASE WHEN B.COL3 = '*' THEN A.COL3 ELSE B.COL3 END
)
, D AS (
SELECT ID, COL1, COL2, COL3, MATCH_CNT, SEQ
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MATCH_CNT DESC) AS RN1
, ROW_NUMBER() OVER(PARTITION BY ID, COL1, COL2, COL3 ORDER BY MATCH_CNT DESC) AS RN2
FROM C
)
SELECT ID, COL1, COL2, COL3
, CASE WHEN RN1 = 1 THEN 'Y' ELSE 'N' END AS MAIN_FLAG
, SEQ
, MATCH_CNT
FROM D
WHERE RN2 = 1

마농님께서 이미 올려주셨지만...

저도 미숙하게나마 만들어본 쿼리 올려봅니다.

by 마농 [2024.10.15 09:34:57]

WITH a AS
(
SELECT 'A' id, '가' col1, '다' col2, '라' col3 FROM dual
UNION ALL SELECT 'A', '가', '나', '라' FROM dual
UNION ALL SELECT 'B', '다', '나', '마' FROM dual
UNION ALL SELECT 'B', '라', '나', '다' FROM dual
UNION ALL SELECT 'C', '나', '다', '라' FROM dual
UNION ALL SELECT 'C', '나', '다', '마' FROM dual
)
, b AS
(
SELECT '가' col1, '*' col2, '*' col3, 1 seq 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 id
     , col1, col2, col3
     , CASE ROW_NUMBER() OVER(PARTITION BY id ORDER BY MAX(cnt) DESC)
       WHEN 1 THEN 'Y' ELSE 'N' END main_flag
     , LISTAGG(seq, ',') WITHIN GROUP(ORDER BY seq) seq
     , MAX(cnt) match_cnt
  FROM (SELECT a.id
             , a.col1, a.col2, a.col3
             , b.seq
             , NVL(b.cnt, 0) cnt
             , RANK() OVER(PARTITION BY a.id, a.col1, a.col2, a.col3 ORDER BY b.cnt DESC) rk
          FROM a
          LEFT OUTER JOIN
               (SELECT col1, col2, col3, seq
                     , NVL(LENGTH(REPLACE(col1 || col2 || col3, '*')), 0) cnt
                  FROM b
                ) b
            ON a.col1 = DECODE(b.col1, '*', a.col1, b.col1)
           AND a.col2 = DECODE(b.col2, '*', a.col2, b.col2)
           AND a.col3 = DECODE(b.col3, '*', a.col3, b.col3)
        )
 WHERE rk = 1
 GROUP BY id, col1, col2, col3
 ORDER BY id, col1, col2, col3
;

댓글등록

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

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

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

데이터베이스 질문과 답변