database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 40825 게시물 읽기
No. 40825
겹치는 필드가 가장 많은 로우와 카운트 찾는 쿼리 질문이요.
작성자
현아범
작성일
2015-06-09 19:47
조회수
8,119

안녕하세요.

 

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

 

필드 단위로 최대로 많이 같은값이 있는 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
7507
40827안녕하세요 split 속도 문제 문의드립니다.. [2]
아벨의꿈
2015-06-10
8478
40826오라클에서 처음조회할때 속도 느린 현상 해결좀... [1]
김삼
2015-06-10
7726
40825겹치는 필드가 가장 많은 로우와 카운트 찾는 쿼리 질문이요. [3]
현아범
2015-06-09
8119
40824쿼리 문의드립니다. [3]
나랑
2015-06-09
8055
40823in 절에 나열된 순서대로 정렬하기 [1]
화생방
2015-06-09
7477
40822select 버퍼 사용 안할려면 [3]
이현정
2015-06-09
8187
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다