MySQL Window Function, check 2 rows behind in a LAG function

Question

I'm trying to figure out how the ROWS clause works in the OVER function. I'm using this query to test the logic, the CASE WHEN section is part of a larger query I'm working on, but for now I want to focus on this small section to understand. I'm working with healthcare data, and basically want to check there is a certain type of visit in the past 2 rows from the current row.

Here is the query:

SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,

CASE WHEN 

LAG(pat_status) OVER(PARTITION BY mr_no ORDER BY admit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )  
LIKE '%IC%'

 THEN 'Y' END AS pat_status_check

FROM chmadmtr
WHERE mr_no = '508249'

and outcome:

visit_no	mr_no	admit_date	dischg_date	pat_status	pat_status_check
7047395	508249	2019-08-06	2019-08-10	AIM	(NULL)
7047983	508249	2019-08-12	2019-08-19	AIC	(NULL)
7049597	508249	2019-08-27	2019-08-29	AIC	(Y)
7052596	508249	2019-08-27	2019-10-01	AIM	(Y)
7052823	508249	2019-10-01	2019-10-01	AOE	(NULL)
7054072	508249	2020-10-16	2019-10-22	AIC	(NULL)
7055128	508249	2019-10-29	2019-11-01	AIC	(Y)
7065066	508249	2020-02-28	2019-03-05	AIC	(Y)
7066857	508249	2020-03-21	2020-03-23	AIM	(Y)
7066961	508249	2020-03-23	2020-03-25	AIY	(NULL)

The last row is where my issue is, from my understanding this query should list the visit ID partitioned by medical record number (mr_no), order them by admit_date and check two rows behind the current row to see if any of the visit types (pat_status) contains "IC", so the pat_status_check should be "Y" for the last row. But it seems like no matter what number I put in the "n PRECEDING", it only checks 1 previous row to see if it contains "IC". Any help or insight is appreciated.

score 0 · Answer 1 · answered Oct 19 '22 at 23:23

First change your frame to ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING, since you don't want to include the current row.

Now you can use NTH_VALUE() for the 1st and 2nd row in the frame:

SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,
  CASE WHEN
    NTH_VALUE(pat_status, 1) OVER w LIKE '%IC%'
    OR 
    NTH_VALUE(pat_status, 2) OVER w LIKE '%IC%'
  THEN 'Y' END AS pat_status_check
FROM chmadmtr WHERE mr_no = '508249'
WINDOW w AS (
  PARTITION BY mr_no
  ORDER BY admit_date 
  ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
)

Or use JSON_ARRAYARG to aggregate the frame values into a single (JSON) string:

SELECT visit_no, mr_no, admit_date, dischg_date, pat_status,
  CASE WHEN
    JSON_ARRAYAGG(pat_status) OVER w LIKE '%IC%'
  THEN 'Y' END AS pat_status_check
FROM chmadmtr WHERE mr_no = '508249'
WINDOW w AS (
  PARTITION BY mr_no
  ORDER BY admit_date 
  ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
)

MySQL Window Function, check 2 rows behind in a LAG function

1 Answers1