利用物化视图和Excel数据透视表.docx

1、利用物化视图和Excel数据透视表利用物化视图和Excel数据透视表实现一卡通数据查询师职门诊一卡通试用以来，院领导常需要有关体系外师职干部门诊就诊情况的有关信息，进而需要体系内的军人就诊情况，甚至包括同比和环比相关就诊信息情况。这些需求，需要的数据是固定的，但展现的形式是不定的。因此，不适合在下发的程序中固定，其实更确切地讲，这是一个初步的BI要求。下文中，主要介绍如何结合ORACLE的物化视图和Excel的数据透视表(图)功能，实现上述要求并提供扩展空间。一 物化视图ORACLE的物化视图(Materialed View)可用于预先计算保存连接或聚集等耗时较多的操作的结果。这和军卫一号中医

2、务统计中的统计中间表功能类似，但更灵活。关于军人门诊就诊和门诊费用的统计，我们也可以将有关统计生成为物化视图，方面查询使用。下面脚本以ORACLE10G为例，其它版本可自行调整。 为不影响HIS系统，我选择单建用户和表空间以方便今后的删除。以DBA 用户执行：- 建立存储表空间CREATE TABLESPACE TSP_INSURANCE_SUM DATAFILE D:ORACLEPRODUCT10.2.0ORADATAORCLDATAFILEO1_MF_TSP_INSU_75H7Z9VT_.DBF SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMIT

3、EDNOLOGGINGDEFAULT COMPRESSONLINEPERMANENTEXTENT MANAGEMENT LOCAL UNIFORM SIZE 10MBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT MANUALFLASHBACK OFF;- 建立用户 CREATE USER CARDSUM IDENTIFIED BY CARDSUM DEFAULT TABLESPACE TSP_INSURANCE_SUM TEMPORARY TABLESPACE TEMP GRANT CREATE MATERIALIZED VIEW TO CARDSUM;GRANT C

4、REATE PROCEDURE TO CARDSUM WITH ADMIN OPTION;GRANT SELECT ANY TABLE TO CARDSUM;GRANT UNLIMITED TABLESPACE TO CARDSUM;GRANT CONNECT TO CARDSUM;ALTER USER CARDSUM DEFAULT ROLE CONNECT;ALTER USER CARDSUM QUOTA UNLIMITED ON TSP_INSURANCE_SUM;以cardsum用户执行：- 判断是否是体系内外，通过判断帐号的体系医院和本院代码/* Formatted on 2011/

5、08/27 20:27 (Formatter Plus v4.8.6) */CREATE OR REPLACE FUNCTION getserviceclass ( a_visit_date DATE, a_insurance_no VARCHAR2) RETURN VARCHAR2IS v_serviceclass VARCHAR2 (30); v_hospital_code hospital_config.unit_code%TYPE; - v_inservicelist VARCHAR2 (1); v_service_class VARCHAR2 (29);/* NAME: GetSer

6、viceClass PURPOSE: 获取指定帐号的身份信息 参数： a_visit_date 就诊时间 a_insurance_no 帐号 返回值： 第一字符标明是体系内(0)还是体系外(1) ,第二个字符标明身份 REVISIONS: Ver Date Author Description - - - - 1.0 2011-8-14 1. Created this function. NOTES: Automatically available Auto Replace Keywords: Object Name: GetServiceClass Sysdate: 2011-8-14 Da

7、te and Time: 2011-8-14, 10:52:27, and 2011-8-14 10:52:27 Username: Table Name: (set in the New PL/SQL Object dialog)*/BEGIN SELECT unit_code INTO v_hospital_code FROM hospital_config; SELECT DECODE (designated_hospital, v_hospital_code, 0, 1), identity_class INTO v_inservicelist, v_service_class FRO

8、M insurance_accounts WHERE insurance_no = a_insurance_no; IF v_inservicelist = 1 THEN -外体系 BEGIN SELECT service_class INTO v_service_class FROM outp_card_bill_items WHERE visit_date = a_visit_date AND insurance_no = a_insurance_no AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF;

9、v_serviceclass := v_inservicelist | v_service_class; RETURN v_serviceclass;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 1其它; WHEN OTHERS THEN - Consider logging the error and then re-raise RETURN 1其它;END getserviceclass;/- 创建门诊就诊的物化视图CREATE MATERIALIZED VIEW cardsum.mv_card_clinicTABLESPACE tsp_insuranc

10、e_sumSTORAGE ( INITIAL 10 m NEXT 5 m )NOCACHENOLOGGINGCOMPRESSNOPARALLELBUILD DEFERREDREFRESH FORCE ON DEMANDSTART WITH TO_DATE(20110828 23:00:00,yyyymmdd hh24:mi:ss)NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE - 1, d) + 7AS SELECT DECODE (SUBSTR (service_class, 1, 1), 0, 体系内, 体系外 ) 体系内外, SUBSTR

11、(service_class, 2, LENGTH (service_class) - 1) 身份, trunc(visit_date ,MM)时间, COUNT (visit_no) 人次 FROM ( SELECT visit_date, visit_no, patient_id, insurance_no, insurance_type, getserviceclass (visit_date, insurance_no) service_class FROM clinic_master WHERE visit_date TO_DATE (20110601, YYYYMMDD) AND

12、charge_type = 军队医改 AND insurance_no IS NOT NULL )GROUP BY trunc(visit_date ,MM), service_class; 需要说明的是： BUILD DEFERRED 表示该物化视图不是立即更新数据，因为更新数据需要消耗较多系统资源，因此建议到数据库闲时操作。START WITH TO_DATE(20110828 23:00:00,yyyymmdd hh24:mi:ss) 表示2011-08-28 23点才第一次更新，这个日期可以设为当日的午夜NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(S

13、YSDATE - 1, d) + 7 表示下次更新是每周周六的午夜。上述语句中的charge_type = 军队医改 是我认为挂号记录着费别为军队医改的就是军人。由于各地费别名称设置不同，这里需要大家依据实际情况设置.如果需要立即刷新，可执行：exec dbms_mview.refresh(mv_card_clinic);创建门诊明细的物化视图： CREATE MATERIALIZED VIEW cardsum.MV_CARD_BILLITEMSTABLESPACE tsp_insurance_sumSTORAGE ( INITIAL 10 m NEXT 5 m )NOCACHENOLOGGI

14、NGCOMPRESSNOPARALLELBUILD DEFERREDREFRESH FORCE ON DEMANDSTART WITH TO_DATE(20110828 23:00:00,yyyymmdd hh24:mi:ss)NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE - 1, d) + 7AS SELECT DECODE (SUBSTR (service_class, 1, 1), 0, 体系内, 体系外 ) 体系内外, SUBSTR (service_class, 2, LENGTH (service_class) - 1) 身份, T

15、RUNC (visit_date, DD) 日期, patient_name 姓名, class_name 类型, item_name 名称, item_spec 规格, amount 数量, units 单位, costs 金额 FROM (SELECT getserviceclass (a.visit_date, c.insurance_no) service_class, c.NAME patient_name, a.visit_date, item_class, d.class_name, item_name, item_spec, amount, units, costs FROM

16、outp_bill_items a, outp_order_desc b, insurance_accounts c, bill_item_class_dict d WHERE a.visit_date = b.visit_date AND a.visit_no = b.visit_no AND a.visit_date = TO_DATE (20110601, YYYYMMDD) AND b.patient_id = c.patient_id AND c.insurance_type = 免费医疗 AND a.item_class = d.class_code); 同样 c.insurance_type = 免费医疗 我假设insurance_accounts 中insurance_type 为免费医疗 是军人，具体情况，可自行修改SQL二 使用EXCEL数据透视表(图)以门诊就诊为例，按下图步骤：同理，也可做成相同的数据透视图和针对收费明细的数据透视表（图）以上内容仅供参考。