[关闭]
@wangyupu 2022-09-02T15:47:13.000000Z 字数 12069 阅读 34

广州项目2021最新入组逻辑及数据测算

数据测算

  1. --一、数据准备
  2. --1、原始表导入
  3. --tb_cis_main_zk_2021条数1340122
  4. select count(1) from tb_cis_main_zk_2021;
  5. --kcub5条数7245016业务条数1340122
  6. select count(1),count(distinct setl_id) from kcub5;
  7. --kcub7总条数1784481业务条数696187
  8. select count(1),count(distinct setl_id) from kcub7;
  9. --bzml_2022条数11128
  10. select count(1) from bzml_2022;
  11. --tb_gzyb_dic_icd9条数13686
  12. select count(1) from tb_gzyb_dic_icd9;
  14. --2、数据质控
  15. --校验是否存在无主诊断编码数据
  16. select * from tb_cis_main_zk_2021 where pdlb in('2','5','11') and 
  17. ywxlh not in (select distinct setl_id from kcub5 where diag_name ='1');
  18. --校验诊断表是否存在垃圾数据
  19. select * from kcub5 where setl_id not in (select ywxlh from tb_cis_main_zk_2021);
  20. --校验手术操作表是否存在垃圾数据
  21. select * from kcub7 where setl_id not in (select ywxlh from tb_cis_main_zk_2021);
  22. --校验诊断是否为有效诊断
  23. select * from kcub5 where DIAG_CODE not in (select jbdm from tb_icd10_2021);
  24. --校验手术操作表是否存在垃圾数据
  25. select distinct OPRN_OPRT_CODE,OPRN_OPRT_NAME from kcub7 where OPRN_OPRT_CODE not in (select SSDM from tb_gzyb_dic_icd9) 
  26. and setl_id in (select ywxlh from tb_cis_main_zk_2021 where  pdlb in('2','5','11'))
  27.  --修复手术操作
  29. update kcub7
  30.    set OPRN_OPRT_CODE = decode(OPRN_OPRT_CODE,
  31.                                '41.3101','41.3800x001',
  32.                                '47.0101','47.0100',
  33.                                '33.2201','33.2200',
  34.                                '37.610101','37.6101',
  35.                                '54.9804','54.9800',
  36.                                '54.9801','54.9800',
  37.                                'Z49.101','39.9500',
  38.                                '67.0 01','67.0x00',
  39.                                '88.550101','88.5500',
  40.                                '73.0101','73.0100',
  41.                                '39.950201','39.9501',
  42.                                '69.9901','69.9900',
  43.                                '54.2101','54.2100',
  44.                                OPRN_OPRT_CODE)
  45.  where OPRN_OPRT_CODE in
  46.        ('41.3101','33.2201','47.0101','37.610101','Z49.101','54.9801',
  47.         '54.9804','73.0101','67.0 01','88.550101','39.950201','69.9901','54.2101');
  48.  commit;
  49. --3、数据准备
  50. --创建诊断临时表 
  51. create table /*+ append parallel (degree 8) nologging */kcub5_hb as
  52.  SELECT /*+ append parallel (degree 8) nologging */
  53.         SETL_ID,
  54.         max(decode(MAINDIAG_FLAG,'1',decode(DIAG_CODE,'p59.901','P59.901',DIAG_CODE),'')) as zzdbm,
  55.         max(decode(MAINDIAG_FLAG,'1',DIAG_NAME,'')) as zzdmc,
  56.         listagg(DIAG_CODE,'+') within group(order by DIAG_CODE asc) as zdzhbm,
  57.         listagg(DIAG_NAME,'+') within group(order by DIAG_CODE asc) as zdzhmc
  58.    FROM kcub5
  59.   GROUP BY SETL_ID;
  61.  --创建手术操作临时表
  62.   create table /*+ append parallel (degree 8) nologging */kcub7_hb as
  63.   SELECT /*+ append parallel (degree 8) nologging */
  64.    t.SETL_ID,
  65.    listagg(t.OPRN_OPRT_CODE, '+') within group(order by t.OPRN_OPRT_CODE asc) as ssczzhbm,
  66.    listagg(t.OPRN_OPRT_NAME, '+') within group(order by t.OPRN_OPRT_CODE asc) as ssczzhmc,
  67.    listagg(decode(t.sslb,'手术','4','介入治疗','4','治疗性操作','3','诊断性操作','2','1'),'+') within group(order by t.OPRN_OPRT_CODE asc) as sslbzh
  68.     FROM （select DISTINCT a.SETL_ID, a.OPRN_OPRT_CODE,a.OPRN_OPRT_NAME,b.sslb from kcub7 a
  69.            left join tb_gzyb_dic_icd9 b on a.OPRN_OPRT_CODE = b.ssdm ）t
  70.    GROUP BY t.SETL_ID
  72. --创建病案首页测算主表
  73. create table /*+ append parallel (degree 8) nologging */basy2021_cs as   
  74. SELECT a.YWXLH,
  75.        a.yljgdj,
  76.        a.sfzhm,
  77.        a.nl,
  78.        a.js_zyts,
  79.        a.js_zfy as zfy,
  80.        a.fzdm as fzdm_2021,
  81.        a.fzdm,
  82.        substr(b.zzdbm, 1,3) as zdbm_lm,
  83.        substr(b.zzdbm, 1,5) as zdbm_ym,
  84.        b.zzdbm,
  85.        b.zzdmc,
  86.        b.zdzhbm,
  87.        b.zdzhmc,
  88.        nvl(c.ssczzhbm,'n(y)'),
  89.        c.ssczzhmc,
  90.        c.sslbzh,
  91.        (case when c.sslbzh like '%4%' then '手术'
  92.             when c.sslbzh like '%3%2%' or sslbzh like '%2%3%' then '治疗+诊断'
  93.             when c.sslbzh like '%3%' then '治疗'
  94.             when c.sslbzh like '%2%' then '诊断'
  95.             when c.ssczzhbm is null then '无操作'
  96.             else '其它' end) as sslbmc
  97.   FROM tb_cis_main_zk_2021 a
  98.   LEFT JOIN kcub5_hb b on a.ywxlh = b.SETL_ID
  99.   LEFT JOIN kcub7_hb c on a.ywxlh = c.SETL_ID    
  100.   where a.pdlb in('2','5','11');
  102. alter table BASY2021_CS modify FZDM NVARCHAR2(1000);  
  103. update /*+ append parallel (degree 8) nologging */basy2021_cs set fzdm = '';
  104. commit;
  106. --更新病种目录权重系数
  107. update bzml_2022 set bzqz='0.0';
  108. commit;
  110. merge /*+ append parallel 8 nologging */
  111. into bzml_2022 a
  112. using (select t.bzbm,
  113.               sum(decode(t.sslb,'手术',1,'介入治疗'，1,0))||'.'||sum(decode(t.ssjb,'四级',1,0)) as bzqz
  114.          from (select a.*,b.ssdm,b.ssmc,b.ssjb,b.sslb from bzml_2022 a,tb_gzyb_dic_icd9 b 
  115.                 where instr(a.ssczzh||'+',b.ssdm||'+')>0) t 
  116.      group by t.bzbm) b 
  117.    on (a.bzbm=b.bzbm)
  118. when matched then
  119.   update set a.bzqz = b.bzqz;
  120. commit;
  122. --三、多诊断入组规则
  123. --规则1、多诊断保守治疗 622 
  124. merge /*+ append parallel 8 nologging */
  125. into (select * from basy2021_cs where fzdm is null and ssczzhbm ='n(y)') a
  126. using (select * from bzml_2022 where bzbm like '%+%:%n(y)') b 
  127.    on (instr(b.zdzhbm, a.zdbm_ym) > 0
  128.    and instr(a.zdzhbm, substr(b.zdzhbm, 1, 5)) > 0
  129.    and instr(a.zdzhbm, substr(b.zdzhbm, 7, 5)) > 0)
  130. when matched then
  131.   update set a.fzdm = b.bzbm;
  132. commit;
  134. --规则2、多诊断非保守治疗 
  135. --7430
  137. select distinct sslbmc from basy2021_cs 
  139. merge /*+ append parallel 8 nologging */
  140. into (select ywxlh,ssczzhbm,zdzhbm,zdbm_lm,sslbmc,fzdm from basy2021_cs where fzdm is null and sslbmc in('手术','治疗','诊断')) a
  141. using (select t.ywxlh,listagg(t.bzbm, '/') as bzbm 
  142.         from (select a.ywxlh,b.bzbm
  143.                 from basy2021_cs a,
  144.                      (select * from bzml_2022 where bzbm like '%+%:%' and ssczzh in('手术','治疗','诊断'))  b
  145.                where a.fzdm is null
  146.                  and instr(b.zdzhbm, a.zdbm_lm) > 0
  147.                  and instr(a.zdzhbm, substr(b.zdzhbm, 1, 3)) > 0
  148.                  and instr(a.zdzhbm, substr(b.zdzhbm, 5, 3)) > 0
  149.                  and a.sslbmc=b.ssczzh）t 
  150.       group by t.ywxlh) b
  151.    on (a.ywxlh=b.ywxlh)
  152. when matched then
  153.   update set a.fzdm = b.bzbm;
  154. commit;
  156. --既有治疗又有诊断
  157. merge /*+ append parallel 8 nologging */
  158. into (select ywxlh,ssczzhbm,zdzhbm,zdbm_lm,sslbmc,fzdm from basy2021_cs where fzdm is null and sslbmc='治疗+诊断') a
  159. using (select /*+ append parallel 8 nologging */t.ywxlh,
  160.   listagg(t.bzbm, '/')  as bzbm from (
  161. select a.ywxlh,b.bzbm,a.sslbmc, b.ssczzh
  162.   from basy2021_cs a, (select * from bzml_2022 where bzbm like '%+%治疗' or bzbm like '%+%诊断') b
  163.  where a.fzdm is null
  164.    and a.sslbmc='治疗+诊断'
  165.    and instr(b.zdzhbm, a.zdbm_lm) > 0
  166.    and instr(a.zdzhbm, substr(b.zdzhbm, 1, 3)) > 0
  167.    and instr(a.zdzhbm, substr(b.zdzhbm, 5, 3)) > 0
  168.    and instr(a.sslbmc, b.ssczzh) > 0 ）t group by t.ywxlh) b
  169.    on (a.ywxlh=b.ywxlh)
  170. when matched then
  171.   update set a.fzdm = b.bzbm;
  172. commit;
  174. select count(1) from basy2021_cs where fzdm is not null; 
  176. --四、核心病种入组规则
  177. --规则2、精确匹配
  179. merge /*+ append parallel 8 nologging */
  180. into (select * from basy2021_cs where fzdm is null) a
  181. using bzml_2022 b 
  182.    on (a.zdbm_ym||':'||a.ssczzhbm=b.bzbm)
  183. when matched then
  184.   update set a.fzdm = b.bzbm;
  186. --规则3、模糊匹配
  188. create table mhpp_tmp1 as 
  189. select /*+ append parallel (degree 8) nologging */a.*,b.bzbm,b.bzqz from basy2021_cs a,bzml_2022 b
  190.   where a.zdbm_ym=b.zdzhbm
  191.   and  a.fzdm is null and a.ssczzhbm is not null
  192.   and instr(a.ssczzhbm,substr(b.ssczzh||'+',1,instr(b.ssczzh||'+','+',1,1)-1))>0
  193.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,1)+1,instr(b.ssczzh||'+','+',1,2)-instr(b.ssczzh||'+','+',1,1)-1),'+'))>0
  194.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,2)+1,instr(b.ssczzh||'+','+',1,3)-instr(b.ssczzh||'+','+',1,2)-1),'+'))>0
  195.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,3)+1,instr(b.ssczzh||'+','+',1,4)-instr(b.ssczzh||'+','+',1,3)-1),'+'))>0
  196.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,4)+1,instr(b.ssczzh||'+','+',1,5)-instr(b.ssczzh||'+','+',1,4)-1),'+'))>0
  197.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,5)+1,instr(b.ssczzh||'+','+',1,6)-instr(b.ssczzh||'+','+',1,5)-1),'+'))>0
  198.   and instr(a.ssczzhbm,nvl( substr(b.ssczzh||'+',instr(b.ssczzh||'+','+',1,6)+1,instr(b.ssczzh||'+','+',1,7)-instr(b.ssczzh||'+','+',1,6)-1),'+'))>0
  200. --200,179
  201. merge /*+ append parallel 8 nologging */
  202. into (select * from basy2021_cs where fzdm is null) a
  203. using (select t.ywxlh, listagg(t.bzbm,'/') within group(order by t.bzbm asc) as rzbm from (
  204. select a.* from mhpp_tmp1 a,
  205. (select ywxlh,max(bzqz) as bzqz from mhpp_tmp1 group by ywxlh) b 
  206. where a.ywxlh=b.ywxlh and a.bzqz=b.bzqz) t group by t.ywxlh) b on (a.ywxlh=b.ywxlh)
  207. when matched then
  208.   update set a.fzdm = b.rzbm;
  209. commit;
  211. --二级核心病种入组规则
  212. --规则四：入不到上面规则的病历，按照以下规则入组
  213. select count(1) from basy2021_cs where fzdm is not null;
  215. merge /*+ append parallel 8 nologging */
  216. into (select * from basy2021_cs where fzdm is null) a
  217. using (select * from bzml_2022 ) b on (a.zdbm_lm||':'||a.sslbmc=b.bzbm)
  218. when matched then
  219.   update set a.fzdm = b.bzbm;
  220. commit;
  222. ----既有诊断又有操作
  224. merge /*+ append parallel 8 nologging */
  225. into (select * from basy2021_cs where fzdm is null) a
  226. using （select t.ywxlh, listagg(t.bzbm,'/') within group(order by t.bzbm asc) as rzbm from (select a.*,b.bzbm from basy2021_cs a,bzml_2022 b where a.fzdm is null and a.sslbmc='治疗+诊断'
  227. and a.zdbm_lm=b.zdzhbm and b.ssczzh in('治疗','诊断')) t group by ywxlh） b on (a.ywxlh=b.ywxlh)
  228. when matched then
  229.   update set a.fzdm = b.rzbm;
  230. commit;
  232. ---综合病种入组规则
  233. select count(1) from basy2021_cs where fzdm is null;
  235.  --
  236.  merge /*+ append parallel 8 nologging */
  237. into (select * from basy2021_cs where fzdm is null) a
  238. using (select * from bzml_2022 where substr(bzbm,2,1)=':') b on (substr(a.zzdbm,1,1)||':'||a.sslbmc=b.bzbm)
  239. when matched then
  240.   update set a.fzdm = b.bzbm;
  241. commit;
  243.  ----既有诊断又有操作
  244.  merge /*+ append parallel 8 nologging */
  245. into (select * from basy2021_cs where fzdm is null) a
  246. using （select t.ywxlh, listagg(t.bzbm,'/') within group(order by t.bzbm asc) as rzbm from (select a.*,b.bzbm from basy2021_cs a,bzml_2022 b where a.fzdm is null and a.sslbmc='治疗+诊断'
  247. and substr(a.zzdbm,1,1)=b.zdzhbm and b.ssczzh in('治疗','诊断')) t group by ywxlh） b on (a.ywxlh=b.ywxlh)
  248. when matched then
  249.   update set a.fzdm = b.rzbm;
  250. commit;
  252. select fzdm from basy2021_cs group by fzdm
  254. select max(length(fzdm)-length(replace(fzdm,'/',''))) from basy2021_cs where fzdm like '%/%'
  255. --拆分
  256. create table cs_tmp1 as
  257. select ywxlh,fzdm,zfy,
  258.        substr(fzdm,1,instr(fzdm,'/',1)-1) as fzdm1,
  259.        substr(fzdm,instr(fzdm||'/','/',1,1)+1,instr(fzdm||'/','/',1,2)-instr(fzdm|| '/','/',1, 1)-1) as fzdm2,
  260.        substr(fzdm,instr(fzdm||'/','/',1,2)+1,instr(fzdm||'/','/',1,3)-instr(fzdm|| '/','/',1, 2)-1) as fzdm3,
  261.        substr(fzdm,instr(fzdm||'/','/',1,3)+1,instr(fzdm||'/','/',1,4)-instr(fzdm|| '/','/',1, 3)-1) as fzdm4,
  262.        substr(fzdm,instr(fzdm||'/','/',1,4)+1,instr(fzdm||'/','/',1,5)-instr(fzdm|| '/','/',1, 4)-1) as fzdm5,
  263.        substr(fzdm,instr(fzdm||'/','/',1,5)+1,instr(fzdm||'/','/',1,6)-instr(fzdm|| '/','/',1, 5)-1) as fzdm6,
  264.        substr(fzdm,instr(fzdm||'/','/',1,6)+1,instr(fzdm||'/','/',1,7)-instr(fzdm|| '/','/',1, 6)-1) as fzdm7,
  265.        substr(fzdm,instr(fzdm||'/','/',1,7)+1,instr(fzdm||'/','/',1,8)-instr(fzdm|| '/','/',1, 7)-1) as fzdm8,
  266.        substr(fzdm,instr(fzdm||'/','/',1,8)+1,instr(fzdm||'/','/',1,9)-instr(fzdm|| '/','/',1, 8)-1) as fzdm9,
  267.        substr(fzdm,instr(fzdm||'/','/',1,9)+1,instr(fzdm||'/','/',1,10)-instr(fzdm|| '/','/',1, 9)-1) as fzdm10,
  268.        substr(fzdm,instr(fzdm||'/','/',1,10)+1,instr(fzdm||'/','/',1,11)-instr(fzdm|| '/','/',1, 10)-1) as fzdm11,
  269.        substr(fzdm,instr(fzdm||'/','/',1,11)+1,instr(fzdm||'/','/',1,12)-instr(fzdm|| '/','/',1, 11)-1) as fzdm12,
  270.        substr(fzdm,instr(fzdm||'/','/',1,12)+1,instr(fzdm||'/','/',1,13)-instr(fzdm|| '/','/',1, 12)-1) as fzdm13,
  271.        substr(fzdm,instr(fzdm||'/','/',1,13)+1,instr(fzdm||'/','/',1,14)-instr(fzdm|| '/','/',1, 13)-1) as fzdm14
  272.  from basy2021_cs where fzdm like '%/%';
  274. drop table cs_tmp2 ;
  275. create table cs_tmp2 as
  276. select ywxlh,fzdm,zfy  from basy2021_cs where fzdm not like '%/%'
  277. union
  278. select ywxlh,fzdm1 as fzdm,zfy from cs_tmp1 where fzdm1 is not null
  279. union
  280. select ywxlh,fzdm2 as fzdm,zfy from cs_tmp1 where fzdm2 is not null
  281. union
  282. select ywxlh,fzdm3 as fzdm,zfy from cs_tmp1 where fzdm3 is not null
  283. union
  284. select ywxlh,fzdm4 as fzdm,zfy from cs_tmp1 where fzdm4 is not null
  285. union
  286. select ywxlh,fzdm5 as fzdm,zfy from cs_tmp1 where fzdm5 is not null
  287. union
  288. select ywxlh,fzdm6 as fzdm,zfy from cs_tmp1 where fzdm6 is not null
  289. union
  290. select ywxlh,fzdm7 as fzdm,zfy from cs_tmp1 where fzdm7 is not null
  291. union
  292. select ywxlh,fzdm8 as fzdm,zfy from cs_tmp1 where fzdm8 is not null
  293. union
  294. select ywxlh,fzdm9 as fzdm,zfy from cs_tmp1 where fzdm9 is not null
  295. union
  296. select ywxlh,fzdm10 as fzdm,zfy from cs_tmp1 where fzdm10 is not null
  297. union
  298. select ywxlh,fzdm11 as fzdm,zfy from cs_tmp1 where fzdm11 is not null
  299. union
  300. select ywxlh,fzdm12 as fzdm,zfy from cs_tmp1 where fzdm12 is not null
  301. union
  302. select ywxlh,fzdm13 as fzdm,zfy from cs_tmp1 where fzdm13 is not null
  303. union
  304. select ywxlh,fzdm14 as fzdm,zfy from cs_tmp1 where fzdm14 is not null;
  306. create table bzml_2022_tmp1 as 
  307. select a.*,nvl(a.分值,b.mnfz) as mnfz from bzml_2022 a 
  308. left join (select fzdm,round(avg(zfy)/14.32) as mnfz from cs_tmp2 group by fzdm) b 
  309. on a.bzbm = b.fzdm
  312. ---------------------------------------------------------.
  313. drop table cs_tmp3
  314. create table cs_tmp3 as
  315. select a.*,abs(round(a.zfy/14.32)-b.mnfz) as fzce from cs_tmp2 a ,bzml_2022_tmp1 b where a.fzdm = b.bzbm;
  318. drop table cs_tmp3
  319. --随机入组逻辑
  320. create table cs_tmp4 as
  321. select *
  322.   from (select t.*,
  323.                row_number() over(partition by ywxlh order by dbms_random.random) as rn
  324.           from (select *
  325.                   from cs_tmp3
  326.                  where (ywxlh, fzce) in
  327.                        (select ywxlh, min(fzce) from cs_tmp3 group by ywxlh)) t
  328.          order by ywxlh, dbms_random.random)
  329.  where rn = 1
  331. drop table cs_tmp3
  332. create table cs_tmp5 as
  333. select *
  334.   from (select t.*,
  335.                row_number() over(partition by ywxlh order by dbms_random.random) as rn
  336.           from (select *
  337.                   from cs_tmp3
  338.                  where (ywxlh, fzce) in
  339.                        (select ywxlh, min(fzce) from cs_tmp3 group by ywxlh)) t
  340.          order by ywxlh, dbms_random.random)
  341.  where rn = 1
  344.  ---------------------
  345.  create table basy2021_cs_tmp1 as select * from basy2021_cs;
  347.   merge /*+ append parallel 8 nologging */
  348. into (select * from basy2021_cs_tmp1 where fzdm like '%/%') a
  349. using cs_tmp4 b on (a.ywxlh = b.ywxlh)
  350. when matched then
  351.   update set a.fzdm = b.fzdm;
  352. commit;
  354. select * from (
  355. select a.*,b.ls as ls2022,b.jcfy as jcfy2022,b.cv as cv2022
  356.   from bzml_2022 a
  357.   left join (select fzdm,
  358.                     count(1) as ls,
  359.                     STDDEV(zfy) / avg(zfy) as cv,
  360.                     avg(zfy) as jcfy
  361.                from basy2021_cs_tmp1
  362.               group by fzdm) b
  363.     on a.bzbm = b.fzdm) t order by to_number(t.测算序号)
  365.  select fzdm,
  366.                     count(1) as ls,
  367.                     STDDEV(zfy) / avg(zfy) as cv,
  368.                     avg(zfy) as jcfy,
  369.                     max(decode(fzdm,'K35.8:47.0100',avg(zfy),'')) as jcfy1
  370.                from basy2021_cs_tmp1
  371.               group by fzdm
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注