regex working in MATCH_RECOGNIZE function-CodePudding

Have a below data set

loginName   emp_name    dept_id     dept_name                   startDate   endDate      
KK          KKLeo       5652        POLO Business – Tech        18-09-17    19-09-17
KK          KKLeo       5652        POLO Business – Tech        19-09-17    28-09-17
KK          KKLeo       5652        POLO Business – Tech        28-09-17    09-11-17
KK          KKLeo       5647        POLO Business               09-11-17    15-03-19
KK          KKLeo       5647        POLO Business               15-03-19    16-04-19
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   16-04-19    03-05-19
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   03-05-19    11-10-19
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   11-10-19    22-07-20
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   22-07-20    26-08-20
KK          KKLeo       5652        POLO Business – Tech        26-08-20    17-02-21
KK          KKLeo       5652        POLO Business – Tech        17-02-21    19-02-21
KK          KKLeo       5652        POLO Business – Tech        19-02-21    null

when I run this query

SELECT *
FROM   empdepthist
MATCH_RECOGNIZE (
  PARTITION BY login_name
  ORDER BY startdate
  MEASURES
    (emp_name) AS emp_name,
    (dept_id) AS dept_id,
    (dept_name) AS dept_name,
    (startDate) AS startdate,
    (endDate) AS enddate 
  PATTERN (same_dept)
  DEFINE same_dept AS FIRST(dept_id) = dept_id
)

In output, I get all records, but when I use same_dept instead same_dept, it give me only 4 records in output

KK          KKLeo       5652        POLO Business – Tech        28-09-17    09-11-17
KK          KKLeo       5647        POLO Business               15-03-19    16-04-19
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   22-07-20    26-08-20
KK          KKLeo       5652        POLO Business – Tech        19-02-21    null

May Know please how this is behaving like this ?

CodePudding user response：

The default behaviour is ONE ROW PER MATCH:

SELECT *
FROM   empdepthist
MATCH_RECOGNIZE (
  PARTITION BY login_name
  ORDER BY startdate
  MEASURES
    (emp_name) AS emp_name,
    (dept_id) AS dept_id,
    (dept_name) AS dept_name,
    (startDate) AS startdate,
    (endDate) AS enddate 
  ONE ROW PER MATCH        -- default behaviour
  PATTERN (same_dept)
  DEFINE same_dept AS FIRST(dept_id) = dept_id
)

Your pattern same_dept will only match a single row. When you change the pattern to same_dept it can match multiple rows; however, you have not told it that you want ALL ROWS PER MATCH so it is only giving you the default ONE ROW PER MATCH. If you do not want the default then you need to state that in the query:

SELECT *
FROM   empdepthist
MATCH_RECOGNIZE (
  PARTITION BY login_name
  ORDER BY startdate
  ALL ROWS PER MATCH        -- not the default behaviour
  PATTERN (same_dept )
  DEFINE same_dept AS FIRST(dept_id) = dept_id
)

db<>fiddle here