[데이터 베이스]PostgreSQL 과제 진행중 발생한 문제 해결 01_ 두 컬럼의 크기비교

오늘은 각 병원마다 다른 EHR(electronic medical record) 전자 의무기록을 

CDM(common data model)로 변환하는 과정에서 데이터 베이스를 접한 기간이 그리 길지 않은 제가 맞닥뜨린 문제를 해결해가는 과정을 적어 내려가 보려 합니다.

 

기존 EHR의 환자 방문 기록 테이블 구조는 간략히

환자 내원 입원 퇴원의 칼럼을 갖고 있다.

환자가 외래진료로 방문하면 내원에 날짜가 기록되고

입원을 하면 입원에 날짜 데이터가 기록된다.

 

데이터를 보면 다음과 같다.

 

a라는 환자는 2019년 1월 1일에 외래진료를 받고 귀가한 뒤

2019년 2월 1일에 다시 입원 2019년 2월 9일에 퇴원한 뒤

2019년 3월 1일에 다시 외래진료를 받았다.

 

이처럼 위 병원의 EHR의 환자 방문 기록 테이블은 환자가 병원에 방문하는 순간 Row가 증가한다.

하지만 변환하고자 하는 CDM에서는 환자별로 최초 방문일부터 최종 방문일까지 한 개의 Row로 관리한다.

 

 

위의 EHR 데이터로부터 각 환자의 최초 방문일과 최종 방문일을 뽑아내야 하는 과제이다.

 

처음 시도는 PostgreSQL에서 아래와 같이 coalesce 함수를 사용해 보았다.

coalesce함수는 ORACLE의 NVL MSSQL이 ISNULL과 같은 함수로

coalesce(param1, param2) 이런 식으로  사용되며 param1과 param2중에 null이 아닌 값을 return 해준다.

둘 다 null이 아닐 경우는 앞의 param1을 return 함.

 

select 환자,
coalesce(min(내원),min(입원)) as 최초방문일
from test
group by 환자

 

위와 같이 coalesce함수를 사용한 결과

아래와 같은 결과가 출력되었다.

 

문제 해결인 듯싶지만 여기서 다시 한번 데이터를 들여다보면 c환자는 2019-07-09일에 내원한 적이 있지만,

그보다 더 전 2019-01-05일에 입원을 했던 적이 있다.

위에서도 말씀드렸듯이 coalesce함수는 둘 다 null이 아닌 값이 들어왔을 때 먼저 들어온 값을 리턴해준다.

때문에 2019-07-09일이 select 된 것이다.

 

 

 

 

 

 

 

 

다음 시도는 case함수를 사용하여 다시 도전해 보았다. 

case함수는 case when 조건 then 값  else 값  end 형식으로 사용한다 반드시 마지막에 end를 꼭 붙여주어야 한다.

select 환자,
case when  min(내원) <=  min(입원)
	then min(내원)
    when  min(내원) >  min(입원)
	then min(입원)
	else min(내원)
	end as 최초방문
from test

group by 환자

 

group by 환자로 묶어서 가장 작은 내원일이 가장 작은 입원일보다 작거나 같으면 입원 이전에 외래로 내원을 했던 적이 있다는 것이기 때문에 then min(내원)을 출력하도록 했고.

 

가장 빠른 내원일 min(내원)이 가장 빠른 입원일 min(입원) 일보다 크면(늦으면)

외래방문을 하기 전에 입원을 했던 적이 있었던 것이므로

min(입원)을 출력하도록 했다. 

 

하지만 여기서 문제가 외래로 방문한 적이 없이 바로 입원을 한 경우이다.

e환자를 보면 min(내원) = null,  min(입원) = 2019-07-07이므로

null이 출력된다.

 

 

 

 

 

다음시도는 PostgreSQL union을 사용하여 접근해보았다.

union은 두 개의 테이블을 하나로 합쳐준다.

아래와 같이 두개의 테이블이 있다고 했을 때 

 

table 1                 table 2

____________       _____________

ID  | value          ID | value

-----------         -------------

a    |  10             a   |   10

b    |  20             d   |   40

c    |  30             e   |  50

 

select * from table1

union

select * from table2 

 

하게 되면 결과는 다음과 같이 출력된다.

ID   |   value

---------------

a    |   10

b    |   20

c    |   30

d    |   40

e    |   50

 

즉 union시 행의 모든 값이 table1의 a , 10과 table 2의 a , 10처럼 같으면 1개만 출력된다.

하지만 union all을 사용하게 되면 중복 생략 없이 a , 10은 두 번 출력되게 된다.

 

 

 

from에서 환자 내원 일과 입원일을 방문일로 각각 select 하여 union all 하여 그중에서 가장 빠른(작은) 날짜를 최초 방문일로 select 하였고 최종 방문일은 case문을 사용하여 select 하였다.

사용한 쿼리는 다음과 같다.

 

select t1.환자 
	, min(t1.방문일) as 최초방문일 
	, (case when max(t1.방문일) <= max(t2.입원) and max(t2.입원)> max(t2.퇴원)
			then  current_date
			when max(t1.방문일) <= max(t2.입원) and max(t2.입원)<= max(t2.퇴원)
			then max(t2.퇴원)
			else max(t1.방문일) end) as 최종방문일

from ( select 환자, 내원 as 방문일
		from test
		where 내원 is not null

		union all

		select 환자, 입원 as 방문일
		from test
		where 입원 is not null) t1
		inner join test  t2 on (t2.환자 = t1.환자) 
		
group by t1.환자

 

 

 

 

 

 

이렇게 결과적으로 원하는 결과를 뽑아낼 수 있었다.