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)最长连续上涨了多少天。
把按日期有序的指数记录分成若干组,连续上涨的记录分成同一组。也就是说,某天的收盘价比上一天是上涨的,则和上一天记录分到同一组,如果下跌了,则开始一个新组。最后看所有分组中最大的成员数量,也就是最多连续上涨的天数。
ORACLE的SQL:
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直接支持按连续条件分组,很容易实现按条件进行分组的计算。