时间戳

首页 > 解决方案 > Oracle SQL 或 PL/SQL：如何仅在上升趋势或下降趋势结束时识别烛台形态并在列中设置标志？

oracle - Oracle SQL 或 PL/SQL：如何仅在上升趋势或下降趋势结束时识别烛台形态并在列中设置标志？

问题描述

此问题和相关答案仅用于教育或学习目的。

这个问题与我的其他帖子有很大不同，并且不重复。由于它造成了混乱并且正如@MT0 所建议的那样，我将其作为一个新问题发布在这里。

我有下表，我每天上传股票数据。

/* CREATE TABLE */
CREATE TABLE RAW_SOURCE(
  Stock  VARCHAR(100),
  Close_Date DATE,
  Open   NUMBER,
  High   NUMBER,
  Low    NUMBER,
  Close  NUMBER,
  Volume NUMBER
);

/* INSERT QUERY NO: 1 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '01/01/2021', 40, 40.5, 38.5, 38.8, 83057
);

/* INSERT QUERY NO: 2 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '02/01/2021', 39.2, 39.2, 37.2, 37.8, 181814
);

/* INSERT QUERY NO: 3 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '03/01/2021', 38, 38.5, 36.5, 37, 117378
);

/* INSERT QUERY NO: 4 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '04/01/2021', 36.5, 36.6, 35.6, 35.7, 93737
);

/* INSERT QUERY NO: 5 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '05/01/2021', 35.35, 36.8, 35.1, 36.7, 169106
);

/* INSERT QUERY NO: 6 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '06/01/2021', 36.5, 38.5, 36.5, 38, 123179
);

/* INSERT QUERY NO: 7 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '07/01/2021', 37.5, 39.5, 37.3, 39.4, 282986
);

/* INSERT QUERY NO: 8 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '08/01/2021', 39, 40.5, 38.5, 40, 117437
);

/* INSERT QUERY NO: 9 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '09/01/2021', 39.7, 39.8, 39.3, 39.4, 873009
);

/* INSERT QUERY NO: 10 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '10/01/2021', 39.2, 39.2, 37.2, 37.8, 62522
);

/* INSERT QUERY NO: 11 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '11/01/2021', 38, 38.5, 36.5, 37, 114826
);

/* INSERT QUERY NO: 12 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '12/01/2021', 36.5, 37.9, 36.3, 37.8, 281461
);

/* INSERT QUERY NO: 13 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '13/01/2021', 37.5, 39.5, 37.3, 39.4, 77334
);

/* INSERT QUERY NO: 14 */
INSERT INTO RAW_SOURCE(Stock, Close_Date, Open, High, Low, Close, Volume)
VALUES
(
'XYZ', '14/01/2021', 39, 40.5, 38.5, 40, 321684
);

以下是一只股票“XYZ”的样本数据：

+-------+------------+-------+------+------+-------+--------+
| Stock | Close Date | 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.5 | 36.6 | 35.6 |  35.7 |  93737 |
| XYZ   | 05-01-2021 | 35.35 | 36.8 | 35.1 |  36.7 | 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 |
| XYZ   | 09-01-2021 |  39.7 | 39.8 | 39.3 |  39.4 | 873009 |
| XYZ   | 10-01-2021 |  39.2 | 39.2 | 37.2 |  37.8 |  62522 |
| XYZ   | 11-01-2021 |    38 | 38.5 | 36.5 |    37 | 114826 |
| XYZ   | 12-01-2021 |  36.5 | 37.9 | 36.3 |  37.8 | 281461 |
| XYZ   | 13-01-2021 |  37.5 | 39.5 | 37.3 |  39.4 |  77334 |
| XYZ   | 14-01-2021 |    39 | 40.5 | 38.5 |    40 | 321684 |
+-------+------------+-------+------+------+-------+--------+

在一段时间内，每个股票代码将有超过数千条记录，我想仅在上升/上升趋势的顶部或在下降/下降趋势的底部识别烛台模式，而不是横盘（因为这将是误报）。以下是示例截图：

在此处输入图像描述

假设今天是2021 年 1 月 12 日，以下是预期输出：

+-------+-------------------+------------+------------+--------------+--------+---------------+
| Stock | Consecutive Count | Start Date |  End Date  | Latest Close | Volume |    Pattern    |
+-------+-------------------+------------+------------+--------------+--------+---------------+
| XYZ   |                 3 | 09-01-2021 | 12-01-2021 |         37.8 | 281461 | Piercing Line |
+-------+-------------------+------------+------------+--------------+--------+---------------+

由于源表将包含许多其他股票，如果发现任何模式，希望在 2021 年 1 月 12 日显示其他股票的结果。我觉得这是非常具有挑战性和复杂的逻辑。因此在这里寻求帮助。提前致谢。

更新：谢谢@JustinCave
这是计算公式：看涨吞没：

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

O1 = Previous day Open price  
C1 = Previous day Close price  
C  = Today's Close price  
O  = Today's Open price  
H1 = Previous day High price  
L1 = Previous day Low price

对于看跌孕产：

(O1 < C1) and (O > C) and (O < C1) and (C > O1) and (H < H1) and (L > L1)
where,
    O1 = Previous day Open price  
    C1 = Previous day Close price  
    C  = Today's Close price  
    O  = Today's Open price  
    H1 = Previous day High price  
    L1 = Previous day Low price 
    H  = Today's High price  
    L  = Today's Low price

对于穿刺线：

(O < C) and (O1 > C1) and (C > (C1 + O1)/2) and (O < C1) and (C < O1)
    where,
        O1 = Previous day Open price  
        C1 = Previous day Close price  
        C  = Today's Close price  
        O  = Today's Open price

标签： oracleplsqloracle-apex

解决方案

模式的MATCH_RECOGNIZE工作方式与正则表达式类似；你想要类似的东西：

（注意：你的PIERCING_LINE公式没有给出预期的输出，所以我假设你想要C > (C1 + O1)/2而不是C > C1 + (O1/2)。）

SELECT *
FROM   raw_source
MATCH_RECOGNIZE (
  PARTITION BY stock
  ORDER BY Close_Date
  MEASURES
    CLASSIFIER() AS pttrn
  ALL ROWS PER MATCH
  PATTERN (
    ^initial_value
    |
    down+ (bullish_engulfing | piercing_line | $)
    |
    up+ (bearish_harami | $)
    |
    other
  )
  DEFINE
    down AS
          PREV(open) > open
      AND PREV(close) > close
      AND PREV(open) > PREV(close)
      AND open > close,
    up AS
          PREV(open) < open
      AND PREV(close) < close
      AND PREV(open) < PREV(close)
      AND open < close,
    bullish_engulfing AS
      -- O1 > C1 and C > O and C > H1 and O < L1
          PREV(open) > PREV(close)
      AND close > open
      AND close > PREV(high)
      AND open  < PREV(low),
    bearish_harami AS
      -- O1 < C1 and O > C and O < C1 and C > O1 and H < H1 and L > L1
          PREV(open) < PREV(close)
      AND open > close
      AND open < PREV(close)
      AND close > PREV(open)
      AND high < PREV(high)
      AND low > PREV(low),
    piercing_line AS
      -- O < C and O1 > C1 and C > (C1 + O1)/2 and O < C1 and C < O1
          open < close
      AND PREV(open) > PREV(close)
      AND close > (PREV(close) + PREV(open))/2
      AND open < PREV(close)
      AND close < PREV(open)
)

哪个输出：

股票 CLOSE_DATE PTTRN 打开 高的 低的 关 体积
XYZ 2021 年 1 月 1 日 初始值 40 40.5 38.5 38.8 83057
XYZ 2021 年 2 月 1 日 下 39.2 39.2 37.2 37.8 181814
XYZ 2021 年 3 月 1 日 下 38 38.5 36.5 37 117378
XYZ 2021 年 4 月 1 日 下 36.5 36.6 35.6 35.7 93737
XYZ 2021 年 5 月 1 日 BULLISH_ENGULFING 35.35 36.8 35.1 36.7 169106
XYZ 2021 年 6 月 1 日 向上 36.5 38.5 36.5 38 123179
XYZ 2021 年 7 月 1 日 向上 37.5 39.5 37.3 39.4 282986
XYZ 2021 年 8 月 1 日 向上 39 40.5 38.5 40 117437
XYZ 2021 年 9 月 1 日 BEARISH_HARAMI 39.7 39.8 39.3 39.4 873009
XYZ 2021 年 10 月 1 日 下 39.2 39.2 37.2 37.8 62522
XYZ 2021 年 11 月 1 日 下 38 38.5 36.5 37 114826
XYZ 2021 年 12 月 1 日 穿孔_LINE 36.5 37.9 36.3 37.8 281461
XYZ 2021 年 13 月 1 日 向上 37.5 39.5 37.3 39.4 77334
XYZ 14/01/2021 向上 39 40.5 38.5 40 321684

db<>在这里摆弄

推荐阅读