用集算器实现报表的外置存储过程数据源效果

原创

datanalysis 2014-07-31 14:34:28 博主文章分类：其他 ©著作权

文章标签 存储过程报表集算器外置 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者datanalysis的原创作品，请联系作者获取转载授权，否则将追究法律责任

业务逻辑相对复杂的报表开发中，开发人员往往会使用存储过程为报表准备数据。而使用存储过程往往受到编写困难、执行效率低下、数据库操作权限限制等问题，着实让开发人员不爽。如何改进或替代存储过程，成了很多程序员的心心声。

本文通过一个例子说明如何使用集算器实现报表存储过程数据源效果。

报表样式

某网络平台需要监测查看一定周期内的用户状况，需要为运营部门出具日报、周报、月报、年报等报表，每类报表中均包含本期与上期、上上期数据比较，故涉及数据量较大。这里以其日报为例（月报年报只是统计周期不同），报表格式如下：

报表分为两部分，上半部分为用户明细数据（本期、上期、上上期在线时长均不为空），由于用户较多报表中只显示按本期在线时长排序后的前十名和后十名；下半部分为本期数据与上期、上上期的比较结果（允许本期、上期、上上期在线时长为空）。

存储过程实现

首先来看一下存储过程的实现方式（为了说明方便，将其分成颜色不同的四部分）：

CREATEORREPLACEPACKAGEBODY CURSPKG AS
PROCEDURE sp_query_user_status_day(data_date INvarchar2,
                                    top10     OUT T_CURSOR,
                                    last10    inout t_cursor,
                                    var1      outnumber,
                                     var2      outnumber,
                                    var3      outnumber,
                                    var4      outnumber,
                                    var5      outnumber,
                                    var6      outnumber) IS
    V_CURSOR1 T_CURSOR; --top10
    V_CURSOR2 T_CURSOR; --last10
    V_CURSOR T_CURSOR; --temp table
    v_ttime   date;
    temp_num number;

    v_valid_user_conti_act1      number;
    v_valid_user_back1           number;
    v_valid_user_conti_act_lost1 number;
    v_valid_user_active_lost1    number;
    v_valid_user_add_lost1       number;
    v_valid_user_back_lost1      number;

BEGIN
    v_ttime := to_date(data_date, 'yyyy-mm-dd');

--for temp table
selectcount(1) into temp_num from account_status_day_temp;
if temp_num > 0 then
deletefrom account_status_day_temp;   --delete first
endif;

insertinto account_status_day_temp
select *
from (select v.userid, v.first_logout_time
from t_dw_zx_valid_account v
where v.standard_7d_time isnotnull) a,
             (select userid, sum(onlinetime)onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime
and logtime < v_ttime + 1
groupby userid
havingmax(account) isnotnull) b,
             (select userid, sum(onlinetime)onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime - 1
and logtime < v_ttime
groupby userid
havingmax(account) isnotnull) c,
             (select userid, sum(onlinetime)onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime - 1 - 1
and logtime < v_ttime - 1
groupby userid
havingmax(account) isnotnull) d
where a.userid = b.userid(+)
and a.userid = c.userid(+);

commit;

--top 10
open V_CURSOR1 for
select *
from (selectrownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetimecurrent_onlinetime,
                     a.conlinetimelast_onlinetime,
                     a.donlinetimelast_last_onlinetime
from account_status_day_temp a
orderby bonlinetime desc)
whererownum< 11;

--last 10
open V_CURSOR2 for
select *
from (selectrownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetimecurrent_onlinetime,
                     a.conlinetime last_onlinetime,
                     a.donlinetimelast_last_onlinetime
from account_status_day_temp a
orderby bonlinetime asc)
whererownum< 11;

       top10 := V_CURSOR1;
       last10 := V_CURSOR2;

--total
select
            valid_user_conti_act
          , valid_user_back
          ,valid_user_conti_act_lost
          , valid_user_active_lost
          , valid_user_add_lost
          , valid_user_back_lost
into
            v_valid_user_conti_act1
          , v_valid_user_back1
          , v_valid_user_conti_act_lost1
          , v_valid_user_active_lost1
          , v_valid_user_add_lost1
          , v_valid_user_back_lost1
from
   (selectcount(casewhenbuserid isnotnulland cuserid isnotnullthen 1 elsenullend)valid_user_conti_act
           , count(casewhencuserid isnulland buserid isnotnulland first_logout_time < v_ttime-1 then 1 elsenullend)
           , count(casewhencuserid isnotnulland buserid isnullthen 1 elsenullend) valid_user_active_lost
           , count(casewhenduserid isnotnulland cuserid isnotnulland buserid isnullthen1 elsenullend) valid_user_conti_act_lost
           , count(casewhenduserid isnulland cuserid isnotnulland first_logout_time <v_ttime-1 and buserid isnullthen 1 elsenullend)valid_user_back_lost
           , count(casewhenbuserid isnulland first_logout_time >= v_ttime-1 and first_logout_time < v_ttime then 1 elsenullend) valid_user_add_lost
from account_status_day_temp);

         var1 := v_valid_user_conti_act1;
         var2 := v_valid_user_back1;
         var3 :=v_valid_user_conti_act_lost1;
         var4 :=v_valid_user_active_lost1;
         var5 := v_valid_user_add_lost1;
         var6 := v_valid_user_back_lost1;

END sp_query_user_status_day;
END CURSPKG;

该存储过程是为一个用户统计日报表服务，主要计算用户当期和历史时期的比较情况，其中包括明细数据前十名和后十名，用户新增与流失统计等。

第一（蓝色）部分：根据用户明细和状态表过滤汇总数据，按用户计算本期、上期、上上期情况统计；该中间结果存入临时表（避免重复计算），供后续计算使用。

第二（橙色）部分：根据第一部分的计算结果排序后，取前十名，结果以游标返回；

第三（绿色）部分：与前项类似，倒序排序取最后十名，结果以游标返回；

第四（紫色）部分：根据第一部分计算结果完成对各项综合统计指标计算，结果以六个输出参数返回。

该存储过程综合考虑了报表工具的计算能力不足的因素，将尽量多的计算都放到存储过程中完成，这点是值得肯定的。但其中使用了大量的复杂sql，以及多结果集的输出方式（游标）无疑增加了存储过程的实现难度。另外，虽然采用了临时表避免了重复计算，不过后续的三次计算均需重复读取该表数据显然对报表和数据库都有较大性能影响。

下面看一下使用了集算器的方案。

集算器实现

集算器脚本

A4-A6：进行数据过滤

A7-A9：按userid分组

A10：将以上结果集进行关联

A11：基于A10进行过滤后按在线时长排序

A12：新序表，用于读取前后十名记录

A13-A14：通过序号分别取前后十名记录

A15-A20：计算汇总值

A22：将前十名、后十名记录以及汇总值分别以不同结果集通过集算器JDBC返回给报表

这里看到，使用存储过程的一些缺点在集算器中均可得到较好解决，不必再去数据库建立中间表（大量中间表会给数据库带来极大的管理难题），而且结果集复用解决了重复取数问题。此外，集算器脚本可以分步编写，按照自然思维实现业务逻辑代码，较为清晰；对于有序运算的有效支持使得取前后十名（A13、A14）非常容易（不同于sql无序，每次都要先排序）。