简化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存储着多年的订单数据，请根据该表计算出指定时间段内各月份的销售额比上期和同期比。部分源数据如下：

OrderID	Client	SellerId	Amount	OrderDate
1	WVF Vip	5	440	2/3/2009
2	UFS Com	13	1863.4	7/5/2009
3	SWFR	2	1813	7/8/2009
4	JFS Pep	27	670.8	7/8/2009
5	DSG	15	3730	7/9/2009
6	JFE	10	1444.8	7/10/2009
7	OLF	16	625.2	7/11/2009
8	PAER	29	2490.5	7/12/2010
9	DY	20	517.8	7/15/2010
10	JDR	17	1119.9	7/16/2010
11	WBG	17	2018.6	7/17/2009
12	GCD	233	100.8	7/18/2010
13	JKD	11	1746.2	7/19/2009

 

 

        集算器脚本：

 

        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调用】

 

跨行计算

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

ID   store

1     33   

2     55   

3     66   

4     88   

5     12

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

 

        目标结果：

ID   store       store1      store2

1     33    33    33

2     55    88    121

3     66    154  275

4     88    242  517

5     12    254  771

 

        集算器脚本：

 

        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

    JOIN test T3 ON T2.ID>=T3.ID

GROUP BY T1.ID,T1.store

ORDER BY T1.ID

 

 

 

 

 

连续升降统计

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

Date Price

2015-1-1 3

2015-1-2 4

2015-1-3 347

2015-1-4 464

2015-1-5 35

2015-1-6 363

2015-1-7 -5

2015-1-8 -3

2015-1-9 -5

2015-1-10      37

2015-1-11       896

2015-1-12      36

2015-1-13      -636

2015-1-14      -353

2015-1-15      -242

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

        目标结果：

Date Price       result

2015-01-01     3     1

2015-01-02     4     2

2015-01-03     347  3

2015-01-04     464  4

2015-01-05     35    5

2015-01-06     363  6

2015-01-07     -5    1

2015-01-08     -3    2

2015-01-09     -5    3

2015-01-10     37    1

2015-01-11     896  2

2015-01-12     36    3

2015-01-13     -636 1

2015-01-14     -353 2

2015-01-15     -242 3

 

        集算器脚本：

 

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

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

 

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

连续数据过滤

        有数据如下：

id    num

1     1

2     1

3     1

4     2

5     1

6     2

7     3

8     3

9     3

10    3

11    4

12    4

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

 

        目标结果：

1

3

 

        集算器脚本：

 

        A1：执行SQL取数；

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

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

相邻数据查询

       有如下数据：

Date Property1       Property2

2014-11-12 00:00:00     4     88

2014-11-13 00:00:00     5     33

2014-11-14 00:00:00     7     200

2014-11-15 00:00:00     15    50

2014-11-16 00:00:00     8     23

2014-11-22 00:00:00     4     142

2014-11-23 00:00:00     58    8

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

 

        目标结果：

Date Property1       Property2

2014-11-14 00:00:00     7     200

2014-11-15 00:00:00     15    50

2014-11-22 00:00:00     4     142

2014-11-23 00:00:00     58    8

 

        集算器脚本：

 

        A1：执行SQL取数；

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

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

区间计数

       有表数据如下：

id    value

1     11

2     3

3     12

4     2

5     1

6     3

7     15

8     24

9     5

10    15

        要求：统计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

1     2013-5-7

2     2013-8-6

3     2013-6-12

4     2013-12-1

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

 

        目标结果：

ID   DDATE

2     2013-8-6

3     2013-6-12

 

        集算器脚本：

 

        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

1001

1002

1003

1007

1008

1009

1010

1012

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

 

        目标结果：

A

1004

1005

1006

1011

 

        集算器脚本：

 

        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

 

区间合并

       有数据如下：

xh    num1      num2

1     10015     30080

2     20022     50090

3     60250     90900

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

 

        目标结果：

xh    num1      num2

1     10015     50090

2     60250     90900

 

     思路是判断是否可以和上一行合并成同一个区间，如果不可以则处理成另一个分组（+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

 

区间生成

有数据如下：

RBD_ID  ODFEE_ID    RBD_VALUE

25    206  ALL

31    205  A

26    205  B

41    205  C

42    205  D

30    205  E

38    205  F

39    205  H

27    205  J

35    205  K

32    205  N

36    205  O

28    205  Q

44    205  R

34    205  S

29    205  U

33    205  V

40    205  X

37    205  Y

43    205  Z

        要求：将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，则当前值单独作为一段。

 

        目标结果：

A-F

H

J-K

N-O

Q-S

U-V

X-Z

 

        集算器脚本：

 

        A1:执行SQL取数；

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

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

        A4:选出非空的组。

 

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