CODEWITHSUNDEEP
mysqlrowwindow-functionsover-clause
oscarmnz8
oscarmnz8

Reputation: 5

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

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.

Upvotes: 0

Views: 637

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31832

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
)

Upvotes: 0

Related Questions