简化SQL有序计算示例

? ? ? ?SQL集合无序使得完成有序计算比较困难，而这类计算在实际业务中又非常常见，网上有很多讨论，比如：

? ? ? ?http://bbs.csdn.net/topics/390959904

? ? ? ?http://bbs.csdn.net/topics/390994046

? ? ? ?http://bbs.csdn.net/topics/390980889

? ? ? ?http://bbs.csdn.net/topics/390998839

? ? ? ?http://bbs.csdn.net/topics/390813196

? ? ? ?http://bbs.csdn.net/topics/390977682

? ? ? ?http://bbs.csdn.net/topics/391020551

? ? ? ?http://bbs.csdn.net/topics/390976752

? ? ? ?http://bbs.csdn.net/topics/391028980

??????

? ?SQL在完成这类运算时往往采用如下手段：

? ?1、使用窗口函数

? ?对于支持窗口函数的数据库（如Oracle MSSQL），使用窗口函数多数情况下可以简化SQL写法，但需要数据库支持，使用有一定局限。

? ? ? ? ?2、使用复杂SQL

?

不支持窗口函数的数据库往往要编写复杂SQL，经常要嵌套多层子查询才能完成，书写十分复杂。其中，支持变量定义，按顺序计算（如MySQL）可以简化部分计算。

? ? ? ? 3、编写存储过程

一些比较复杂的场景，就需要在存储过程中一步步来完成，实现并不简单。

?

???????如果主程序是Java的，可以使用免费的集算器来协助，集算器支持有序计算，比SQL要简单许多，下面来看一些具体实例。

同比环比计算

? ? ? ? 行间计算（如比上期、比同期）在不支持窗口函数的数据库下实现尤为困难，需要更换思路使用连接（join）运算替代，不仅难以理解而且效率低下。即使可以使用窗口函数仍要面临嵌套子查询等问题，SQL语句仍较为冗长。

? ? ? ? 来看集算器的解法，表sales存储着多年的订单数据，请根据该表计算出指定时间段内各月份的销售额比上期和同期比。部分源数据如下：

?

?

? ? ? ? 集算器脚本：

?

? ? ? ? A1：按时间段从数据库查询数据，并按OrderDate的年月分组汇总Amount，begin和end是外部参数，比如begin="2011-01-01 00:00:00"，end="2014-07-08 00:00:00"。

? ? ? ? A2：在A1基础上增加一个新的字段lrr，即按月比上期和比去年同期，前者表达式为mAmount/mAmount[-1]。集算器可以用 [N]或[-N]来表达相对于当前记录之后的第N条记录，或之前的第N条记录，因此代码中mAmount代表当期销售额，mAmount[-1]代表上期销售额。需要注意的是，初始月份的比上期值为空（即2011年1月）。

?

? ? ? ? A3：将A2按照月、年排序，以便计算同期比。完整的代码应当是：=A3.sort(m,y)，由于A3本来就是按年排序的，因此只需按月排序就可以达到目的，即A3.sort(m)，这样性能也高。

? ? ? ? A4：在A3的基础上计算销售额的同期比，月份相同时才进行同期比计算。

?

集算脚本的计算结果可以作为报表数据源供报表使用，还可以在JAVA程序中通过JDBC的方式读取并使用，JAVA读取调用集算脚本代码如下：

?????????? Class.forName("com.esproc.jdbc.InternalDriver");

? ? ? ? ? ?con= DriverManager.getConnection("jdbc:esproc:local://");

? ? ? ? ? ?//调用集算器脚本（类似存储过程），其中p1是集算脚本的文件名

? ? ? ? ? ?st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call p1()");

? ? ? ? ? ?st.setObject(1,"2011-01-01 00:00:00");

?

? ? ? ? ? ?st.setObject(2,"2014-07-08 00:00:00");

?

? ? ? ? ? ? ? ? ?//执行脚本

? ? ? ? ? ?st.execute();

? ? ? ? ? ?//获取结果集

? ? ? ? ? ?ResultSet rs = st.getResultSet();

???????????????????? ……

返回值是符合JDBC标准的ResultSet对象，调用集算器脚本和访问数据库的方法完全一样，熟悉JDBC的程序员可以很快掌握。

关于集算器JDBC的部署和调用的更详细信息可参考【集算器集成应用之被JAVA调用】

?

跨行计算

?????? 同期比环比属于一类行间计算，这里来看另一类跨行计算的例子。有数据如下：

? ? ? ?要求：增加两列store1和store2，第一条记录store1=store，store2=store，从第二条开始store1=store+store1[-1]，store2=store1+store2[-1]，其中[-1]代表上一记录。

?

? ? ? ? 目标结果：

?

? ? ? ? 集算器脚本：

?

? ? ? ? A1：执行SQL取数

? ? ? ? A2：创建结果集，设置store1=store+store1[-1]，store2=store1+store2[-1]，集算器中使用[-1]表示相对位置，即上一条记录。

?

?????? 这里也可以看看SQL的实现，比较二者的区别：

?

?

?

?

?

连续升降统计

?????? 股票记录数据如下，要求计算Price连续为正或连续为负的天数。

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

? ? ? ? 目标结果：

?

? ? ? ? 集算器脚本：

?

? ? ? ? A1：执行SQL取数并按日期排序；

? ? ? ? A2：增加result字段，如果当前Price值和上一条Price值同为正或同为负，则result加1，否则为1。

?

?????? 在集算器中使用相对位置[-1]表示上一条记录，还能用(-2)取倒数第2条，用{-1,1}表示相对区间，可以简化复杂的行间计算

连续数据过滤

? ? ? ? 有数据如下：

? ? ? ? 要求：统计num中至少连续出现3次的num值。

?

? ? ? ? 目标结果：

?

? ? ? ? 集算器脚本：

?

? ? ? ? A1：执行SQL取数；

? ? ? ??A2：创建变量lx并赋初值为1；

? ? ? ??A3：判断当前记录的num值如果与上一记录的num相等，则lx+1，最后选出lx为3的num值。

相邻数据查询

?????? 有如下数据：

? ? ? ??要求：查询Property2大于100的记录和它的下一条记录。

?

? ? ? ??目标结果：

?

? ? ? ??集算器脚本：

?

? ? ? ??A1：执行SQL取数；

? ? ? ??A2：获取Property2>100记录的位置；

? ? ? ??A3：根据A2的位置序列，从A1中取所在位置记录和下一条记录。

区间计数

?????? 有表数据如下：

? ? ? ??要求：统计value小于10（或其他值）的行数，但如果多个连续行均小于10，则连续多行只计为1行，所以上述数据的计算结果应为3.

?

?

? ? ? ??集算器脚本：

?

? ? ? ??A1：执行SQL取数；

? ? ? ??A2：按value大于10或小于10做相邻分组，@o表示只比较相邻数据，再统计value小于10的分组个数。

?

?????? 不同于SQL分组后必须聚合，集算器的分组可以保留分组成员以便后续计算使用，因而可以简化SQL运算。

?

?????? 同样可看看SQL的实现：

?

区间过滤

?????? 有数据如下：

? ? ? ??要求：根据ID升序排序，找出DDATE乱序的记录，即日期未按升序排列的记录。

?

? ? ? ??目标结果：

?

? ? ? ??集算器脚本：

?

? ? ? ??A1：执行SQL取数，按ID排序；

? ? ? ??A2：选出 与前面所有记录比较，不是最大的；或者与后面所有记录比较，不是最小的记录。其中DDATE{,0}表示开始到当前所有DDATE集合，DDATE{0,}表示当前到最后所有DDATE集合。

?

? ? ? ??SQL的实现：

?

区间补齐

?????? 有数据如下：

? ? ? ??要求：找出根据A字段最小和最大值生成的数值区间内，缺失的数字。

?

? ? ? ??目标结果：

?

? ? ? ??集算器脚本：

?

? ? ? ??A1：执行SQL查询数据，并按A排序；

? ? ? ??A2：根据首位记录生成连续区间，计算其与A1的补集得到目标结果。

?

? ? ? ??SQL实现：

?

区间合并

?????? 有数据如下：

? ? ? ??要求：判断各条记录中num1和num2生成的区间是否重叠，若重叠则合并。

?

? ? ? ??目标结果：

?

???? 思路是判断是否可以和上一行合并成同一个区间，如果不可以则处理成另一个分组（+1），可以则处理成同一个分组（+0），最后在每组算最小最大端点即可。

?

? ? ? ??集算器脚本：

?

? ? ? ??A1：执行SQL取数，并按num1排序；

? ? ? ??A2：设置临时变量a，并初始化值为0；

? ? ? ??A3：使用排序后的num1与上一条num2做比较，大于属于下一分组，否则合并区间生成新区间。

?

? ? ? ??SQL实现：

?

区间生成

有数据如下：

? ? ? ??要求：将RBD_VALUE字段值拼成分段的字符串，形如： A-F,H,J-K,N-O,Q-S,U-V,X-Z

? ? ? ??具体规则是：

如果RBD_VALUE=“ALL”，则舍去该值。

如果RBD_VALUE前后都是连续的字母，则取第一个字母和最后一个字母作为一段，中间用横杠，比如“A-F”。

如果RBD_VALUE出现间隔，比如K和N之间缺少L和M，则K之前作为一段，N之后作为一段，两段之间用逗号分隔，形如：“J-K,N-O”。

如果RBD_VALUE前后都不连续，比如H前面缺少G，后面缺少I，则当前值单独作为一段。

?

? ? ? ??目标结果：

?

? ? ? ??集算器脚本：

?

? ? ? ??A1:执行SQL取数；

? ? ? ??A2:将A1的RBD_VALUE按照字母表对齐；

? ? ? ??A3：对A2进行分组，RBD_VALUE等于null时分一组，不等于null时再分一组。@o表示归并式分组，只对前后数据进行比较。

? ? ? ??A4:选出非空的组。

?

? ? ? ??A5:根据组内成员个数拼凑字符串。~.m(-1)表示取当前组的最后一条记录。