퀴즈로 배우는 SQL
목록
[퀴즈] 공통점이 가장 많은 친구 찾기 0 4 99,999+

by 마농 SELF JOIN UNPIVOT ORA-01719 LIASAGG [2015.12.14]

이번 퀴즈로 배워보는 SQL 시간에는 공통점이 가장 많은 친구를 찾는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

  • [리스트 1] 원본리스트 
  •  CREATE TABLE 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] 원본테이블
  • NM           C1           C2                 C3                 C4
------------ ------------ ----------------- ----------------- ---------
마농         사과         배                 자두               딸기
재석         배           수박               바나나
정식         메론         바나나             자두               딸기
마소         메론
민용         배           자두               사과               딸기
혜연         자두         딸기               사과               배
수지         오디         코코넛             두리안             머루

  • [표 2] 결과테이블 
  • NM         C1         C2         C3         C4         NM2                         CNT
---------- ---------- ---------- ---------- ---------- -------------------- ----------
마농       사과       배         자두       딸기       민용,혜연                     4
마소       메론                                       정식                          1
민용       배         자두       사과       딸기       마농,혜연                     4
수지       오디       코코넛     두리안     머루                                     0
재석       배         수박       바나나                마농,민용,정식,혜연           1
정식       메론       바나나     자두       딸기       마농,민용,혜연                2
혜연       자두       딸기       사과       배         마농,민용                     4

문제설명

마농군은 친구들이 좋아하는 과일이 무엇인지를 조사했습니다. 친구들은 최소 한 개 이상 네 개 이하의 좋아하는 과일 이름을 마농군에게 적어줬습니다.

마농군은 이 정보를 <표 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))
;

  • [표 3] 열을 행으로(UNPIVOT)
  • NM         G    C
---------- ---- ------------
마농       C1   사과
마농       C2   배
마농       C3   자두
마농       C4   딸기
재석       C1   배
재석       C2   수박
재석       C3   바나나
정식       C1   메론
정식       C2   바나나
정식       C3   자두
정식       C4   딸기
마소       C1   메론
민용       C1   배
민용       C2   자두
민용       C3   사과
민용       C4   딸기
혜연       C1   자두
혜연       C2   딸기
혜연       C3   사과
혜연       C4   배
수지       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)
             *
ERROR at line 5:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

오류 : 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
;

  • [표 4] 결과 집계
  • NM         C1         C2         C3         C4         X                   CNT         RK
---------- ---------- ---------- ---------- ---------- ------------ ---------- ----------
마농       사과       배         자두       딸기       민용                  4          1
마농       사과       배         자두       딸기       혜연                  4          1
마농       사과       배         자두       딸기       정식                  2          3
마농       사과       배         자두       딸기       재석                  1          4
마소       메론                                        정식                  1          1
민용       배         자두       사과       딸기       마농                  4          1
민용       배         자두       사과       딸기       혜연                  4          1
민용       배         자두       사과       딸기       정식                  2          3
민용       배         자두       사과       딸기       재석                  1          4
수지       오디       코코넛     두리안     머루                             0          1
재석       배         수박       바나나                민용                  1          1
재석       배         수박       바나나                정식                  1          1
재석       배         수박       바나나                혜연                  1          1
재석       배         수박       바나나                마농                  1          1
정식       메론       바나나     자두       딸기       민용                  2          1
정식       메론       바나나     자두       딸기       마농                  2          1
정식       메론       바나나     자두       딸기       혜연                  2          1
정식       메론       바나나     자두       딸기       마소                  1          4
정식       메론       바나나     자두       딸기       재석                  1          4
혜연       자두       딸기       사과       배         민용                  4          1
혜연       자두       딸기       사과       배         마농                  4          1
혜연       자두       딸기       사과       배         정식                  2          3
혜연       자두       딸기       사과       배         재석                  1          4

<리스트 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 를 이용하여 문자열 합치기

- 강좌 URL : http://www.gurubee.net/lecture/2954

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by mugyeong [2015.12.15 10:29:01]

잘배우고갑니다

by 요도리 [2015.12.16 10:48:38]

간단한줄 알고 덤볐다가 큰코 다쳤네요 .. 감사합니다.

by 봄빛 [2018.10.06 05:06:40] 
--postgreSQL에서 작성하였습니다.

--너무 어렵네요 하루종일 밤샘해서 겨우 결과가 나왔네요 

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

WITH data_t(nm,c1,c2,c3,c4) AS
(
  SELECT '마농', '사과', '배'    , '자두'  , '딸기' UNION ALL
  SELECT '재석', '배'  , '수박'  , '바나나', ''     UNION ALL
  SELECT '정식', '메론', '바나나', '자두'  , '딸기'  UNION ALL
  SELECT '마소', '메론', ''      , ''      , ''    UNION ALL
  SELECT '민용', '배'  , '자두'  , '사과'  , '딸기' UNION ALL
  SELECT '혜연', '자두', '딸기'  , '사과'  , '배'   UNION ALL
  SELECT '수지', '오디', '코코넛', '두리안', '머루'  
)

,nm_fruit_t (nm,fruit) AS(
    SELECT  nm,c1 FROM data_t UNION ALL
    SELECT  nm,c2 FROM data_t UNION ALL
    SELECT  nm,c3 FROM data_t UNION ALL
    SELECT  nm,c4 FROM data_t
)
,array_fruit_t (nm,arr_fruit) AS(
    SELECT nm,unnest(ARRAY_ACCUM(fruit))
    from nm_fruit_t
    GROUP BY NM
    order by 1
)
,array_nm_t(fruit,nm) AS 
(
    select  fruit,unnest(ARRAY_ACCUM(nm)) 
    from nm_fruit_t
    GROUP BY  fruit
    ORDER BY 1
)
,f_nm_t (nm_a,nm_b,fruit,l_cnt) AS
(
    select distinct a.nm,b.nm,b.fruit ,count(b.fruit) over (partition by  a.nm ,b.nm  )
    from array_fruit_t a
        ,array_nm_t b
    where a.nm <> b.nm
    and b.fruit <> ''
    and b.fruit in (a.arr_fruit)
    group by a.nm,b.nm,b.fruit
    order by 1
)
,f_nm_cnt(nm ,list_nm,list_cnt) AS
(
    select nm_a ,ARRAY_ACCUM(DISTINCT nm_b) as list_nm,l_cnt
    from f_nm_t 
    group by nm_a ,l_cnt
)
select a.nm,a.c1,a.c2,a.c3,a.c4,min(b.list_nm) AS nm2, max(list_cnt) AS cnt
  from data_t a
      ,f_nm_cnt b
where a.nm = b.nm        
group by a.nm,a.c1,a.c2,a.c3,a.c4 
order by a.nm,a.c1,a.c2,a.c3,a.c4 

 

by 만세 [2021.12.23 16:40:34] 
MariaDB 로 비슷하게 해봤습니다. 결과는 얼추 비슷한데 좋은 방법인지는 잘 모르겠네요. ^^;;

SELECT NM, C1, C2, C3, C4, group_concat(CNM) NM2, CNT
  FROM (
         SELECT NM, C1, C2, C3, C4, CNM, C1E + C2E + C3E + C4E CNT, RANK() OVER(PARTITION BY NM ORDER BY CNT DESC) CNTRANK
           FROM (
                  SELECT A.NM, A.C1, A.C2, A.C3, A.C4
                       , CASE WHEN A.C1 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C1) != 0 THEN 1 ELSE 0 END C1E
                       , CASE WHEN A.C2 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C2) != 0 THEN 1 ELSE 0 END C2E
                       , CASE WHEN A.C3 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C3) != 0 THEN 1 ELSE 0 END C3E
                       , CASE WHEN A.C4 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C4) != 0 THEN 1 ELSE 0 END C4E
                       , CASE WHEN (A.C1 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C1) != 0) OR
                                   (A.C2 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C2) != 0) OR
                                   (A.C3 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C3) != 0) OR
                                   (A.C4 != '' AND INSTR(CONCAT(B.C1, B.C2, B.C3, B.C4), A.C4) != 0) THEN B.NM END CNM
                    FROM test.t3 A, test.t3 B
                   WHERE A.NM <> B.NM 
				 ) T
         ) X
 WHERE CNTRANK = 1
 GROUP BY NM;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입