꿈꾸는 개발자, DBA 커뮤니티 구루비

Data Correction query를 작성하고 싶습니다.

by 바람이불면 [SQL Query] 중복삭제 쿼리 프로시져 [2013.12.23 10:10:14]

안녕하세요 바람이 불면입니다.
아래와 같은 자료가 있습니다.

id     prj_id     name    period     amount (KRW to USD)
==================================
1    10          A         2013-01       100
2        10          A         2013-02       100.1
3        10          A         2013-02       100
4    10          A         2013-03       100
5        10          A         2013-03       100.3
6        10          A         2013-03       99.89

프로시저로 history를 처리하다가 저런 결과가 나왔습니다.

각 period 별로 2013-01의 amount인 100을 제외한 나머지 값을 제거하여 아래와 같은 결과를 만드려고 합니다.
즉, 기간에 중복으로 들어간 id들( 2, 5, 6 )을 삭제하여 아래와 같은 결과를 만드려고 합니다.

id     prj_id     name    period     amount (KRW to USD)
==================================
1    10          A         2013-01       100
3        10          A         2013-02       100
4    10          A         2013-03       100

어떻게 하면 될까요?

* 부연 설명입니다.
다른 prj_id가 많이 있는 상황입니다. 그중에 prj_id는 중복자료가 없는 경우도 있습니다.
추측컨데, key로 쓸수 있는 값이 prj_id 와 period 조합이며 기간을 표시해놓은 것은 2013-01 이전에도 자료가 들어가 있는 상황입니다.
아울러 재귀 호출로 중복이 되지 않는 최초의 자료를 찾아서 나머지들과 비교하여 삭제를 할 수 있도록 해야하는데 방법을 못찾겠네요...

by 용근님 [2013.12.23 10:12:27]

not in ??

by 아발란체 [2013.12.23 10:17:27]

SELECT * FROM TABLE_NAME WHERE amount = 100

--또는

SELECT id, name, period, amout FROM (
  SELECT
   id, period, amount, ROW_NUMBER() OVER(PARTITION BY name, period ORDER BY id) AS rno 
  FROM
   TABLE_NAME 
)
WHERE
  rno = 1

by 빈이 [2013.12.23 10:19:08]

SELECT * FROM TAB WHERE AMONUT = '100';

예제로 주신 데이터 위에 처럼만 해도 답이 나오는데요~_~?

추가로 설명에서 2013-01을 설명하신 이유가 있나요?

by 바람이불면 [2013.12.23 10:24:25]

좀 더 상세한 정보들로 수정하였습니다.

by 아발란체 [2013.12.23 11:57:56]

--최초 1건
WITH T(id, prj_id, name, period, amount) AS (
 SELECT '1','10','A','2013-01','100' FROM DUAL UNION ALL
 SELECT '2','10','A','2013-02','100.1' FROM DUAL UNION ALL
 SELECT '3','10','A','2013-02','100' FROM DUAL UNION ALL
 SELECT '4','10','A','2013-03','100' FROM DUAL UNION ALL
 SELECT '5','10','A','2013-03','100.3' FROM DUAL UNION ALL
 SELECT '6','10','A','2013-03','99.89' FROM DUAL
)
SELECT
 id, period, amount
FROM (
 SELECT
  id, period, amount, ROW_NUMBER() OVER(PARTITION BY name, period ORDER BY id) AS rno
 FROM
  T
) WHERE
 rno = 1
;


--100 값을 가진 최초 1건, 100인 것이 없을 경우 최초 1건
WITH T(id, prj_id, name, period, amount) AS (
 SELECT '1','10','A','2013-01','100' FROM DUAL UNION ALL
 SELECT '2','10','A','2013-02','100.1' FROM DUAL UNION ALL
 SELECT '3','10','A','2013-02','100' FROM DUAL UNION ALL
 SELECT '4','10','A','2013-03','100' FROM DUAL UNION ALL
 SELECT '5','10','A','2013-03','100.3' FROM DUAL UNION ALL
 SELECT '6','10','A','2013-03','99.89' FROM DUAL
)
SELECT
 id, period, amount
FROM (
 SELECT
  id, period, amount, ROW_NUMBER() OVER(PARTITION BY name, period ORDER BY DECODE(amount, '100', 1, id)) AS rno
 FROM
  T
) WHERE
 rno = 1

by 마농 [2013.12.23 12:02:18]

-- 우선 빈이님 말대로 = 100 조건을 주고 조회한 값이 정상적으로 나온다면?
-- 나머지 데이터들을 != 100 조건 주고 지우시면 됩니다.
-- 꼭 100 이 아니라 최초로 입력된 자료만 남기고자 한다면?
DELETE FROM t a
 WHERE id > (SELECT MIN(id)
               FROM t
              WHERE prj_id = a.prj_id
                AND period = a.period
             )
;
-- 그러나 이 쿼리를 사용하기 위해서는 반드시 다음 인덱스가 있어야 합니다.
-- (prj_id, period, id) 없을 경우 이거라도 (prj_id, period)

데이터베이스 질문과 답변