이번 포스팅에서는 Greenplum DB, Postgresql DB에서 중복된 관측치(duplicated observations, duplicated rows)가 있을 경우에 제일 처음나 제일 마지막의 관측치 하나만 남겨놓고 나머지 중복 관측치들은 삭제하여 유일한 관측치만 남기는 2가지 방법을 소개하겠습니다. 


(방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


(방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거(drop)하기

            : CREATE TABLE new_table & DROP TABLE original_table






  (방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


Greenplum Database에 중복된 관측치가 들어있는 간단한 예제 테이블을 만들어보겠습니다. 'name'과 'price'의 두 개 변수를 기준으로 해서 중복 여부를 판단하여 중복된 관측치를 제거하는 예제입니다. 



drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


select * from prod_master;

 





이제 DELETE query를 사용하여 중복된 관측치 중에서 첫번째 것만 남기고 나머지 중복된 관측치들은 제거해보겠습니다. DELETE 문은 'DELETE FROM table_name WHERE [conditio];' 의 형태로 사용합니다. 


이때 주의할 점은 sub query로 row_number() over (partition by ) 라는 window function을 사용해야만 중복된 관측치들 중에서 각각의 "첫번째 관측치"를 남겨놓을 수 있다는 것입니다 (아래 query의 빨간색 부분).  자칫 잘못하면 중복이 된 값은 하나도 남김없이 모조리 삭제하는 실수를 범할 수 있으니 조심하시기 바랍니다. 



delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1

);

 


[Messages]

DELETE 4 Query returned successfully in 177 msec



select * from prod_master;






혹시 중복된 관측치들 중에서 "가장 앞에 있는"(위의 예시) 관측치 대신에 "가장 뒤에 있는" 관측치를 남기고 나머지 중복된 관측치는 제거하고 싶다면 row_number() over() 의 window function 에서 order by id desc 를 사용해주면 됩니다. 



--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);



---- keep the last observation in case of duplication

delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id desc) as row_num 

from prod_master) a

where a.row_num > 1

);


select * from prod_master;


 



위의 방법 1은 원래의 테이블을 그대로 유지한 상태에서 중복된 관측치를 삭제하므로, 새로운 테이블을 만들거나 기존 테이블을 삭제할 필요가 없습니다만, 대용량 데이터를 대상으로 다수의 중복된 관측치를 제거해야 하는 경우 (아래의 방법2 대비 상대적으로) 속도가 느리다는 단점이 있습니다.  대용량 데이터의 경우 빠른 속도로 중복처리하려면 아래의 '방법2'를 고려해보길 권합니다. ('Messages'에 나오는 실행 속도를 비교해보면 아래의 '방법2'가 빠른 것을 알 수 있습니다. 지금 예제야 관측치 7개짜리의 간단한 예제인지라 177 msec vs. 118 msec로 밀리세컨 단위 차이라고 무시할 수도 있겠지만, 데이터가 대용량이 되면 차이가 무시할 수 없게 커질 수 있습니다.)




  (방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거하기

            : CREATE TABLE new_table & DROP TABLE original_table


 

--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


---- keep the first observation in case of duplication by creating a new table

drop table if exists prod_master_unique;

create table prod_master_unique as (

select * from prod_master 

where id NOT IN (

select id

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1)

) distributed randomly;




[Messages]

SELECT 3 Query returned successfully in 118 msec.

 



select * from prod_master_unique order by id;





-- Drop the original table to save disk storage

drop table prod_master;

 




 많은 도움이 되었기를 바랍니다. 


Posted by R Friend R_Friend