英文:
Need help using match_recognize in Oracle 21c
问题
希望我能在这里找到对这段代码出现问题的理解。
我有一些设备会将事件数据作为事件标签发送到数据库，每个事件都带有设备原始时间戳。
一个设备的外围设备出现了问题，因此受影响的设备每次都发送相关的事件标签，一共16个，按时间顺序排列。在表格上手动搜索数据时，确实包含这种模式，来自不同的设备ID。
目的是量化这个问题（模式）发生的次数，以及发生在哪些设备上。
在研究可以帮助我的功能时，我遇到了 Oracle MATCH_RECOGNIZE 作为可能的工具来解决问题。如果我没记错的话，这个函数使用或模拟某种正则表达式（我不是正则表达式专家）。我还没有能调整代码来显示它是否可以提取模式。我在 PATTERN 子句中使用了 '+'（一个或多个 - 加号量词）或 ''（零个或多个 - 星号量词）。如果我使用 '+'，结果为空。如果我使用 ''，我得到了数据，但也出现了不属于模式的其他标签。
我想让代码获取模式，而没有其他事件标签。
下面我分享代码。
--删除表TESTPATTERN
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH:MM:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN
MATCH_RECOGNIZE (
PARTITION BY EqpID
ORDER BY EventDateTime
MEASURES
FIRST(EventDateTime) AS start_date,
LAST(EventDateTime) AS end_date,
FIRST(EventTag) AS first_tag,
LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+ EventDateTime1+)
DEFINE
EventTag1 AS EventTag = 'THOR',
EventTag2 AS EventTag = 'MJOLNIR',
EventTag3 AS EventTag = 'LOKI',
EventTag4 AS EventTag = 'HULK',
EventTag5 AS EventTag = 'HAWKEYE',
EventTag6 AS EventTag = 'CAPNAMER',
EventTag7 AS EventTag = 'FURY',
EventTag8 AS EventTag = 'STARK',
EventTag9 AS EventTag = 'FURY',
EventTag10 AS EventTag = 'STARK',
EventTag11 AS EventTag = 'THOR',
EventTag12 AS EventTag = 'MJOLNIR',
EventTag13 AS EventTag = 'LOKI',
EventTag14 AS EventTag = 'HULK',
EventTag15 AS EventTag = 'HAWKEYE',
EventTag16 AS EventTag = 'CAPNAMER',
EventDateTime1 as EventDateTime > prev(EventDateTime)
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
谢谢。
更新：我分享一些数据样本：
<日期时间戳>，<设备ID_X>，THOR
<日期时间戳>，<设备ID_X>，MJOLNIR
<日期时间戳>，<设备ID_X>，LOKI
<日期时间戳>，<设备ID_X>，HULK
<日期时间戳>，<设备ID_X>，HAWKEYE
<日期时间戳>，<设备ID_X>，CAPNAMER
<日期时间戳>，<设备ID_X>，FURY
<日期时间戳>，<设备ID_X>，STARK
<日期时间戳>，<设备ID_X>，FURY
<日期时间戳>，<设备ID_X>，STARK
<日期时间戳>，<设备ID_X>，THOR
<日期时间戳>，<设备ID_X>，MJOLNIR
<日期时间戳>，<设备ID_X>，LOKI
<日期时间戳>，<设备ID_X>，HULK
<日期时间戳>，<设备ID_X>，HAWKEYE
<日期时间戳>，<设备ID_X>，CAPNAMER
<日期时间戳>，<设备ID_Y>，THOR
<日期时间戳>，<设备ID_Y>，MJOLNIR
<日期时间戳>，<设备ID_Y>，LOKI
<日期时间戳>，<设备ID_Y>，HULK
<日期时间戳>，<设备ID_Y>，HAWKEYE
<日期时间戳>，<设备ID_Y>，CAPNAMER
<日期时间戳>，<设备ID_Y>，FURY
<日期时间戳>，<设备ID_Y>，STARK
<日期时间戳>，<设备ID_Y>，FURY
<日期时间戳>，<设备ID_Y>，STARK
<日期时间戳>，<设备ID_Y>，THOR
<日期时间戳>，<设备ID_Y>，MJOLNIR
<日期时间戳>，<设备ID_Y>，LOKI
<日期时间戳>，<设备ID_Y>，HULK
<日期时间戳>，<设备ID_Y>，HAWKEYE
<日期时间戳>，<设备ID_Y>，CAPNAMER
...
注：我没说清楚，这些标签也出现在设备的其他外围设备的其他问题中，换句话说，它们对于一个问题/外围设备并不是唯一的。
英文:
I truly hope I can find understaning here as to what is wrong with this code.
I have some equipment that send event data as event tags to a database; each timestamped with the time of the equipment of origin.
There is a problem with an equipment's peripheral, and as a consequence these affected equipment sent the relevant event tags, exactly 16 of them, everytime, chronologically ordered. Searching the data manually on the table, it does contain the pattern, coming from different equipment ID.
The purpose is to quantify how many times this problem (pattern) happpens, and on which equipment.
As I was researching what function could help me, I came across Oracle MATCH_RECOGNIZE as a possible tool to obtain a solution. If I am not mistaken, this function uses or emulates regex of some sort (I am not an regex expert). I haven't been able to fine tune the code to show me if it can pick the pattern. I was using inside the PATTERN clause either a '+' (one or more -- plus quantifier), or '*' (zero or more -- star quantifier). If I use +, the result is zilch. If I use * I get data, but there also appears other tags with do not belong to the pattern.
What I would like is to make the code obtain the pattern, with no other event tag.
Below I am sharing the code.
--DROP TABLE TESTPATTERN
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH:MM:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN
MATCH_RECOGNIZE (
PARTITION BY EqpID
ORDER BY EventDateTime
MEASURES
FIRST(EventDateTime) AS start_date,
LAST(EventDateTime) AS end_date,
FIRST(EventTag) AS first_tag,
LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+ EventDateTime1+)
DEFINE
EventTag1 AS EventTag = 'THOR',
EventTag2 AS EventTag = 'MJOLNIR',
EventTag3 AS EventTag = 'LOKI',
EventTag4 AS EventTag = 'HULK',
EventTag5 AS EventTag = 'HAWKEYE',
EventTag6 AS EventTag = 'CAPNAMER',
EventTag7 AS EventTag = 'FURY',
EventTag8 AS EventTag = 'STARK',
EventTag9 AS EventTag = 'FURY',
EventTag10 AS EventTag = 'STARK',
EventTag11 AS EventTag = 'THOR',
EventTag12 AS EventTag = 'MJOLNIR',
EventTag13 AS EventTag = 'LOKI',
EventTag14 AS EventTag = 'HULK',
EventTag15 AS EventTag = 'HAWKEYE',
EventTag16 AS EventTag = 'CAPNAMER',
EventDateTime1 as EventDateTime > prev(EventDateTime)
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
Thank you.
UPDATE: I am sharing a sample of the data:
EventDateTime,EqpID,EventTag
1/1/2023,93258,VGBBZX
1/1/2023,97606,IYEOCJ
1/1/2023,84895,BDXWTD
1/1/2023,34603,PYMVRP
1/1/2023,25339,NORDZO
1/1/2023,38960,LXBJTU
1/7/2023,92141,DOHTUC
1/8/2023,69069,HGDCKT
1/9/2023,86781,DBPWTV
1/10/2023,98633,JXBDSU
1/11/2023,56817,RTUJMS
1/12/2023,44871,OIQFTF
1/13/2023,14115,THOR
1/13/2023,14115,MJOLNIR
1/13/2023,14115,LOKI
1/13/2023,14115,HULK
1/13/2023,14115,HAWKEYE
1/13/2023,14115,CAPNAMER
1/13/2023,14115,FURY
1/13/2023,14115,STARK
1/13/2023,14115,FURY
1/13/2023,14115,STARK
1/13/2023,14115,THOR
1/13/2023,14115,MJOLNIR
1/13/2023,14115,LOKI
1/13/2023,14115,HULK
1/13/2023,14115,HAWKEYE
1/13/2023,14115,CAPNAMER
1/24/2023,36384,CPRBPF
1/24/2023,90629,RKORUN
1/24/2023,82093,NXKCFA
1/24/2023,27730,GPDMUK
2/2/2023,45098,TEHAZM
2/3/2023,55356,EFXAXF
2/4/2023,44523,EDBTGA
2/5/2023,41042,FXPUSS
2/6/2023,34602,AWAPLI
2/7/2023,78860,AMYGPI
2/8/2023,88861,FPLDGU
2/9/2023,13026,YDBZOD
2/10/2023,38968,XDBMYF
2/11/2023,30377,SVLHSA
2/12/2023,20511,IPKOTF
2/13/2023,69469,FDZEFQ
2/14/2023,79857,ZFPBLX
2/15/2023,94276,YGQYTD
2/16/2023,87478,EXINDO
2/17/2023,95831,JBYHTT
2/18/2023,46950,YYPHXY
2/19/2023,56393,CXBJTQ
2/20/2023,70004,JUEQSC
2/21/2023,96613,THOR
2/21/2023,96613,MJOLNIR
2/21/2023,96613,LOKI
2/21/2023,96613,HULK
2/21/2023,96613,HAWKEYE
2/21/2023,96613,CAPNAMER
2/21/2023,96613,FURY
2/21/2023,96613,STARK
2/21/2023,96613,FURY
2/21/2023,96613,STARK
2/21/2023,96613,THOR
2/21/2023,96613,MJOLNIR
2/21/2023,96613,LOKI
2/21/2023,96613,HULK
2/21/2023,96613,HAWKEYE
2/21/2023,96613,CAPNAMER
3/6/2023,26084,PWNIZZ
3/6/2023,19965,LVMVJH
3/11/2023,69352,WTJJDF
3/12/2023,53016,KACILZ
3/13/2023,33807,SVUNNW
3/14/2023,29156,OSNHOS
3/15/2023,90282,HYNIKV
3/16/2023,23421,HHAZLV
3/17/2023,22600,HTIURE
3/18/2023,46236,PFJLSA
3/19/2023,80520,WYCUNF
3/20/2023,96436,XRZIWB
3/21/2023,48785,RMVTFK
3/22/2023,91108,JQRHHE
3/23/2023,22307,DKMDXC
3/24/2023,24770,YXKMXE
3/25/2023,50685,BXCLEJ
3/26/2023,51084,AKSPTS
3/27/2023,59554,RWIPGQ
3/28/2023,90829,FZVAIE
3/29/2023,49152,QXTITE
3/30/2023,43857,WNPBOI
3/31/2023,81333,KCFBZP
4/1/2023,72786,VHWUEK
4/2/2023,36813,LSETZR
4/3/2023,26868,RFYOJR
4/4/2023,20333,VEOINT
4/5/2023,24696,EPVRVE
4/6/2023,67335,MTZBJG
4/7/2023,97784,WEXDAQ
4/8/2023,76681,OWHZBS
4/9/2023,10958,UECZBX
4/10/2023,75890,QGSMIT
4/11/2023,32046,QXWKYJ
4/12/2023,75267,URAEXB
4/13/2023,76813,ERGNKO
4/14/2023,63003,KHDDRT
4/15/2023,47214,NWNOOQ
4/16/2023,97560,HNIRAO
4/17/2023,96776,MFFYEA
4/18/2023,58917,FHVFHA
4/19/2023,45970,AZRTOP
4/20/2023,60381,MAKSVY
4/21/2023,31555,UEUAWN
4/22/2023,67409,EFKJCI
4/23/2023,95617,EHSETF
4/24/2023,41442,TOTYHJ
4/25/2023,42228,JWTZEA
4/26/2023,37667,WEGYCA
4/27/2023,51275,LQBNED
4/28/2023,16357,QSFTKT
4/29/2023,50233,VOHHXV
4/30/2023,41551,THOGRL
5/1/2023,13884,THOR
5/1/2023,13884,MJOLNIR
5/1/2023,13884,LOKI
5/1/2023,13884,HULK
5/1/2023,13884,HAWKEYE
5/1/2023,13884,CAPNAMER
5/1/2023,13884,FURY
5/1/2023,13884,STARK
5/1/2023,13884,FURY
5/1/2023,13884,STARK
5/1/2023,13884,THOR
5/1/2023,13884,MJOLNIR
5/1/2023,13884,LOKI
5/1/2023,13884,HULK
5/1/2023,13884,HAWKEYE
5/1/2023,13884,CAPNAMER
5/13/2023,53389,HHKFVV
5/13/2023,40702,IEKFMJ
5/19/2023,14518,FJRSTM
5/20/2023,64307,RJHQHU
5/21/2023,51366,HVIQWR
5/22/2023,86614,LJCONO
5/23/2023,44585,BGIVJO
5/24/2023,86491,MYBBXX
5/25/2023,61505,BZCVJJ
5/25/2023,21615,HXFVNN
5/25/2023,31148,BGPZRY
5/25/2023,15959,MLJPIC
5/25/2023,32447,HOLCSJ
5/25/2023,58595,WIDSVF
5/31/2023,19729,MMSEGW
6/1/2023,60302,AMTOLN
6/2/2023,57501,NTIEJN
6/3/2023,38026,FOGINM
6/4/2023,62404,RYLIWP
6/5/2023,10116,FDIEUK
6/6/2023,60250,JXBUWQ
6/7/2023,56934,CERWGP
6/8/2023,65327,ITUJRG
6/9/2023,24821,YDJLDL
6/10/2023,13959,ZJKUTJ
6/11/2023,81594,EOXQKE
6/12/2023,51408,UIJRNV
6/13/2023,58315,ATWTQD
6/14/2023,38403,YXYGVC
6/15/2023,21385,OCSYMZ
6/16/2023,90186,NSFPIM
6/17/2023,74306,LVDIDX
6/18/2023,34833,FAVCOV
6/19/2023,69123,EYNYSI
6/20/2023,54018,RZDKIQ
6/21/2023,71083,THOR
6/21/2023,71083,MJOLNIR
6/21/2023,71083,LOKI
6/21/2023,71083,HULK
6/21/2023,71083,HAWKEYE
6/21/2023,71083,CAPNAMER
6/21/2023,71083,FURY
6/21/2023,71083,STARK
6/21/2023,71083,FURY
6/21/2023,71083,STARK
6/21/2023,71083,THOR
6/21/2023,71083,MJOLNIR
6/21/2023,71083,LOKI
6/21/2023,71083,HULK
6/21/2023,71083,HAWKEYE
6/21/2023,71083,CAPNAMER
7/7/2023,15970,VCIZZD
7/8/2023,46295,DUWOAI
7/9/2023,42284,EROYFB
7/10/2023,26038,FJKWGG
7/11/2023,58263,DKEXQJ
7/12/2023,22648,WAHYGN
7/13/2023,76327,HTEEFH
7/14/2023,43199,UZTJAO
7/15/2023,41376,GPVJQM
7/16/2023,48875,RVDYCO
7/17/2023,31696,QXLOUW
7/18/2023,82655,SHCEPV
7/19/2023,21862,ETKIRV
7/20/2023,65806,QOSMWV
7/21/2023,47544,PUXYQK
7/22/2023,61099,ABOMQS
7/23/2023,41702,UMZECW
7/24/2023,48088,TZYAMT
7/25/2023,29845,LTVPVY
7/26/2023,45772,HGFXHX
7/27/2023,76154,PJRRYP
7/28/2023,53096,ZXQGER
7/29/2023,96343,AJBIHA
7/30/2023,61177,CLPPRT
7/31/2023,43500,ZZRYQN
8/1/2023,96134,KUBPUV
8/2/2023,83022,BZJNPK
8/3/2023,79227,TNTMKG
8/4/2023,72617,SGOFXV
8/5/2023,50638,CDFSLR
8/6/2023,35464,NKILHL
8/7/2023,24123,HYHJCD
8/8/2023,21229,LKAGFB
8/9/2023,58962,YYYQZU
8/10/2023,69372,ZROCUL
8/11/2023,50196,QWLURC
8/12/2023,19607,BMWMPU
8/13/2023,63682,XXSSGP
8/14/2023,17084,DZLIGS
8/15/2023,28881,RSENYR
8/16/2023,18611,JPTHKQ
8/17/2023,27402,RXQWOG
8/18/2023,35217,HANFEH
8/19/2023,33933,VQVLFA
8/20/2023,63436,KGVLPN
8/21/2023,86121,IWRBGI
8/22/2023,65971,XSSDJI
8/23/2023,69247,RLRIZC
8/24/2023,77961,FEOFUZ
8/25/2023,47074,AOOCLE
8/26/2023,79347,VCINIP
8/27/2023,24031,NYWQBU
8/28/2023,94265,CQPHKB
8/29/2023,14505,SCFQKY
8/30/2023,81924,CZDOBI
8/31/2023,75484,TQKYZI
9/1/2023,37607,ZOYADT
9/2/2023,30527,WWMHBD
9/3/2023,26774,QYAYWB
9/4/2023,48792,TNJCLB
9/5/2023,72891,EAHYPB
9/6/2023,49372,FFBRGE
9/7/2023,75255,BKYRQA
9/8/2023,17539,FUHMXX
9/9/2023,53570,CAOFII
9/10/2023,22257,THOR
9/10/2023,22257,MJOLNIR
9/10/2023,22257,LOKI
9/10/2023,22257,HULK
9/10/2023,22257,HAWKEYE
9/10/2023,22257,CAPNAMER
9/10/2023,22257,FURY
9/10/2023,22257,STARK
9/10/2023,22257,FURY
9/10/2023,22257,STARK
9/10/2023,22257,THOR
9/10/2023,22257,MJOLNIR
9/10/2023,22257,LOKI
9/10/2023,22257,HULK
9/10/2023,22257,HAWKEYE
9/10/2023,22257,CAPNAMER
9/26/2023,80826,FIDSRJ
9/27/2023,27732,IZCUFV
9/28/2023,12519,GMQAMR
9/29/2023,30560,GRCOSA
9/30/2023,71680,XUPPST
10/1/2023,78700,HFNKZO
10/2/2023,46046,CEKVIA
10/3/2023,83822,LVEWGO
10/4/2023,89002,WQDDDG
10/5/2023,68492,GGDDSI
10/6/2023,44314,IPSMYN
10/7/2023,17276,IHVLYK
10/8/2023,55134,MJARHY
10/9/2023,70363,VTKFKR
The output should be:
<date-time stamp of row>,<eqipmentID_X>,THOR
<date-time stamp of row>,<eqipmentID_X>,MJOLNIR
<date-time stamp of row>,<eqipmentID_X>,LOKI
<date-time stamp of row>,<eqipmentID_X>,HULK
<date-time stamp of row>,<eqipmentID_X>,HAWKEYE
<date-time stamp of row>,<eqipmentID_X>,CAPNAMER
<date-time stamp of row>,<eqipmentID_X>,FURY
<date-time stamp of row>,<eqipmentID_X>,STARK
<date-time stamp of row>,<eqipmentID_X>,FURY
<date-time stamp of row>,<eqipmentID_X>,STARK
<date-time stamp of row>,<eqipmentID_X>,THOR
<date-time stamp of row>,<eqipmentID_X>,MJOLNIR
<date-time stamp of row>,<eqipmentID_X>,LOKI
<date-time stamp of row>,<eqipmentID_X>,HULK
<date-time stamp of row>,<eqipmentID_X>,HAWKEYE
<date-time stamp of row>,<eqipmentID_X>,CAPNAMER
<date-time stamp of row>,<eqipmentID_Y>,THOR
<date-time stamp of row>,<eqipmentID_Y>,MJOLNIR
<date-time stamp of row>,<eqipmentID_Y>,LOKI
<date-time stamp of row>,<eqipmentID_Y>,HULK
<date-time stamp of row>,<eqipmentID_Y>,HAWKEYE
<date-time stamp of row>,<eqipmentID_Y>,CAPNAMER
<date-time stamp of row>,<eqipmentID_Y>,FURY
<date-time stamp of row>,<eqipmentID_Y>,STARK
<date-time stamp of row>,<eqipmentID_Y>,FURY
<date-time stamp of row>,<eqipmentID_Y>,STARK
<date-time stamp of row>,<eqipmentID_Y>,THOR
<date-time stamp of row>,<eqipmentID_Y>,MJOLNIR
<date-time stamp of row>,<eqipmentID_Y>,LOKI
<date-time stamp of row>,<eqipmentID_Y>,HULK
<date-time stamp of row>,<eqipmentID_Y>,HAWKEYE
<date-time stamp of row>,<eqipmentID_Y>,CAPNAMER
.
.
.
PD: I have failed to say that those tagnames also occur onother problems with other peripherals of the equipment, in other words, they are not unique to a problem/peripheral.
答案1
得分: 1
你有两个问题：
- 你的模式以
EventTag16+ EventDateTime1+结尾，它会匹配第16个与EventTag16定义匹配的项目，然后寻找第17个与EventDateTime1定义匹配的项目。你的数据中从未出现过第17个项目，因此MATCH_RECOGNIZE正确地指出没有与你的模式匹配的项目。 - 所有你的
EventDateTime值都具有完全相同的时间，并且将从数据库文件中以有效随机顺序读取，然后按查询排序；这不太可能与你将数据插入到数据库中的相同顺序。因此，你不太可能匹配相同的模式。
如果你的数据可以按正确顺序读取（通过具有递增的时间戳或包含一个顺序的另一行），那么如果你移除第17个项目，你的查询将起作用：
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH24:MI:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN
MATCH_RECOGNIZE (
PARTITION BY EqpID
ORDER BY EventDateTime
MEASURES
FIRST(EventDateTime) AS start_date,
LAST(EventDateTime) AS end_date,
FIRST(EventTag) AS first_tag,
LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+)
DEFINE
EventTag1 AS EventTag = 'THOR',
EventTag2 AS EventTag = 'MJOLNIR',
EventTag3 AS EventTag = 'LOKI',
EventTag4 AS EventTag = 'HULK',
EventTag5 AS EventTag = 'HAWKEYE',
EventTag6 AS EventTag = 'CAPNAMER',
EventTag7 AS EventTag = 'FURY',
EventTag8 AS EventTag = 'STARK',
EventTag9 AS EventTag = 'FURY',
EventTag10 AS EventTag = 'STARK',
EventTag11 AS EventTag = 'THOR',
EventTag12 AS EventTag = 'MJOLNIR',
EventTag13 AS EventTag = 'LOKI',
EventTag14 AS EventTag = 'HULK',
EventTag15 AS EventTag = 'HAWKEYE',
EventTag16 AS EventTag = 'CAPNAMER'
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
注意：你可能希望使用 HH24:MI:SS 格式化你的日期以获取时间组件（而不是 HH，它只会显示一个12小时制的时钟，这样你就无法区分上午和下午，以及 MM，它是月份，而不是分钟）。
对于样本数据：
CREATE TABLE testpattern ( EventDateTime,EqpID,EventTag) As
SELECT DATE '2023-01-10', 98633, 'JXBDSU' FROM DUAL UNION ALL
SELECT DATE '2023-01-11', 56817, 'RTUJMS' FROM DUAL UNION ALL
SELECT DATE '2023-01-12', 44871, 'OIQFTF' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '0' SECOND, 14115, 'THOR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '1' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '2' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '3' SECOND, 14115, 'HULK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '4' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '5' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '6' SECOND, 14115, 'FURY' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '7' SECOND, 14115, 'STARK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '8' SECOND, 14115, 'FURY' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '9' SECOND, 14115, 'STARK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '10' SECOND, 14115, 'THOR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '11' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '12' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '13' SECOND, 14115, 'HULK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '14' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '15' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 36384, 'CPRBPF' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 90629, 'RKORUN' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 82093, 'NXKCFA' FROM DUAL;
输出：
| EVENTDATETIME | EQPID | EVENTTAG |
|---|---|---|
| 2023-01-13 00:00:00 | 14115 | THOR |
| 2023-01-13 |
英文:
You have two problems:
- Your pattern ends with
EventTag16+ EventDateTime1+which matches the 16th term with theEventTag16definition and then looks for a 17th term matching theEventDateTime1definition. Your data never has a 17th term soMATCH_RECOGNIZEcorrectly states that there are no matches to your pattern. - All your
EventDateTimevalues have exactly the same time and will be read from the database file in an effectively random order and the ordered by the query; this is not likely to be the same order that you inserted the data into the database so you are unlikely to match the same pattern.
If you have the data so that it can be read in the correct order (either by having increasing timestamps or another row containing an order) then your query works if you remove that 17th term:
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH24:MI:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN
MATCH_RECOGNIZE (
PARTITION BY EqpID
ORDER BY EventDateTime
MEASURES
FIRST(EventDateTime) AS start_date,
LAST(EventDateTime) AS end_date,
FIRST(EventTag) AS first_tag,
LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+)
DEFINE
EventTag1 AS EventTag = 'THOR',
EventTag2 AS EventTag = 'MJOLNIR',
EventTag3 AS EventTag = 'LOKI',
EventTag4 AS EventTag = 'HULK',
EventTag5 AS EventTag = 'HAWKEYE',
EventTag6 AS EventTag = 'CAPNAMER',
EventTag7 AS EventTag = 'FURY',
EventTag8 AS EventTag = 'STARK',
EventTag9 AS EventTag = 'FURY',
EventTag10 AS EventTag = 'STARK',
EventTag11 AS EventTag = 'THOR',
EventTag12 AS EventTag = 'MJOLNIR',
EventTag13 AS EventTag = 'LOKI',
EventTag14 AS EventTag = 'HULK',
EventTag15 AS EventTag = 'HAWKEYE',
EventTag16 AS EventTag = 'CAPNAMER'
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
Note: you probably want to format your dates using HH24:MI:SS for the time component (rather than HH which will only show a 12-hour clock, and then you cannot differentiate between AM and PM, and MM which is months, not minutes).
Which for the sample data:
CREATE TABLE testpattern ( EventDateTime,EqpID,EventTag) As
SELECT DATE '2023-01-10', 98633, 'JXBDSU' FROM DUAL UNION ALL
SELECT DATE '2023-01-11', 56817, 'RTUJMS' FROM DUAL UNION ALL
SELECT DATE '2023-01-12', 44871, 'OIQFTF' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '0' SECOND, 14115, 'THOR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '1' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '2' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '3' SECOND, 14115, 'HULK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '4' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '5' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '6' SECOND, 14115, 'FURY' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '7' SECOND, 14115, 'STARK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '8' SECOND, 14115, 'FURY' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '9' SECOND, 14115, 'STARK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '10' SECOND, 14115, 'THOR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '11' SECOND, 14115, 'MJOLNIR' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '12' SECOND, 14115, 'LOKI' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '13' SECOND, 14115, 'HULK' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '14' SECOND, 14115, 'HAWKEYE' FROM DUAL UNION ALL
SELECT DATE '2023-01-13' + INTERVAL '15' SECOND, 14115, 'CAPNAMER' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 36384, 'CPRBPF' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 90629, 'RKORUN' FROM DUAL UNION ALL
SELECT DATE '2023-01-24', 82093, 'NXKCFA' FROM DUAL;
Outputs:
| EVENTDATETIME | EQPID | EVENTTAG |
|---|---|---|
| 2023-01-13 00:00:00 | 14115 | THOR |
| 2023-01-13 00:00:01 | 14115 | MJOLNIR |
| 2023-01-13 00:00:02 | 14115 | LOKI |
| 2023-01-13 00:00:03 | 14115 | HULK |
| 2023-01-13 00:00:04 | 14115 | HAWKEYE |
| 2023-01-13 00:00:05 | 14115 | CAPNAMER |
| 2023-01-13 00:00:06 | 14115 | FURY |
| 2023-01-13 00:00:07 | 14115 | STARK |
| 2023-01-13 00:00:08 | 14115 | FURY |
| 2023-01-13 00:00:09 | 14115 | STARK |
| 2023-01-13 00:00:10 | 14115 | THOR |
| 2023-01-13 00:00:11 | 14115 | MJOLNIR |
| 2023-01-13 00:00:12 | 14115 | LOKI |
| 2023-01-13 00:00:13 | 14115 | HULK |
| 2023-01-13 00:00:14 | 14115 | HAWKEYE |
| 2023-01-13 00:00:15 | 14115 | CAPNAMER |
答案2
得分: 0
当您始终具有完全相同的模式并且只想进行计数时，请简单地执行
SELECT
设备ID,
COUNT(*) / 2 作为 事件_计数
WHERE 事件标签 = 'CAPNAMER'
并且（开始日期不为空或结束日期不为空或第一个标签不为空或最后一个标签不为空）
并且（开始日期不为空或结束日期不为空或第一个标签不为空或最后一个标签不为空）
GROUP BY 设备ID
事件标签 = 'CAPNAMER' 出现两次，这就是 COUNT(*) / 2 的原因。 根据您的问题，不需要其他任何内容。
英文:
When you always have exactly this pattern and you just like to count, then simply do
SELECT
EqpID,
COUNT(*) / 2 AS Event_COUNT
WHERE EventTag = 'CAPNAMER'
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
GROUP BY EqpID
EventTag = 'CAPNAMER' appears twice, that's the reason for COUNT(*) / 2. Anything else is not needed according to your question.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论