[표준SQL] 퀴즈로 배우는 SQL : 공통점이 가장 많은 친구 찾기

퀴즈로 배우는 SQL : 공통점이 가장 많은 친구 찾기

퀴즈로 배우는 SQL

공통점이 가장 많은 친구 찾기

이번 퀴즈로 배워보는 SQL 시간에는 공통점이 가장 많은 친구를 찾는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

[문제]

<리스트 1> 원본리스트WITH t AS
(
SELECT '마농' nm
, '사과' c1, '배' c2, '자두' c3, '딸기' c4 FROM dual
UNION ALL SELECT '재석'
, '배' , '수박' , '바나나', '' FROM dual
UNION ALL SELECT '정식'
, '메론', '바나나', '자두' , '딸기' FROM dual
UNION ALL SELECT '마소'
, '메론', '' , '' , '' FROM dual
UNION ALL SELECT '민용'
, '배' , '자두' , '사과' , '딸기' FROM dual
UNION ALL SELECT '혜연'
, '자두', '딸기' , '사과' , '배' FROM dual
UNION ALL SELECT '수지'
, '오디', '코코넛', '두리안', '머루' FROM dual
)
SELECT * FROM t;

[문제설명]

마농군은 친구들이 좋아하는 과일이 무엇인지를 조사했습니다. 친구들은 최소 한 개 이상 네 개 이하의 좋아하는 과일 이름을 마농군에게 적어줬습니다. 마농군은 이 정보를 <표 1> 형태의 원본테이블에 저장했습니다. 이 정보를 이용해 <표 2> 의 결과테이블 얻고자 합니다. 서로 좋아하는 과일이 일치하는 수가 가장 많은 건수(CNT)와 친구(NM2)를 표시하는 문제입니다. 일치하는 과일수가 가장 많은 친구가 여러 명일 경우에는 친구이름을 쉼표(,)로 구분하여 모두 표시해야 합니다. <표 2>의 결과를 보면 마농군이 좋아하는 과일 4가지가 모두 일치하는 친구가 2명(민용,혜연)이 있음을 보여주는 것입니다. 수지가 좋아하는 과일을 좋아하는 친구는 아무도 없음을 나타냅니다.

[정답]

문제를 스스로 해결해 보셨나요 이제 정답을 알아보겠습니다.

<리스트 2> 정답 리스트SELECT nm
, c1, c2, c3, c4
, LISTAGG(x, ',') WITHIN GROUP(ORDER BY x) nm2
, cnt
FROM (SELECT a.nm
, a.c1, a.c2, a.c3, a.c4
, b.nm x
, COUNT(b.nm) cnt
, RANK() OVER(PARTITION BY a.nm
ORDER BY COUNT(b.nm) DESC) rk
FROM t a
LEFT OUTER JOIN t UNPIVOT
(c FOR g IN (c1, c2, c3, c4)) b
ON b.nm != a.nm
AND b.c IN (a.c1, a.c2, a.c3, a.c4)
GROUP BY a.nm
, a.c1, a.c2, a.c3, a.c4
, b.nm
)
WHERE rk = 1
GROUP BY nm, c1, c2, c3, c4, cnt
;

어떤가요 여러분이 만들어본 리스트와 같은가요 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 수는 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

[해설]

이번 문제는 설문조사 결과를 이용해 서로간의 공통점을 파악해내는 문제입니다. 한번에 정답을 살펴보기보다는 단계별로 차근차근 접근해 보도록 하겠습니다. 하나의 테이블 내에서 다른 친구들과 연결관계를 파악해야 하는 문제입니다. 이 경우에는 Self Join을 이용할 수 있습니다.

<리스트 3> Self Join 시도SELECT *
FROM t a
, t b
WHERE a.nm != b.nm
AND (a.c1, a.c2, a.c3, a.c4) (b.c1, b.c2, b.c3, b.c4)
;

<리스트 3>의 쿼리를 이용해 조인을 시도해 보았습니다. 우선 같은 테이블을 FROM절에서 두 번 사용하여 별칭(Alias)을 다르게 줘 Self Join을 시도합니다. 같은 행끼리는 비교할 필요가 없겠지요. 같은 행끼리 비교하지 않도록 조건을 줍니다.

WHERE a.nm != b.nm

이번에는 과일항목 네 개를 서로 비교해야 합니다. 그런데 네 개 항목을 한꺼번에 비교할 수 있는 비교식이 존재하지 않네요 하나의 컬럼을 여러 컬럼과 비교하는 것은 가능합니다.

AND a.c1 IN (b.c1, b.c2, b.c3, b.c4)

a의 컬럼이 네 개이므로 이런 조건을 네 번 사용해야 하겠네요.

AND ( a.c1 IN (b.c1, b.c2, b.c3, b.c4) OR
a.c2 IN (b.c1, b.c2, b.c3, b.c4) OR
a.c3 IN (b.c1, b.c2, b.c3, b.c4) OR
a.c4 IN (b.c1, b.c2, b.c3, b.c4) )

하지만 이 방법은 다소 복잡해 보이네요. 네 개 컬럼을 하나의 컬럼으로 만든다면 어떨까요 조인 비교식이 간결해 지겠네요. 1행 4열의 자료를 4행 1열의 자료로 만들어보겠습니다.

<리스트 4> 열을 행으로(UNPIVOT)SELECT *
FROM t UNPIVOT (c FOR g IN (c1, c2, c3, c4))
;

<리스트 4>의 쿼리를 이용해 <표 3>의 결과를 얻었습니다. UNPIVOT 구문을 이용했습니다.

FROM t UNPIVOT (c FOR g IN (c1, c2, c3, c4))

UNPIVOT 은 FROM절에서 사용되며, 간단하게 열을 행으로 바꿔주는 기능을 합니다. 위 구문은 한 행의 (c1, c2, c3, c4) 네 개 컬럼을 네 개 행으로 구별하여 하나의 컬럼 c 에 표현하게 되며, 이 때 각 4개 행을 구분하는 구분항목은 g가 됩니다. 이 UNPIVOT 집합을 원본집합과의 조인에 이용해 보겠습니다. 또한, 수지처럼 조인에 실패해도 결과가 나오게 하기 위해 아우터 조인을 함게 이용해 보겠습니다.

<리스트 5> 원본집합과 UNPIVOT 집합 아우터조인SELECT *
FROM t a
, t UNPIVOT (c FOR g IN (c1, c2, c3, c4)) b
WHERE b.nm(+) != a.nm
AND b.c (+) IN (a.c1, a.c2, a.c3, a.c4)
; AND b.c(+) IN (a.c1, a.c2, a.c3, a.c4)
*

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

오류 : ORA-01719: 포괄 조인 운영 (+)는 OR 또는 IN의 연산수를 허용하지 않습니다

<리스트 5>의 쿼리 결과 에러를 만났습니다.
아우터 조인에 IN 조건을 주어 에러가 발생됐습니다. 이는 ANSI 조인을 이용해 해결이 가능합니다.

<리스트 6> ANSI 아우터 조인SELECT *
FROM t a
LEFT OUTER JOIN
t UNPIVOT (c FOR g IN (c1, c2, c3, c4)) b
ON b.nm != a.nm
AND b.c IN (a.c1, a.c2, a.c3, a.c4)
;

<리스트 6>의 쿼리 결과 정상적으로 조인이 수행됐습니다. 원본 집합을 기준으로 UNPIVOT 집합을 아우터 조인했습니다. 이번에는 Group By를 통해 조인 결과를 집계해 보겠습니다.

<리스트 7> 결과 집계
SELECT a.nm
, a.c1, a.c2, a.c3, a.c4
, b.nm x
, COUNT(b.nm) cnt
, RANK() OVER(PARTITION BY a.nm
ORDER BY COUNT(b.nm) DESC) rk
FROM t a
LEFT OUTER JOIN t UNPIVOT
(c FOR g IN (c1, c2, c3, c4)) b
ON b.nm != a.nm
AND b.c IN (a.c1, a.c2, a.c3, a.c4)
GROUP BY a.nm, b.nm
, a.c1, a.c2, a.c3, a.c4
;

<리스트 7>의 쿼리를 이용해 <표 4>의 결과를 얻었습니다. a.nm과 b.nm을 기준으로 Group By해 집계합니다.

GROUP BY a.nm, b.nm

a.nm에 종속적인 (a.c1, a.c2, a.c3, a.c4)는 Group By에 포함시켜도 결과에 영향을 주지 않습니다.

GROUP BY a.nm, b.nm, a.c1, a.c2, a.c3, a.c4

b.nm을 카운트합니다.

, COUNT(b.nm) cnt

이 건수가 바로 친구끼리 공통으로 좋아하는 과일의 건수입니다. 그런데 왜 COUNT(*)가 아닌 COUNT(b.nm)일까요 공통점이 없는 수지의 경우 0건이 나오도록 하기 위함입니다. b.nm이 널(null)이기 때문에 건수에서 제외되어 0이 나오게 됩니다. 최종적으로 필요한것은 연관된 모든 친구가 아닌, 가장 많이 일치하는 친구만 가져오는 것이므로, RANK 를 이용해 공통점에 대한 순위를 부여합니다.

, RANK() OVER(PARTITION BY a.nm
ORDER BY COUNT(b.nm) DESC) rk
이렇게 구한 순위 중 1등만 뽑아낸다면 원하는 결과가 나올 것입니다.

<리스트 8> 정답 리스트SELECT nm
, c1, c2, c3, c4
, LISTAGG(x, ',') WITHIN GROUP(ORDER BY x) nm2
, cnt
FROM ( -- <리스트 7> -- )
WHERE rk = 1
GROUP BY nm, c1, c2, c3, c4, cnt
;

<리스트 8> 정답 리스트가 완성됐습니다. <리스트 7>의 결과에서 순위가 1등인 것만을 뽑아냅니다.

WHERE rk = 1

공통으로 좋아하는 과일의 개수가 가장 많은 친구들만 선별합니다. <리스트 7>에서 Group By a.nm, b.nm을 했다면 <리스트 8>에서 최종 Group By a.nm을 합니다.

GROUP BY nm

a.nm에 부수적인 항목들도 Group By에 포함시킵니다.

GROUP BY nm, c1, c2, c3, c4, cnt

a.nm을 기준으로 집계를 하였으므로 b.nm이 여러명일 수 있습니다. 마지막으로 1등이 여러 명일 경우 하나로 합칩니다.

, LISTAGG(x, ',') WITHIN GROUP(ORDER BY x) nm2

최종 정답 쿼리 <리스트 2>가 완성됩니다. 이번 퀴즈로 배우는 SQL 시간에 다룬 내용을 정리해 볼까요

- Self Join
- UNPIVOT
- ANSI JOIN 구문 사용으로 포괄조인 오류 해결 방법
- 집계 함수 이용한 Count
- 분석함수 Rank 를 이용하여 순위 구하기
- LiasAgg 를 이용하여 문자열 합치기

출처 : 마이크로소프트웨어 7월호

제공 : 데이터 전문가 지식포털 DBguide.net

[출처] https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?pageid=12&mod=document&uid=235851