时间戳

首页 > 解决方案 > Oracle sql 或 pl/sql：根据前一行值和日期列计算

sql - Oracle sql 或 pl/sql：根据前一行值和日期列计算

问题描述

我在 Python 技术上找到了很多与我的问题相关的解决方案，但在 Oracle SQL 或 PL/SQL 解决方案上却找不到太多。因此，使用 Oracle Application Express 在 Oracle SQL 上进行尝试。

我有 db 表，其中包含 8 天的样本库存数据。每天收市后都会插入新记录。我要查询价格连续上涨/下跌的股票。现在我想再添加一列，我可以在其中看到基于当前日期和先前日期值的称为看涨或看跌的模式。

+-------+------------+--------+-------+-------+--------+--------+
| 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 的方式： 在此处输入图像描述

2021 年 1 月 5 日，趋势发生了变化，我想根据公式添加新列“看涨”：

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

在哪里，

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

预期输出将是一个新列，在模式列下显示“看涨”：

+-------+------------+-------+------+------+-------+--------+---------+
| 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 日，趋势发生了变化，我想根据公式添加新列“看跌”：

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

在哪里，

O1 = Previous day Open price  
C1 = Previous day Close price  
C = Today's Close price  
O = Today's Open price  


+-------+------------+--------+-------+-------+--------+--------+---------+
| 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 年 5 月 1 日看涨 在此处输入图像描述

股票 DDD，2021 年 5 月 1 日看跌 在此处输入图像描述

谢谢，
里查

标签： sqloracleoracle-apex

解决方案

在 Oracle 12c 中，您可以使用MATCH_RECOGNIZE：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY stock
  ORDER     BY cdate
  MEASURES
    CLASSIFIER() AS pttrn
  ALL ROWS PER MATCH
  PATTERN (bullish|bearish|other)
  DEFINE
    bullish AS  PREV(open) > PREV(close)
            AND Close > Open
            AND Close > PREV(High)
            AND Open  < PREV(Low),
    bearish AS  Close < Open
            AND Close < PREV(Close)
            AND Open  < PREV(Close)
            AND PREV(Open) < PREV(Close)
            AND Close > PREV(Open)
            AND Open  < PREV(Close)
)

其中，对于样本数据：

CREATE TABLE table_name (Stock, Cdate, Open, High, Low, Close, Volume ) AS
SELECT 'XYZ', DATE '2021-01-01',  40.00,  40.50,  38.50,  38.80,  83057 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-02',  39.20,  39.20,  37.20,  37.80, 181814 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-03',  38.00,  38.50,  36.50,  37.00, 117378 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-04',  36.00,  36.10,  35.60,  35.70,  93737 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-05',  35.35,  36.80,  35.10,  36.60, 169106 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-06',  36.50,  38.50,  36.50,  38.00, 123179 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-07',  37.50,  39.50,  37.30,  39.40, 282986 FROM DUAL UNION ALL
SELECT 'XYZ', DATE '2021-01-08',  39.00,  40.50,  38.50,  40.00, 117437 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-01', 135.35, 136.80, 135.10, 136.60,  16454 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-02', 136.50, 138.50, 136.50, 138.00, 281461 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-03', 137.50, 139.50, 137.30, 139.40,  77334 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-04', 139.00, 140.50, 138.50, 140.00, 321684 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-05', 139.70, 139.80, 139.30, 139.40, 873009 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-06', 139.20, 139.20, 137.20, 137.80,  62522 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-07', 138.00, 138.50, 136.50, 137.00, 114826 FROM DUAL UNION ALL
SELECT 'DDD', DATE '2021-01-08', 136.60, 136.80, 135.10, 135.35,  27317 FROM DUAL;

输出：

股票 日期 PTTRN 打开 高的 低的 关闭 体积
DDD 01-JAN-21 其他 135.35 136.8 135.1 136.6 16454
DDD 21 年 1 月 2 日 其他 136.5 138.5 136.5 138 281461
DDD 21 年 1 月 3 日 其他 137.5 139.5 137.3 139.4 77334
DDD 21 年 1 月 4 日 其他 139 140.5 138.5 140 321684
DDD 21 年 1 月 5 日 看跌 139.7 139.8 139.3 139.4 873009
DDD 21 年 1 月 6 日 其他 139.2 139.2 137.2 137.8 62522
DDD 21 年 1 月 7 日 其他 138 138.5 136.5 137 114826
DDD 21 年 1 月 8 日 其他 136.6 136.8 135.1 135.35 27317
XYZ 01-JAN-21 其他 40 40.5 38.5 38.8 83057
XYZ 21 年 1 月 2 日 其他 39.2 39.2 37.2 37.8 181814
XYZ 21 年 1 月 3 日 其他 38 38.5 36.5 37 117378
XYZ 21 年 1 月 4 日 其他 36 36.1 35.6 35.7 93737
XYZ 21 年 1 月 5 日 看涨 35.35 36.8 35.1 36.6 169106
XYZ 21 年 1 月 6 日 其他 36.5 38.5 36.5 38 123179
XYZ 21 年 1 月 7 日 其他 37.5 39.5 37.3 39.4 282986
XYZ 21 年 1 月 8 日 其他 39 40.5 38.5 40 117437

您还可以使用LAG分析功能（在 Oracle 12 之前可用）：

SELECT t.*,
       CASE
       WHEN LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
            > LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Close > Open
       AND  Close > LAG(high) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(low) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'BULLISH'
       WHEN Close < Open
       AND  Close < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
            < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Close > LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'BEARISH'
       ELSE 'OTHER'
       END AS pttrn
FROM   table_name t

（这给出了类似的输出。）

db<>在这里摆弄

更新：向上和向下跟踪：

同样，这MATCH_RECOGNIZE是为了：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY stock
  ORDER     BY cdate
  MEASURES
    CLASSIFIER() AS pttrn
  ALL ROWS PER MATCH
  PATTERN (^initial_value|bullish|bearish|up|down|other)
  DEFINE
    bullish AS  PREV(open) > PREV(close)
            AND Close > Open
            AND Close > PREV(High)
            AND Open  < PREV(Low),
    bearish AS  Close < Open
            AND Close < PREV(Close)
            AND Open  < PREV(Close)
            AND PREV(Open) < PREV(Close)
            AND Close > PREV(Open)
            AND Open  < PREV(Close),
    up      AS  close > PREV(close)
            AND open  > PREV(open),
    down    AS  close < PREV(close)
            AND open  < PREV(open)
)

但是你可以这样做LAG：

SELECT t.*,
       CASE
       WHEN ROW_NUMBER() OVER (PARTITION BY stock ORDER BY cdate) = 1
       THEN 'INITIAL_VALUE'
       WHEN LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
            > LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Close > Open
       AND  Close > LAG(high) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(low) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'BULLISH'
       WHEN Close < Open
       AND  Close < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
            < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Close > LAG(open) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(close) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'BEARISH'
       WHEN Close > LAG(Close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  > LAG(Open) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'UP'
       WHEN Close < LAG(Close) OVER (PARTITION BY stock ORDER BY cdate)
       AND  Open  < LAG(Open) OVER (PARTITION BY stock ORDER BY cdate)
       THEN 'DOWN'
       ELSE 'OTHER'
       END AS pttrn
FROM   table_name t

db<>在这里摆弄

推荐阅读