问题描述
亲爱的汤姆先生，
感谢您为支持我们的请求提供的所有帮助和时间。我对匹配识别有一些问题
Oracle版本-12.1.0.2.0
操作系统-REDHAT Linux
随着日期的分散，预计会有不止一行。
祝你有美好的一天，
感谢和问候
巴斯卡兰·维斯瓦纳坦
感谢您为支持我们的请求提供的所有帮助和时间。我对匹配识别有一些问题
Oracle版本-12.1.0.2.0
操作系统-REDHAT Linux
CREATE TABLE test_match_recognize(employment_id NUMBER (10, 0) NOT NULL,test_id NUMBER(10), as_of_date DATE NOT NULL
);
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/1/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/11/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/11/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/12/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/21/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/3/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/6/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/3/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/5/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/7/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/10/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/10/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/14/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/16/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/29/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/9/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/11/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/15/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/8/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/31/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/12/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/19/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/24/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/26/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/5/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/6/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/8/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/10/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/8/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/9/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/6/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/13/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/18/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/27/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/4/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/2/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('3/7/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/22/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/23/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/25/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/1/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/2/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/7/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('1/30/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/4/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('2/12/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/17/2017','MM/DD/RRRR'));
INSERT INTO test_match_recognize (EMPLOYMENT_ID,TEST_ID,AS_OF_DATE)
VALUES(1,10,TO_DATE('4/20/2017','MM/DD/RRRR'));
commit;
The following sql is not returning the results in multiple rows as the dates are changing. Please help me
SELECT employment_id,
test_id,
MIN (d_from) start_date,
MAX (d_to) end_date,
ROUND (
REPLACE (
( TO_DATE (TO_CHAR (MAX (d_to), 'YYYYMMDD'), 'YYYYMMDD')
- TO_DATE (TO_CHAR (MIN (d_from), 'YYYYMMDD'), 'YYYYMMDD'))
/ 7,
'-',
''))
no_of_weeks
FROM test_match_recognize a MATCH_RECOGNIZE (
PARTITION BY employment_id,
as_of_date,
test_id
ORDER BY as_of_date
MEASURES FIRST (as_of_date) d_from,
LAST (as_of_date) d_to
--ALL ROWS PER MATCH
ONE ROW PER MATCH
PATTERN (a b *)
DEFINE b AS (a.test_id = prev (a.test_id)))
WHERE employment_id = 1 AND test_id = 10
GROUP BY employment_id, test_id
EMPLOYMENT_ID TEST_ID START_DATE END_DATE NO_OF_WEEKS
------------- ------- ---------- --------- -----------
1 10 1/1/2017 4/29/2017 17复制随着日期的分散，预计会有不止一行。
祝你有美好的一天，
感谢和问候
巴斯卡兰·维斯瓦纳坦
专家解答
匹配 _ 识别is返回许多行:
但你已经按employment_id，test_id分组。并且在输出中只有一个值。因此，一排!
你想在这里做什么？
危险地猜测，您想将具有连续日期的行分组在一起。
这意味着对你的match_regnize子句进行一些严重的返工。
分区子句是:
因此，您将为每个as_of_date一行。这是你数据集中的每一行!
沟as_of_date来解决这个问题:
但是还有更多问题。模式变量b定义为:
Test_id在分区by所以组中的所有行都将具有相同的test_id。这意味着这将把行分成两组。一个用于A (这是第一个，总是真实的行)。然后是B。
这是因为prev test_id总是等于电流。除了组中的第一排。它有一个空prev (test_id)。因为没有!
我怀疑你正在寻找的是当前日期 = 前一个日期1的所有行。例如:
把它们放在一起，你会得到:
注意: 没有团体!
SELECT *
FROM test_match_recognize a match_recognize (
PARTITION BY employment_id,
as_of_date,
test_id
ORDER BY as_of_date
MEASURES FIRST (as_of_date) d_from,
LAST (as_of_date) d_to
--ALL ROWS PER MATCH
ONE ROW PER MATCH
PATTERN (a b*)
DEFINE b AS (a.test_id = prev (a.test_id)))
WHERE employment_id = 1 AND test_id = 10;
EMPLOYMENT_ID AS_OF_DATE TEST_ID D_FROM D_TO
1 01-JAN-2017 00:00:00 10 01-JAN-2017 00:00:00 01-JAN-2017 00:00:00
1 02-JAN-2017 00:00:00 10 02-JAN-2017 00:00:00 02-JAN-2017 00:00:00
1 03-JAN-2017 00:00:00 10 03-JAN-2017 00:00:00 03-JAN-2017 00:00:00
1 04-JAN-2017 00:00:00 10 04-JAN-2017 00:00:00 04-JAN-2017 00:00:00
1 05-JAN-2017 00:00:00 10 05-JAN-2017 00:00:00 05-JAN-2017 00:00:00
1 06-JAN-2017 00:00:00 10 06-JAN-2017 00:00:00 06-JAN-2017 00:00:00
1 07-JAN-2017 00:00:00 10 07-JAN-2017 00:00:00 07-JAN-2017 00:00:00
1 08-JAN-2017 00:00:00 10 08-JAN-2017 00:00:00 08-JAN-2017 00:00:00
1 30-JAN-2017 00:00:00 10 30-JAN-2017 00:00:00 30-JAN-2017 00:00:00
1 31-JAN-2017 00:00:00 10 31-JAN-2017 00:00:00 31-JAN-2017 00:00:00
1 01-FEB-2017 00:00:00 10 01-FEB-2017 00:00:00 01-FEB-2017 00:00:00
1 02-FEB-2017 00:00:00 10 02-FEB-2017 00:00:00 02-FEB-2017 00:00:00
1 03-FEB-2017 00:00:00 10 03-FEB-2017 00:00:00 03-FEB-2017 00:00:00
1 04-FEB-2017 00:00:00 10 04-FEB-2017 00:00:00 04-FEB-2017 00:00:00
1 05-FEB-2017 00:00:00 10 05-FEB-2017 00:00:00 05-FEB-2017 00:00:00
1 06-FEB-2017 00:00:00 10 06-FEB-2017 00:00:00 06-FEB-2017 00:00:00
1 07-FEB-2017 00:00:00 10 07-FEB-2017 00:00:00 07-FEB-2017 00:00:00
1 08-FEB-2017 00:00:00 10 08-FEB-2017 00:00:00 08-FEB-2017 00:00:00
1 09-FEB-2017 00:00:00 10 09-FEB-2017 00:00:00 09-FEB-2017 00:00:00
1 10-FEB-2017 00:00:00 10 10-FEB-2017 00:00:00 10-FEB-2017 00:00:00
1 11-FEB-2017 00:00:00 10 11-FEB-2017 00:00:00 11-FEB-2017 00:00:00
1 12-FEB-2017 00:00:00 10 12-FEB-2017 00:00:00 12-FEB-2017 00:00:00
1 06-MAR-2017 00:00:00 10 06-MAR-2017 00:00:00 06-MAR-2017 00:00:00
1 07-MAR-2017 00:00:00 10 07-MAR-2017 00:00:00 07-MAR-2017 00:00:00
1 08-MAR-2017 00:00:00 10 08-MAR-2017 00:00:00 08-MAR-2017 00:00:00
1 09-MAR-2017 00:00:00 10 09-MAR-2017 00:00:00 09-MAR-2017 00:00:00
1 10-MAR-2017 00:00:00 10 10-MAR-2017 00:00:00 10-MAR-2017 00:00:00
1 11-MAR-2017 00:00:00 10 11-MAR-2017 00:00:00 11-MAR-2017 00:00:00
1 12-MAR-2017 00:00:00 10 12-MAR-2017 00:00:00 12-MAR-2017 00:00:00
1 10-APR-2017 00:00:00 10 10-APR-2017 00:00:00 10-APR-2017 00:00:00
1 11-APR-2017 00:00:00 10 11-APR-2017 00:00:00 11-APR-2017 00:00:00
1 12-APR-2017 00:00:00 10 12-APR-2017 00:00:00 12-APR-2017 00:00:00
1 13-APR-2017 00:00:00 10 13-APR-2017 00:00:00 13-APR-2017 00:00:00
1 14-APR-2017 00:00:00 10 14-APR-2017 00:00:00 14-APR-2017 00:00:00
1 15-APR-2017 00:00:00 10 15-APR-2017 00:00:00 15-APR-2017 00:00:00
1 16-APR-2017 00:00:00 10 16-APR-2017 00:00:00 16-APR-2017 00:00:00
1 17-APR-2017 00:00:00 10 17-APR-2017 00:00:00 17-APR-2017 00:00:00
1 18-APR-2017 00:00:00 10 18-APR-2017 00:00:00 18-APR-2017 00:00:00
1 19-APR-2017 00:00:00 10 19-APR-2017 00:00:00 19-APR-2017 00:00:00
1 20-APR-2017 00:00:00 10 20-APR-2017 00:00:00 20-APR-2017 00:00:00
1 21-APR-2017 00:00:00 10 21-APR-2017 00:00:00 21-APR-2017 00:00:00
1 22-APR-2017 00:00:00 10 22-APR-2017 00:00:00 22-APR-2017 00:00:00
1 23-APR-2017 00:00:00 10 23-APR-2017 00:00:00 23-APR-2017 00:00:00
1 24-APR-2017 00:00:00 10 24-APR-2017 00:00:00 24-APR-2017 00:00:00
1 25-APR-2017 00:00:00 10 25-APR-2017 00:00:00 25-APR-2017 00:00:00
1 26-APR-2017 00:00:00 10 26-APR-2017 00:00:00 26-APR-2017 00:00:00
1 27-APR-2017 00:00:00 10 27-APR-2017 00:00:00 27-APR-2017 00:00:00
1 29-APR-2017 00:00:00 10 29-APR-2017 00:00:00 29-APR-2017 00:00:00 复制但你已经按employment_id，test_id分组。并且在输出中只有一个值。因此，一排!
你想在这里做什么？
危险地猜测，您想将具有连续日期的行分组在一起。
这意味着对你的match_regnize子句进行一些严重的返工。
分区子句是:
PARTITION BY employment_id, as_of_date, test_id复制
因此，您将为每个as_of_date一行。这是你数据集中的每一行!
沟as_of_date来解决这个问题:
PARTITION BY employment_id, test_id复制
但是还有更多问题。模式变量b定义为:
a.test_id = prev (a.test_id)复制
Test_id在分区by所以组中的所有行都将具有相同的test_id。这意味着这将把行分成两组。一个用于A (这是第一个，总是真实的行)。然后是B。
这是因为prev test_id总是等于电流。除了组中的第一排。它有一个空prev (test_id)。因为没有!
我怀疑你正在寻找的是当前日期 = 前一个日期1的所有行。例如:
as_of_date = prev (as_of_date) + 1)复制
把它们放在一起，你会得到:
SELECT *
FROM test_match_recognize a
match_recognize (
PARTITION BY employment_id, test_id
ORDER BY as_of_date
MEASURES FIRST (as_of_date) d_from,
LAST (as_of_date) d_to,
match_number() grp
ONE ROW PER MATCH
PATTERN (a b*)
DEFINE b AS (
as_of_date = prev (as_of_date) + 1
)
)
WHERE employment_id = 1 AND test_id = 10;
EMPLOYMENT_ID TEST_ID D_FROM D_TO GRP
1 10 01-JAN-2017 00:00:00 08-JAN-2017 00:00:00 1
1 10 30-JAN-2017 00:00:00 12-FEB-2017 00:00:00 2
1 10 06-MAR-2017 00:00:00 12-MAR-2017 00:00:00 3
1 10 10-APR-2017 00:00:00 27-APR-2017 00:00:00 4
1 10 29-APR-2017 00:00:00 29-APR-2017 00:00:00 5复制注意: 没有团体!
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
623次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
610次阅读
2025-04-15 17:24:06
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
508次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
495次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
471次阅读
2025-04-22 00:20:37
Oracle 19c RAC更换IP实战，运维必看！
szrsu
448次阅读
2025-04-08 23:57:08
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
442次阅读
2025-04-22 00:13:51
一页概览：Oracle GoldenGate
甲骨文云技术
440次阅读
2025-04-30 12:17:56
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
438次阅读
2025-04-17 17:02:24
火焰图--分析复杂SQL执行计划的利器
听见风的声音
390次阅读
2025-04-17 09:30:30
