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<CLOSING[-1]).max(~.len())

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