# Inter-row calculations on grouping subsets

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.

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, esProc 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 esProc 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 esProc, 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.