데이터베이스 질문과 답변
데이터에 쌍으로 존재하는 데이터만 추출하는 쿼리를 여쭈어봅니다.
0
7
2,972
by 짱구 [Oracle 기초] oracle 쌍 query [2021.04.19 13:30:11]
데이터 중 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 ('한국', '중국')
G ------- 한국 중국 중국 한국 한국
이렇게 나옵니다. 하지만 구하고자 하는 답은 아래와 같습니다.
G ------- 한국 중국 중국 한국
즉, WHERE IN 조건에 제일 낮은 COUNT를 기준으로 페어링 되게 데이터를 가져오고 싶습니다.
이게 query로 가능할까요?
아니면 procedure로 해야 하나요?
읽어주셔서 감사합니다.
by 샤랄라
[2021.04.19 16:22: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
;
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]
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]
WHERE G IN ('한국', '중국', '미국')
이러면 0건이 나와야 하는데 이것 또한 여쭈어봅니다.
by 마농
[2021.04.20 09:08:49]
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]
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.