진료내역 공공데이터를 사용한 진료 경향성 분석: 연령,도시,진료과목에 따른

[7기_정호진]

medical_upload.pptx

PLSQL_portfolio.docx

질문

1. 연령별로 진료과목별 입/내원, 방문횟수 순위 1~3위까지를 살펴보고 경향분석

2. 서울지역와 그 외 지역의  진료일수가 유의미한 차이가 나는가? 

3. 5년간 20~34 세 사이에 진료일수 증가율 출력 (상위 10개 진료과목에 대해서)

데이터

국가중점 개방데이터 (진료내역정보) 2011년, 2016년 데이터 (총 약 2400만건)

(대한민국 인구에서 무작위 100만명 추출후 데이터 구성)

(다운로드) https://www.data.go.kr/dataset/15007115/fileData.do

데이터 특성

기준년도, 가입자번호, 성별, 진료과목 , 시/도, 연령대, 진료기간 …. 등 총 19칼럼으로 구성

100만명이으로 1년당 고유 가입자번호가 100만명 평균 11~12건 병원방문으로 총 1100~1200만 rows가 기록됨.

이런식의 데이터 형태로 제공됨. 전부코드형태라서 의미있는 이름을 보려면 조인을 시도해야함.

Table 생성 (메인 데이터(2016,2011) / 연령 조인/ 진료과목 조인) 

Main table x2 (medical, medical3)

CREATE TABLE medical(

    stnd_y NUMBER(10),

    div_id NUMBER(20),

    KEY_seq        NUMBER(10),

    sex NUMBER(10),

    age_Group NUMBER(10),

    sido NUMBER(10),

    recu_fr_dt NUMBER(10),

    form_cd varchar2(30),

    dsbjt_cd varchar2(30),

    main_sick VARCHAR2(50),

    sub_sick VARCHAR2(50),

    vscn NUMBER(10),

    recn NUMBER(10),

    edec_add_rt NUMBER(10),

    edec_tramt NUMBER(10),

    edec_sbrdn_amt NUMBER(10),

    edec_jbrdn_amt NUMBER(10),

    tot_pres_dd_cnt NUMBER(10),

    data_std_dt NUMBER(10)

    );

연령대 정보 테이블 생성

CREATE TABLE age_group_name(

G_num NUMBER(10),

    age_gap VARCHAR2(30)

    );

진료과목 테이블 생성

CREATE TABLE dsbjt_cd_name(

code varchar2(20),

    cname VARCHAR2(100)

    );

연령대 조회 테이블 생성

CREATE table age_range(

age NUMBER(10)

    );

1. 연령별로 진료과목별 입/내원, 방문횟수 순위 1~3위까지를 살펴보고 경향분석

연령별 진료과목별 방문횟수  

0~4세를 제외한 모든 연령대에서 내과가 1위를 차지 하였으면 40세 이전에는 이빈후과를 찾는 빈도수가 정형외과보다 높았지만 40세 이후로는 정형외과가 앞지르기 시작하며 나이대가 올라갈수록 점점 비율이 높아짐을 알수있다. 

60세 이후부터는 이비인후과가 3위밖으로 밀려남과 동시에 안과가 3위권으로 진입하기 시작한다.

진료일수로 분석  

방문횟수와 마찬가지로 이비인후과와 정형외과가 거의 상위권을 차지했다 하지만 특이한점은 방문횟수에서 보이지 않던 정신과가 15세 이상 연령대에서 대부분 2위를 차지 하였다. 정신과는 1인당 방문빈도는 적지만 한번 방문할경우 치료기간이  길다는것을 유추할수 있다. 

set serveroutput on

begin

for i in (WITH v AS ( SELECT COUNT(*) cnt_group, age_group ag FROM MEDICAL WHERE stnd_y=2016 GROUP BY age_group)

  ,v2 AS (SELECT age_group ag,COUNT(DISTINCT(div_id)) cnt_v2 FROM MEDICAL WHERE stnd_y=2016 GROUP BY age_group)

SELECT age_group, cname, ROUND((cnt/(SELECT cnt_v2 FROM v2 WHERE ag=ee1.age_group )),2) avg_people ,(ROUND(cnt / (SELECT cnt_group FROM v WHERE ag=ee1.age_group ),3) *100) norm , age_gap,rk,cnt 

FROM

(

SELECT e1.r,e1.age_group,e2.cname cname,cnt ,e3.age_gap,RANK() OVER (PARTITION BY age_group ORDER BY cnt desc) rk 

FROM ( SELECT age_group,dsbjt_cd r , count(*)  cnt

FROM medical

WHERE stnd_y=2016

GROUP BY GROUPING sets((age_group,dsbjt_cd))

ORDER BY age_group DESC, cnt  DESC) e1, 

dsbjt_cd_name e2 , 

age_group_name e3 

WHERE e1.r=e2.code AND e1.age_group=e3.g_num

) ee1 where rk<4

) loop                                 

if i.rk =1 then

dbms_output.put_line(chr(7));

dbms_output.put_line(i.age_gap);

end if;

dbms_output.put_line('r' || rpad(i.rk,2,' ')|| ': ' ||  rpad(i.cname,12,' ') ||  ' ' || rpad(i.avg_people,3,' ') || ' 번   ' || rpad('■',round(i.avg_people*3),'■') || '(' || i.norm || '%' || ')');

end loop;

end;

/

2. 서울지역와 그 외 지역의  진료일수가 유의미한 차이가 나는가? (2016년 자료 기준)

		25~39세	60세~74세
평균일수	서울	11.7일	35.74일
	그외지역	12.23일	44.08일
Welch Two sample T-test P(T<= t)		0.0000	0.0000

서울과 서울이외 지역의 1인당 평균 병원진료일수가 유의한 차이를 보임

http://stattrek.com/online-calculator/t-distribution.aspx 에서 위의 df 값과 t-score 값으로 확인가능

set serveroutput on

set lines 4000

declare 

sp number(38,10);

ss1 number(38,10);

ss2 number(38,10);

n1 number(10);

n2 number(10);

u1 number(38,10);

u2 number(38,10);

df number(38,10);

t_score number(38,10);

type v_arr is table of number(10);

age_list v_arr;

start_age varchar2(20);

finish_age varchar2(20);

temp number(10);

begin

-- 검색할 age 영역 지정 --

age_list:=v_arr(13,14,15);

delete from age_range;

-- 테이블에 집어넣음

for i in 1..age_list.count loop

insert into age_range values(age_list(i));

end loop;

-- start age name 얻음

select age_gap into start_age

from age_group_name

where g_num=age_list(1);

-- finish age name 얻음

temp:=age_list.count;

temp:=age_list(temp);

select age_gap into finish_age

from age_group_name

where g_num=temp;

--서울지역 평균 진료일수 계산

SELECT (SELECT SUM(recn) from medical where sido=11 and stnd_y=2016  AND age_group IN (select * from age_range)) /  (SELECT COUNT(DISTINCT(div_id)) FROM medical where sido=11 and stnd_y=2016  AND age_group IN (select * from age_range)) into u1

FROM dual ;

--그외지역 평균진료일수 계산

SELECT (SELECT SUM(recn) from medical WHERE sido!=11 and stnd_y=2016 AND age_group IN (select * from age_range)) /  (SELECT COUNT(DISTINCT(div_id)) FROM medical WHERE sido!=11 and stnd_y=2016 AND age_group IN (select * from age_range)) into u2

FROM dual ;

-- 서울지역 표본개수

SELECT COUNT(DISTINCT(div_id)) into n1

FROM medical where sido=11 and stnd_y=2016  AND age_group IN (select * from age_range);

-- 그외지역 표본개수

SELECT COUNT(DISTINCT(div_id)) into n2

FROM medical WHERE sido!=11 and stnd_y=2016 AND age_group IN (select * from age_range);

-- 서울지역 variance 계산 --

SELECT sqaure/(n1-1) into ss1

from(

SELECT SUM(POWER(sum_r-u1,2)) AS sqaure

FROM (SELECT SUM(recn) sum_r

FROM medical 

where sido=11 and stnd_y=2016  AND age_group IN (select * from age_range)

GROUP BY div_id));

-- 서울외지역 variance 계산 --

SELECT sqaure/(n2-1) into ss2

from

(

SELECT SUM(POWER(sum_r-u2,2)) AS sqaure

FROM (SELECT SUM(recn) sum_r

FROM medical

WHERE sido!=11 and stnd_y=2016 AND age_group IN (select * from age_range)

GROUP BY div_id));

-- degree of freedom 계산 --

df := power((ss1/n1 + ss2/n2),2) / (ss1*ss1 / (n1*n1*(n1-1)) + ss2*ss2/(n2*n2*(n2-1)));

-- t score caculation

t_score:= ((u1-u2)/ (sqrt(ss1/n1 + ss2/n2)));

dbms_output.put_line( substr(start_age,1,2) || '세 부터 ' || substr(finish_age,-3) || ' 까지');

dbms_output.put_line(rpad('*',60,'*' ));

dbms_output.put_line( rpad('서울지역 평균:',20,' ') || rpad(round(u1,2),20,' ') || '서울외 평균: ' || rpad(round(u2,2),20,' ') );

dbms_output.put_line(rpad('서울표본개수: ',20,' ') || rpad(round(n1,2),20,' ') ||'서울외 표본개수: '  || rpad(round(n2,2),20,' ') );

dbms_output.put_line(rpad('서울표본분산: ',20,' ') || rpad(round(ss1,2),20,' ') ||'서울외 표본분산: '  || rpad(round(ss2,2),20,' ') );

dbms_output.put_line(chr(13));

dbms_output.put_line('degree of freedom: ' || df);

-- welch's t-test 

dbms_output.put_line(chr(13));

dbms_output.put_line('Welch Two sample T-test');

dbms_output.put_line('t-score: ' || t_score );

dbms_output.put_line( rpad('*',60,'*') );

end;

/

3.  5년간 20~34 세 사이에 진료일수 증가율 출력 (상위 10개 진료과목에 대해서)

set serveroutput on

set lines 4000

declare

v_string varchar2(2000);

limit_rank number(10):=10;

scale number(10):=4;

begin

for i in (

WITH v AS ( SELECT dsbjt_cd

FROM(

SELECT dsbjt_cd,sum(recn) cnt,RANK() OVER (ORDER BY sum(recn) desc) rn

FROM medical3

WHERE stnd_y=2011

GROUP BY dsbjt_cd

ORDER BY cnt

)

WHERE rn<=limit_rank)

SELECT ds, cname, rate, RANK() OVER (ORDER BY rate desc) rk

from

(

SELECT e1.dsbjt_cd ds ,e2.dsbjt_cd, e3.cname, ROUND(((e1.cnt-e2.cnt)*100/e2.cnt),2) rate

FROM (

SELECT dsbjt_cd,sum(recn) cnt

FROM medical

WHERE stnd_y=2016

GROUP BY dsbjt_cd) e1,

(

SELECT dsbjt_cd,sum(recn) cnt

FROM medical3

WHERE stnd_y=2011

GROUP BY dsbjt_cd

) e2,

dsbjt_cd_name e3

WHERE e1.dsbjt_cd=e3.code AND e1.dsbjt_cd=e2.dsbjt_cd AND e1.dsbjt_cd IN (SELECT dsbjt_cd FROM v)

ORDER BY rate

)

) loop

v_string:='';

if i.rate < 0 then

v_string:= v_string || rpad(chr(7),50+round(i.rate/10*scale),' ') || rpad('■',-round(i.rate/10*scale),'■');

else

v_string:= v_string || rpad(chr(7),50,' ');

end if;

v_string := v_string || rpad('(' || i.rate || '%) ',12,' ') || rpad( i.cname ,16,' ');

if i.rate > 0 then

v_string:= v_string || rpad(chr(7),round(i.rate/10*scale),'■');

end if;

dbms_output.put_line(v_string);

end loop;

end;

/

Conclusion / Eval‎uation of data set.

Positive aspect

데이터 정제가 상당히 잘되있어서 따로 전처리가 필요하지 않았음 (국민건강보험공단 빅데이터 운영실)

Negative aspect

표본조사가 제대로 되었는지 의문이 상당함. 특히 한방내과, 치과보존과등 상식적으로 100만명에서 나올수 없는 수치들이 존재함을 확인. (ex. 2011 년 치과보존과 85일) 따라서 위의 데이터로부터 얻은 모든 결과를 개인적으로 신뢰할수 없음.

모든 병원에서 데이터가 전송이 되지 않을 가능성이 가장 크다고 판단

그렇기 때문의 아래와 같이 증가율이 엄청난 진료과목이 나와서 문제3에서 상위 10개 진료과목만 데이터로 선정함

Figure  전연령대에서 2011~2016년 1인당 평균 진료일수 증가율 출력