Category: SQL

CDM을 이용하는 사람이 생김에 따라서 person_id를 한 번 더 익명화하기로 결심했다. 매번 CDM에 자료가 추가되는 방법이면 난수를 생성하는 방법을 고민해야 할 것이다. 하지만, 수정하면서 자료를 추가하고 있기 때문에 매번 새로운 수를 만들어도 될 것 같았다. 수십만건의 난수를 효과적으로 만들기 위하여 여러 방법을 시도해 보았다. R이라면 sample(, replace = F)로 만들면 그만인데, SQL에서는 그런 명령어는 못 찾았다. 그래서 비슷한 개념으로 만들어 쿼리를 작성해 보았다. 

CREATE TABLE mapping.person_anonymous_from AS SELECT DISTINCT person_id FROM cdm.person;
ALTER TABLE mapping.person_anonymous_from ADD COLUMN temp_join_a bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 );

우선 person 테이블에서 고유한 person_id를 추출하여 테이블을 하나 만든다. 그리고 생성된 테이블에서 bigint 속성을 가지며 추가될 때마다 1씩 커지는 temp_join_a 컬럼을 만든다.

CREATE TABLE mapping.person_anonymous_to AS (SELECT DISTINCT trunc(random() * 1e8 + 1) AS random_key FROM generate_series(1, 1e8));
ALTER TABLE mapping.person_anonymous_to ADD COLUMN temp_join_b bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 );

random() 함수는 0~1의 실수값을 생성하기 때문에 정수값으로 변환한다. 병원 환자 수를 보니 1천만개 정도 만들면 몇 년은 충분할 것 같았다. 그래서 생성된 난수에 1천만을 곱하고 1을 더한 후 정수값으로 처리한 작업을 1천만번 반복한다. 이런 방법으로 난수를 만들면 중복이 생기기 때문에 고유한 값만을 선택하여 이 값을 다른 테이블에 random_key라는 컬럼 이름으로 만든다. 이 방법은 중복되지 않는 수를 생성하는데에는 좀 멍청한 것 같다. 1억개를 만들면 60% 정도만 남는다. 그리고 마찬가지로 temp_join_b 컬럼을 만든다.

CREATE TABLE mapping.person_anonymous AS (SELECT a.person_id, b.random_key FROM mapping.person_anonymous_from AS a INNER JOIN mapping.person_anonymous_to AS b ON a.temp_join_a = b.temp_join_b);

이렇게 만들어진 random_key의 순서는 충분히 임의적이라고 할 수 있다. temp_join_a에 맞추어 INNER JOIN의 형태로 결합하여 테이블을 만든다. random_key가 person_id보다 충분히 숫자가 크기 때문에 공통인 것만 선택을 하면 된다. 이러면 충분히 식별력이 없는 새로운 person_id를 만들어 낼 수 있다.

PostgreSQL 문서에 의하면 distinct는 처음 나타나는 순서대로 정렬이 되는 것이라고 한다.

오류 없이 import 가 가능하기 때문에 date 속성을 text로 받아서 처리를 하였으나, 각종 CDM 쿼리들은 start_date 를 이용하여 쿼리가 수행되기 때문에 변경하는 과정을 추가하기로 했다. 우선 절대 다수의 원자료 값들은 YYYY-MM-DD의 10글자로 되어 있는데 일부에서는 4글자 종류가 있는 것이 있었고 확인해보니 NULL으로 변환전의 ‘해당없음’이었다. 그렇다면 ‘해당없음’을 NULL로 변환한 이후에 변환 과정을 하면 된다.

ALTER TABLE cdm.drug_exposure ALTER COLUMN drug_exposure_start_date TYPE DATE USING to_date(drug_exposure_start_date, 'YYYY-MM-DD');

자료를 입력할 때에는 결측 값을 미리 확실하게 정해 두어야 한다고 강의들 들은 적이 있다. Vital 테이블을 보니 그게 왜 필요한지 알겠다. 사람마다 빈 칸, ‘.’, ‘-‘으로 여러 방식으로 입력하고 있다. 음수가 나올 수 없는 경우에 음수가 있는 경우도 있는데, 이런 항목도 역시 고려가 필요하다.

컬럼 속성을 변경하지 않고 다른 속성인 것처럼 처리를 해야할 일이 있을 경우에는 CAST를 사용한다.

CAST(VS_PULSE_RATE AS NUMERIC)

CDM 구축하는 과정에서 파악한 것으로 vital sign 측정 결과를 입력할 때 다른 항목을 입력하지 않으면 공란으로 처리가 된다는 것 이다. Vital sign을 measurement 테이블에 넣는 과정에서 빈 칸을 그대로 두면 DB 사이즈가 굉장히 커지게 된다. 따라서 공란이 있으면 제거하는 과정이 필요하다. 빈 칸을 찾는 방법 중 하나는 trim과 length를 이용한 것이다. 빈 칸은 trim 을 거 치면 빈 칸에 대한 정보가 소실되게 되고 길이로 바꾸면 0이 된다.

UPDATE cdm.vital SET vs_height=NULL WHERE LENGTH(TRIM(vs_height))=0;

Measurement 테이블에 결과를 합칠 때에 다음을 추가하면 값이 있는 항목만 선택되게 된다.

WHERE vs_height IS NOT NULL