存储过程是SQL语句和流程控制语句的集合，常用来把一个复杂的计算目标分解为多个简单的计算步骤。虽然以复杂性换来了灵活性，但SQL语句固有的缺点仍然体现在存储过程中，比如：分步不彻底、数据无序、缺乏集合、缺乏引用，之前我们也分析过存储过程作为报表数据源的利弊，可点击这里查看。

集算器解决了存储过程的上述缺陷，降低了对开发人员的技术要求，是存储过程理想的替代工具。

wKioL1PF452CEc1dAABu5RWaJa8929.jpg

报表例子说明

某电信产品厂商有一张报表，主要目的是分析优势产品的销售额、销量、环比等指标，其中优势产品的定义是”在每个州的销量均在前10名的产品”，数据主要存储在stateSales table，其数据结构如下：


wKioL1PF452QqWriAABpWyUcmL8622.jpg

 

stateSales table中的数据主要是各州各产品的销量等信息，但这些数据难以被报表工具直接使用，因为：

  • 数据尚未整理，存在着state和product相同的多条数据。

  • 算法复杂，优势产品难以计算。

将stateSalestable中的数据经过处理，变得易于被外部使用，这个过程称为“数据准备”。让我们计算这个案例中的”优势产品”，看看存储过程和esProc是如何进行数据准备的。

 

用存储过程

01  create or replace package salesPkg

02  as

03     type salesCur is ref cursor;

04  end;

05  CREATE OR REPLACE PROCEDURE topPro(io_cursor OUTsalesPkg.salesCur) 

06  is

07    varSql varchar2(2000);

08    tb_count integer;

09  BEGIN 

10    select count(*) into tb_count from dba_tables where table_name='TOPPROTMP';

11    if tb_count=0then

12   strCreate:='CREATE GLOBAL TEMPORARY TABLE TOPPROTMP (

                     stateTmp NUMBERnot null,

                     productTmp varchar2(10)  not null,

                     amountTmp NUMBER not null

              )

              ON COMMIT PRESERVE ROWS';

13    execute immediate strCreate;

14    endif;

15    execute immediate'truncate tableTOPPROTMP';

16    insert into TOPPROTMP(stateTmp,productTmp,amountTmp)

     select state,product,amount from stateSales a

       wherenot(

         (a.state,a.product) in (

           select state,product from stateSales group by state,product having count(*) > 1

         )

         and rowid not in (

           select min(rowid) from stateSales group by state,product having count(*)>1

         )

       )

     orderby state,product;

17    OPEN io_cursor for

18    SELECT productTmp  FROM (

   SELECT stateTmp,productTmp,amountTmp,rankorder

   FROM (SELECTstateTmp,productTmp,amountTmp,RANK() OVER(PARTITIONBY stateTmp ORDERBY amountTmp DESC) rankorder 

          FROM TOPPROTMP

         ) 

   WHERE rankorder<=10orderby stateTmp

    )

 GROUPBY productTmp 

  HAVINGC OUNT(*)=(SELECT COUNT(DISTINCT stateTmp ) FROM TOPPROTMP);

END;

01-04:定义”cursor”

05-09:定义存储过程及变量

10-15:定义”temporary table”，检查是否重复定义，清空临时数据。

16:过滤重复数据，并把过滤后的数据写入”temporary table”。直接取不重复的数据比较困难，这里使用了一个技巧：找到重复数据，然后用not反向取条件，剩下的就是不重复的数据。通过嵌入两个子查询可以实现这个功能。

17:打开cusor，为输出数据做准备。

18:取得各州排名均是前10的产品。首先用窗口函数取得各州内产品的排名rankorder，然后过滤出各州内排名前10的产品，最后取得在各州都是前10的产品。由于SQL没有求交集这样的函数，这里使用了一个技巧：按照产品分组，如果相同产品的数量等于州的数量，则说明这个产品在各州均是前10名。通过三层嵌套查询和一个子查询可以实现这个功能。这里用到了SQL-2003的窗口函数，可以显著降低难度。

存储过程实现这个目标的方法很多，这里是把计算过程拆分成2个清晰的步骤，实际上可以用更多的临时表和更多的SQL语句把计算过程拆分得更细，当然代码量也会更大。

 

用集算器解答

wKiom1PF4oeRdLXKAACsHGV77TI937.jpg

A1格：取出数据

A2格：过滤掉A1中的重复数据。

A3格：把A2中的数据按state进行分组。

B3格：在A3的每个组（州）里，求出销售额前10的记录序号。

A4格：用序号从A3中取出产品。

A5格：求A4中各组产品的交集。

这里的函数”rank”用来求排名，以amount为比较依据；函数”pselect”用来求记录的序号，条件是排名小于10；函数isect用来求多组数据的交集。

 

比较

存储过程解决此类问题需要很高的技术能力，只能在难懂的语句和大量的代码之间做选择，前者对SQL技术要求非常高，后者对编程技术要求非常高。另一方面，存储过程推出的主要目的是弥补SQL语句的缺陷，但存储过程仍然是以SQL语句为基础的，难以从根本上改变这些缺陷。

可以看到，集算器解决这个问题是按照直观的业务描述进行的，没有所谓”技巧”之类难懂的语句；每一步的分析都清晰易读，可以逐步推进，轻松实现分析目标。

比其存储过程来，集算器更适合解决类似的复杂计算，这是由集算器的一些特性决定的。

                                   

特性：JDBC接口

JDBC是一种应用广泛而且标准规范的接口，常用来被JAVA架构的应用程序调用，达到访问各类数据库的目的。集算器由纯JAVA语言编写而成，对外提供了JDBC接口，集算器将被识别为一个数据库，外部程序（比如报表工具）可以用SQL语句访问集算器。

 

特性：专业的批量数据计算工具

集算器是专为批量数据计算而生的工具，具备SQL语句和存储过程的分析能力，一方面可以像SQL语句那样对数据进行查询、过滤、分组，统计；另一方面也可像存储过程那样对分析过程进行循环、分支判断。

 

特性：超越存储过程

同为批量数据计算的工具，SQL语句和存储过程存在着一些明显的缺陷：分步不彻底、集合化不彻底、缺乏序号、缺乏引用。因此遇到复杂计算时，设计SQL语句或存储过程往往会变得异常艰难，对开发人员的技术能力也要求极高。

集算器的分析能力也大大强于存储过程，克服了这些固有缺陷，可以轻松实现复杂的计算，对技术人员的要求也大大降低。

 

集算器的价值

集算器强大的分析能力，较低的技术要求使开发人员摆脱了复杂难懂的存储过程。

集算器不绑定数据库，支持多数据源，可以方便的进行移植，是高效易维护的开发工具。

集算器专为批量数据计算而打造，是数据计算更好的选择。