'postgresql db' 태그의 글 목록 (2 Page)

'postgresql db'에 해당되는 글 12건

2019.04.11 [Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷)
2018.08.29 [Greenplum, Postgresql] 중복된 관측치 제거하기 (how to delete duplicated rows)

[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷)

Greenplum and PostgreSQL Database 2019. 4. 11. 13:22

이번 포스팅에서는 공간지리 데이터 포맷 중에서도 GML format, MapInfo MIF & TAB format, KML format 등의 벡터 데이터 (vector data)를 GDAL의 ogr2ogr 툴을 사용하여 PostgreSQL, Greenplum DB에 import하는 방법을 소개하겠습니다.

ogr2ogr 은 GDAL(Geospatial Data Abstraction Library)의 벡터 변환 유틸리티이며, 소스파일 다운로드 및 설치는 아래 링크된 사이트를 참조하세요.

GDAL download: https://trac.osgeo.org/gdal/wiki/DownloadSource
GDAL 설치 가이드 : https://trac.osgeo.org/gdal/wiki/BuildingOnUnix

참고로, 저는 처음에 GDAL 1.x 버전으로 깔았더니 아래처럼 importing 에 필요한 driver 를 찾을 수 없다는 에러가 나더군요. 그래서 GDAL2.4.1 최신 버전으로 새로 설치를 했더니 문제가 해결되었습니다. (Thanks Jack!)

ERROR 1: Unable to find driver PostgreSQL'.
  The following drivers are available:
    ->PCIDSK' -> JP2OpenJPEG'
    ->PDF' -> ESRI Shapefile'
    ->MapInfo File' -> UK .NTF'
    ->OGR_SDTS' -> S57'
    ->DGN' -> OGR_VRT'
    ->REC' -> Memory'
    ->BNA' -> CSV'
    ->GML' -> GPX'
    ->KML' -> GeoJSON'

(1) GML 포맷의 공간지리 벡터 데이터 Import 하기

포스팅에 사용한 샘플 데이터(sx9090.gml 주소 데이터)와 예제 코드는 'Mastering PostGIS' (by Dominikwicz 외) 을 참고하였습니다.

sx9090.gml 파일 다운로드: https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/os-addressbase-gml-sample-data GML 데이터 포맷은 아래에 sx9090.gml 파일을 열어서 화면캡쳐 해놓은 것처럼 Geometry 데이터를 XML(eXtensible Markup Language) 형태로 저장해놓은 파일 형식입니다.

docker로 Greenplum DB 설치하고 PostGIS 설치, 시작하는 방법은 https://rfriend.tistory.com/435 를 참고하세요.

자, 샘플 데이터를 다운로드 했다면 이제 시작해볼까요?

먼저 명령 프롬프트 창에서 sx9090.gml 파일을 docker cp로 Greenplum DB w/PostGIS 의 tmp 경로로 복사를 하겠습니다.

-- (명령 프롬프트에서) sx9090.gml 파일을 docker gpdb에 복사

ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-addressbase-gml-sample-data/sx9090.gml gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:data ihongdon$

다른 명령 프롬프트 창에서 Docker의 Greenplum DB의 gpadmin 계정으로 tmp 폴더를 확인해보면 sx9090.gml 파일이 잘 복사되었음을 확인할 수 있습니다.

-- (docker gpdb 명령 프롬프트에서) importing GML data

[gpadmin@mdw gdal-2.4.1]$ cd /tmp

[gpadmin@mdw tmp]$ ls

2.5_day_age.kml a.sql gdal-2.4.1 hsperfdata_root ne_110m_coastline.dbf ne_110m_coastline.shx sx9090.gml

[gpadmin@mdw tmp]$

명령 프롬프트 창에서 ogrinfo 유틸리티로 sx9090.gml 데이터셋의 메타데이터(metadata) 정보를 알아보겠습니다. 2015년에 GeoPlace가 만든 Ordnance Survey의 주소(address) 공간지리 데이터셋이네요.

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml

INFO: Open of `sx9090.gml'

using driver `GML' successful.

Metadata:

1: Address (Point)

[gpadmin@mdw tmp]$

ogrinfo 유틸리티로 '1: Address (Point)' layer 정보를 더 자세히 살펴보겠습니다. (ogrinfo의 -so 파라미터는 요약 정보만 보여달라는 의미임)

gml_id를 key로 하고, 총 22개의 칼럼을 가진 공간지리 데이터셋이네요.

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml Address -so

INFO: Open of `sx9090.gml'

using driver `GML' successful.

Metadata:

Layer name: Address

Geometry: Point

Feature Count: 42861

Extent: (-3.560100, 50.699470) - (-3.488340, 50.744770)

Layer SRS WKT:

GEOGCS["ETRS89",

DATUM["European_Terrestrial_Reference_System_1989",

SPHEROID["GRS 1980",6378137,298.257222101,

AUTHORITY["EPSG","7019"]],

TOWGS84[0,0,0,0,0,0,0],

AUTHORITY["EPSG","6258"]],

PRIMEM["Greenwich",0,

AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,

AUTHORITY["EPSG","9122"]],

AUTHORITY["EPSG","4258"]]

gml_id: String (0.0) NOT NULL

uprn: Real (0.0)

osAddressTOID: String (20.0)

udprn: Integer (0.0)

subBuildingName: String (25.0)

buildingName: String (36.0)

thoroughfare: String (27.0)

postTown: String (6.0)

postcode: String (7.0)

postcodeType: String (1.0)

rpc: Integer (0.0)

country: String (1.0)

changeType: String (1.0)

laStartDate: String (10.0)

rmStartDate: String (10.0)

lastUpdateDate: String (10.0)

class: String (1.0)

buildingNumber: Integer (0.0)

dependentLocality: String (27.0)

organisationName: String (55.0)

dependentThoroughfare: String (27.0)

poBoxNumber: Integer (0.0)

doubleDependentLocality: String (21.0)

departmentName: String (37.0)

[gpadmin@mdw tmp]$

ogr2ogr 로 sm9090.gml 데이터셋을 PostgreSQL, Greenplum DB에 import 해보겠습니다. 아래 ogr2ogr에서 사용한 파라미터들의 기능은 아래와 같으며, 이 외에 ogr2ogr 의 여러 파라미터 기능은 https://www.gdal.org/ogr2ogr.html 를 참고하세요. DB접속 정보는 각자 자신의 host, port, user, dbname 을 설정해주시면 됩니다.

-f : 아웃풋의 포맷이며, PostGIS로 importing할 경우 -f "PostgreSQL" 이라고 해주면 됩니다.
-nln : Importing 할 DB 스키마와 테이블 이름 (예: data_import 스키마의 osgb_address_base_gml 테이블 이름)
geomfield : 공간 필터가 동작하는 geometry field의 이름

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL"
PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin"
sx9090.gml
-nln data_import.osgb_address_base_gml -geomfield geom

[gpadmin@mdw tmp]$

이제 DBeaver query tool에서 data_import.osgb_address_base_gml 테이블을 조회해보겠습니다.

SELECT * FROM data_import.osgb_address_base_gml ORDER BY gml_id LIMIT 10;

SELECT gml_id, uprn, osaddresstoid, wkb_geometry

FROM data_import.osgb_address_base_gml

ORDER BY gml_id

LIMIT 10;

(2) MIF 포맷 (MapInfo formats) 데이터셋을 ogr2ogr 유틸리티로 PostGIS에 Import 하기

다음으로 MIF 포맷(MapInfo formats)데이터셋을 import 하는 방법을 소개할텐데요, 위에서 GML 포맷 데이터 import하는 방법과 동일합니다. 먼저 명령 프롬프트 창에서 docker cp 를 사용해서 EX_sample.mif 이름의 MIF 파일을 docker GPDB로 복사해서 넣겠습니다. (VM 환경에서 GPDB 사용 시 scp 로 파일 복사)

-- (명령 프롬프트 창에서) MIF 파일을 docker gpdb로 복사해서 넣기

ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-code-point-polygons-mif-sample-data/EX_sample.mif gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:data ihongdon$

다른 명령 프롬프트 창에서 docker GPDB의 gpadmin 계정으로 들어가서 /tmp 경로에 들어있는 파일을 조회해보면 EX_sample.mif 파일이 잘 복사되었음을 알 수 있습니다.

[gpadmin@mdw]$ cd /tmp

[gpadmin@mdw tmp]$ ls -la

total 50844

drwxrwxrwt 1 root root 4096 Apr 10 01:42 .

drwxr-xr-x 1 root root 4096 Apr 9 07:11 ..

drwxrwxrwt 2 root root 4096 Sep 11 2017 .ICE-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .Test-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .X11-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .XIM-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .font-unix

srwxrwxr-x 1 gpadmin gpadmin 0 Mar 22 07:19 .s.GPMC.sock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 01:20 .s.PGSQL.40000

-rw------- 1 gpadmin gpadmin 25 Apr 10 01:20 .s.PGSQL.40000.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 01:20 .s.PGSQL.40001

-rw------- 1 gpadmin gpadmin 25 Apr 10 01:20 .s.PGSQL.40001.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 01:20 .s.PGSQL.5432

-rw------- 1 gpadmin gpadmin 25 Apr 10 01:20 .s.PGSQL.5432.lock

-rw-r--r-- 1 501 games 3624013 Apr 8 06:12 EX_sample.mif

[gpadmin@mdw tmp]$

명령 프롬프트 창에서 ogrinfo 유틸리티로 EX_sample.mif의 메타 데이터와 요약 설명을 알아보겠습니다.

[gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif

INFO: Open of `./EX_sample.mif'

using driver `MapInfo File' successful.

1: EX_sample

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif EX_Sample -so

INFO: Open of `./EX_sample.mif'

using driver `MapInfo File' successful.

Layer name: EX_sample

Geometry: Unknown (any)

Feature Count: 4142

Extent: (281282.800000, 85614.570000) - (300012.000000, 100272.000000)

Layer SRS WKT:

PROJCS["unnamed",

GEOGCS["unnamed",

DATUM["OSGB_1936",

SPHEROID["Airy 1930",6377563.396,299.3249646],

TOWGS84[375,-111,431,0,0,0,0]],

PRIMEM["Greenwich",0],

UNIT["degree",0.0174532925199433]],

PROJECTION["Transverse_Mercator"],

PARAMETER["latitude_of_origin",49],

PARAMETER["central_meridian",-2],

PARAMETER["scale_factor",0.9996012717],

PARAMETER["false_easting",400000],

PARAMETER["false_northing",-100000],

UNIT["Meter",1.0]]

POSTCODE: String (8.0)

UPP: String (20.0)

PC_AREA: String (2.0)

[gpadmin@mdw tmp]$

준비가 되었으니 ogr2ogr 로 EX_sample.mif 데이터셋을 data_import.osgb_code_point_polygons_mif 라는 이름으로 Greenplum DB에 import 하겠습니다. (아래 PG: "xxxx" 안의 DB 설정 정보는 각자 자신의 것으로 입력해주면 됨)

-lco GEOMETRY_NAME : 레이어 생성 옵션 (디폴트 wkb_geometry)
-s_srs : input SRID
-a_srs : output SRID

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" EX_sample.mif -nln data_import.osgb_code_point_polygons_mif -lco GEOMETRY_NAME=geom -a_srs EPSG:27700

[gpadmin@mdw tmp]$

DB query tool에서 data_import.osgb_code_point_polygons_mif 테이블을 조회해보면 아래와 같이 POLYGON 공간지리 정보가 들어있는 테이블이 잘 생성되었음을 알 수 있습니다.

-- DBeaver에서 조회

SELECT * FROM data_import.osgb_code_point_polygons_mif ORDER BY ogc_fid LIMIT 10;

(3) KML(Keyhole Markup Language) 데이터셋을 ogr2ogr 유틸리티로 PostgreSQL, Greenplum DB에 import 하기

KML (Keyhole Markup Language) 데이터셋은 Google Earth에서 2D 혹은 3D로 웹브라우저 상에서 시각화할 수 있는 XML 기반의 공간지리 데이터 포맷입니다.

PostgreSQL, Greenplum DB에 KML 포맷 데이터를 Import 할 때도 GDAL의 ogr2ogr 유틸리티를 사용합니다.

먼저, 명령 프롬프트 창에서 docker cp 로 '2.5_day_age.kml' 데이터셋을 Greenplum DB docker container로 복사하겠습니다.

-- (1) Copy '2.5_day_age.kml' file to GPDB

ihongdon-ui-MacBook-Pro:~ ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/usgs-earthquakes/2.5_day_age.kml gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:~ ihongdon$

다음으로, 다른 명령 프롬프트에서 Greenplum gpadmin 계정으로 들어가서 파일이 잘 복사가 되었는지 확인해보겠습니다.

-- (2) (GPDB 명령 프롬프트 창에서) orginfo => 4개의 layer가 있음

[gpadmin@mdw tmp]$ ls -la

total 123532

drwxrwxrwt 1 root root 4096 Apr 10 13:13 .

drwxr-xr-x 1 root root 4096 Apr 9 07:11 ..

drwxrwxrwt 2 root root 4096 Sep 11 2017 .ICE-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .Test-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .X11-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .XIM-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .font-unix

srwxrwxr-x 1 gpadmin gpadmin 0 Mar 22 07:19 .s.GPMC.sock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 16 05:36 .s.PGSQL.40000

-rw------- 1 gpadmin gpadmin 27 Apr 16 05:36 .s.PGSQL.40000.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 16 05:36 .s.PGSQL.40001

-rw------- 1 gpadmin gpadmin 27 Apr 16 05:36 .s.PGSQL.40001.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 16 05:36 .s.PGSQL.5432

-rw------- 1 gpadmin gpadmin 27 Apr 16 05:36 .s.PGSQL.5432.lock

-rw-r--r-- 1 gpadmin gpadmin 4787 Apr 8 06:21 2.5_day.csv

-rw-r--r-- 1 501 games 30548 Apr 8 06:21 2.5_day_age.kml

[gpadmin@mdw tmp]$

ogrinfo 명령어로 '2.5_day_age.kml' 데이터의 메타정보를 확인해보겠습니다. Layer가 총 4개 있고, 3D Point 정보가 들어있는 KML 포맷을 공간지리 데이터셋임을 알 수 있습니다.

-- (3) metadata info.

[gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml

INFO: Open of `2.5_day_age.kml'

using driver `KML' successful.

1: Magnitude 5 (3D Point)

2: Magnitude 4 (3D Point)

3: Magnitude 3 (3D Point)

4: Magnitude 2 (3D Point)

[gpadmin@mdw tmp]$

ogrinfo 2.5_day_age.kml -al -so 로 메타정보의 4개 Layer에 대한 상세 정보를 확인해보겠습니다.

-- (4) review metadata for each layer at once in depth

[gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml -al -so

INFO: Open of `2.5_day_age.kml'

using driver `KML' successful.

Layer name: Magnitude 5

Geometry: 3D Point

Feature Count: 2

Extent: (-101.000100, -36.056300) - (120.706400, 13.588200)

Layer SRS WKT:

GEOGCS["WGS 84",

DATUM["WGS_1984",

SPHEROID["WGS 84",6378137,298.257223563,

AUTHORITY["EPSG","7030"]],

AUTHORITY["EPSG","6326"]],

PRIMEM["Greenwich",0,

AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,

AUTHORITY["EPSG","9122"]],

AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 4

Geometry: 3D Point

Feature Count: 8

Extent: (-93.869400, -30.966800) - (127.154100, 41.012000)

Layer SRS WKT:

GEOGCS["WGS 84",

DATUM["WGS_1984",

SPHEROID["WGS 84",6378137,298.257223563,

AUTHORITY["EPSG","7030"]],

AUTHORITY["EPSG","6326"]],

PRIMEM["Greenwich",0,

AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,

AUTHORITY["EPSG","9122"]],

AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 3

Geometry: 3D Point

Feature Count: 6

Extent: (-155.372167, 18.242700) - (-64.691100, 36.431400)

Layer SRS WKT:

GEOGCS["WGS 84",

DATUM["WGS_1984",

SPHEROID["WGS 84",6378137,298.257223563,

AUTHORITY["EPSG","7030"]],

AUTHORITY["EPSG","6326"]],

PRIMEM["Greenwich",0,

AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,

AUTHORITY["EPSG","9122"]],

AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 2

Geometry: 3D Point

Feature Count: 9

Extent: (-154.990005, 17.871900) - (-65.022300, 63.207400)

Layer SRS WKT:

GEOGCS["WGS 84",

DATUM["WGS_1984",

SPHEROID["WGS 84",6378137,298.257223563,

AUTHORITY["EPSG","7030"]],

AUTHORITY["EPSG","6326"]],

PRIMEM["Greenwich",0,

AUTHORITY["EPSG","8901"]],

UNIT["degree",0.0174532925199433,

AUTHORITY["EPSG","9122"]],

AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

[gpadmin@mdw tmp]$

마지막으로, 명령 프롬프트 창에서 ogr2ogr 유틸리티로 PosgreSQL, Greenplum DB에 KML 파일을 Import 해보겠습니다. (사용하고 있는 DB의 host, port, user, DBname 으로 설정 변경해주세요.)

제일 마지막에 '-append' 인자는 '2.5_day_age.kml' 데이터셋의 4개 Layer를 하나씩 순차적으로 읽어서 먼저 읽은 데이터셋 뒤에 붙여넣기로 Import 하라는 뜻입니다. ('-append' 인자를 추가하지 않으면 기존에 테이블이 존재한다는 에러 메시지가 뜹니다). 아래처럼 Warning 메시지가 나왔으면 잘 Import 가 된 것입니다.

-- (5) Import KML dataset to GPDB

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" 2.5_day_age.kml -nln data_import.usgs_earthquakes_kml -lco GEOMETRY_NAME=geom -append

Warning 1: Layer creation options ignored since an existing layer is

being appended to.

Warning 1: Layer creation options ignored since an existing layer is

being appended to.

Warning 1: Layer creation options ignored since an existing layer is

being appended to.

[gpadmin@mdw tmp]$

데이터가 잘 Import 되었으니 DBeaver DB tool에서 SQL query로 데이터를 조회해 보겠습니다.

-- (DBeaver tool에서) Select KML dataset
SELECT * FROM data_import.usgs_earthquakes_kml LIMIT 10;

서두에 KML 데이터 포맷이 Google Earth 에서 2D, 3D로 시각화해볼 수 있다고 소개하였습니다. 실제로 Google Earth 애플리케이션에서 '2.5_day_age.kml' 데이터셋을 시각화해보면 아래와 같습니다.

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore) (0)	2019.04.27
[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기 (0)	2019.04.17
[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11
[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10
[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27

Posted by Rfriend

[Greenplum, Postgresql] 중복된 관측치 제거하기 (how to delete duplicated rows)

Greenplum and PostgreSQL Database 2018. 8. 29. 22:09

이번 포스팅에서는 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;

R에서 중복된 관측치를 제거하고, 중복된 관측치에서 한개씩만 남기기는 여기를 참고하세요.
=> http://rfriend.tistory.com/165 , http://rfriend.tistory.com/235
Python에서 중복된 관측치를 제거하고, 중복된 관측치에서 한개씩만 남기기는 여기를 참고하세요.
=> http://rfriend.tistory.com/266

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

Greenplum DB, Postgresql DB에 사용할 수 있는 오픈소스 database tool DBeaver 설치 및 사용 방법 (0)	2019.03.04
[Docker] Error response from daemon: driver failed programming external connectivity on endpoint, port is already allocated 대처 방법 (port 강제 종료) (11)	2018.09.24
[Greenplum DB] Greenplum DB, MADlib, PL/R, PL/Python을 Docker Image를 이용하여 환경구성 하기 (0)	2018.08.13
[Greenplum DB] Greenplum DB에 MADlib, PL/R, PL/Python 설치한 Docker Image 만들어서 Docker Hub에 올리기 (0)	2018.08.13
[Greenplum DB] 세계 최초 오픈소스 대용량 데이터 병렬 처리 데이터 분석 플랫폼, Greenplum Database (GPDB) (3)	2018.08.08

Posted by Rfriend

이전 1 2 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'postgresql db'에 해당되는 글 12건

[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷)

(1) GML 포맷의 공간지리 벡터 데이터 Import 하기

(2) MIF 포맷 (MapInfo formats) 데이터셋을 ogr2ogr 유틸리티로 PostGIS에 Import 하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum, Postgresql] 중복된 관측치 제거하기 (how to delete duplicated rows)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바