How SQL Gets Continuous Records Meeting a Specified Condition
Data table SSEC stores historical SSE Composite Index data. Below is part of it:
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
SELECT COUNT(*) AS CONTINUOUSDAYS
SELECT SUM(RISINGFLAG) OVER (ORDER BY TDATE) AS NORISINGDAYS
WHEN CLOSING > LAG(CLOSING) OVER (ORDER BY TDATE) THEN0
END AS RISINGFLAG
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:
=connect("ORACLE").query@x("SELECT TDATE,CLOSING FROM SSEC WHERE EXTRACT(YEAR FROM TDATE) = 2020")
SPL offers direct support of grouping operations by a continuous condition. It is easy for it to handle such a computing task.