[Oracle] 분석함수(OVER)를 활용하여 같은 값 찾기

작성일자 : 2023-11-05

Ver 0.1.1

0. Intro

Oracle에서 같은 값을 찾기 위해서는 서브쿼리나 GROUP BY 및 HAVING 절을 사용할 수 있지만, 분석함수(OVER 절)를  사용하는 것이 가장 효율적이다.

 

같은 값을 찾기 위해 동명이인을 찾는다는 시나리오를 설정했을 때, 동일한 이름이 여러 건 조회되었을 경우일 수도 있고, 동일한 이름이 여러 건 조회되고 동일한 이름 내에서 생년월일 등 추가로 식별할 수 있는 조건이 다를 경우 동명이인이라고 판단할 수 있다.

 

분석 함수 및 GROUP BY 에 대한 내용은 아래 포스팀을 참고하자.

[SQL]PARTITION/WINDOW

 

[SQL]GROUP BY

---

1. 이름으로만 동명이인 찾기

(쿼리문은 Oracle의 LIVESQL을 통해 실행)

WITH player_t(player, birthdate, team) AS (
  SELECT 'Silva', '1994-08-14', 'Manchester City' FROM dual UNION ALL
  SELECT 'Silva', '1984-09-22', 'Chelsea'    FROM dual UNION ALL
  SELECT 'Foden', '2000-05-28', 'Manchester City' FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Manchester United'    FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Sporting CP'  FROM dual
)

SELECT player
     , birthdate
     , team
     , COUNT(*) OVER(PARTITION BY player) AS sname_cnt
  FROM player_t
 ORDER 
    BY sname_cnt desc;

SNAME_CNT에 의해  Fernandes와 Silva라는 이름이 포함된 선수가 누구인지 파악할 수 있다.

그렇지만 동명이인을 가리기 위해서는 이름만으로는 구별할 수 없다. 이에 생년월일까지 포함해서 건수를 집계해봐야한다.

---

2. 이름 + 생년월일로 동명이인 찾기

WITH player_t(player, birthdate, team) AS (
  SELECT 'Silva', '1994-08-14', 'Manchester City' FROM dual UNION ALL
  SELECT 'Silva', '1984-09-22', 'Chelsea'    FROM dual UNION ALL
  SELECT 'Foden', '2000-05-28', 'Manchester City' FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Manchester United'    FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Sporting CP'  FROM dual
)

SELECT player
     , birthdate
     , team
     , COUNT(DISTINCT birthdate) OVER(PARTITION BY player) AS sname_cnt
  FROM player_t
 ORDER 
    BY sname_cnt desc;

 

SELECT문의 COUNT안에 DISTINCT birthdate을 넣은 결과 Fernandes는 동일 인물임이 판별되었다. 

---

3. 동명이인 여부 표시하기

DECODE 함수를 사용하여 동명이인 여부를 표현할 수도 있다.

WITH player_t(player, birthdate, team) AS (
  SELECT 'Silva', '1994-08-14', 'Manchester City' FROM dual UNION ALL
  SELECT 'Silva', '1984-09-22', 'Chelsea'    FROM dual UNION ALL
  SELECT 'Foden', '2000-05-28', 'Manchester City' FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Manchester United'    FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Sporting CP'  FROM dual
)

SELECT player
     , birthdate
     , team
     , DECODE( COUNT(DISTINCT birthdate) OVER(PARTITION BY player)
             , 1, 'N', 'Y' ) AS sname_yn
  FROM player_t;

---

4. GROUP BY ~ HAVING으로 동명이인 찾기

동명이인에 해당하는 사람의 이름만 추출하고 싶을 때는 분석함수를 사용하기 보다는 GROUP BY 절과 HAVING 절을 활용하면 된다.

WITH player_t(player, birthdate, team) AS (
  SELECT 'Silva', '1994-08-14', 'Manchester City' FROM dual UNION ALL
  SELECT 'Silva', '1984-09-22', 'Chelsea'    FROM dual UNION ALL
  SELECT 'Foden', '2000-05-28', 'Manchester City' FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Manchester United'    FROM dual UNION ALL
  SELECT 'Fernandes', '1994-09-08', 'Sporting CP'  FROM dual
)

SELECT player
     , COUNT(*) AS sname_cnt
  FROM player_t
 GROUP 
    BY player
HAVING COUNT(DISTINCT birthdate) >= 2

 

참고 사이트 : https://gent.tistory.com/524