데이터베이스 질문과 답변

데이터에 쌍으로 존재하는 데이터만 추출하는 쿼리를 여쭈어봅니다. 0 7 3,324

by 짱구 [Oracle 기초] oracle 쌍 query [2021.04.19 13:30:11]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
데이터 중 WHERE IN 조건에 개수만큼의 쌍으로 된 데이터만 구하고 싶습니다.
 
예)
 
WITH TEST AS (
  SELECT '한국' AS G FROM DUAL UNION ALL
  SELECT '중국' AS G FROM DUAL UNION ALL
  SELECT '중국' AS G FROM DUAL UNION ALL
  SELECT '일본' AS G FROM DUAL UNION ALL
  SELECT '한국' AS G FROM DUAL UNION ALL
  SELECT '한국' AS G FROM DUAL UNION ALL
  SELECT '일본' AS G FROM DUAL
)
SELECT *
  FROM TEST
 WHERE G IN ('한국', '중국')

 

?
1
2
3
4
5
6
7
G
-------
한국
중국
중국
한국
한국

이렇게 나옵니다. 하지만 구하고자 하는 답은 아래와 같습니다.

?
1
2
3
4
5
6
G
-------
한국
중국
중국
한국

즉, WHERE IN 조건에 제일 낮은 COUNT를 기준으로 페어링 되게 데이터를 가져오고 싶습니다.

이게 query로 가능할까요?

아니면 procedure로 해야 하나요?

읽어주셔서 감사합니다.

by 샤랄라 [2021.04.19 16:22:19]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH TEST AS (
  SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 8 seq, '대만' AS G FROM DUAL
)
select *
from (SELECT g
           , row_number() over(partition by g order by seq) rn
           , count(*) over(partition by g) cn
      FROM TEST
      WHERE G IN ('한국', '중국')
      order by seq
     )
where rn <= 2
and cn >= 2
;

 

by 짱구 [2021.04.19 16:37:19]

답변 감사합니다.

염치 없지만 추가로 여쭈어 봅니다.

WITH TEST AS (
  SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 8 seq, '대만' AS G FROM DUAL 
)
select *
from (SELECT g
           , row_number() over(partition by g order by seq) rn
           , count(*) over(partition by g) cn
      FROM TEST
      WHERE G IN ('한국', '중국','대만')
      order by seq
     )
where rn <= 2
and cn >= 2
;

에 결과는

G
-------
한국
중국
대만

이렇게 나와야 하거든요.

where rn <= 2
and cn >= 2
;

이 부분도 query화 할 수 있을까요?

by 샤랄라 [2021.04.19 16:49:59]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH TEST AS (
  SELECT 1 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 2 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 3 seq, '중국' AS G FROM DUAL UNION ALL
  SELECT 4 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 5 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 6 seq, '한국' AS G FROM DUAL UNION ALL
  SELECT 7 seq, '일본' AS G FROM DUAL UNION ALL
  SELECT 8 seq, '대만' AS G FROM DUAL
)
select g
from (select seq, g
           , rn
           , cn
           , min(cn) over() mn
      from (SELECT seq, g
                 , row_number() over(partition by g order by seq) rn
                 , count(*) over(partition by g) cn
            FROM TEST
            WHERE G IN ('한국', '중국', '대만')
           )
     )
where 1=1
and cn >= mn
and rn <= mn
order by seq

 

by 짱구 [2021.04.19 17:02:28]

정말 감사합니다.~

by 짱구 [2021.04.19 17:41:43]
?
1
WHERE G IN ('한국', '중국', '미국')

이러면 0건이 나와야 하는데 이것 또한 여쭈어봅니다.

by 마농 [2021.04.20 09:08:49]
?
1
2
3
4
5
6
7
8
9
10
11
SELECT g
  FROM (SELECT g
             , COUNT(*) OVER(PARTITION BY rn) cnt
          FROM (SELECT g
                     , ROW_NUMBER() OVER(PARTITION BY g ORDER BY 1) rn
                  FROM test
                 WHERE g IN ('한국', '중국', '미국')
                )
        )
 WHERE cnt = 3  -- IN 조건의 개수
;

 

by 짱구 [2021.04.20 09:26:09]

감사합니다.

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