Inter-row calculations on grouping subsets

Find out the stocks that reached the daily trading limit (price gains 10%) for three consecutive days.

SQL

WITH A AS
     (SELECT code,trade_date, close_price/lag(close_price)
         OVER(PARTITION BY stock ORDER BY trade_date)-1
         rising_range
     FROM stock_price),
     B AS
     (SELECT code,
         CASE WHEN rising_range>=0.1 AND
             lag(rising_range) OVER(PARTITION BY code
                  ORDER BY trade_date)>=0.1 AND
             lag(rising_range,2) OVER(PARTITION BY code
                  ORDER BY trade_date)>=0.1
             THEN 1 ELSE 0 END three_consecutive_days_gains
      FROM A)
SELECT DISTINCT code FROM B WHERE three_consecutive_days_gains=1

Use the window function to compute the growth rates, and then apply the window function to the growth rates to get the result indicating three consecutive days of gains, and then use multi-level nested subqueries to get the final result. An additional level of grouping dramatically increases the complexity, but luckily the user can use with clause to split the nested SQL statement into what looks like stepwise computations.

SPL

Compute the result gradually by using a loop statement.

A B C
1 =demo.query("select * from stock_price").group(code).(~.sort(trade_date)) =[] Result set in C1
2 for A1 =0
3 if A2.pselect(B2=if(close_price/close_price[-1]>=1.1,B2+1,0):3)>0 Whether there are three consecutive days of gains
4 >C1=C1|A2.code

Alternatively, compute the result by using the sub-computation statement.

A B
1 =demo.query("select * from stock_price").group(code).(~.sort(trade_date))
2 ==A1.select(??) =0
3 =~.pselect(B2=if(close_price/close_price[-1]>=1.1,B2+1,0):3)>0
4 =A2.(code)

With intrinsic support to the stepwise computation, SPL will not increase the complexity very much even if an additional level of grouping is needed. By using the sub-computation statement or loop to handle the computation in the outer layer, the inner layer computation becomes as easy as the other computations with fewer levels, so it will not add extra difficulty when the user tries to figure out how to code.

Besides, the above SPL code can be easily extended to find out the stocks that reach the daily trading limit for any number of consecutive days, and it will stop calculating the growth rates once it finds the first day of the consecutive days of price gains. Unlike SPL, the above SQL syntax is very difficult to expand, and the window function must complete all of the inter-row computations before it can filter data.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.