데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 40825 게시물 읽기

No. 40825

겹치는 필드가 가장 많은 로우와 카운트 찾는 쿼리 질문이요.
작성자	현아범	작성일	2015-06-09 19:47	조회수	7,802

안녕하세요.

좀 막히는 쿼리가 있어 조언을 구합니다.

필드 단위로 최대로 많이 같은값이 있는 SEQ와 카운트를 구하고 싶습니다.

설명하기가 어려워 예제를 들면

WITH

V_TEMP AS (

SELECT 'A' AS SEQ, '사과' AS T1, '배' AS T2, '자두' AS T3, '딸기' AS T4, 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

UNION ALL

SELECT 'B' ,'배', '수박', '바나나', '', 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

UNION ALL

SELECT 'C', '메론', '바나나', '자두', '딸기', 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

UNION ALL

SELECT 'D', '메론', '', '', '', 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

UNION ALL

SELECT 'E', '배', '바나나', '메론', '딸기', 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

UNION ALL

SELECT 'F', '자두' , '딸기' , '사과' , '배' , 0 AS SUM_QTY, '' AS TARGET_SEQ FROM DUAL

)

SELECT * FROM V_TEMP

예를 들면 위와 같은 데이터가 있을때 SUM_QTY 필드에

만약 SEQ가 A 인 경우 SUM_QTY 의 값에는 4 ,TARGET_SEQ 의 값은 F <--------( F 에 자두, 딸기, 사과 , 배) 가 가장 많이 일치하므로.

같은방식으로

SEQ가 B 인 경우 SUM_QTY 의 값에는 2, TARGET_SEQ 의 값은 E

만약 SEQ가 C 인 경우 SUM_QTY 의 값에는 2, TARGET_SEQ 의 값은 A

만약 SEQ가 D 인 경우 SUM_QTY 의 값에는 1, TARGET_SEQ 의 값은 C 또는 E

만약 SEQ가 E 인 경우 SUM_QTY 의 값에는 2, TARGET_SEQ 의 값은 A 또는 B 또는 F

만약 SEQ가 F 인 경우 SUM_QTY 의 값에는 4, TARGET_SEQ 의 값은 A

고수님 제발 도와주시면 감사하겠습니다.

이 글에 대한 댓글이 총 3건 있습니다.

WITH v_temp AS
(
SELECT 'A' seq, '사과' t1, '배' t2, '자두' t3, '딸기' t4 FROM dual
UNION ALL SELECT 'B' ,'배' , '수박' , '바나나', ''     FROM dual
UNION ALL SELECT 'C', '메론', '바나나', '자두' , '딸기' FROM dual
UNION ALL SELECT 'D', '메론', ''      , ''      , ''     FROM dual
UNION ALL SELECT 'E', '배' , '바나나', '메론' , '딸기' FROM dual
UNION ALL SELECT 'F', '자두', '딸기' , '사과' , '배'   FROM dual
UNION ALL SELECT 'G', '오디', '코코넛', '두리안', '키위' FROM dual
)
SELECT b.seq
     , b.t1, b.t2, b.t3, b.t4
     , NVL(a.sum_qty, 0) sum_qty
     , a.target_seq
FROM (SELECT seq
             , target_seq
             , COUNT(*) sum_qty
             , ROW_NUMBER() OVER(
               PARTITION BY seq ORDER BY COUNT(*) DESC, target_seq) rn
          FROM (SELECT CONNECT_BY_ROOT seq seq
                     , seq target_seq
                  FROM v_temp
                 UNPIVOT (t FOR gb IN (t1, t2, t3, t4))
                 WHERE LEVEL = 2
                 CONNECT BY PRIOR t = t
                        AND PRIOR seq != seq
                        AND LEVEL <= 2
                )
         GROUP BY seq, target_seq
        ) a
     , v_temp b
WHERE a.seq(+) = b.seq
   AND a.rn (+) = 1
;

마농(manon94)님이 2015-06-10 09:23에 작성한 댓글입니다.

대박~~~ 마농님 정말 대단하십니다.

변형해서 하나만 더 질문드릴께요.

마농님... 혹시 두개의 테이블에서도 가능할까요?

답변을 변형해서 해볼려니깐 잘 안돼네요...

WITH

V_TEMP AS (

SELECT 'A' SEQ, '사과' t1, '배' t2, '자두' t3, '딸기' t4 FROM dual

UNION ALL SELECT 'B' ,'배' , '수박' , '바나나', '' FROM dual

UNION ALL SELECT 'C', '메론', '바나나', '자두' , '딸기' FROM dual

UNION ALL SELECT 'D', '메론', '' , '' , '' FROM dual

UNION ALL SELECT 'E', '배' , '바나나', '메론' , '딸기' FROM dual

UNION ALL SELECT 'F', '자두', '딸기' , '사과' , '배' FROM dual

UNION ALL SELECT 'G', '오디', '코코넛', '두리안', '키위' FROM dual

)

, V_TEMP2 AS (

SELECT 'A1' SEQ, '버너너' t1, '배' t2, '두리안' t3, '딸기' t4 FROM dual

UNION ALL SELECT 'B1' ,'배' , '수박' , '바나나', '' FROM dual

UNION ALL SELECT 'C1', '메론', '오디', '자두' , '딸기' FROM dual

UNION ALL SELECT 'D1', '메론', '' , '' , '' FROM dual

UNION ALL SELECT 'E1', '사과' , '바나나', '메론' , '딸기' FROM dual

UNION ALL SELECT 'F1', '자두', '딸기' , '배' , '자두' FROM dual

UNION ALL SELECT 'G1', '오디', '코코넛', '키위' , '' FROM dual

)

현아범님이 2015-06-10 10:05에 작성한 댓글입니다. Edit Ｘ

WITH v_temp AS
(
SELECT 'A' seq, '사과' t1, '배' t2, '자두' t3, '딸기' t4 FROM dual
UNION ALL SELECT 'B' ,'배' , '수박' , '바나나', ''     FROM dual
UNION ALL SELECT 'C', '메론', '바나나', '자두' , '딸기' FROM dual
UNION ALL SELECT 'D', '메론', ''      , ''      , ''     FROM dual
UNION ALL SELECT 'E', '배' , '바나나', '메론' , '딸기' FROM dual
UNION ALL SELECT 'F', '자두', '딸기' , '사과' , '배'   FROM dual
UNION ALL SELECT 'G', '오디', '코코넛', '두리안', '키위' FROM dual
)
, v_temp2 AS
(
SELECT 'A1' seq, '버너너' t1, '배' t2, '두리안' t3, '딸기' t4 FROM dual
UNION ALL SELECT 'B1', '배' , '수박' , '바나나', ''     FROM dual
UNION ALL SELECT 'C1', '메론', '오디' , '자두' , '딸기' FROM dual
UNION ALL SELECT 'D1', '메론', ''      , ''      , ''     FROM dual
UNION ALL SELECT 'E1', '사과', '바나나', '메론' , '딸기' FROM dual
UNION ALL SELECT 'F1', '자두', '딸기' , '배'    , '자두' FROM dual
UNION ALL SELECT 'G1', '오디', '코코넛', '키위' , ''     FROM dual
)
SELECT seq
     , t1, t2, t3, t4
     , sum_qty
     , target_seq
FROM (SELECT a.seq
             , a.t1, a.t2, a.t3, a.t4
             , b.seq target_seq
             , COUNT(b.seq) sum_qty
             , ROW_NUMBER() OVER(
               PARTITION BY a.seq ORDER BY COUNT(b.seq) DESC, b.seq) rn
          FROM v_temp a
          LEFT OUTER JOIN v_temp2 UNPIVOT (t FOR gb IN (t1, t2, t3, t4)) b
            ON b.t IN (a.t1, a.t2, a.t3, a.t4)
         GROUP BY a.seq
             , a.t1, a.t2, a.t3, a.t4
             , b.seq
        )
WHERE rn = 1
;

마농(manon94)님이 2015-06-10 10:52에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
40828	이 쿼리실행시 접속된 유저 전체테이블이 삭제되는건가요? [1]	DBA희망자	2015-06-11	7193
40827	안녕하세요 split 속도 문제 문의드립니다.. [2]	아벨의꿈	2015-06-10	8106
40826	오라클에서 처음조회할때 속도 느린 현상 해결좀... [1]	김삼	2015-06-10	7410
40825	겹치는 필드가 가장 많은 로우와 카운트 찾는 쿼리 질문이요. [3]	현아범	2015-06-09	7802
40824	쿼리 문의드립니다. [3]	나랑	2015-06-09	7743
40823	in 절에 나열된 순서대로 정렬하기 [1]	화생방	2015-06-09	7165
40822	select 버퍼 사용 안할려면 [3]	이현정	2015-06-09	7864