My SQL 计算连续涨跌

【问题】

Hello i'm trying to create a rally Up rally DOWN stock indicator. i have thre columns :

Date    Price   Result

1/1/2015    3       1 here start from 1

2/1/2015    4       2

3/1/2015    347     3

4/1/2015    464     4

5/1/2015    35      5

6/1/2015    363     6

7/1/2015    -5      1 here restart from 1 because it is negative

8/1/2015    -3      2

9/1/2015    -5      3

10/1/2015   37      1 here restart from 1 because it is positive

11/1/2015   896     2

12/1/2015   36      3

13/1/2015   -636    1

14/1/2015   -353    2

15/1/2015   -242    3

I want to calculate positive continues days values and add to RESULT and negative continues days values and add to RESULT.

For example if 1/1/2015 is positive then RESULT = 1

            if 2/1/2015 is positive then RESULT = previous Result + 1

                                         ........

            if 7/1/2015 is negative then Result = 1

            if 8/1/2015 is negative then Result = previous Result + 1

 

【回答】

       SQL不擅长表达相对位置,特别是计算连续涨跌的情况,虽然使用窗口函数可以解决这个问题,但代码非常复杂难懂,这有个SQL写出来的类似例子:

http://www.raqsoft.com/compare-over-the-previous-period-for-consecutive-intervals.html

这种情况用SPL辅助就很简单,只需两行代码:


A

1

$select   Date,Price from stock order by Date

2

=A1.derive(if(Price*Price[-1]<0,1,result[-1]+1):result)

A1sql取数,按照Date排序

A2:给A1增加result字段,字段值根据if(Price*Price[-1]<0,1,result[-1]+1)判断,当Price由正数变成负数,或者由负数变成正数时,result1重新计数,否则累加1连续计数

       写好的脚本如何在应用程序中调用,可以参考Java 如何调用 SPL 脚本