Difference in returned record when running the same query to obtain aki_stage_creat in MySQL and BigQuery (edit to previously closed issue)

[rielmvp]

Prerequisites

• [ X] Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.mit.edu/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

I'm working on a project to apply machine learning techniques to predict AKI using the KDIGO serum creatinine and urine output criteria. I noticed the MIMIC code repository has SQL scripts ready to create derived tables and query results such as 'kdigo_creatinine'. I'm working on two SQL environments now, BigQuery on the cloud and MySQL on a server. I wanted to validate if the results I got in MySQL (since there are no MySQL scripts for the MIMIC code concepts) are correct, so I ran a query to get KDIGO_creatinine as follows:

SELECT aki_stage_creat, count(aki_stage_creat) FROM ( with cr as ( select ie.icustay_id , ie.intime, ie.outtime , le.valuenum as creat , le.charttime , ie.DBSOURCE from ICUSTAYS ie left join LABEVENTS le on ie.subject_id = le.subject_id and le.ITEMID = 50912 #Creatinine (not Creatinine, Serum) and le.VALUENUM is not null and le.CHARTTIME between DATE_SUB(ie.intime, interval '7' day) and DATE_ADD(ie.intime, interval '7' day) ) -- add in the lowest value in the previous 48 hours/7 days SELECT cr.icustay_id , cr.charttime , cr.creat , MIN(cr48.creat) AS creat_low_past_48hr , MIN(cr7.creat) AS creat_low_past_7day -- added case to count the stage of AKI by creatinine , CASE -- 3x baseline WHEN cr.creat >= (MIN(cr7.creat)3.0) then 3 -- OR cr >= 4.0 with associated increase WHEN cr.creat >= 4 -- For patients reaching Stage 3 by SCr >4.0 mg/dl -- require that the patient first achieve ... acute increase >= 0.3 within 48 hr -- or an increase of >= 1.5 times baseline and (MIN(cr48.creat) <= 3.7 OR cr.creat >= (1.5MIN(cr7.creat))) then 3 -- TODO: initiation of RRT when cr.creat >= (MIN(cr7.creat)*2.0) then 2 when cr.creat >= (MIN(cr48.creat)+0.3) then 1 when cr.creat >= (MIN(cr7.creat)*1.5) then 1 else 0 end as aki_stage_creat FROM cr -- add in all creatinine values in the last 48 hours LEFT JOIN cr cr48 ON cr.icustay_id = cr48.icustay_id AND cr48.charttime < cr.charttime AND cr48.charttime >= DATE_SUB(cr.charttime, INTERVAL '48' HOUR) -- add in all creatinine values in the last 7 days LEFT JOIN cr cr7 ON cr.icustay_id = cr7.icustay_id AND cr7.charttime < cr.charttime AND cr7.charttime >= DATE_SUB(cr.charttime, INTERVAL '7' DAY) GROUP BY cr.icustay_id, cr.charttime, cr.creat ORDER BY cr.icustay_id, cr.charttime, cr.creat) creat GROUP BY aki_stage_creat;

I understand that it's not exactly the same SQL query as the one in MIMIC code to create the kdigo_creatinine derived table. I just wanted to verify if the results I got were the same in MySQL and BigQuery, and it seems that I got different results. I don't think it's a MIMIC version issue since the MySQL implementation is v1.4 and I run the checks.sql script to find out that all the checks passed (all the row counts of the MySQL). Here are pictures to show difference in results when running the same script:

BigQuery

MySQL

I want to work on this project using MySQL due to its convenience in interacting with Python, but I'm worried because the MIMIC code concepts are not provided in MySQL and I might make mistakes from conversion of available PostgreSQL and BigQuery scripts to MySQL. I think, even between PostgreSQL and BigQuery there are SQL conversion problems (as identified in issue #1549). Could there be some underlying explanation behind this difference? Also, is there any proposed way to replicate the MIMIC code concepts, especially regarding KDIGO AKI patient detection using MySQL?

Thanks beforehand,