【Question】

Hello I'm trying to create a rally Up rally DOWN stock indicator. There are three 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 continuous, positive days values and add to RESULT and continuous, negative 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

【Answer】

It’s inconvenient to express and reference a position relatively in SQL. A typical case is calculating continuous ups and downs in price. Window functions are useful but the code is complicated and hard to understand. Here’s such an example:

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

It’s simple to do it in SPL (Structured Process Language) with a two-liner:

A1: Retrieve data ordered by Date in SQL.

A2: Add a result field to A1. Its values are determined by if(Price*Price[-1]<0,1,result[-1]+1). When Price value changes from a positive to a negative, or vice versa, result value is reset to 1; When Price value remain being a positive or a negative, add 1 for each next record.

About calling an SPL script in Java, see How to Call an SPL Script in Java.