# How SQL Gets Continuous Records Meeting a Specified Condition

Data table SSEC stores historical SSE Composite Index data. Below is part of it:

 TDATE CLOSING OPENING HIGHEST LOWEST VOLUME … … … … … 2020-01-02 3085.2 3066.34 3098.1 3066.34 29.25B 2020-01-03 3083.79 3089.02 3093.82 3074.52 26.15B 2020-01-06 3083.41 3070.91 3107.2 3065.31 31.26B 2020-01-07 3104.8 3085.49 3105.45 3084.33 27.66B 2020-01-08 3066.89 3094.24 3094.24 3059.13 29.79B … … … … …

We are trying to calculate the largest number of consecutively rising days for the CLOSING price of the year 2020.

That is, grouping the index records ordered by date by putting continuously rising ones in one group (when the closing price on a certain date is higher than the previous price, put it and the previous record to the same group; otherwise, create a new group), and count the members in each group to find the largest one, which is the largest number of consecutively rising days.

SQL wrote in ORACLE:

SELECT MAX(CONTINUOUSDAYS) AS MAX_DAYS

FROM (

SELECT COUNT(*) AS CONTINUOUSDAYS

FROM (

SELECT SUM(RISINGFLAG) OVER (ORDER BY TDATE) AS NORISINGDAYS

FROM (

SELECT TDATE

, CASE

WHEN CLOSING > LAG(CLOSING) OVER (ORDER BY TDATE) THEN0

ELSE 1

END AS RISINGFLAG

FROM SSEC

WHERE EXTRACT(YEAR FROM TDATE) = 2020

)

)

GROUP BY NORISINGDAYS

)

The approach is simple. We just need to compare records one by one to the end. During the process the closing price of the current record rises if it is higher than that of the previous one and will be put in the same group, otherwise it decreases and will be put into a new group. SQL’s issue is that it can only perform equi-grouping by column data and does not support grouping operations by a continuous condition. The language is unable to implement a natural and straightforward solution, instead it resorts to a roundabout and complicated way, which is inventing a column according to which the equi-grouping operation can be performed.

It is convenient to achieve the natural solution using the open-source esProc SPL:

 A 1 =connect("ORACLE").query@x("SELECT TDATE,CLOSING FROM SSEC WHERE EXTRACT(YEAR FROM TDATE) = 2020") 2 =A1.sort(TDATE).group@i(CLOSING

SPL offers direct support of grouping operations by a continuous condition. It is easy for it to handle such a computing task.