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:

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