利用物化视图和Excel数据透视表.docx
《利用物化视图和Excel数据透视表.docx》由会员分享，可在线阅读，更多相关《利用物化视图和Excel数据透视表.docx（14页珍藏版）》请在冰豆网上搜索。
利用物化视图和Excel数据透视表
利用物化视图和Excel数据透视表
实现一卡通数据查询
师职门诊一卡通试用以来，院领导常需要有关体系外师职干部门诊就诊情况的有关信息，进而需要体系内的军人就诊情况，甚至包括同比和环比相关就诊信息情况。
这些需求，需要的数据是固定的，但展现的形式是不定的。
因此，不适合在下发的程序中固定，其实更确切地讲，这是一个初步的BI要求。
下文中，主要介绍如何结合ORACLE的物化视图和Excel的数据透视表（图）功能，实现上述要求并提供扩展空间。
一．物化视图
ORACLE的物化视图（MaterialedView）可用于预先计算保存连接或聚集等耗时较多的操作的结果。
这和军卫一号中医务统计中的统计中间表功能类似，但更灵活。
关于军人门诊就诊和门诊费用的统计，我们也可以将有关统计生成为物化视图，方面查询使用。
下面脚本以ORACLE10G为例，其它版本可自行调整。
为不影响HIS系统，我选择单建用户和表空间以方便今后的删除。
以DBA用户执行：
---建立存储表空间
CREATETABLESPACETSP_INSURANCE_SUMDATAFILE
'D:
\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_TSP_INSU_75H7Z9VT_.DBF'SIZE100MAUTOEXTENDONNEXT5MMAXSIZEUNLIMITED
NOLOGGING
DEFAULTCOMPRESS
ONLINE
PERMANENT
EXTENTMANAGEMENTLOCALUNIFORMSIZE10M
BLOCKSIZE8K
SEGMENTSPACEMANAGEMENTMANUAL
FLASHBACKOFF;
---建立用户
CREATEUSER"CARDSUM"IDENTIFIEDBY"CARDSUM"
DEFAULTTABLESPACETSP_INSURANCE_SUM
TEMPORARYTABLESPACETEMP
GRANTCREATEMATERIALIZEDVIEWTOCARDSUM;
GRANTCREATEPROCEDURETOCARDSUMWITHADMINOPTION;
GRANTSELECTANYTABLETOCARDSUM;
GRANTUNLIMITEDTABLESPACETOCARDSUM;
GRANT"CONNECT"TO"CARDSUM";
ALTERUSER"CARDSUM"DEFAULTROLE"CONNECT";
ALTERUSERCARDSUMQUOTAUNLIMITEDONTSP_INSURANCE_SUM;
以cardsum用户执行：
---判断是否是体系内外，通过判断帐号的体系医院和本院代码
/*Formattedon2011/08/2720:
27（FormatterPlusv4.8.6）*/
CREATEORREPLACEFUNCTIONgetserviceclass（
a_visit_dateDATE,
a_insurance_noVARCHAR2
）
RETURNVARCHAR2
IS
v_serviceclassVARCHAR2（30）;
v_hospital_codehospital_config.unit_code%TYPE;--±¾Ôº´úÂë
v_inservicelistVARCHAR2
（1）;
v_service_classVARCHAR2（29）;
/******************************************************************************
NAME:
GetServiceClass
PURPOSE:
获取指定帐号的身份信息
参数：
a_visit_date就诊时间
a_insurance_no帐号
返回值：
第一字符标明是体系内（0）还是体系外
（1）,第二个字符标明身份
REVISIONS:
VerDateAuthorDescription
----------------------------------------------------------------------
1.02011-8-141.Createdthisfunction.
NOTES:
AutomaticallyavailableAutoReplaceKeywords:
ObjectName:
GetServiceClass
Sysdate:
2011-8-14
DateandTime:
2011-8-14,10:
52:
27,and2011-8-1410:
52:
27
Username:
TableName:
（setinthe"NewPL/SQLObject"dialog）
******************************************************************************/
BEGIN
SELECTunit_code
INTOv_hospital_code
FROMhospital_config;
SELECTDECODE（designated_hospital,v_hospital_code,0,1）,identity_class
INTOv_inservicelist,v_service_class
FROMinsurance_accounts
WHEREinsurance_no=a_insurance_no;
IFv_inservicelist=1
THEN--外体系
BEGIN
SELECTservice_class
INTOv_service_class
FROMoutp_card_bill_items
WHEREvisit_date=a_visit_date
ANDinsurance_no=a_insurance_no
ANDROWNUM=1;
EXCEPTION
WHENNO_DATA_FOUND
THEN
NULL;
END;
ENDIF;
v_serviceclass:
=v_inservicelist||v_service_class;
RETURNv_serviceclass;
EXCEPTION
WHENNO_DATA_FOUND
THEN
RETURN'1其它';
WHENOTHERS
THEN
--Considerloggingtheerrorandthenre-raise
RETURN'1其它';
ENDgetserviceclass;
/
---创建门诊就诊的物化视图
CREATEMATERIALIZEDVIEWcardsum.mv_card_clinic
TABLESPACEtsp_insurance_sum
STORAGE（
INITIAL10m
NEXT5m
）
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILDDEFERRED
REFRESHFORCEONDEMAND
STARTWITHTO_DATE（'2011082823:
00:
00','yyyymmddhh24:
mi:
ss'）
NEXTTRUNC（SYSDATE）-TO_NUMBER（TO_CHAR（SYSDATE-1,'d'））+7
AS
SELECTDECODE（SUBSTR（service_class,1,1）,
'0','体系内',
'体系外'
）"体系内外",
SUBSTR（service_class,2,LENGTH（service_class）-1）"身份",
trunc（visit_date,'MM'）"时间",COUNT（visit_no）"人次"
FROM（
SELECTvisit_date,visit_no,patient_id,insurance_no,insurance_type,
getserviceclass（visit_date,insurance_no）service_class
FROMclinic_master
WHEREvisit_date>TO_DATE（'20110601','YYYYMMDD'）
ANDcharge_type='军队医改'ANDinsurance_noISNOTNULL）
GROUPBYtrunc（visit_date,'MM'）,service_class;
需要说明的是：
BUILDDEFERRED表示该物化视图不是立即更新数据，因为更新数据需要消耗较多系统资源，因此建议到数据库闲时操作。
STARTWITHTO_DATE（'2011082823:
00:
00','yyyymmddhh24:
mi:
ss'）表示2011-08-2823点才第一次更新，这个日期可以设为当日的午夜
NEXTTRUNC（SYSDATE）-TO_NUMBER（TO_CHAR（SYSDATE-1,'d'））+7表示下次更新是每周周六的午夜。
上述语句中的charge_type='军队医改’是我认为挂号记录着费别为'军队医改’的就是军人。
由于各地费别名称设置不同，这里需要大家依据实际情况设置.
如果需要立即刷新，可执行：
execdbms_mview.refresh（'mv_card_clinic'）;
创建门诊明细的物化视图：
CREATEMATERIALIZEDVIEWcardsum.MV_CARD_BILLITEMS
TABLESPACEtsp_insurance_sum
STORAGE（
INITIAL10m
NEXT5m
）
NOCACHE
NOLOGGING
COMPRESS
NOPARALLEL
BUILDDEFERRED
REFRESHFORCEONDEMAND
STARTWITHTO_DATE（'2011082823:
00:
00','yyyymmddhh24:
mi:
ss'）
NEXTTRUNC（SYSDATE）-TO_NUMBER（TO_CHAR（SYSDATE-1,'d'））+7
AS
SELECTDECODE（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（SELECTgetserviceclass（a.visit_date,c.insurance_no）service_class,
c.NAMEpatient_name,a.visit_date,item_class,d.class_name,
item_name,item_spec,amount,units,costs
FROMoutp_bill_itemsa,
outp_order_descb,
insurance_accountsc,
bill_item_class_dictd
WHEREa.visit_date=b.visit_date
ANDa.visit_no=b.visit_no
ANDa.visit_date>=TO_DATE（'20110601','YYYYMMDD'）
ANDb.patient_id=c.patient_id
ANDc.insurance_type='免费医疗'
ANDa.item_class=d.class_code）;
同样c.insurance_type='免费医疗'我假设insurance_accounts中insurance_type为'免费医疗'是军人，具体情况，可自行修改SQL
二．使用EXCEL数据透视表（图）
以门诊就诊为例，按下图步骤：
同理，也可做成相同的数据透视图和针对收费明细的数据透视表（图）
以上内容仅供参考。
升级会员 