SQL 如何找出连续满足某种条件的记录

 

数据表SSEC是上证指数历史数据,部分数据如下:

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


需要计算2020 年收盘价(CLOSING)最长连续上涨了多少天。

 

把按日期有序的指数记录分成若干组,连续上涨的记录分成同一组。也就是说,某天的收盘价比上一天是上涨的,则和上一天记录分到同一组,如果下跌了,则开始一个新组。最后看所有分组中最大的成员数量,也就是最多连续上涨的天数。

ORACLESQL

SELECT MAX(CONTINUOUSDAYS) - 1 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

)

 

解这道题其实很简单,就是一条一条记录挨个去对比,如果当前这条记录的收盘价(CLOSING)比上一条的高,那说明当前这天是涨的,就和上一天的记录算作是同一组,否则就要再分一个新组,继续做这样的对比直至所有记录比完。但是SQL没有直接提供按连续条件的分组,只能按列数据做等值分组,没办法按上述自然思维去工作,需要繁琐的办法,先人为造出可以用于等值分组的列数据再分组。

 

用开源集算器的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()-1)

SPL直接支持按连续条件分组,很容易实现按条件进行分组的计算。