本文將說明如何在一連串的資料列(Rows)中辨識是否符合特定模式，如找出股市V形反轉之轉折點，先前版本中難以採用SQL直接完成，雖有一些解決方法，但通常都些許繁雜且難以撰寫，甚至可能還有效能議題，ORACLE於12C 版本起提供MATCH_RECOGNIZE子句，可直接透過原生且高效之SQL指令來實作以滿足此類需求。
將以2019年11月台積電(2330)收盤價資料，找出反轉向下(倒V)模式為例，由以下趨勢圖呈現出四個反轉點，將以兩個範例說明:
2019年11月-台積電(2330)收盤價走勢
|
|||||||||||||||||||||||||||||||||||
 |
|||||||||||||||||||||||||||||||||||
註: 2019-11-30(六)為複製前一日收盤價，用以模擬平盤狀況。
|
測試1: 找出UP-FLAT-DOWN模式
由2019-11月台積電收盤價走勢圖，得知共有四次漲跌循環(UP-FLAT-DOWN)，由SQL可產生如上表中，如起漲點、反轉向下(高點)及低點等(注意，本文重點在於功能闡述，非投資概念)，說明如下:
SELECT STK_NO
, MNO
, TO_CHAR(DATE_ST, 'YYYY-MM-DD') DATE_ST
,
TO_CHAR(DATE_TOP, 'YYYY-MM-DD') DATE_TOP
,
TO_CHAR(EDATE_ED, 'YYYY-MM-DD') EDATE_ED
, PRI_ST
, PRI_TOP
, PRI_ED
FROM STK
MATCH_RECOGNIZE
(
PARTITION BY STK_NO
ORDER BY Date_Cls
MEASURES MATCH_NUMBER() AS MNO
,
STRT.Date_Cls AS DATE_ST
, LAST(UP.Date_Cls) AS DATE_TOP
, LAST(DOWN.Date_Cls) AS EDATE_ED
,
STRT.Cls_Pri AS PRI_ST
, LAST(UP.Cls_Pri) AS PRI_TOP
, LAST(DOWN.Cls_Pri)
AS PRI_ED
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ FLAT*
DOWN+) --測試1
--PATTERN (STRT UP{2,} FLAT* DOWN{2,}) --測試2
DEFINE
UP AS UP.Cls_Pri > PREV(UP.Cls_Pri)
, FLAT AS FLAT.Cls_Pri =
PREV(FLAT.Cls_Pri)
, DOWN AS DOWN.Cls_Pri <
PREV(DOWN.Cls_Pri)
) MR
ORDER BY MR.STK_NO
, MR.DATE_ST
前述SQL重點在於如何利用MATCH_RECOGNIZE子句辨識特定模式(上漲-持平-下跌)，找出股票反轉點向下日期及價格。針對MATCH_RECOGNIZE進行分析說明如下:
MATCH_RECOGNIZE子句
#
|
子句
|
用途/說明
|
1
|
PARTITION BY STK_NO
ORDER BY Date_Cls
|
對MATCH_RECOGNIZE所用資料進行邏輯分群及排序。
以股票代碼分群，以收盤價日期進行排序。
|
2
|
MEASURES
|
度量定義，於SQL其他查詢部分所引用。
- LAST()函數:取得符合匹配模式中最後收盤日的資料。
- MATCH_NUMBER():取得符合匹配模式流水編號。
|
3
|
[ONE| ALL] ROW PER MATCH
--測試1:
ONE ROW PER MATCH
--測試2:
ALL ROWS PER MATCH
WITH UNMATCHED ROWS
|
ONE: 類以群組概念，滿足模式識別者
ALL: 顯示所有可呈現資料
WITH UNMATCHED ROWS: 顯示未匹配資料列(Row)
|
4
|
AFTER MATCH SKIP TO LAST DOWN
|
當符合模式匹配後，定義重新搜索另一模式匹配。以此為例將於DOWN模數變數的最後一筆重新展開搜尋，如2019/11/11為模式匹配群組1最後一筆資料(下跌反轉)，也將作為匹配群組2之起始點。
|
5
|
PATTERN
--測試1
(STRT UP+ FLAT* DOWN+)
--測試2
(STRT UP{2,} FLAT* DOWN{2,})
|
定義資料列(Row)之匹配模式，規則運算式定義在括弧中，並佐以POSIX語法定義出現次數態樣。
- STRT：由於STRT未指定條件，任何資料列均可被映射，可作為測試匹配之起點。
- UP+：符合UP(上漲)模式變數，一次以上。
- FLAT*：符合FLAT(平盤)模式變數，0次或多次。
- DOWN+：符合DOWN(下跌)模式變數，一次以上
註: {2,}為符合2次(含)以上。
|
6
|
DEFINE
UP AS UP.Cls_Pri>PREV(UP.Cls_Pri)
|
定義模式變數及其邏輯運算式(必要子句)。資料列被映射至特定模式變數所須滿足的邏輯運算式。
以UP變數(上漲)為例定義為其價格需大於前期價格，透過PREV()函數以取得前期資料。
|
測試2: 連續上漲2天UP{2,}及連續下跌二天DOWN{2,}模式
上圖中，(1)、(3)兩群才符合連續上漲二天及連續下跌二天之需求，也可用ONE
ROW PER MATCH產生出彙總表亦可得知結果。
SDATE
|
PDATE
|
EDATE
|
PRI_ST
|
PRI_TOP
|
PRI_ED
|
MNO
|
CFR
|
2019/11/01
|
2019/11/06
|
2019/11/11
|
299
|
311
|
301
|
1
|
DOWN
|
2019/11/14
|
2019/11/19
|
2019/11/25
|
303.5
|
315
|
307
|
2
|
DOWN
|
再者利用ALL ROWS PER MATCH WITH UNMATCHED ROWS模式列出原始資料，由下表也可得如何產生出彙總資料。
DATE_CLS
|
CLS_PRI
|
MNO
|
CFR
|
SDATE
|
PDATE
|
EDATE
|
PRI_ST
|
PRI_TOP
|
PRI_ED
|
2019/11/01
|
299.00
|
1
|
STRT
|
11/01
|
299
|
||||
2019/11/04
|
307.00
|
1
|
UP
|
11/01
|
11/04
|
299
|
307
|
||
2019/11/05
|
310.50
|
1
|
UP
|
11/01
|
11/05
|
299
|
310.5
|
||
2019/11/06
|
311.00
|
1
|
UP
|
11/01
|
11/06
|
299
|
311
|
||
2019/11/07
|
309.00
|
1
|
DOWN
|
11/01
|
11/06
|
11/07
|
299
|
311
|
309
|
2019/11/08
|
305.50
|
1
|
DOWN
|
11/01
|
11/06
|
11/08
|
299
|
311
|
305.5
|
2019/11/11
|
301.00
|
1
|
DOWN
|
11/01
|
11/06
|
11/11
|
299
|
311
|
301
|
2019/11/12
|
305.00
|
||||||||
2019/11/13
|
304.00
|
||||||||
2019/11/14
|
303.50
|
2
|
STRT
|
11/14
|
303.5
|
||||
2019/11/15
|
307.00
|
2
|
UP
|
11/14
|
11/15
|
303.5
|
307
|
||
2019/11/18
|
311.00
|
2
|
UP
|
11/14
|
11/18
|
303.5
|
311
|
||
2019/11/19
|
315.00
|
2
|
UP
|
11/14
|
11/19
|
303.5
|
315
|
||
2019/11/20
|
313.50
|
2
|
DOWN
|
11/14
|
11/19
|
11/20
|
303.5
|
315
|
313.5
|
2019/11/21
|
311.00
|
2
|
DOWN
|
11/14
|
11/19
|
11/21
|
303.5
|
315
|
311
|
2019/11/22
|
309.00
|
2
|
DOWN
|
11/14
|
11/19
|
11/22
|
303.5
|
315
|
309
|
2019/11/25
|
307.00
|
2
|
DOWN
|
11/14
|
11/19
|
11/25
|
303.5
|
315
|
307
|
2019/11/26
|
307.00
|
||||||||
2019/11/27
|
311.00
|
||||||||
2019/11/28
|
309.50
|
||||||||
2019/11/29
|
305.00
|
MATCH_RECOGNIZE簡便且彈性針對一系列的資料進行分析，查看是否呈現特定模式，如SPC(Statistical Process Control統計製程分析上應用，找出機台產出不良品問題是否呈現連續五點/七點等特定模式(Pattern)，諸如此類的分析資料樣貌找出可能問題之應用，此指令提供更為簡便之方法，解決過去難題。
參考文件:
2. https://www.oracle.com/technetwork/database/bi-datawarehousing/mr-deep-dive-3769287.pdf
沒有留言:
張貼留言