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

利用物化视图和Excel数据透视表
实现一卡通数据查询
师职门诊一卡通试用以来，院领导常需要有关体系外师职干部门诊就诊情况的有关信息，进而需要体系内的军人就诊情况，甚至包括同比和环比相关就诊信息情况。

这些需求，需要的数据是固定的，但展现的形式是不定的。

因此，不适合在下发的程序中固定，其实更确切地讲，这是一个初步的BI要求。

下文中，主要介绍如何结合ORACLE的物化视图和Excel的数据透视表(图)功能，实现上述要求并提供扩展空间。

一．物化视图
ORACLE的物化视图(Materialed View)可用于预先计算保存连接或聚集等耗时较多的操作的结果。

这和军卫一号中医务统计中的统计中间表功能类似，但更灵活。

关于军人门诊就诊和门诊费用的统计，我们也可以将有关统计生成为物化视图，方面查询使用。

下面脚本以ORACLE10G为例，其它版本可自行调整。

为不影响HIS系统，我选择单建用户和表空间以方便今后的删除。

以DBA 用户执行：
--- 建立存储表空间
CREATE TABLESPACE TSP_INSURANCE_SUM DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TSP_INSU_75H7Z9 VT_.DBF' SIZE 100M AUTOEXTEND ON NEXT5M MAXSIZE UNLIMITED
NOLOGGING
DEFAULT COMPRESS
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE10M
BLOCKSIZE8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK OFF;
--- 建立用户
CREATE USER "CARDSUM" IDENTIFIED BY "CARDSUM"
DEFAULT TABLESPACE TSP_INSURANCE_SUM
TEMPORARY TABLESPACE TEMP
GRANT CREATE MATERIALIZED VIEW TO CARDSUM;
GRANT CREATE 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/08/27 20:27 (Formatter Plus v4.8.6) */
CREATE OR REPLACE FUNCTION getserviceclass (
a_visit_date DATE,
a_insurance_no VARCHAR2
)
RETURN VARCHAR2
IS
v_serviceclass VARCHAR2(30);
v_hospital_code hospital_config.unit_code%TYPE;--
±¾Ôº´úÂë
v_inservicelist VARCHAR2(1);
v_service_class VARCHAR2(29);
/******************************************************************** **********
NAME: GetServiceClass
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
Date 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
FROM 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;
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_clinic
TABLESPACE tsp_insurance_sum
STORAGE (
INITIAL 10 m
NEXT 5 m
)
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH FORCE ON DEMAND
START WITH TO_DATE('20110828 23:00:00','yyyymmdd hh24:mi:ss')
NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE - 1, 'd')) + 7
AS
SELECT DECODE (SUBSTR (service_class, 1, 1),
'0', '体系内',
'体系外'
) "体系内外",
SUBSTR (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 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(SYSDATE - 1, 'd')) + 7 表示下次更新是每周周六的午夜。

上述语句中的charge_type = '军队医改’是我认为挂号记录着费别为'军队医改’的就是军人。

由于各地费别名称设置不同，这里需要大家依据实际情况设置.
如果需要立即刷新，可执行：
exec dbms_mview.refresh('mv_card_clinic');
创建门诊明细的物化视图：
CREATE MATERIALIZED VIEW cardsum.MV_CARD_BILLITEMS
TABLESPACE tsp_insurance_sum
STORAGE (
INITIAL 10 m
NEXT 5 m
)
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH FORCE ON DEMAND
START WITH TO_DATE('20110828 23:00:00','yyyymmdd hh24:mi:ss')
NEXT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE - 1, 'd')) + 7
AS
SELECT DECODE (SUBSTR (service_class, 1, 1),
'0', '体系内',
'体系外'
) "体系内外",
SUBSTR (service_class, 2, LENGTH (service_class) - 1) "身份",
TRUNC (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,
patient_name, a.visit_date, item_class,
d.class_name,
item_name, item_spec, amount, units, costs
FROM 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数据透视表(图)
以门诊就诊为例，按下图步骤：
同理，也可做成相同的数据透视图和针对收费明细的数据透视表（图）以上内容仅供参考。