Dear Mr. Tom,
Thank you for all your help and time in supporting our requests. I have some issues with MATCH_RECOGNIZE
Oracle Version - 12.1.0.2.0
OS - 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
Expecting more than one row as the dates are spread with gaps in between.
Have a great day,
Thanks and regards
Baskaran Viswanathan
Match_recognize
is returning many rows:
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
But you've grouped by employment_id, test_id. And only have one value for each in the output. Hence, one row!
So what are you trying to do here?
Hazarding a guess, you want to group the rows with consecutive dates together.
Which means some serious rework for your match_recognize clause.
The partition clause is:
PARTITION BY employment_id, as_of_date, test_id
So you'll have one row for every as_of_date. Which is every row in your data set!
Ditch as_of_date to fix this:
PARTITION BY employment_id, test_id
But there's more problems. The pattern variable b is defined as:
a.test_id = prev (a.test_id)
Test_id is in the partition by. So all the rows in the group will have the same test_id. Meaning this will split the rows into two groups. One for A (which is the first, always true row). Then another for B.
This is because the prev test_id always equals the current. Except for the first row in the group. Which has a null prev (test_id). Because there isn't one!
I suspect that what you're looking for is all those rows where the current date = previous date + 1. e.g.:
as_of_date = prev (as_of_date) + 1)
Put it all together and you get:
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
Note: no group by!