Oracle SQL/PLSQL基于历史行与日期列识别股票看涨看跌趋势

云上老周

2026-10-2

我找到了很多Python技术栈下相关问题的解决方案，但很少有Oracle SQL或PL/SQL的实现，因此我尝试基于Oracle Application Express使用Oracle SQL实现该需求。

我有一张存储8天样例股票数据的数据库表，每日收盘后会插入新的交易记录。目前我已有查询可识别股价连续涨跌的股票，现在需要新增一列Pattern，根据当日与前一交易日的取值标注看涨（Bullish）或看跌（Bearish）趋势。

+-------+------------+--------+-------+-------+--------+--------+
| Stock |   Cdate    |  Open  | High  |  Low  | Close  | Volume |
+-------+------------+--------+-------+-------+--------+--------+
| XYZ   | 01-01-2021 |     40 |  40.5 |  38.5 |   38.8 |  83057 |
| XYZ   | 02-01-2021 |   39.2 |  39.2 |  37.2 |   37.8 | 181814 |
| XYZ   | 03-01-2021 |     38 |  38.5 |  36.5 |     37 | 117378 |
| XYZ   | 04-01-2021 |     36 |  36.1 |  35.6 |   35.7 |  93737 |
| XYZ   | 05-01-2021 |  35.35 |  36.8 |  35.1 |   36.6 | 169106 |
| XYZ   | 06-01-2021 |   36.5 |  38.5 |  36.5 |     38 | 123179 |
| XYZ   | 07-01-2021 |   37.5 |  39.5 |  37.3 |   39.4 | 282986 |
| XYZ   | 08-01-2021 |     39 |  40.5 |  38.5 |     40 | 117437 |
|       |            |        |       |       |        |        |
| DDD   | 01-01-2021 | 135.35 | 136.8 | 135.1 |  136.6 |  16454 |
| DDD   | 02-01-2021 |  136.5 | 138.5 | 136.5 |    138 | 281461 |
| DDD   | 03-01-2021 |  137.5 | 139.5 | 137.3 |  139.4 |  77334 |
| DDD   | 04-01-2021 |    139 | 140.5 | 138.5 |    140 | 321684 |
| DDD   | 05-01-2021 |  139.7 | 139.8 | 139.3 |  139.4 | 873009 |
| DDD   | 06-01-2021 |  139.2 | 139.2 | 137.2 |  137.8 |  62522 |
| DDD   | 07-01-2021 |    138 | 138.5 | 136.5 |    137 | 114826 |
| DDD   | 08-01-2021 |  136.6 | 136.8 | 135.1 | 135.35 |  27317 |
+-------+------------+--------+-------+-------+--------+--------+

股票XYZ的走势如下图：
XYZ股票走势

2021年1月5日趋势发生变化，满足如下公式则标注为Bullish：

O1 > C1 and C > O and C > H1 and O < L1

其中变量定义如下：

O1 = 前一交易日开盘价
C1 = 前一交易日收盘价
C = 当日收盘价
O = 当日开盘价
H1 = 前一交易日最高价
L1 = 前一交易日最低价

预期输出将新增Pattern列，符合条件的行标注Bullish，样例如下：

+-------+------------+-------+------+------+-------+--------+---------+
| Stock |   Cdate    | Open  | High | Low  | Close | Volume | Pattern |
+-------+------------+-------+------+------+-------+--------+---------+
| XYZ   | 01-01-2021 |    40 | 40.5 | 38.5 |  38.8 |  83057 |         |
| XYZ   | 02-01-2021 |  39.2 | 39.2 | 37.2 |  37.8 | 181814 |         |
| XYZ   | 03-01-2021 |    38 | 38.5 | 36.5 |    37 | 117378 |         |
| XYZ   | 04-01-2021 |    36 | 36.1 | 35.6 |  35.7 |  93737 |         |
| XYZ   | 05-01-2021 | 35.35 | 36.8 | 35.1 |  36.6 | 169106 | Bullish |
| XYZ   | 06-01-2021 |  36.5 | 38.5 | 36.5 |    38 | 123179 |         |
| XYZ   | 07-01-2021 |  37.5 | 39.5 | 37.3 |  39.4 | 282986 |         |
| XYZ   | 08-01-2021 |    39 | 40.5 | 38.5 |    40 | 117437 |         |
+-------+------------+-------+------+------+-------+--------+---------+

我在网上搜索相关解决方案，最接近的是单字段连续增长行识别的相关内容，我修改了查询但无法添加公式或新增字段，我尝试的查询代码如下：

WITH StockRow AS (SELECT stock, close, cdate,
                         ROW_NUMBER() OVER(PARTITION BY stock
                                           ORDER BY cdate) rn
                  FROM sampletable where volume > 200000),

     RunGroup AS (SELECT Base.stock, Base.cdate, base.close,
                         MAX(Restart.rn) OVER(PARTITION BY Base.stock
                                              ORDER BY Base.cdate) groupingId
                  FROM StockRow Base
                  LEFT JOIN StockRow Restart
                         ON Restart.stock = Base.stock
                            AND Restart.rn = Base.rn - 1
                            AND Restart.CLOSE < Base.CLOSE)

SELECT stock, 
       COUNT(*) AS consecutiveCount, 
       MIN(cdate) AS startDate, MAX(cdate) AS endDate, min(close) as latestclose
FROM RunGroup
GROUP BY stock, groupingId
HAVING COUNT(*) >= 3
and MAX(cdate) = (select max(cdate) from sampletable)
ORDER BY CONSECUTIVECOUNT desc, stock, startDate

同理股票DDD的走势如下：
DDD股票看跌走势

2021年1月5日趋势发生变化，满足如下公式则标注为Bearish：

C < O and C < C1 and O < C1 and O1 < C1 and C > O1 and O < C1

其中变量定义如下：

O1 = 前一交易日开盘价
C1 = 前一交易日收盘价
C = 当日收盘价
O = 当日开盘价

最终预期输出样例如下：

+-------+------------+--------+-------+-------+--------+--------+---------+
| Stock |   Cdate    |  Open  | High  | Low  | Close  | Volume | Pattern |
+-------+------------+--------+-------+-------+--------+--------+---------+
| XYZ   | 01-01-2021 |     40 |  40.5 |  38.5 |   38.8 |  83057 |         |
| XYZ   | 02-01-2021 |   39.2 |  39.2 |  37.2 |   37.8 | 181814 |         |
| XYZ   | 03-01-2021 |     38 |  38.5 |  36.5 |     37 | 117378 |         |
| XYZ   | 04-01-2021 |     36 |  36.1 |  35.6 |   35.7 |  93737 |         |
| XYZ   | 05-01-2021 |  35.35 |  36.8 |  35.1 |   36.6 | 169106 | Bullish |
| XYZ   | 06-01-2021 |   36.5 |  38.5 |  36.5 |     38 | 123179 |         |
| XYZ   | 07-01-2021 |   37.5 |  39.5 |  37.3 |   39.4 | 282986 |         |
| XYZ   | 08-01-2021 |     39 |  40.5 |  38.5 |     40 | 117437 |         |
|       |            |        |       |       |        |        |         |
| DDD   | 01-01-2021 | 135.35 | 136.8 | 135.1 |  136.6 |  16454 |         |
| DDD   | 02-01-2021 |  136.5 | 138.5 | 136.5 |    138 | 281461 |         |
| DDD   | 03-01-2021 |  137.5 | 139.5 | 137.3 |  139.4 |  77334 |         |
| DDD   | 04-01-2021 |    139 | 140.5 | 138.5 |    140 | 321684 |         |
| DDD   | 05-01-2021 |  139.7 | 139.8 | 139.3 |  139.4 | 873009 | Bearish |
| DDD   | 06-01-2021 |  139.2 | 139.2 | 137.2 |  137.8 |  62522 |         |
| DDD   | 07-01-2021 |    138 | 138.5 | 136.5 |    137 | 114826 |         |
| DDD   | 08-01-2021 |  136.6 | 136.8 | 135.1 | 135.35 |  27317 |         |
+-------+------------+--------+-------+-------+--------+--------+---------+

补充说明：我希望识别截图中黄色高亮的上涨/下跌趋势拐点区域。恳请各位提供实现方案，非常感谢。

XYZ股票2021年1月5日看涨拐点
XYZ看涨拐点

DDD股票2021年1月5日看跌拐点
DDD看跌拐点

此致
Richa

内容的提问来源于stack exchange，提问作者Richa