润乾集算报表实现减少存储过程

  在报表应用中经常会使用存储过程实现数据源计算，但这会带来多方面的问题。首先，存储过程的包只提供一层分类，无法用树形结构，容易造成代码管理混乱。有些程序员直接在现场在线修改存储过程，也不利于代码管理。其次，升级存储过程的时候需要数据库的写权限，会对数据安全造成影响。同时，由于SQL固有的一些问题（数据无序、缺乏集合、无法引用、分步不彻底）等，使得存储过程的编程比较困难。

  很多情况下是为了提高性能而选择存储过程，但实际效果也不尽如人意。这主要是因为报表数据的计算一般都比较复杂，很难用SQL直接完成，需要通过循环遍历等代码来完成。这样的计算要比SQL慢一个数量级，有些语句的执行速度甚至还会低于外部的Java程序。

  还有一个问题，存储过程是存储在数据库内的，而报表工具的模板一般是文件形式，两者要一起工作才能完成报表，但分开存储容易导致版本不一致，会增加管理难度。例如：某报表模板已经被删除了，但对应的存储过程未删除等；或者别人把某个存储过程改了而未通知报表开发者（因为数据库是所有开发者共享的）。

  可以采用润乾集算报表及其内置的集算引擎来完成复杂的数据源计算，从而尽量避免使用存储过程。系统结构对比图如下：

  集算报表的报表文件（.rpx）和集算脚本（.dfx）是独立的文件，可以用文件系统的树形目录管理，某些简单脚本还可以直接写进rpx，两者很容易保持一致，方便管理。集算报表在应用程序中的升级时是通过替换这两种文件来完成的，可以避免在线修改运行环境。集算脚本解决了前述SQL的固有问题，更接近自然思维，编程比存储过程更加容易。

  从性能角度来看，集算引擎提供了并行计算的能力，可以充分发挥服务器多CPU多核的作用，也可以连接集算服务器集群，实现多机并行，在许多情况可以获得超过存储过程的性能。

  从上图也可以看到，某些情况下还是要用存储过程，这是因为涉及数据量大的库内运算用存储过程会更快。我们的目标是利用集算报表尽量减少存储过程的使用，而不是完全替代存储过程。

  下面，我们通过具体的例子，来看一下集算报表是如何减少存储过程的。

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

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

  数据来自于两个数据表：

create table T_DW_ZX_ACCOUNT_STATUS_DAY

(

  LOGTIME    DATE,–日志时间

  USERID     NUMBER(12),–用户号

  ACCOUNT    VARCHAR2(50),–账号

  ONLINETIME NUMBER(8),–在线时间

  PAY        NUMBER(11),

  EXPEND     NUMBER(11),

  TOP_LEVEL  NUMBER(4)

);

create table T_DW_ZX_VALID_ACCOUNT

(

  USERID            NUMBER,–用户编号

  FIRST_LOGOUT_TIME DATE,–第一次登出时间

  STANDARD_7D_TIME  DATE,

  STANDARD_14D_TIME DATE,

  ACCOUNT           VARCHAR2(50)

);

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

CREATE OR REPLACE PACKAGE BODY CURSPKG AS
  PROCEDURE sp_query_user_status_day(data_date IN varchar2,
         top10     OUT T_CURSOR,
         last10    in out t_cursor,
         var1      out number,
         var2      out number,
         var3      out number,
         var4      out number,
         var5      out number,
         var6      out number) 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
    select count(1) into temp_num from account_status_day_temp;
    if temp_num > 0 then
      delete from account_status_day_temp;    –delete first
    end if;
 
    insert into 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 is not null) a,
             (select userid, sum(onlinetime) onlinetime, max(account)
                from t_dw_zx_account_status_day
               where logtime >= v_ttime
                 and logtime < v_ttime + 1
               group by userid
              having max(account) is not null) b,
             (select userid, sum(onlinetime) onlinetime, max(account)
                from t_dw_zx_account_status_day
               where logtime >= v_ttime – 1
                 and logtime < v_ttime
               group by userid
              having max(account) is not null) 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
               group by userid
              having max(account) is not null) d
       where a.userid = b.userid(+)
         and a.userid = c.userid(+);
 
     commit;
    
     –top 10
    open V_CURSOR1 for
      select *
        from (select rownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetime current_onlinetime,
                     a.conlinetime last_onlinetime,
                     a.donlinetime last_last_onlinetime
                from account_status_day_temp a
               order by bonlinetime desc)
       where rownum < 11;
      
      –last 10
      open V_CURSOR2 for
      select *
        from (select rownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetime current_onlinetime,
                     a.conlinetime last_onlinetime,
                     a.donlinetime last_last_onlinetime
                from account_status_day_temp a
               order by bonlinetime asc)
       where rownum < 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
   (select  count(case when buserid is not null and cuserid is not null then 1 else null end) valid_user_conti_act  
           , count(case when cuserid is null and buserid is not null and first_logout_time  <  v_ttime-1 then 1 else null end)
           , count(case when cuserid is not null and buserid is null then 1 else null end)  valid_user_active_lost 
           , count(case when duserid is not null and cuserid is not null and buserid is nullthen 1 else null end) valid_user_conti_act_lost   
           , count(case when duserid is null and cuserid is not null and first_logout_time < v_ttime-1 and buserid is null then 1 else null end) valid_user_back_lost   
           , count(case when buserid is null and first_logout_time  >= v_ttime-1 and  first_logout_time < v_ttime then 1 else null end)  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，以及多结果集的输出方式（游标），这又增加了编程难度。

  用集算报表来实现这个需求，首先要编写集算器脚本：

	A
1	=connect(“ora”)
2	=A1.query(“select userid,first_logout_time,standard_7d_time from t_dw_zx_valid_account where standard_7d_time is not null”)
3	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,’yyyy-MM-dd’)=”+” ‘”+string(data_date)+”‘ “+” group by userid  having max(account) is not null”)
4	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,’yyyy-MM-dd’)=”+” ‘”+string(data_date-1)+”‘ “+” group by userid  having max(account) is not null”)
5	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,’yyyy-MM-dd’)=”+” ‘”+string(data_date-2)+”‘ “+” group by userid having max(account) is not null”)
6	=A1.query(“select userid,onlinetime,account,logtime from t_dw_zx_account_status_day where to_char(logtime,’yyyy-MM-dd’)=”+”‘”+string(data_date-2)+”‘”)
7	=join@1(A2:a,USERID;A3:b,USERID;A4:c,USERID;A5:d,USERID)
8	=A7.new(#:no,a.USERID:userid,a.FIRST_LOGOUT_TIME:first_logout_time,b.ONLINETIME:current_time, c.ONLINETIME:last_time,c.ONLINETIME:last_last_time)
9	=A8.to(10)
10	=A8.to(A7.len()-10+1,)
11	=A6.count@b(d!=null && c!=null)
12	=A6.count@b(b==null && c!=null && d!=null)
13	=A6.count@b(a.FIRST_LOGOUT_TIME>data_date-1 && a.FIRST_LOGOUT_TIME<data_date)< td="">
14	=A6.count@b(b==null && a.FIRST_LOGOUT_TIME>data_date-1 && a.FIRST_LOGOUT_TIME<data_date)< td="">
15	=A6.count@b(d==null && c!=null && a.FIRST_LOGOUT_TIME<data_date-1)< td="">
15	=A6.count@b(d==null && c!=null && a.FIRST_LOGOUT_TIME<data_date-1)< td="">
16	=A6.count@b(b==null && c!=null && d==null && a.FIRST_LOGOUT_TIME<data_date-1)< td="">
17	=new(A11:num1,A12:num2,A13:num3,A14:num4,A15:num5,A16:num6)
18	>A1.close()
19	>A1.close()

  A1：连接提前配置好的oracle数据库。

  A2-A6：从数据库按照条件和分组汇总、取数。其中的A3、A4、A5的group虽然也可以放到集算脚本中实现，但是用sql实现的好处是：简单运算尽量让数据库去做，可以让取出数据变少，节省JDBC的传输时间；复杂的过程性计算才放到集算脚本做，可以发挥数据库和集算脚本各自的优势。

  A7：将以上结果集进行关联。

  A8：根据A7建立新序表，用于读取前后十名记录。

  A9-A10：通过序号分别取前后十名记录。

  A11-A17：计算汇总值。

  A19：将前十名、后十名记录以及汇总值分别以不同结果集返回给集算报表。

  集算器脚本编写之后，保存为test.dfx，集算报表要新增集算器数据集来调用：

  集算报表接收集算器脚本返回的三个结果集，其中”test.dfx”为集算器脚本名称。

  下一步，要按照需求绘制报表模板文件，即可完成报表设计过程：