简化各种SQL有序计算的示例

来源：互联网发布：linux u盘删除分区表编辑：程序博客网时间：2024/05/22 03:06

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的实现，比较二者的区别：

SELECT T1.ID,T1.store    ,SUM(DISTINCT T2.store)store1    ,SUM(T3.store)store2  FROM test T1    JOIN test T2 ON T1.ID>=T2.ID    OIN test T3 ON T2.ID>=T3.ID  GROUP BY T1.ID,T1.store  ORDER BY T1.ID

连续升降统计

股票记录数据如下，要求计算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的实现：

select count(*)  from (select t.*,      if(@pp := @prev,        if(@prev := value, @pp, @pp)      ) as prev_value     from table t cross join        (select @prev := NULL) vars     order by id     ) t  where value > 10 and (prev_value is null or prev_value < 10);

区间过滤

有数据如下：

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

目标结果：

集算器脚本

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

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

SQL的实现：

  WITH T1 as (  select id,ddate,max(ddate)over(order by id) d1  from t0042),T2 as (  select id,min(ddate)over(order by id desc) d2  from t0042)  select T1.id,T1.ddate  from T2,T1  WHERE T1.id=T2.id and (t1.ddate<>T1.d1 or t1.ddate<>T2.d2)

区间补齐

有数据如下：

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

目标结果：

集算器脚本：

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

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

SQL实现：

 SELECT 1000+T2.number  FROM TB T1 RIGHT JOIN master..spt_values T2 ON T1.A=T2.number+1000  WHERE T2.type=’P'AND T2.number>0 AND 1000+T2.number<=(SELECT MAX(A)FROM TB) AND T1.A IS NULL

区间合并

有数据如下：

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

目标结果：

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

集算器脚本：

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

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

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

SQL实现：

  select row_number()over(order by rn) xh,    min(num1) num1,    max(num2) num2  from  (  select num1,    num2,    last_value(case when rn is not null then rn end ignore nulls)over(order by rownum) rn  from  (  select num1,    num2,    lag(num2)over(order by xh),    (case when num1 – lag(num2)over(order by xh) < 0 then null else xh end) rn  from tt  ) t  ) t1  group by  rn