# KPI模型数据补正问题及方法

KPI模型数据补正问题及方法

1.先在KPI事实表内，插入各科室级别的出院病人数

2.然后在kpi事实表插入一条"虚拟科室"的数据，虚拟科室的出院病人数=医院的实际病人数-各科室的病人数之和

3.并且将这个"虚拟科室"补全到科室维表

KPI事实表结构：

DESC t_kpi_ft

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

KPI_DATEUID NUMBER Y kpi的日期维

KPI_FYID NUMBER Y kpi所属分院

KPI_KSBZM VARCHAR2(30) Y kpi所属科室标准码

kpi_ysbzdm VARCHAR2(50) Y kpi所属医生标准码

KPI_ID NUMBER Y kpi的ID号

--KPI_VALUE NUMBER Y kpi值(做成前台rpd虚拟列）

KPI_NUMERATOR NUMBER Y kpi值-分子(如果无分母，则直接表示kpi)

KPI_DENOMINATOR NUMBER Y kpi值-分母(如果无分母，则为空)

KPI_TARGET_VALUE NUMBER Y kpi目标值

REPEAT_FLAG varchar2(1) 是否kpi重复值修正Y/N

--医生

insert into t_kpi_ft(

。。。。。。)

select 。。。。。。

group by a.cyrquid, a.fyid, a.fyksbzdm, a.ysbzdm, b.kpi_id;

--医生-》诊疗组补正

insert into t_kpi_ft(

。。。。。。)

with ys as (select a.KPI_DATEUID,

。。。。。。

ys_xz as (select a.KPI_DATEUID,

。。。。。。

xz as (

。。。。。。)

Select 。。。。。。

from xz a, t_kpi_dm b, ys_xz c

where b.kpi_id = 9

and a.kpi_xzbzdm=c.kpi_xzbzdm

and a.kpi_dateuid=c.kpi_dateuid

group by a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_KSBZM,

a.kpi_xzbzdm||'_-1', b.kpi_id

having nvl(sum(a.KPI_NUMERATOR)-sum(c.KPI_NUMERATOR),0)<>0 or

nvl(sum(a.KPI_DENOMINATOR)-sum(c.KPI_DENOMINATOR),0)<>0 or

nvl(sum(a.KPI_TARGET_VALUE)-sum(c.KPI_TARGET_VALUE),0)<>0;

--诊疗组-》科室补正

insert into t_kpi_ft(

。。。。。。)

with xz as (select a.KPI_DATEUID,

。。。。。。

xz_ks as (select a.KPI_DATEUID,

。。。。。。

ks as (

。。。。。。)

Select 。。。。。。

from ks a, t_kpi_dm b, xz_ks c

where b.kpi_id = 9

and a.KPI_KSBZM=c.KPI_KSBZM

and a.kpi_dateuid=c.kpi_dateuid

group by a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_KSBZM,

a.KPI_KSBZM||'_-1_-1', b.kpi_id

having nvl(sum(a.KPI_NUMERATOR)-sum(c.KPI_NUMERATOR),0)<>0 or

nvl(sum(a.KPI_DENOMINATOR)-sum(c.KPI_DENOMINATOR),0)<>0 or

nvl(sum(a.KPI_TARGET_VALUE)-sum(c.KPI_TARGET_VALUE),0)<>0;

--科室-》分院补正

insert into t_kpi_ft(

KPI_DATEUID ,

KPI_FYID ,

KPI_KSBZM ,

kpi_ysbzdm ,

KPI_ID ,

KPI_NUMERATOR ,

KPI_DENOMINATOR ,

KPI_TARGET_VALUE ,

repeat_flag)

with ks as (select a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_KSBZM,

b.kpi_id KPI_ID,

sum(a.KPI_NUMERATOR) KPI_NUMERATOR,

sum(a.KPI_DENOMINATOR) KPI_DENOMINATOR,

sum(a.KPI_TARGET_VALUE) KPI_TARGET_VALUE

from t_kpi_ft a, t_kpi_dm b

where b.kpi_id = 9 and a.kpi_id=b.kpi_id

group by a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_KSBZM,

b.kpi_id),

ks_fy as (select a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_ID,

sum(KPI_NUMERATOR) KPI_NUMERATOR,

sum(KPI_DENOMINATOR) KPI_DENOMINATOR,

sum(KPI_TARGET_VALUE) KPI_TARGET_VALUE

from ks a

group by a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_ID),

fy as (。。。。。。 )

select a.KPI_DATEUID,

a.KPI_FYID,

a.KPI_FYID||'_-1' KPI_KSBZM,

a.KPI_FYID||'_-1_-1_-1' kpi_ysbzdm,

b.kpi_id KPI_ID,

sum(a.KPI_NUMERATOR)-sum(c.KPI_NUMERATOR) KPI_NUMERATOR,

sum(a.KPI_DENOMINATOR)-sum(c.KPI_DENOMINATOR) KPI_DENOMINATOR,

sum(a.KPI_TARGET_VALUE)-sum(c.KPI_TARGET_VALUE) KPI_TARGET_VALUE,

'Y' repeat_flag

from fy a, t_kpi_dm b, ks_fy c

where b.kpi_id = 9

and a.KPI_FYID=c.KPI_FYID

and a.kpi_dateuid=c.kpi_dateuid

group by a.KPI_DATEUID,

a.KPI_FYID,

b.kpi_id

having nvl(sum(a.KPI_NUMERATOR)-sum(c.KPI_NUMERATOR),0)<>0 or

nvl(sum(a.KPI_DENOMINATOR)-sum(c.KPI_DENOMINATOR),0)<>0 or

nvl(sum(a.KPI_TARGET_VALUE)-sum(c.KPI_TARGET_VALUE),0)<>0;

--维表补全

--医生维

insert into t_ys_dm

select distinct 。。。。。。

where a.kpi_ysbzdm not in ( select bzdm from t_ys_dm );

--科室维

insert into t_ksdm_dm

select distinct a.kpi_ksbzm,a.kpi_fyid,b.flmc,0,'其他',0,'其他',-1,'补全',-1,'补全',-1,'补全',-1,'补全'

from t_kpi_ft a,t_fydm_dm b

where a.kpi_fyid=b.fyid

and a.kpi_ksbzm not in ( select bzdm from t_ksdm_dm );

commit;

--下钻-》

--下钻-》

• 博文量
99
• 访问量
1869433