Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Baskaran.

Asked: April 12, 2018 - 4:31 pm UTC

Last updated: April 17, 2018 - 1:53 am UTC

Version: BANNER ---------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

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


and Chris said...

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!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Baskaran Viswanathan, April 16, 2018 - 7:40 pm UTC

Dear Chris,

What an analysis and detailed advice on getting the code gives correct results. It is perfectly working. God bless you and your family. It saved my time and gave me lot of insight about MATCH_RECOGNIZE.

Have a great day,

Thanks and regards
Baskaran Viswanathan

Connor McDonald
April 17, 2018 - 1:53 am UTC

glad we could help

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.