MySQL 计算连续涨跌
【问题】
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) |
A1：sql取数，按照Date排序
A2：给A1增加result字段，字段值根据if(Price*Price[-1]<0,1,result[-1]+1)判断，当Price由正数变成负数，或者由负数变成正数时，result为1重新计数，否则累加1连续计数
写好的脚本如何在应用程序中调用，可以参考Java 如何调用 SPL 脚本
