--胆囊切除术的患者
CREATE TABLE cholecystectomy
AS
SELECT
subject_id,hadm_id,icd_code,seq_num,chartdate
FROM
mimiciv.mimiciv_hosp.procedures_icd
WHERE icd_code IN ('5102','5103','5104','5121','5122',
'5123','5124','5192','9641','9754')

SELECT subject_id
from cholecystectomy
GROUP BY subject_id
--2753


DELETE 
FROM cholecystectomy_copy1 
WHERE (subject_id,chartdate)NOT IN(
SELECT subject_id,MIN(chartdate)min_chartdate
FROM cholecystectomy_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_copy1 
WHERE (subject_id,seq_num)NOT IN(
SELECT subject_id,MIN(seq_num)min_seq_num
FROM cholecystectomy_copy1 
GROUP BY subject_id
);


--匹配住院及出院时间
CREATE TABLE cholecystectomy_1
AS
SELECT
a.subject_id,a.hadm_id,icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,DATETIME_DIFF(dischtime,admittime, 'DAY') as los_zhuyuan
FROM cholecystectomy_copy1  a
LEFT JOIN
mimiciv_hosp.admissions b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan

DELETE 
FROM cholecystectomy_1_copy1 
WHERE (subject_id,admittime)NOT IN(
SELECT subject_id,MIN(admittime)min_admittime
FROM cholecystectomy_1_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_2
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title
FROM cholecystectomy_1_copy1  a
LEFT JOIN
mimiciv_hosp.d_icd_procedures b
ON a.icd_code=b.icd_code
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title

CREATE TABLE cholecystectomy_3
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,DATETIME_DIFF(dod,dischtime, 'DAY') as os_time
FROM cholecystectomy_2  a
LEFT JOIN
mimiciv_hosp.patients b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time

--ICU的记录
CREATE TABLE cholecystectomy_4
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,stay_id,intime,outtime,los
FROM cholecystectomy_3  a
LEFT JOIN
mimiciv_icu.icustays b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,stay_id,intime,outtime,los

DELETE 
FROM cholecystectomy_4_copy1 
WHERE (subject_id,intime)NOT IN(
SELECT subject_id,MIN(intime)min_intime
FROM cholecystectomy_4_copy1 
GROUP BY subject_id
);

--评分系统



CREATE TABLE cholecystectomy_5
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index
FROM cholecystectomy_4_copy1  a
LEFT JOIN
mimiciv_derived.charlson b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index



CREATE TABLE cholecystectomy_6
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa
FROM cholecystectomy_5  a
LEFT JOIN
mimiciv_derived.first_day_sofa b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa

DELETE 
FROM cholecystectomy_6_copy1 
WHERE (subject_id,sofa)NOT IN(
SELECT subject_id,MIN(sofa)min_sofa
FROM cholecystectomy_6_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_7
AS
SELECT a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii
FROM cholecystectomy_6_copy1  a
LEFT JOIN
mimiciv_derived.apsiii b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii

DELETE 
FROM cholecystectomy_7_copy1 
WHERE (subject_id,apsiii)NOT IN(
SELECT subject_id,MIN(apsiii)min_apsiii
FROM cholecystectomy_7_copy1 
GROUP BY subject_id
);

--治疗胆囊炎的药物
CREATE TABLE Atropine_sulfate
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Atropine Sulfate%';

CREATE TABLE Meperidine
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%eperidin%';

CREATE TABLE diclofenac_sodiu
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%iclofenac Sodiu%';


CREATE TABLE Indomethacin
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Indomethacin%';

CREATE TABLE Ciprofloxacin
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Ciprofloxacin%';

CREATE TABLE Ertapenem_sodium
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Ertapenem Sodium%';

CREATE TABLE Levofloxacin
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Levofloxacin%';

CREATE TABLE Amoxicillin
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Amoxicillin%';

CREATE TABLE penicillin
AS
SELECT * 
FROM mimiciv_hosp.prescriptions
WHERE drug LIKE '%Penicillin%';

CREATE TABLE cholecystectomy_amoxicillin
AS
SELECT a.subject_id,a.hadm_id,starttime_amoxicillin,stoptime_amoxicillin,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
amoxicillin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_amoxicillin,stoptime_amoxicillin,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin

DELETE 
FROM cholecystectomy_amoxicillin_copy1 
WHERE (subject_id,starttime_amoxicillin)NOT IN(
SELECT subject_id,MIN(starttime_amoxicillin)min_starttime_amoxicillin
FROM cholecystectomy_amoxicillin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_amoxicillin_copy1 
WHERE (subject_id,stoptime_amoxicillin)NOT IN(
SELECT subject_id,MIN(stoptime_amoxicillin)min_stoptime_amoxicillin
FROM cholecystectomy_amoxicillin_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_atropine_sulfate
AS
SELECT a.subject_id,a.hadm_id,starttime_atropine_sulfate,stoptime_atropine_sulfate,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate
FROM cholecystectomy_6_copy1  a
LEFT JOIN
atropine_sulfate b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_atropine_sulfate,stoptime_atropine_sulfate,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate

DELETE 
FROM cholecystectomy_atropine_sulfate_copy1 
WHERE (subject_id,starttime_atropine_sulfate)NOT IN(
SELECT subject_id,MIN(starttime_atropine_sulfate)min_starttime_atropine_sulfate
FROM cholecystectomy_atropine_sulfate_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_atropine_sulfate_copy1 
WHERE (subject_id,stoptime_atropine_sulfate)NOT IN(
SELECT subject_id,MIN(stoptime_atropine_sulfate)min_stoptime_atropine_sulfate
FROM cholecystectomy_atropine_sulfate_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_ciprofloxacin
AS
SELECT a.subject_id,a.hadm_id,starttime_ciprofloxacin,stoptime_ciprofloxacin,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
ciprofloxacin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_ciprofloxacin,stoptime_ciprofloxacin,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin

DELETE 
FROM cholecystectomy_ciprofloxacin_copy1 
WHERE (subject_id,starttime_ciprofloxacin)NOT IN(
SELECT subject_id,MIN(starttime_ciprofloxacin)min_starttime_ciprofloxacin
FROM cholecystectomy_ciprofloxacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ciprofloxacin_copy1 
WHERE (subject_id,stoptime_ciprofloxacin)NOT IN(
SELECT subject_id,MIN(stoptime_ciprofloxacin)min_stoptime_ciprofloxacin
FROM cholecystectomy_ciprofloxacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ciprofloxacin_copy1 
WHERE (subject_id,dose_val_rx_ciprofloxacin)NOT IN(
SELECT subject_id,MIN(dose_val_rx_ciprofloxacin)min_stoptime_dose_val_rx_ciprofloxacin
FROM cholecystectomy_ciprofloxacin_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_diclofenac_sodiu
AS
SELECT a.subject_id,a.hadm_id,starttime_diclofenac_sodiu,stoptime_diclofenac_sodiu,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu
FROM cholecystectomy_6_copy1  a
LEFT JOIN
diclofenac_sodiu b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_diclofenac_sodiu,stoptime_diclofenac_sodiu,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu

DELETE 
FROM cholecystectomy_diclofenac_sodiu_copy1 
WHERE (subject_id,starttime_diclofenac_sodiu)NOT IN(
SELECT subject_id,MIN(starttime_diclofenac_sodiu)min_starttime_diclofenac_sodiu
FROM cholecystectomy_diclofenac_sodiu_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_diclofenac_sodiu_copy1 
WHERE (subject_id,stoptime_diclofenac_sodiu)NOT IN(
SELECT subject_id,MIN(stoptime_diclofenac_sodiu)min_stoptime_diclofenac_sodiu
FROM cholecystectomy_diclofenac_sodiu_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_diclofenac_sodiu_copy1 
WHERE (subject_id,dose_val_rx_diclofenac_sodiu)NOT IN(
SELECT subject_id,MIN(dose_val_rx_diclofenac_sodiu)min_stoptime_dose_val_rx_diclofenac_sodiu
FROM cholecystectomy_diclofenac_sodiu_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_ertapenem_sodium
AS
SELECT a.subject_id,a.hadm_id,starttime_ertapenem_sodium,stoptime_ertapenem_sodium,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium
FROM cholecystectomy_6_copy1  a
LEFT JOIN
ertapenem_sodium b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_ertapenem_sodium,stoptime_ertapenem_sodium,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium

DELETE 
FROM cholecystectomy_ertapenem_sodium_copy1 
WHERE (subject_id,starttime_ertapenem_sodium)NOT IN(
SELECT subject_id,MIN(starttime_ertapenem_sodium)min_starttime_ertapenem_sodium
FROM cholecystectomy_ertapenem_sodium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ertapenem_sodium_copy1 
WHERE (subject_id,stoptime_ertapenem_sodium)NOT IN(
SELECT subject_id,MIN(stoptime_ertapenem_sodium)min_stoptime_ertapenem_sodium
FROM cholecystectomy_ertapenem_sodium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ertapenem_sodium_copy1 
WHERE (subject_id,dose_val_rx_ertapenem_sodium)NOT IN(
SELECT subject_id,MIN(dose_val_rx_ertapenem_sodium)min_stoptime_dose_val_rx_ertapenem_sodium
FROM cholecystectomy_ertapenem_sodium_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_indomethacin
AS
SELECT a.subject_id,a.hadm_id,starttime_indomethacin,stoptime_indomethacin,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
indomethacin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_indomethacin,stoptime_indomethacin,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin

DELETE 
FROM cholecystectomy_indomethacin_copy1 
WHERE (subject_id,starttime_indomethacin)NOT IN(
SELECT subject_id,MIN(starttime_indomethacin)min_starttime_indomethacin
FROM cholecystectomy_indomethacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_indomethacin_copy1 
WHERE (subject_id,stoptime_indomethacin)NOT IN(
SELECT subject_id,MIN(stoptime_indomethacin)min_stoptime_indomethacin
FROM cholecystectomy_indomethacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_indomethacin_copy1 
WHERE (subject_id,dose_val_rx_indomethacin)NOT IN(
SELECT subject_id,MIN(dose_val_rx_indomethacin)min_stoptime_dose_val_rx_indomethacin
FROM cholecystectomy_indomethacin_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_levofloxacin
AS
SELECT a.subject_id,a.hadm_id,starttime_levofloxacin,stoptime_levofloxacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
levofloxacin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_levofloxacin,stoptime_levofloxacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin

DELETE 
FROM cholecystectomy_levofloxacin_copy1 
WHERE (subject_id,starttime_levofloxacin)NOT IN(
SELECT subject_id,MIN(starttime_levofloxacin)min_starttime_levofloxacin
FROM cholecystectomy_levofloxacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_levofloxacin_copy1 
WHERE (subject_id,stoptime_levofloxacin)NOT IN(
SELECT subject_id,MIN(stoptime_levofloxacin)min_stoptime_levofloxacin
FROM cholecystectomy_levofloxacin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_levofloxacin_copy1 
WHERE (subject_id,dose_val_rx_levofloxacin)NOT IN(
SELECT subject_id,MIN(dose_val_rx_levofloxacin)min_stoptime_dose_val_rx_levofloxacin
FROM cholecystectomy_levofloxacin_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_meperidine
AS
SELECT a.subject_id,a.hadm_id,starttime_meperidine,stoptime_meperidine,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine
FROM cholecystectomy_6_copy1  a
LEFT JOIN
meperidine b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_meperidine,stoptime_meperidine,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine

DELETE 
FROM cholecystectomy_meperidine_copy1 
WHERE (subject_id,starttime_meperidine)NOT IN(
SELECT subject_id,MIN(starttime_meperidine)min_starttime_meperidine
FROM cholecystectomy_meperidine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_meperidine_copy1 
WHERE (subject_id,stoptime_meperidine)NOT IN(
SELECT subject_id,MIN(stoptime_meperidine)min_stoptime_meperidine
FROM cholecystectomy_meperidine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_meperidine_copy1 
WHERE (subject_id,dose_val_rx_meperidine)NOT IN(
SELECT subject_id,MIN(dose_val_rx_meperidine)min_stoptime_dose_val_rx_meperidine
FROM cholecystectomy_meperidine_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_penicillin
AS
SELECT a.subject_id,a.hadm_id,starttime_penicillin,stoptime_penicillin,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
penicillin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_penicillin,stoptime_penicillin,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin

DELETE 
FROM cholecystectomy_penicillin_copy1 
WHERE (subject_id,starttime_penicillin)NOT IN(
SELECT subject_id,MIN(starttime_penicillin)min_starttime_penicillin
FROM cholecystectomy_penicillin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_penicillin_copy1 
WHERE (subject_id,stoptime_penicillin)NOT IN(
SELECT subject_id,MIN(stoptime_penicillin)min_stoptime_penicillin
FROM cholecystectomy_penicillin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_penicillin_copy1 
WHERE (subject_id,dose_val_rx_penicillin)NOT IN(
SELECT subject_id,MIN(dose_val_rx_penicillin)min_stoptime_dose_val_rx_penicillin
FROM cholecystectomy_penicillin_copy1 
GROUP BY subject_id
);

--合并药物
CREATE TABLE cholecystectomy_durg
AS
SELECT a.subject_id,a.hadm_id,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin
FROM cholecystectomy_6_copy1  a
LEFT JOIN
cholecystectomy_amoxicillin_copy1 b
ON a.hadm_id=b.hadm_id
LEFT JOIN
cholecystectomy_atropine_sulfate_copy1 b1
ON a.hadm_id=b1.hadm_id
LEFT JOIN
cholecystectomy_ciprofloxacin_copy1 b2
ON a.hadm_id=b2.hadm_id
LEFT JOIN
cholecystectomy_diclofenac_sodiu_copy1 b3
ON a.hadm_id=b3.hadm_id
LEFT JOIN
cholecystectomy_ertapenem_sodium_copy1 b4
ON a.hadm_id=b4.hadm_id
LEFT JOIN
cholecystectomy_indomethacin_copy1 b5
ON a.hadm_id=b5.hadm_id
LEFT JOIN
cholecystectomy_levofloxacin_copy1 b6
ON a.hadm_id=b6.hadm_id
LEFT JOIN
cholecystectomy_meperidine_copy1 b7
ON a.hadm_id=b7.hadm_id
LEFT JOIN
cholecystectomy_penicillin_copy1 b8
ON a.hadm_id=b8.hadm_id
GROUP BY a.subject_id,a.hadm_id,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin

--麻醉药物
CREATE TABLE cholecystectomy_propofol
AS
SELECT a.subject_id,a.hadm_id,starttime_propofol,stoptime_propofol,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol
FROM cholecystectomy_6_copy1  a
LEFT JOIN
propofol b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_propofol,stoptime_propofol,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol

DELETE 
FROM cholecystectomy_propofol_copy1 
WHERE (subject_id,starttime_propofol)NOT IN(
SELECT subject_id,MIN(starttime_propofol)min_starttime_propofol
FROM cholecystectomy_propofol_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_propofol_copy1 
WHERE (subject_id,stoptime_propofol)NOT IN(
SELECT subject_id,MIN(stoptime_propofol)min_stoptime_propofol
FROM cholecystectomy_propofol_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_propofol_copy1 
WHERE (subject_id,dose_val_rx_propofol)NOT IN(
SELECT subject_id,MIN(dose_val_rx_propofol)min_stoptime_dose_val_rx_propofol
FROM cholecystectomy_propofol_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_midazolam
AS
SELECT a.subject_id,a.hadm_id,starttime_midazolam,stoptime_midazolam,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam
FROM cholecystectomy_6_copy1  a
LEFT JOIN
midazolam b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_midazolam,stoptime_midazolam,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam

DELETE 
FROM cholecystectomy_midazolam_copy1 
WHERE (subject_id,starttime_midazolam)NOT IN(
SELECT subject_id,MIN(starttime_midazolam)min_starttime_midazolam
FROM cholecystectomy_midazolam_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_midazolam_copy1 
WHERE (subject_id,stoptime_midazolam)NOT IN(
SELECT subject_id,MIN(stoptime_midazolam)min_stoptime_midazolam
FROM cholecystectomy_midazolam_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_midazolam_copy1 
WHERE (subject_id,dose_val_rx_midazolam)NOT IN(
SELECT subject_id,MIN(dose_val_rx_midazolam)min_stoptime_dose_val_rx_midazolam
FROM cholecystectomy_midazolam_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_fentanyl
AS
SELECT a.subject_id,a.hadm_id,starttime_fentanyl,stoptime_fentanyl,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl
FROM cholecystectomy_6_copy1  a
LEFT JOIN
fentanyl b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_fentanyl,stoptime_fentanyl,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl

DELETE 
FROM cholecystectomy_fentanyl_copy1 
WHERE (subject_id,starttime_fentanyl)NOT IN(
SELECT subject_id,MIN(starttime_fentanyl)min_starttime_fentanyl
FROM cholecystectomy_fentanyl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_fentanyl_copy1 
WHERE (subject_id,stoptime_fentanyl)NOT IN(
SELECT subject_id,MIN(stoptime_fentanyl)min_stoptime_fentanyl
FROM cholecystectomy_fentanyl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_fentanyl_copy1 
WHERE (subject_id,dose_val_rx_fentanyl)NOT IN(
SELECT subject_id,MIN(dose_val_rx_fentanyl)min_stoptime_dose_val_rx_fentanyl
FROM cholecystectomy_fentanyl_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_dexmedetomidine
AS
SELECT a.subject_id,a.hadm_id,starttime_dexmedetomidine,stoptime_dexmedetomidine,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine
FROM cholecystectomy_6_copy1  a
LEFT JOIN
dexmedetomidine b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,starttime_dexmedetomidine,stoptime_dexmedetomidine,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine

DELETE 
FROM cholecystectomy_dexmedetomidine_copy1 
WHERE (subject_id,starttime_dexmedetomidine)NOT IN(
SELECT subject_id,MIN(starttime_dexmedetomidine)min_starttime_dexmedetomidine
FROM cholecystectomy_dexmedetomidine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_dexmedetomidine_copy1 
WHERE (subject_id,stoptime_dexmedetomidine)NOT IN(
SELECT subject_id,MIN(stoptime_dexmedetomidine)min_stoptime_dexmedetomidine
FROM cholecystectomy_dexmedetomidine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_dexmedetomidine_copy1 
WHERE (subject_id,dose_val_rx_dexmedetomidine)NOT IN(
SELECT subject_id,MIN(dose_val_rx_dexmedetomidine)min_stoptime_dose_val_rx_dexmedetomidine
FROM cholecystectomy_dexmedetomidine_copy1 
GROUP BY subject_id
);

--合并麻醉药
CREATE TABLE cholecystectomy_durg1
AS
SELECT a.subject_id,a.hadm_id,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine
FROM cholecystectomy_6_copy1  a
LEFT JOIN
cholecystectomy_propofol_copy1 b
ON a.hadm_id=b.hadm_id
LEFT JOIN
cholecystectomy_midazolam_copy1 b1
ON a.hadm_id=b1.hadm_id
LEFT JOIN
cholecystectomy_fentanyl_copy1 b2
ON a.hadm_id=b2.hadm_id
LEFT JOIN
cholecystectomy_dexmedetomidine_copy1 b3
ON a.hadm_id=b3.hadm_id
GROUP BY a.subject_id,a.hadm_id,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine

--药
CREATE TABLE cholecystectomy_durg_t
AS
SELECT a.subject_id,a.hadm_id,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin
FROM cholecystectomy_durg1  a
LEFT JOIN
cholecystectomy_durg b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin

--吸烟
CREATE TABLE cholecystectomy_cigarette_use
AS
SELECT a.subject_id,a.hadm_id,icd_code_cigarette_use,seq_num_cigarette_use
FROM cholecystectomy_6_copy1  a
LEFT JOIN
cigarette_use b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,icd_code_cigarette_use,seq_num_cigarette_use

DELETE 
FROM cholecystectomy_cigarette_use_copy1 
WHERE (subject_id,seq_num_cigarette_use)NOT IN(
SELECT subject_id,MIN(seq_num_cigarette_use)min_seq_num_cigarette_use
FROM cholecystectomy_cigarette_use_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_alcohol_use
AS
SELECT a.subject_id,a.hadm_id,icd_code_alcohol_use,seq_num_alcohol_use
FROM cholecystectomy_6_copy1  a
LEFT JOIN
alcohol_use b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,icd_code_alcohol_use,seq_num_alcohol_use

DELETE 
FROM cholecystectomy_alcohol_use_copy1 
WHERE (subject_id,seq_num_alcohol_use)NOT IN(
SELECT subject_id,MIN(seq_num_alcohol_use)min_seq_num_alcohol_use
FROM cholecystectomy_alcohol_use_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_alcohol_use_copy1 
WHERE (subject_id,icd_code_alcohol_use)NOT IN(
SELECT subject_id,MIN(icd_code_alcohol_use)min_icd_code_alcohol_use
FROM cholecystectomy_alcohol_use_copy1 
GROUP BY subject_id
);

CREATE TABLE Height_2
AS
SELECT * 
FROM mimiciv_hosp.omr
WHERE result_name LIKE '%Height%';


CREATE TABLE cholecystectomy_height
AS
SELECT a.subject_id,a.hadm_id,chartdate_height,seq_num_height,result_name_height,result_value_height
FROM cholecystectomy_6_copy1  a
LEFT JOIN
height_2 b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,chartdate_height,seq_num_height,result_name_height,result_value_height

DELETE 
FROM cholecystectomy_height_copy1 
WHERE (subject_id,chartdate_height)NOT IN(
SELECT subject_id,MIN(chartdate_height)min_chartdate_height
FROM cholecystectomy_height_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_height_copy1 
WHERE (subject_id,seq_num_height)NOT IN(
SELECT subject_id,MIN(seq_num_height)min_seq_num_height
FROM cholecystectomy_height_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_height_copy1 
WHERE (subject_id,result_value_height)NOT IN(
SELECT subject_id,MIN(result_value_height)min_result_value_height
FROM cholecystectomy_height_copy1 
GROUP BY subject_id
);


CREATE TABLE Weight_2
AS
SELECT * 
FROM mimiciv_hosp.omr
WHERE result_name LIKE '%Weight%';

CREATE TABLE cholecystectomy_weight
AS
SELECT a.subject_id,a.hadm_id,chartdate_weight,seq_num_weight,result_name_weight,result_value_weight
FROM cholecystectomy_6_copy1  a
LEFT JOIN
weight_2 b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,chartdate_weight,seq_num_weight,result_name_weight,result_value_weight


DELETE 
FROM cholecystectomy_weight_copy1 
WHERE (subject_id,chartdate_weight)NOT IN(
SELECT subject_id,MIN(chartdate_weight)min_chartdate_weight
FROM cholecystectomy_weight_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_weight_copy1 
WHERE (subject_id,seq_num_weight)NOT IN(
SELECT subject_id,MIN(seq_num_weight)min_seq_num_weight
FROM cholecystectomy_weight_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_weight_copy1 
WHERE (subject_id,result_value_weight)NOT IN(
SELECT subject_id,MIN(result_value_weight)min_result_value_weight
FROM cholecystectomy_weight_copy1 
GROUP BY subject_id
);



CREATE TABLE Blood_Pressure_2
AS
SELECT * 
FROM mimiciv_hosp.omr
WHERE result_name LIKE '%Blood Pressure%';

CREATE TABLE cholecystectomy_blood_pressure
AS
SELECT a.subject_id,a.hadm_id,chartdate_blood_pressure,seq_num_blood_pressure,result_name_blood_pressure,result_value_blood_pressure
FROM cholecystectomy_6_copy1  a
LEFT JOIN
blood_pressure_2 b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,chartdate_blood_pressure,seq_num_blood_pressure,result_name_blood_pressure,result_value_blood_pressure

DELETE 
FROM cholecystectomy_blood_pressure_copy1 
WHERE (subject_id,chartdate_blood_pressure)NOT IN(
SELECT subject_id,MIN(chartdate_blood_pressure)min_chartdate_blood_pressure
FROM cholecystectomy_blood_pressure_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_blood_pressure_copy1 
WHERE (subject_id,seq_num_blood_pressure)NOT IN(
SELECT subject_id,MIN(seq_num_blood_pressure)min_seq_num_blood_pressure
FROM cholecystectomy_blood_pressure_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_blood_pressure_copy1 
WHERE (subject_id,result_value_blood_pressure)NOT IN(
SELECT subject_id,MIN(result_value_blood_pressure)min_result_value_blood_pressure
FROM cholecystectomy_blood_pressure_copy1 
GROUP BY subject_id
);

CREATE TABLE BMI_2
AS
SELECT * 
FROM mimiciv_hosp.omr
WHERE result_name LIKE '%BMI%';

CREATE TABLE cholecystectomy_bmi
AS
SELECT a.subject_id,a.hadm_id,chartdate_bmi,seq_num_bmi,result_name_bmi,result_value_bmi
FROM cholecystectomy_6_copy1  a
LEFT JOIN
bmi_2 b
ON a.subject_id=b.subject_id
GROUP BY a.subject_id,a.hadm_id,chartdate_bmi,seq_num_bmi,result_name_bmi,result_value_bmi

DELETE 
FROM cholecystectomy_bmi_copy1 
WHERE (subject_id,chartdate_bmi)NOT IN(
SELECT subject_id,MIN(chartdate_bmi)min_chartdate_bmi
FROM cholecystectomy_bmi_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bmi_copy1 
WHERE (subject_id,seq_num_bmi)NOT IN(
SELECT subject_id,MIN(seq_num_bmi)min_seq_num_bmi
FROM cholecystectomy_bmi_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bmi_copy1 
WHERE (subject_id,result_value_bmi)NOT IN(
SELECT subject_id,MIN(result_value_bmi)min_result_value_bmi
FROM cholecystectomy_bmi_copy1 
GROUP BY subject_id
);




CREATE TABLE cholecystectomy_vitalsign
AS
SELECT a.subject_id,a.hadm_id,a.stay_id,heart_rate_min,heart_rate_max,heart_rate_mean,sbp_min,sbp_max,sbp_mean,dbp_min,dbp_max,dbp_mean,mbp_min,mbp_max,mbp_mean,resp_rate_min,resp_rate_max,resp_rate_mean,temperature_min,temperature_max,temperature_mean,spo2_min,spo2_max,spo2_mean,glucose_min,glucose_max,glucose_mean
FROM cholecystectomy_6_copy1  a
LEFT JOIN
mimiciv_derived.first_day_vitalsign b
ON a.stay_id=b.stay_id
GROUP BY a.subject_id,a.hadm_id,a.stay_id,heart_rate_min,heart_rate_max,heart_rate_mean,sbp_min,sbp_max,sbp_mean,dbp_min,dbp_max,dbp_mean,mbp_min,mbp_max,mbp_mean,resp_rate_min,resp_rate_max,resp_rate_mean,temperature_min,temperature_max,temperature_mean,spo2_min,spo2_max,spo2_mean,glucose_min,glucose_max,glucose_mean

CREATE TABLE cholecystectomy_t1
AS
SELECT a.subject_id,a.hadm_id,a.stay_id,heart_rate_min,heart_rate_max,heart_rate_mean,sbp_min,sbp_max,sbp_mean,dbp_min,dbp_max,dbp_mean,mbp_min,mbp_max,mbp_mean,resp_rate_min,resp_rate_max,resp_rate_mean,temperature_min,temperature_max,temperature_mean,spo2_min,spo2_max,spo2_mean,glucose_min,glucose_max,glucose_mean,icd_code_cigarette_use,seq_num_cigarette_use,icd_code_alcohol_use,seq_num_alcohol_use,result_name_height,result_value_height,result_name_weight,result_value_weight,result_name_blood_pressure,result_value_blood_pressure,result_name_bmi,result_value_bmi
FROM
cholecystectomy_vitalsign a
LEFT JOIN
cholecystectomy_bmi_copy1 b
ON a.subject_id=b.subject_id
LEFT JOIN
cholecystectomy_blood_pressure_copy1 b1
ON a.subject_id=b1.subject_id
LEFT JOIN
cholecystectomy_weight_copy1 b2
ON a.subject_id=b2.subject_id
LEFT JOIN
cholecystectomy_height_copy1 b3
ON a.subject_id=b3.subject_id
LEFT JOIN
cholecystectomy_cigarette_use_copy1 b4
ON a.subject_id=b4.subject_id
LEFT JOIN
cholecystectomy_alcohol_use_copy1 b5
ON a.subject_id=b5.subject_id
GROUP BY a.subject_id,a.hadm_id,a.stay_id,heart_rate_min,heart_rate_max,heart_rate_mean,sbp_min,sbp_max,sbp_mean,dbp_min,dbp_max,dbp_mean,mbp_min,mbp_max,mbp_mean,resp_rate_min,resp_rate_max,resp_rate_mean,temperature_min,temperature_max,temperature_mean,spo2_min,spo2_max,spo2_mean,glucose_min,glucose_max,glucose_mean,icd_code_cigarette_use,seq_num_cigarette_use,icd_code_alcohol_use,seq_num_alcohol_use,result_name_height,result_value_height,result_name_weight,result_value_weight,result_name_blood_pressure,result_value_blood_pressure,result_name_bmi,result_value_bmi




--机器通气
CREATE TABLE mechanically_ventilated
AS
SELECT *
FROM mimiciv_icu.chartevents  a
WHERE itemid IN ('226260')


CREATE TABLE cholecystectomy_hypertension
AS
SELECT a.subject_id,a.hadm_id,icd_code_hypertension,seq_num_hypertension
FROM cholecystectomy_6_copy1  a
LEFT JOIN
hypertension b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_hypertension,seq_num_hypertension


DELETE 
FROM cholecystectomy_hypertension_copy1
WHERE (subject_id,seq_num_hypertension)NOT IN(
SELECT subject_id,MIN(seq_num_hypertension)min_seq_num_hypertension
FROM cholecystectomy_hypertension_copy1 
GROUP BY subject_id
);


CREATE TABLE cholecystectomy_hyperlipidemia
AS
SELECT a.subject_id,a.hadm_id,icd_code_hyperlipidemia,seq_num_hyperlipidemia
FROM cholecystectomy_6_copy1  a
LEFT JOIN
hyperlipidemia b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_hyperlipidemia,seq_num_hyperlipidemia


DELETE 
FROM cholecystectomy_hyperlipidemia_copy1
WHERE (subject_id,seq_num_hyperlipidemia)NOT IN(
SELECT subject_id,MIN(seq_num_hyperlipidemia)min_seq_num_hyperlipidemia
FROM cholecystectomy_hyperlipidemia_copy1 
GROUP BY subject_id
);


CREATE TABLE gout
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('2740',	'27400',	'27401',	'27402',	'27403',	'27410',	'27419',	'27481',	'27482',	'27489',	'2749',	'M1A051 ',	'M1A0510',	'M1A0511',	'M1A052 ',	'M1A0520',	'M1A0521',	'M1A0491',	'M1A05  ',	'M10    ',	'M100   ',	'M1000  ',	'M1001  ',	'M10011 ',	'M10012 ',	'M10019 ',	'M1002  ',	'M10021 ',	'M10022 ',	'M10029 ',	'M1003  ',	'M10031 ',	'M10032 ',	'M10039 ',	'M1004  ',	'M10041 ',	'M10042 ',	'M10049 ',	'M1005  ',	'M10051 ',	'M10052 ',	'M10059 ',	'M1006  ',	'M10061 ',	'M10062 ',	'M10069 ',	'M1007  ',	'M10071 ',	'M10072 ',	'M10079 ',	'M1008  ',	'M1009  ',	'M101   ',	'M1010  ',	'M1011  ',	'M10111 ',	'M10112 ',	'M10119 ',	'M1012  ',	'M10121 ',	'M10122 ',	'M10129 ',	'M1013  ',	'M10131 ',	'M10132 ',	'M10139 ',	'M1014  ',	'M10141 ',	'M10142 ',	'M10149 ',	'M1015  ',	'M10151 ',	'M10152 ',	'M10159 ',	'M1016  ',	'M10161 ',	'M10162 ',	'M10169 ',	'M1017  ',	'M10171 ',	'M10172 ',	'M10179 ',	'M1018  ',	'M1019  ',	'M102   ',	'M1020  ',	'M1021  ',	'M10211 ',	'M10212 ',	'M10219 ',	'M1022  ',	'M10221 ',	'M10222 ',	'M10229 ',	'M1023  ',	'M10231 ',	'M10232 ',	'M10239 ',	'M1024  ',	'M10241 ',	'M10242 ',	'M10249 ',	'M1025  ',	'M10251 ',	'M10252 ',	'M10259 ',	'M1026  ',	'M10261 ',	'M10262 ',	'M10269 ',	'M1027  ',	'M10271 ',	'M10272 ',	'M10279 ',	'M1028  ',	'M1029  ',	'M103   ',	'M1030  ',	'M1031  ',	'M10311 ',	'M10312 ',	'M10319 ',	'M1032  ',	'M10321 ',	'M10322 ',	'M10329 ',	'M1033  ',	'M10331 ',	'M10332 ',	'M10339 ',	'M1034  ',	'M10341 ',	'M10342 ',	'M10349 ',	'M1035  ',	'M10351 ',	'M10352 ',	'M10359 ',	'M1036  ',	'M10361 ',	'M10362 ',	'M10369 ',	'M1037  ',	'M10371 ',	'M10372 ',	'M10379 ',	'M1038  ',	'M1039  ',	'M104   ',	'M1040  ',	'M1041  ',	'M10411 ',	'M10412 ',	'M10419 ',	'M1042  ',	'M10421 ',	'M10422 ',	'M10429 ',	'M1043  ',	'M10431 ',	'M10432 ',	'M10439 ',	'M1044  ',	'M10441 ',	'M10442 ',	'M10449 ',	'M1045  ',	'M10451 ',	'M10452 ',	'M10459 ',	'M1046  ',	'M10461 ',	'M10462 ',	'M10469 ',	'M1047  ',	'M10471 ',	'M10472 ',	'M10479 ',	'M1048  ',	'M1049  ',	'M109   ',	'M1A    ',	'M1A0   ',	'M1A00  ',	'M1A00X0',	'M1A00X1',	'M1A01  ',	'M1A011 ',	'M1A0110',	'M1A0111',	'M1A012 ',	'M1A0120',	'M1A0121',	'M1A019 ',	'M1A0190',	'M1A0191',	'M1A02  ',	'M1A021 ',	'M1A0210',	'M1A0211',	'M1A022 ',	'M1A0220',	'M1A0221',	'M1A029 ',	'M1A0290',	'M1A0291',	'M1A03  ',	'M1A031 ',	'M1A0310',	'M1A0311',	'M1A032 ',	'M1A0320',	'M1A0321',	'M1A039 ',	'M1A0390',	'M1A0391',	'M1A04  ',	'M1A041 ',	'M1A0410',	'M1A0411',	'M1A042 ',	'M1A0420',	'M1A0421',	'M1A049 ',	'M1A0490',	'M1A059 ',	'M1A0590',	'M1A0591',	'M1A06  ',	'M1A061 ',	'M1A0610',	'M1A0611',	'M1A062 ',	'M1A0620',	'M1A0621',	'M1A069 ',	'M1A0690',	'M1A0691',	'M1A07  ',	'M1A071 ',	'M1A0710',	'M1A0711',	'M1A072 ',	'M1A0720',	'M1A0721',	'M1A079 ',	'M1A0790',	'M1A0791',	'M1A08  ',	'M1A08X0',	'M1A08X1',	'M1A09  ',	'M1A09X0',	'M1A09X1',	'M1A1   ',	'M1A10  ',	'M1A10X0',	'M1A10X1',	'M1A11  ',	'M1A111 ',	'M1A1110',	'M1A1111',	'M1A112 ',	'M1A1120',	'M1A1121',	'M1A119 ',	'M1A1190',	'M1A1191',	'M1A12  ',	'M1A121 ',	'M1A1210',	'M1A1211',	'M1A122 ',	'M1A1220',	'M1A1221',	'M1A129 ',	'M1A1290',	'M1A1291',	'M1A13  ',	'M1A131 ',	'M1A1310',	'M1A1311',	'M1A132 ',	'M1A1320',	'M1A1321',	'M1A139 ',	'M1A1390',	'M1A1391',	'M1A14  ',	'M1A141 ',	'M1A1410',	'M1A1411',	'M1A142 ',	'M1A1420',	'M1A1421',	'M1A149 ',	'M1A1490',	'M1A1491',	'M1A15  ',	'M1A151 ',	'M1A1510',	'M1A1511',	'M1A152 ',	'M1A1520',	'M1A1521',	'M1A159 ',	'M1A1590',	'M1A1591',	'M1A16  ',	'M1A161 ',	'M1A1610',	'M1A1611',	'M1A162 ',	'M1A1620',	'M1A1621',	'M1A169 ',	'M1A1690',	'M1A1691',	'M1A17  ',	'M1A171 ',	'M1A1710',	'M1A1711',	'M1A172 ',	'M1A1720',	'M1A1721',	'M1A179 ',	'M1A1790',	'M1A1791',	'M1A18  ',	'M1A18X0',	'M1A18X1',	'M1A19  ',	'M1A19X0',	'M1A19X1',	'M1A2   ',	'M1A20  ',	'M1A20X0',	'M1A20X1',	'M1A21  ',	'M1A211 ',	'M1A2110',	'M1A2111',	'M1A212 ',	'M1A2120',	'M1A2121',	'M1A219 ',	'M1A2190',	'M1A2191',	'M1A22  ',	'M1A221 ',	'M1A2210',	'M1A2211',	'M1A222 ',	'M1A2220',	'M1A2221',	'M1A229 ',	'M1A2290',	'M1A2291',	'M1A23  ',	'M1A231 ',	'M1A2310',	'M1A2311',	'M1A232 ',	'M1A2320',	'M1A2321',	'M1A239 ',	'M1A2390',	'M1A2391',	'M1A24  ',	'M1A241 ',	'M1A2410',	'M1A2411',	'M1A242 ',	'M1A2420',	'M1A2421',	'M1A249 ',	'M1A2490',	'M1A2491',	'M1A25  ',	'M1A251 ',	'M1A2510',	'M1A2511',	'M1A252 ',	'M1A2520',	'M1A2521',	'M1A259 ',	'M1A2590',	'M1A2591',	'M1A26  ',	'M1A261 ',	'M1A2610',	'M1A2611',	'M1A262 ',	'M1A2620',	'M1A2621',	'M1A269 ',	'M1A2690',	'M1A2691',	'M1A27  ',	'M1A271 ',	'M1A2710',	'M1A2711',	'M1A272 ',	'M1A2720',	'M1A2721',	'M1A279 ',	'M1A2790',	'M1A2791',	'M1A28  ',	'M1A28X0',	'M1A28X1',	'M1A29  ',	'M1A29X0',	'M1A29X1',	'M1A3   ',	'M1A30  ',	'M1A30X0',	'M1A30X1',	'M1A31  ',	'M1A311 ',	'M1A3110',	'M1A3111',	'M1A312 ',	'M1A3120',	'M1A3121',	'M1A319 ',	'M1A3190',	'M1A3191',	'M1A32  ',	'M1A321 ',	'M1A3210',	'M1A3211',	'M1A322 ',	'M1A3220',	'M1A3221',	'M1A329 ',	'M1A3290',	'M1A3291',	'M1A33  ',	'M1A331 ',	'M1A3310',	'M1A3311',	'M1A332 ',	'M1A3320',	'M1A3321',	'M1A339 ',	'M1A3390',	'M1A3391',	'M1A34  ',	'M1A341 ',	'M1A3410',	'M1A3411',	'M1A342 ',	'M1A3420',	'M1A3421',	'M1A349 ',	'M1A3490',	'M1A3491',	'M1A35  ',	'M1A351 ',	'M1A3510',	'M1A3511',	'M1A352 ',	'M1A3520',	'M1A3521',	'M1A359 ',	'M1A3590',	'M1A3591',	'M1A36  ',	'M1A361 ',	'M1A3610',	'M1A3611',	'M1A362 ',	'M1A3620',	'M1A3621',	'M1A369 ',	'M1A3690',	'M1A3691',	'M1A37  ',	'M1A371 ',	'M1A3710',	'M1A3711',	'M1A372 ',	'M1A3720',	'M1A3721',	'M1A379 ',	'M1A3790',	'M1A3791',	'M1A38  ',	'M1A38X0',	'M1A38X1',	'M1A39  ',	'M1A39X0',	'M1A39X1',	'M1A4   ',	'M1A40  ',	'M1A40X0',	'M1A40X1',	'M1A41  ',	'M1A411 ',	'M1A4110',	'M1A4111',	'M1A412 ',	'M1A4120',	'M1A4121',	'M1A419 ',	'M1A4190',	'M1A4191',	'M1A42  ',	'M1A421 ',	'M1A4210',	'M1A4211',	'M1A422 ',	'M1A4220',	'M1A4221',	'M1A429 ',	'M1A4290',	'M1A4291',	'M1A43  ',	'M1A431 ',	'M1A4310',	'M1A4311',	'M1A432 ',	'M1A4320',	'M1A4321',	'M1A439 ',	'M1A4390',	'M1A4391',	'M1A44  ',	'M1A441 ',	'M1A4410',	'M1A4411',	'M1A442 ',	'M1A4420',	'M1A4421',	'M1A449 ',	'M1A4490',	'M1A4491',	'M1A45  ',	'M1A451 ',	'M1A4510',	'M1A4511',	'M1A452 ',	'M1A4520',	'M1A4521',	'M1A459 ',	'M1A4590',	'M1A4591',	'M1A46  ',	'M1A461 ',	'M1A4610',	'M1A4611',	'M1A462 ',	'M1A4620',	'M1A4621',	'M1A469 ',	'M1A4690',	'M1A4691',	'M1A47  ',	'M1A471 ',	'M1A4710',	'M1A4711',	'M1A472 ',	'M1A4720',	'M1A4721',	'M1A479 ',	'M1A4790',	'M1A4791',	'M1A48  ',	'M1A48X0',	'M1A48X1',	'M1A49  ',	'M1A49X0',	'M1A49X1',	'M1A9   ',	'M1A9XX0',	'M1A9XX1',	'V775   ')

CREATE TABLE cholecystectomy_gout
AS
SELECT a.subject_id,a.hadm_id,icd_code_gout,seq_num_gout
FROM cholecystectomy_6_copy1  a
LEFT JOIN
gout b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_gout,seq_num_gout


DELETE 
FROM cholecystectomy_gout_copy1
WHERE (subject_id,seq_num_gout)NOT IN(
SELECT subject_id,MIN(seq_num_gout)min_seq_num_gout
FROM cholecystectomy_gout_copy1 
GROUP BY subject_id
);

CREATE TABLE cholecystectomy_osteoporosis
AS
SELECT a.subject_id,a.hadm_id,icd_code_osteoporosis,seq_num_osteoporosis
FROM cholecystectomy_6_copy1  a
LEFT JOIN
osteoporosis b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_osteoporosis,seq_num_osteoporosis



CREATE TABLE cholecystectomy_pulmonary_embolism
AS
SELECT a.subject_id,a.hadm_id,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism
FROM cholecystectomy_6_copy1  a
LEFT JOIN
pulmonary_embolism b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism


DELETE 
FROM cholecystectomy_pulmonary_embolism_copy1
WHERE (subject_id,seq_num_pulmonary_embolism)NOT IN(
SELECT subject_id,MIN(seq_num_pulmonary_embolism)min_seq_num_pulmonary_embolism
FROM cholecystectomy_pulmonary_embolism_copy1 
GROUP BY subject_id
);



CREATE TABLE protein_calorie_malnutrition
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('262',	'2632',	'2638',	'2639',	'E43',	'E44',	'E440',	'E441',	'E45',	'E46',	'E640')

CREATE TABLE cholecystectomy_protein_calorie_malnutrition
AS
SELECT a.subject_id,a.hadm_id,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition
FROM cholecystectomy_6_copy1  a
LEFT JOIN
protein_calorie_malnutrition b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition


CREATE TABLE cholecystitis
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('57400',	'57401',	'57410',	'57411',	'57420',	'57421',	'57430',	'57431',	'57440',	'57441',	'57450',	'57451',	'57460',	'57461',	'57470',	'57471',	'57480',	'57481',	'57490',	'57491',	'5750',	'57510',	'57511',	'57512',	'K800   ',	'K8000  ',	'K8001  ',	'K801   ',	'K8010  ',	'K8011  ',	'K8012  ',	'K8013  ',	'K8018  ',	'K8019  ',	'K802   ',	'K8020  ',	'K8021  ',	'K804   ',	'K8040  ',	'K8041  ',	'K8042  ',	'K8043  ',	'K8044  ',	'K8045  ',	'K8046  ',	'K8047  ',	'K805   ',	'K8050  ',	'K8051  ',	'K806   ',	'K8060  ',	'K8061  ',	'K8062  ',	'K8063  ',	'K8064  ',	'K8065  ',	'K8066  ',	'K8067  ',	'K807   ',	'K8070  ',	'K8071  ',	'K81    ',	'K810   ',	'K811   ',	'K812   ',	'K819   ',	'K82A1  ',	'K82A2  ')


CREATE TABLE cholecystectomy_cholecystitis
AS
SELECT a.subject_id,a.hadm_id,icd_code_cholecystitis,seq_num_cholecystitis
FROM cholecystectomy_6_copy1  a
LEFT JOIN
cholecystitis b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_cholecystitis,seq_num_cholecystitis


DELETE 
FROM cholecystectomy_cholecystitis_copy1
WHERE (subject_id,seq_num_cholecystitis)NOT IN(
SELECT subject_id,MIN(seq_num_cholecystitis)min_seq_num_cholecystitis
FROM cholecystectomy_cholecystitis_copy1 
GROUP BY subject_id
);


CREATE TABLE postprocedural_fever
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('R5082')


CREATE TABLE cholecystectomy_postprocedural_fever
AS
SELECT a.subject_id,a.hadm_id,icd_code_postprocedural_fever,seq_num_postprocedural_fever
FROM cholecystectomy_6_copy1  a
LEFT JOIN
postprocedural_fever b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_postprocedural_fever,seq_num_postprocedural_fever



CREATE TABLE vomiting
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('78701',	'78703',	'R11',	'R111',	'R1110',	'R1111',	'R1112',	'R1113',	'R112')


CREATE TABLE cholecystectomy_vomiting
AS
SELECT a.subject_id,a.hadm_id,icd_code_vomiting,seq_num_vomiting
FROM cholecystectomy_6_copy1  a
LEFT JOIN
vomiting b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_vomiting,seq_num_vomiting



CREATE TABLE infection
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('37960',	'37961',	'37962',	'37963',	'99859',	'H594',	'H5940',	'H5941',	'H5942','H5943')

CREATE TABLE cholecystectomy_infection
AS
SELECT a.subject_id,a.hadm_id,icd_code_infection,seq_num_infection
FROM cholecystectomy_6_copy1  a
LEFT JOIN
infection b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_infection,seq_num_infection


CREATE TABLE subcutaneous_emphysema
AS
SELECT
subject_id,hadm_id,icd_code,seq_num
FROM
mimiciv.mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('9587',	'T797',	'T797XXA',	'T797XXD',	'T797XXS')


CREATE TABLE cholecystectomy_subcutaneous_emphysema
AS
SELECT a.subject_id,a.hadm_id,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema
FROM cholecystectomy_6_copy1  a
LEFT JOIN
subcutaneous_emphysema b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema

--合并疾病
CREATE TABLE cholecystectomy_jibin
AS
SELECT a.subject_id,a.hadm_id,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema
FROM cholecystectomy_6_copy1  a
LEFT JOIN
cholecystectomy_hypertension_copy1 b1	
ON a.hadm_id=b1.hadm_id
LEFT JOIN
cholecystectomy_hyperlipidemia_copy1 b2
ON a.hadm_id=b2.hadm_id
LEFT JOIN
cholecystectomy_gout_copy1 b3
ON a.hadm_id=b3.hadm_id
LEFT JOIN
cholecystectomy_osteoporosis b4
ON a.hadm_id=b4.hadm_id
LEFT JOIN
cholecystectomy_pulmonary_embolism_copy1 b5
ON a.hadm_id=b5.hadm_id
LEFT JOIN
cholecystectomy_protein_calorie_malnutrition b6
ON a.hadm_id=b6.hadm_id
LEFT JOIN
cholecystectomy_cholecystitis_copy1 b7
ON a.hadm_id=b7.hadm_id
LEFT JOIN
cholecystectomy_postprocedural_fever b8
ON a.hadm_id=b8.hadm_id
LEFT JOIN
cholecystectomy_vomiting b9
ON a.hadm_id=b9.hadm_id
LEFT JOIN
cholecystectomy_infection b10
ON a.hadm_id=b10.hadm_id
LEFT JOIN
cholecystectomy_subcutaneous_emphysema b11
ON a.hadm_id=b11.hadm_id
GROUP BY a.subject_id,a.hadm_id,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema

--入院24h的实验室指标（血液，生化）
--实验室指标

CREATE TABLE cholecystectomy_albumin
AS
SELECT a.subject_id,a.hadm_id,charttime_albumin,storetime_albumin,itemid_albumin,valuenum_albumin,valueuom_albumin
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
albumin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_albumin,storetime_albumin,itemid_albumin,valuenum_albumin,valueuom_albumin;



CREATE TABLE cholecystectomy_alt
AS
SELECT a.subject_id,a.hadm_id,charttime_alt,storetime_alt,itemid_alt,valuenum_alt,valueuom_alt
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
alt b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_alt,storetime_alt,itemid_alt,valuenum_alt,valueuom_alt;


CREATE TABLE cholecystectomy_anion_gap
AS
SELECT a.subject_id,a.hadm_id,charttime_anion_gap,storetime_anion_gap,itemid_anion_gap,valuenum_anion_gap,valueuom_anion_gap
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
anion_gap b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_anion_gap,storetime_anion_gap,itemid_anion_gap,valuenum_anion_gap,valueuom_anion_gap;


CREATE TABLE cholecystectomy_ast
AS
SELECT a.subject_id,a.hadm_id,charttime_ast,storetime_ast,itemid_ast,valuenum_ast,valueuom_ast
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
ast b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_ast,storetime_ast,itemid_ast,valuenum_ast,valueuom_ast;

CREATE TABLE cholecystectomy_bicarbonate
AS
SELECT a.subject_id,a.hadm_id,charttime_bicarbonate,storetime_bicarbonate,itemid_bicarbonate,valuenum_bicarbonate,valueuom_bicarbonate
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
bicarbonate b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_bicarbonate,storetime_bicarbonate,itemid_bicarbonate,valuenum_bicarbonate,valueuom_bicarbonate;

CREATE TABLE cholecystectomy_blood_urea_nitrogen
AS
SELECT a.subject_id,a.hadm_id,charttime_blood_urea_nitrogen,storetime_blood_urea_nitrogen,itemid_blood_urea_nitrogen,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
blood_urea_nitrogen b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_blood_urea_nitrogen,storetime_blood_urea_nitrogen,itemid_blood_urea_nitrogen,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen;

CREATE TABLE cholecystectomy_c_reactive_protein
AS
SELECT a.subject_id,a.hadm_id,charttime_c_reactive_protein,storetime_c_reactive_protein,itemid_c_reactive_protein,valuenum_c_reactive_protein,valueuom_c_reactive_protein
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
c_reactive_protein b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_c_reactive_protein,storetime_c_reactive_protein,itemid_c_reactive_protein,valuenum_c_reactive_protein,valueuom_c_reactive_protein;


CREATE TABLE cholecystectomy_calcium
AS
SELECT a.subject_id,a.hadm_id,charttime_calcium,storetime_calcium,itemid_calcium,valuenum_calcium,valueuom_calcium
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
calcium b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_calcium,storetime_calcium,itemid_calcium,valuenum_calcium,valueuom_calcium;

CREATE TABLE cholecystectomy_chloride
AS
SELECT a.subject_id,a.hadm_id,charttime_chloride,storetime_chloride,itemid_chloride,valuenum_chloride,valueuom_chloride
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
chloride b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_chloride,storetime_chloride,itemid_chloride,valuenum_chloride,valueuom_chloride;

CREATE TABLE cholecystectomy_creatinine
AS
SELECT a.subject_id,a.hadm_id,charttime_creatinine,storetime_creatinine,itemid_creatinine,valuenum_creatinine,valueuom_creatinine
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
creatinine b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_creatinine,storetime_creatinine,itemid_creatinine,valuenum_creatinine,valueuom_creatinine;


CREATE TABLE cholecystectomy_glucose
AS
SELECT a.subject_id,a.hadm_id,charttime_glucose,storetime_glucose,itemid_glucose,valuenum_glucose,valueuom_glucose
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
glucose b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_glucose,storetime_glucose,itemid_glucose,valuenum_glucose,valueuom_glucose;


CREATE TABLE cholecystectomy_hdl
AS
SELECT a.subject_id,a.hadm_id,charttime_hdl,storetime_hdl,itemid_hdl,valuenum_hdl,valueuom_hdl
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
hdl b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_hdl,storetime_hdl,itemid_hdl,valuenum_hdl,valueuom_hdl;

CREATE TABLE cholecystectomy_hematocrit
AS
SELECT a.subject_id,a.hadm_id,charttime_hematocrit,storetime_hematocrit,itemid_hematocrit,valuenum_hematocrit,valueuom_hematocrit
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
hematocrit b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_hematocrit,storetime_hematocrit,itemid_hematocrit,valuenum_hematocrit,valueuom_hematocrit;

CREATE TABLE cholecystectomy_hemoglobin
AS
SELECT a.subject_id,a.hadm_id,charttime_hemoglobin,storetime_hemoglobin,itemid_hemoglobin,valuenum_hemoglobin,valueuom_hemoglobin
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
hemoglobin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_hemoglobin,storetime_hemoglobin,itemid_hemoglobin,valuenum_hemoglobin,valueuom_hemoglobin;


CREATE TABLE cholecystectomy_inr
AS
SELECT a.subject_id,a.hadm_id,charttime_inr,storetime_inr,itemid_inr,valuenum_inr,valueuom_inr
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
inr b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_inr,storetime_inr,itemid_inr,valuenum_inr,valueuom_inr;

CREATE TABLE cholecystectomy_ldl
AS
SELECT a.subject_id,a.hadm_id,charttime_ldl,storetime_ldl,itemid_ldl,valuenum_ldl,valueuom_ldl
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
ldl b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_ldl,storetime_ldl,itemid_ldl,valuenum_ldl,valueuom_ldl;

CREATE TABLE cholecystectomy_pco2
AS
SELECT a.subject_id,a.hadm_id,charttime_pco2,storetime_pco2,itemid_pco2,valuenum_pco2,valueuom_pco2
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
pco2 b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_pco2,storetime_pco2,itemid_pco2,valuenum_pco2,valueuom_pco2;

CREATE TABLE cholecystectomy_ph
AS
SELECT a.subject_id,a.hadm_id,charttime_ph,storetime_ph,itemid_ph,valuenum_ph,valueuom_ph
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
ph b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_ph,storetime_ph,itemid_ph,valuenum_ph,valueuom_ph;
CREATE TABLE cholecystectomy_platelets
AS
SELECT a.subject_id,a.hadm_id,charttime_platelets,storetime_platelets,itemid_platelets,valuenum_platelets,valueuom_platelets
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
platelets b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_platelets,storetime_platelets,itemid_platelets,valuenum_platelets,valueuom_platelets;


CREATE TABLE cholecystectomy_po2
AS
SELECT a.subject_id,a.hadm_id,charttime_po2,storetime_po2,itemid_po2,valuenum_po2,valueuom_po2
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
po2 b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_po2,storetime_po2,itemid_po2,valuenum_po2,valueuom_po2;

CREATE TABLE cholecystectomy_potassium
AS
SELECT a.subject_id,a.hadm_id,charttime_potassium,storetime_potassium,itemid_potassium,valuenum_potassium,valueuom_potassium
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
potassium b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_potassium,storetime_potassium,itemid_potassium,valuenum_potassium,valueuom_potassium;

CREATE TABLE cholecystectomy_pt
AS
SELECT a.subject_id,a.hadm_id,charttime_pt,storetime_pt,itemid_pt,valuenum_pt,valueuom_pt
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
pt b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_pt,storetime_pt,itemid_pt,valuenum_pt,valueuom_pt;

CREATE TABLE cholecystectomy_rbc
AS
SELECT a.subject_id,a.hadm_id,charttime_rbc,storetime_rbc,itemid_rbc,valuenum_rbc,valueuom_rbc
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
rbc b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_rbc,storetime_rbc,itemid_rbc,valuenum_rbc,valueuom_rbc;

CREATE TABLE cholecystectomy_rdw
AS
SELECT a.subject_id,a.hadm_id,charttime_rdw,storetime_rdw,itemid_rdw,valuenum_rdw,valueuom_rdw
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
rdw b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_rdw,storetime_rdw,itemid_rdw,valuenum_rdw,valueuom_rdw;

CREATE TABLE cholecystectomy_sodium
AS
SELECT a.subject_id,a.hadm_id,charttime_sodium,storetime_sodium,itemid_sodium,valuenum_sodium,valueuom_sodium
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
sodium b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_sodium,storetime_sodium,itemid_sodium,valuenum_sodium,valueuom_sodium;

CREATE TABLE cholecystectomy_total_bilirubin
AS
SELECT a.subject_id,a.hadm_id,charttime_total_bilirubin,storetime_total_bilirubin,itemid_total_bilirubin,valuenum_total_bilirubin,valueuom_total_bilirubin
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
total_bilirubin b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_total_bilirubin,storetime_total_bilirubin,itemid_total_bilirubin,valuenum_total_bilirubin,valueuom_total_bilirubin;


CREATE TABLE cholecystectomy_total_cholesterol
AS
SELECT a.subject_id,a.hadm_id,charttime_total_cholesterol,storetime_total_cholesterol,itemid_total_cholesterol,valuenum_total_cholesterol,valueuom_total_cholesterol
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
total_cholesterol b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_total_cholesterol,storetime_total_cholesterol,itemid_total_cholesterol,valuenum_total_cholesterol,valueuom_total_cholesterol;

CREATE TABLE cholecystectomy_triglyceride
AS
SELECT a.subject_id,a.hadm_id,charttime_triglyceride,storetime_triglyceride,itemid_triglyceride,valuenum_triglyceride,valueuom_triglyceride
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
triglyceride b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_triglyceride,storetime_triglyceride,itemid_triglyceride,valuenum_triglyceride,valueuom_triglyceride;


CREATE TABLE cholecystectomy_wbc
AS
SELECT a.subject_id,a.hadm_id,charttime_wbc,storetime_wbc,itemid_wbc,valuenum_wbc,valueuom_wbc
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
wbc b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_wbc,storetime_wbc,itemid_wbc,valuenum_wbc,valueuom_wbc;

CREATE TABLE cholecystectomy_lactate
AS
SELECT a.subject_id,a.hadm_id,charttime_lactate,storetime_lactate,itemid_lactate,valuenum_lactate,valueuom_lactate
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
lactate b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_lactate,storetime_lactate,itemid_lactate,valuenum_lactate,valueuom_lactate;

DELETE 
FROM cholecystectomy_albumin_copy1 
WHERE (subject_id,charttime_albumin)NOT IN(
SELECT subject_id,MIN(charttime_albumin)min_charttime_albumin
FROM cholecystectomy_albumin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_alt_copy1 
WHERE (subject_id,charttime_alt)NOT IN(
SELECT subject_id,MIN(charttime_alt)min_charttime_alt
FROM cholecystectomy_alt_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_anion_gap_copy1 
WHERE (subject_id,charttime_anion_gap)NOT IN(
SELECT subject_id,MIN(charttime_anion_gap)min_charttime_anion_gap
FROM cholecystectomy_anion_gap_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_anion_gap_copy1 
WHERE (subject_id,storetime_anion_gap)NOT IN(
SELECT subject_id,MIN(storetime_anion_gap)min_storetime_anion_gap
FROM cholecystectomy_anion_gap_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ast_copy1 
WHERE (subject_id,charttime_ast)NOT IN(
SELECT subject_id,MIN(charttime_ast)min_charttime_ast
FROM cholecystectomy_ast_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_bicarbonate_copy1 
WHERE (subject_id,charttime_bicarbonate)NOT IN(
SELECT subject_id,MIN(charttime_bicarbonate)min_charttime_bicarbonate
FROM cholecystectomy_bicarbonate_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bicarbonate_copy1 
WHERE (subject_id,storetime_bicarbonate)NOT IN(
SELECT subject_id,MIN(storetime_bicarbonate)min_storetime_bicarbonate
FROM cholecystectomy_bicarbonate_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_blood_urea_nitrogen_copy1 
WHERE (subject_id,charttime_blood_urea_nitrogen)NOT IN(
SELECT subject_id,MIN(charttime_blood_urea_nitrogen)min_charttime_blood_urea_nitrogen
FROM cholecystectomy_blood_urea_nitrogen_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_blood_urea_nitrogen_copy1 
WHERE (subject_id,storetime_blood_urea_nitrogen)NOT IN(
SELECT subject_id,MIN(storetime_blood_urea_nitrogen )min_storetime_blood_urea_nitrogen
FROM cholecystectomy_blood_urea_nitrogen_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_calcium_copy1 
WHERE (subject_id,charttime_calcium)NOT IN(
SELECT subject_id,MIN(charttime_calcium)min_charttime_calcium
FROM cholecystectomy_calcium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_c_reactive_protein_copy1 
WHERE (subject_id,charttime_c_reactive_protein)NOT IN(
SELECT subject_id,MIN(charttime_c_reactive_protein)min_charttime_c_reactive_protein
FROM cholecystectomy_c_reactive_protein_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_chloride_copy1 
WHERE (subject_id,charttime_chloride)NOT IN(
SELECT subject_id,MIN(charttime_chloride)min_charttime_chloride
FROM cholecystectomy_chloride_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_chloride_copy1 
WHERE (subject_id,storetime_chloride)NOT IN(
SELECT subject_id,MIN(storetime_chloride)min_storetime_chloride
FROM cholecystectomy_chloride_copy1 
GROUP BY subject_id
);



DELETE 
FROM cholecystectomy_creatinine_copy1 
WHERE (subject_id,charttime_creatinine)NOT IN(
SELECT subject_id,MIN(charttime_creatinine)min_charttime_creatinine
FROM cholecystectomy_creatinine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_glucose_copy1 
WHERE (subject_id,charttime_glucose)NOT IN(
SELECT subject_id,MIN(charttime_glucose)min_charttime_glucose
FROM cholecystectomy_glucose_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_hdl_copy1 
WHERE (subject_id,charttime_hdl)NOT IN(
SELECT subject_id,MIN(charttime_hdl)min_charttime_hdl
FROM cholecystectomy_hdl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_hematocrit_copy1 
WHERE (subject_id,charttime_hematocrit)NOT IN(
SELECT subject_id,MIN(charttime_hematocrit)min_charttime_hematocrit
FROM cholecystectomy_hematocrit_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_hemoglobin_copy1 
WHERE (subject_id,charttime_hemoglobin)NOT IN(
SELECT subject_id,MIN(charttime_hemoglobin)min_charttime_hemoglobin
FROM cholecystectomy_hemoglobin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_inr_copy1 
WHERE (subject_id,charttime_inr)NOT IN(
SELECT subject_id,MIN(charttime_inr)min_charttime_inr
FROM cholecystectomy_inr_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_lactate_copy1 
WHERE (subject_id,charttime_lactate)NOT IN(
SELECT subject_id,MIN(charttime_lactate)min_charttime_lactate
FROM cholecystectomy_lactate_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ldl_copy1 
WHERE (subject_id,charttime_ldl)NOT IN(
SELECT subject_id,MIN(charttime_ldl)min_charttime_ldl
FROM cholecystectomy_ldl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_pco2_copy1 
WHERE (subject_id,charttime_pco2)NOT IN(
SELECT subject_id,MIN(charttime_pco2)min_charttime_pco2
FROM cholecystectomy_pco2_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ph_copy1 
WHERE (subject_id,charttime_ph)NOT IN(
SELECT subject_id,MIN(charttime_ph)min_charttime_ph
FROM cholecystectomy_ph_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_platelets_copy1 
WHERE (subject_id,charttime_platelets)NOT IN(
SELECT subject_id,MIN(charttime_platelets)min_charttime_platelets
FROM cholecystectomy_platelets_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_po2_copy1 
WHERE (subject_id,charttime_po2)NOT IN(
SELECT subject_id,MIN(charttime_po2)min_charttime_po2
FROM cholecystectomy_po2_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_potassium_copy1 
WHERE (subject_id,charttime_potassium)NOT IN(
SELECT subject_id,MIN(charttime_potassium)min_charttime_potassium
FROM cholecystectomy_potassium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_pt_copy1 
WHERE (subject_id,charttime_pt)NOT IN(
SELECT subject_id,MIN(charttime_pt)min_charttime_pt
FROM cholecystectomy_pt_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_rbc_copy1 
WHERE (subject_id,charttime_rbc)NOT IN(
SELECT subject_id,MIN(charttime_rbc)min_charttime_rbc
FROM cholecystectomy_rbc_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_rdw_copy1 
WHERE (subject_id,charttime_rdw)NOT IN(
SELECT subject_id,MIN(charttime_rdw)min_charttime_rdw
FROM cholecystectomy_rdw_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_sodium_copy1 
WHERE (subject_id,charttime_sodium)NOT IN(
SELECT subject_id,MIN(charttime_sodium)min_charttime_sodium
FROM cholecystectomy_sodium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_total_bilirubin_copy1 
WHERE (subject_id,charttime_total_bilirubin)NOT IN(
SELECT subject_id,MIN(charttime_total_bilirubin)min_charttime_total_bilirubin
FROM cholecystectomy_total_bilirubin_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_total_cholesterol_copy1 
WHERE (subject_id,charttime_total_cholesterol)NOT IN(
SELECT subject_id,MIN(charttime_total_cholesterol)min_charttime_total_cholesterol
FROM cholecystectomy_total_cholesterol_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_triglyceride_copy1 
WHERE (subject_id,charttime_triglyceride)NOT IN(
SELECT subject_id,MIN(charttime_triglyceride)min_charttime_triglyceride
FROM cholecystectomy_triglyceride_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_wbc_copy1 
WHERE (subject_id,charttime_wbc)NOT IN(
SELECT subject_id,MIN(charttime_wbc)min_charttime_wbc
FROM cholecystectomy_wbc_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_albumin_copy1 
WHERE (subject_id,storetime_albumin)NOT IN(
SELECT subject_id,MIN(storetime_albumin)min_storetime_albumin
FROM cholecystectomy_albumin_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_alt_copy1 
WHERE (subject_id,storetime_alt)NOT IN(
SELECT subject_id,MIN(storetime_alt)min_storetime_alt
FROM cholecystectomy_alt_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_anion_gap_copy1 
WHERE (subject_id,storetime_anion_gap)NOT IN(
SELECT subject_id,MIN(storetime_anion_gap)min_storetime_anion_gap
FROM cholecystectomy_anion_gap_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_ast_copy1 
WHERE (subject_id,storetime_ast)NOT IN(
SELECT subject_id,MIN(storetime_ast)min_storetime_ast
FROM cholecystectomy_ast_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bicarbonate_copy1 
WHERE (subject_id,storetime_bicarbonate)NOT IN(
SELECT subject_id,MIN(storetime_bicarbonate)min_storetime_bicarbonate
FROM cholecystectomy_bicarbonate_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_blood_urea_nitrogen_copy1 
WHERE (subject_id,storetime_blood_urea_nitrogen)NOT IN(
SELECT subject_id,MIN(storetime_blood_urea_nitrogen)min_storetime_blood_urea_nitrogen
FROM cholecystectomy_blood_urea_nitrogen_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_calcium_copy1 
WHERE (subject_id,storetime_calcium)NOT IN(
SELECT subject_id,MIN(storetime_calcium)min_storetime_calcium
FROM cholecystectomy_calcium_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_c_reactive_protein_copy1 
WHERE (subject_id,storetime_c_reactive_protein)NOT IN(
SELECT subject_id,MIN(storetime_c_reactive_protein)min_storetime_c_reactive_protein
FROM cholecystectomy_c_reactive_protein_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_chloride_copy1 
WHERE (subject_id,storetime_chloride)NOT IN(
SELECT subject_id,MIN(storetime_chloride)min_storetime_chloride
FROM cholecystectomy_chloride_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_creatinine_copy1 
WHERE (subject_id,storetime_creatinine)NOT IN(
SELECT subject_id,MIN(storetime_creatinine)min_storetime_creatinine
FROM cholecystectomy_creatinine_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_glucose_copy1 
WHERE (subject_id,storetime_glucose)NOT IN(
SELECT subject_id,MIN(storetime_glucose)min_storetime_glucose
FROM cholecystectomy_glucose_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ldl_copy1 
WHERE (subject_id,itemid_ldl)NOT IN(
SELECT subject_id,MIN(itemid_ldl)min_itemid_ldl
FROM cholecystectomy_ldl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_hdl_copy1 
WHERE (subject_id,storetime_hdl)NOT IN(
SELECT subject_id,MIN(storetime_hdl)min_storetime_hdl
FROM cholecystectomy_hdl_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_hematocrit_copy1 
WHERE (subject_id,storetime_hematocrit)NOT IN(
SELECT subject_id,MIN(storetime_hematocrit)min_storetime_hematocrit
FROM cholecystectomy_hematocrit_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_hemoglobin_copy1 
WHERE (subject_id,storetime_hemoglobin)NOT IN(
SELECT subject_id,MIN(storetime_hemoglobin)min_storetime_hemoglobin
FROM cholecystectomy_hemoglobin_copy1 
GROUP BY subject_id
);



DELETE 
FROM cholecystectomy_inr_copy1 
WHERE (subject_id,storetime_inr)NOT IN(
SELECT subject_id,MIN(storetime_inr)min_storetime_inr
FROM cholecystectomy_inr_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_lactate_copy1 
WHERE (subject_id,storetime_lactate)NOT IN(
SELECT subject_id,MIN(storetime_lactate)min_storetime_lactate
FROM cholecystectomy_lactate_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_ldl_copy1 
WHERE (subject_id,storetime_ldl)NOT IN(
SELECT subject_id,MIN(storetime_ldl)min_storetime_ldl
FROM cholecystectomy_ldl_copy1 
GROUP BY subject_id
);



DELETE 
FROM cholecystectomy_pco2_copy1 
WHERE (subject_id,storetime_pco2)NOT IN(
SELECT subject_id,MIN(storetime_pco2)min_storetime_pco2
FROM cholecystectomy_pco2_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ph_copy1 
WHERE (subject_id,storetime_ph)NOT IN(
SELECT subject_id,MIN(storetime_ph)min_storetime_ph
FROM cholecystectomy_ph_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_platelets_copy1 
WHERE (subject_id,storetime_platelets)NOT IN(
SELECT subject_id,MIN(storetime_platelets)min_storetime_platelets
FROM cholecystectomy_platelets_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_po2_copy1 
WHERE (subject_id,storetime_po2)NOT IN(
SELECT subject_id,MIN(storetime_po2)min_storetime_po2
FROM cholecystectomy_po2_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_potassium_copy1 
WHERE (subject_id,storetime_potassium)NOT IN(
SELECT subject_id,MIN(storetime_potassium)min_storetime_potassium
FROM cholecystectomy_potassium_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_pt_copy1 
WHERE (subject_id,storetime_pt)NOT IN(
SELECT subject_id,MIN(storetime_pt)min_storetime_pt
FROM cholecystectomy_pt_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_rbc_copy1 
WHERE (subject_id,storetime_rbc)NOT IN(
SELECT subject_id,MIN(storetime_rbc)min_storetime_rbc
FROM cholecystectomy_rbc_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_rdw_copy1 
WHERE (subject_id,storetime_rdw)NOT IN(
SELECT subject_id,MIN(storetime_rdw)min_storetime_rdw
FROM cholecystectomy_rdw_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_sodium_copy1 
WHERE (subject_id,storetime_sodium)NOT IN(
SELECT subject_id,MIN(storetime_sodium)min_storetime_sodium
FROM cholecystectomy_sodium_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_total_bilirubin_copy1 
WHERE (subject_id,storetime_total_bilirubin)NOT IN(
SELECT subject_id,MIN(storetime_total_bilirubin)min_storetime_total_bilirubin
FROM cholecystectomy_total_bilirubin_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_total_cholesterol_copy1 
WHERE (subject_id,storetime_total_cholesterol)NOT IN(
SELECT subject_id,MIN(storetime_total_cholesterol)min_storetime_total_cholesterol
FROM cholecystectomy_total_cholesterol_copy1 
GROUP BY subject_id
);



DELETE 
FROM cholecystectomy_triglyceride_copy1 
WHERE (subject_id,storetime_triglyceride)NOT IN(
SELECT subject_id,MIN(storetime_triglyceride)min_storetime_triglyceride
FROM cholecystectomy_triglyceride_copy1 
GROUP BY subject_id
);


DELETE 
FROM cholecystectomy_wbc_copy1 
WHERE (subject_id,storetime_wbc)NOT IN(
SELECT subject_id,MIN(storetime_wbc)min_storetime_wbc
FROM cholecystectomy_wbc_copy1 
GROUP BY subject_id
);


CREATE TABLE magnesium
AS
SELECT *
FROM
mimiciv.mimiciv_icu.chartevents
WHERE itemid IN ('220635')


CREATE TABLE cholecystectomy_magnesium
AS
SELECT a.subject_id,a.hadm_id,charttime_magnesium,storetime_magnesium,itemid_magnesium,valuenum_magnesium,valueuom_magnesium
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
magnesium b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_magnesium,storetime_magnesium,itemid_magnesium,valuenum_magnesium,valueuom_magnesium;


CREATE TABLE cholecystectomy_neutrophil_count
AS
SELECT a.subject_id,a.hadm_id,charttime_neutrophil_count,storetime_neutrophil_count,itemid_neutrophil_count,valuenum_neutrophil_count,valueuom_neutrophil_count
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
neutrophil_count b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_neutrophil_count,storetime_neutrophil_count,itemid_neutrophil_count,valuenum_neutrophil_count,valueuom_neutrophil_count;

CREATE TABLE cholecystectomy_uric_acid
AS
SELECT a.subject_id,a.hadm_id,charttime_uric_acid,storetime_uric_acid,itemid_uric_acid,valuenum_uric_acid,valueuom_uric_acid
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
uric_acid b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_uric_acid,storetime_uric_acid,itemid_uric_acid,valuenum_uric_acid,valueuom_uric_acid;

CREATE TABLE neutrophils
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51256')


CREATE TABLE eosinophils
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51200')

CREATE TABLE basophils
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51146')

CREATE TABLE lymphocyte
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51244')

CREATE TABLE white_blood_cells
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51301')


51117
CREATE TABLE macrophage
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51117')

50866
CREATE TABLE ammonia
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('50866')


CREATE TABLE cholesterol_ratio
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('50903')
51149
CREATE TABLE bleeding_time
AS
SELECT *
FROM
mimiciv.mimiciv_hosp.labevents
WHERE itemid IN ('51149')


CREATE TABLE cholecystectomy_neutrophils
AS
SELECT a.subject_id,a.hadm_id,charttime_neutrophils,storetime_neutrophils,itemid_neutrophils,valuenum_neutrophils,valueuom_neutrophils
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
neutrophils b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_neutrophils,storetime_neutrophils,itemid_neutrophils,valuenum_neutrophils,valueuom_neutrophils;

CREATE TABLE cholecystectomy_eosinophils
AS
SELECT a.subject_id,a.hadm_id,charttime_eosinophils,storetime_eosinophils,itemid_eosinophils,valuenum_eosinophils,valueuom_eosinophils
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
eosinophils b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_eosinophils,storetime_eosinophils,itemid_eosinophils,valuenum_eosinophils,valueuom_eosinophils;


CREATE TABLE cholecystectomy_basophils
AS
SELECT a.subject_id,a.hadm_id,charttime_basophils,storetime_basophils,itemid_basophils,valuenum_basophils,valueuom_basophils
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
basophils b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_basophils,storetime_basophils,itemid_basophils,valuenum_basophils,valueuom_basophils;


CREATE TABLE cholecystectomy_lymphocyte
AS
SELECT a.subject_id,a.hadm_id,charttime_lymphocyte,storetime_lymphocyte,itemid_lymphocyte,valuenum_lymphocyte,valueuom_lymphocyte
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
lymphocyte b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_lymphocyte,storetime_lymphocyte,itemid_lymphocyte,valuenum_lymphocyte,valueuom_lymphocyte;

CREATE TABLE cholecystectomy_white_blood_cells
AS
SELECT a.subject_id,a.hadm_id,charttime_white_blood_cells,storetime_white_blood_cells,itemid_white_blood_cells,valuenum_white_blood_cells,valueuom_white_blood_cells
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
white_blood_cells b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_white_blood_cells,storetime_white_blood_cells,itemid_white_blood_cells,valuenum_white_blood_cells,valueuom_white_blood_cells


CREATE TABLE cholecystectomy_macrophage
AS
SELECT a.subject_id,a.hadm_id,charttime_macrophage,storetime_macrophage,itemid_macrophage,valuenum_macrophage,valueuom_macrophage
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
macrophage b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_macrophage,storetime_macrophage,itemid_macrophage,valuenum_macrophage,valueuom_macrophage;


CREATE TABLE cholecystectomy_ammonia
AS
SELECT a.subject_id,a.hadm_id,charttime_ammonia,storetime_ammonia,itemid_ammonia,valuenum_ammonia,valueuom_ammonia
FROM
cholecystectomy_7_copy1 a
nbLEFT JOIN
ammonia b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_ammonia,storetime_ammonia,itemid_ammonia,valuenum_ammonia,valueuom_ammonia;


CREATE TABLE cholecystectomy_cholesterol_ratio
AS
SELECT a.subject_id,a.hadm_id,charttime_cholesterol_ratio,storetime_cholesterol_ratio,itemid_cholesterol_ratio,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
cholesterol_ratio b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_cholesterol_ratio,storetime_cholesterol_ratio,itemid_cholesterol_ratio,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio;

CREATE TABLE cholecystectomy_bleeding_time
AS
SELECT a.subject_id,a.hadm_id,charttime_bleeding_time,storetime_bleeding_time,itemid_bleeding_time,valuenum_bleeding_time,valueuom_bleeding_time
FROM
cholecystectomy_7_copy1 a
LEFT JOIN
bleeding_time b
ON a.hadm_id=b.hadm_id
GROUP BY a.subject_id,a.hadm_id,charttime_bleeding_time,storetime_bleeding_time,itemid_bleeding_time,valuenum_bleeding_time,valueuom_bleeding_time;


DELETE 
FROM cholecystectomy_magnesium_copy1 
WHERE (subject_id,charttime_magnesium)NOT IN(
SELECT subject_id,MIN(charttime_magnesium)min_charttime_magnesium
FROM cholecystectomy_magnesium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_magnesium_copy1 
WHERE (subject_id,storetime_magnesium)NOT IN(
SELECT subject_id,MIN(storetime_magnesium)min_storetime_magnesium
FROM cholecystectomy_magnesium_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_uric_acid_copy1 
WHERE (subject_id,charttime_uric_acid)NOT IN(
SELECT subject_id,MIN(charttime_uric_acid)min_charttime_uric_acid
FROM cholecystectomy_uric_acid_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_uric_acid_copy1 
WHERE (subject_id,storetime_uric_acid)NOT IN(
SELECT subject_id,MIN(storetime_uric_acid)min_storetime_uric_acid
FROM cholecystectomy_uric_acid_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_neutrophils_copy1 
WHERE (subject_id,charttime_neutrophils)NOT IN(
SELECT subject_id,MIN(charttime_neutrophils)min_charttime_neutrophils
FROM cholecystectomy_neutrophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_neutrophils_copy1 
WHERE (subject_id,storetime_neutrophils)NOT IN(
SELECT subject_id,MIN(storetime_neutrophils)min_storetime_neutrophils
FROM cholecystectomy_neutrophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_eosinophils_copy1 
WHERE (subject_id,charttime_eosinophils)NOT IN(
SELECT subject_id,MIN(charttime_eosinophils)min_charttime_eosinophils
FROM cholecystectomy_eosinophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_eosinophils_copy1 
WHERE (subject_id,storetime_eosinophils)NOT IN(
SELECT subject_id,MIN(storetime_eosinophils)min_storetime_eosinophils
FROM cholecystectomy_eosinophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_basophils_copy1 
WHERE (subject_id,charttime_basophils)NOT IN(
SELECT subject_id,MIN(charttime_basophils)min_charttime_basophils
FROM cholecystectomy_basophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_basophils_copy1 
WHERE (subject_id,storetime_basophils)NOT IN(
SELECT subject_id,MIN(storetime_basophils)min_storetime_basophils
FROM cholecystectomy_basophils_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_lymphocyte_copy1 
WHERE (subject_id,charttime_lymphocyte)NOT IN(
SELECT subject_id,MIN(charttime_lymphocyte)min_charttime_lymphocyte
FROM cholecystectomy_lymphocyte_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_lymphocyte_copy1 
WHERE (subject_id,storetime_lymphocyte)NOT IN(
SELECT subject_id,MIN(storetime_lymphocyte)min_storetime_lymphocyte
FROM cholecystectomy_lymphocyte_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_white_blood_cells_copy1 
WHERE (subject_id,charttime_white_blood_cells)NOT IN(
SELECT subject_id,MIN(charttime_white_blood_cells)min_charttime_white_blood_cells
FROM cholecystectomy_white_blood_cells_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_white_blood_cells_copy1 
WHERE (subject_id,storetime_white_blood_cells)NOT IN(
SELECT subject_id,MIN(storetime_white_blood_cells)min_storetime_white_blood_cells
FROM cholecystectomy_white_blood_cells_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_macrophage_copy1 
WHERE (subject_id,charttime_macrophage)NOT IN(
SELECT subject_id,MIN(charttime_macrophage)min_charttime_macrophage
FROM cholecystectomy_macrophage_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_macrophage_copy1 
WHERE (subject_id,storetime_macrophage)NOT IN(
SELECT subject_id,MIN(storetime_macrophage)min_storetime_macrophage
FROM cholecystectomy_macrophage_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ammonia_copy1 
WHERE (subject_id,charttime_ammonia)NOT IN(
SELECT subject_id,MIN(charttime_ammonia)min_charttime_ammonia
FROM cholecystectomy_ammonia_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_ammonia_copy1 
WHERE (subject_id,storetime_ammonia)NOT IN(
SELECT subject_id,MIN(storetime_ammonia)min_storetime_ammonia
FROM cholecystectomy_ammonia_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_cholesterol_ratio_copy1 
WHERE (subject_id,charttime_cholesterol_ratio)NOT IN(
SELECT subject_id,MIN(charttime_cholesterol_ratio)min_charttime_cholesterol_ratio
FROM cholecystectomy_cholesterol_ratio_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_cholesterol_ratio_copy1 
WHERE (subject_id,storetime_cholesterol_ratio)NOT IN(
SELECT subject_id,MIN(storetime_cholesterol_ratio)min_storetime_cholesterol_ratio
FROM cholecystectomy_cholesterol_ratio_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bleeding_time_copy1 
WHERE (subject_id,charttime_bleeding_time)NOT IN(
SELECT subject_id,MIN(charttime_bleeding_time)min_charttime_bleeding_time
FROM cholecystectomy_bleeding_time_copy1 
GROUP BY subject_id
);

DELETE 
FROM cholecystectomy_bleeding_time_copy1 
WHERE (subject_id,storetime_bleeding_time)NOT IN(
SELECT subject_id,MIN(storetime_bleeding_time)min_storetime_bleeding_time
FROM cholecystectomy_bleeding_time_copy1 
GROUP BY subject_id
);


--合并
CREATE TABLE cholecystectomy_lab
AS
SELECT a.subject_id,a.hadm_id,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,b22.valuenum_rbc,b22.valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time
FROM
cholecystectomy_lab a
LEFT JOIN
cholecystectomy_albumin_copy1 b1
ON a.hadm_id=b1.hadm_id
LEFT JOIN
cholecystectomy_alt_copy1 b2
ON a.hadm_id=b2.hadm_id
LEFT JOIN
cholecystectomy_ast_copy1 b3
ON a.hadm_id=b3.hadm_id
LEFT JOIN
cholecystectomy_blood_urea_nitrogen_copy1 b4
ON a.hadm_id=b4.hadm_id
LEFT JOIN
cholecystectomy_bicarbonate_copy1 b5
ON a.hadm_id=b5.hadm_id
LEFT JOIN
cholecystectomy_calcium_copy1 b6
ON a.hadm_id=b6.hadm_id
LEFT JOIN
cholecystectomy_c_reactive_protein_copy1 b7
ON a.hadm_id=b7.hadm_id
LEFT JOIN
cholecystectomy_chloride_copy1 b8
ON a.hadm_id=b8.hadm_id
LEFT JOIN
cholecystectomy_creatinine_copy1 b9
ON a.hadm_id=b9.hadm_id
LEFT JOIN
cholecystectomy_glucose_copy1 b10
ON a.hadm_id=b10.hadm_id
LEFT JOIN
cholecystectomy_hdl_copy1 b11
ON a.hadm_id=b11.hadm_id
LEFT JOIN
cholecystectomy_hematocrit_copy1 b12
ON a.hadm_id=b12.hadm_id
LEFT JOIN
cholecystectomy_inr_copy1 b13
ON a.hadm_id=b13.hadm_id
LEFT JOIN
cholecystectomy_lactate_copy1 b14
ON a.hadm_id=b14.hadm_id
LEFT JOIN
cholecystectomy_ldl_copy1 b15
ON a.hadm_id=b15.hadm_id
LEFT JOIN
cholecystectomy_pco2_copy1 b16
ON a.hadm_id=b16.hadm_id
LEFT JOIN
cholecystectomy_ph_copy1 b17
ON a.hadm_id=b17.hadm_id
LEFT JOIN
cholecystectomy_platelets_copy1 b18
ON a.hadm_id=b18.hadm_id
LEFT JOIN
cholecystectomy_po2_copy1 b19
ON a.hadm_id=b19.hadm_id
LEFT JOIN
cholecystectomy_potassium_copy1 b20
ON a.hadm_id=b20.hadm_id
LEFT JOIN
cholecystectomy_pt_copy1 b21
ON a.hadm_id=b21.hadm_id
LEFT JOIN
cholecystectomy_rbc_copy1 b22
ON a.hadm_id=b22.hadm_id
LEFT JOIN
cholecystectomy_triglyceride_copy1  b23
ON a.hadm_id=b23.hadm_id
LEFT JOIN
cholecystectomy_wbc_copy1 b24
ON a.hadm_id=b24.hadm_id
LEFT JOIN
cholecystectomy_lymphocyte_copy1 b25
ON a.hadm_id=b25.hadm_id
LEFT JOIN
cholecystectomy_uric_acid_copy1 b26
ON a.hadm_id=b26.hadm_id
LEFT JOIN
cholecystectomy_neutrophils_copy1 b27
ON a.hadm_id=b27.hadm_id
LEFT JOIN
cholecystectomy_eosinophils_copy1 b28
ON a.hadm_id=b28.hadm_id
LEFT JOIN
cholecystectomy_macrophage_copy1 b29
ON a.hadm_id=b29.hadm_id
LEFT JOIN
cholecystectomy_white_blood_cells_copy1 b30
ON a.hadm_id=b30.hadm_id
LEFT JOIN
cholecystectomy_bleeding_time_copy1 b31
ON a.hadm_id=b31.hadm_id
LEFT JOIN
cholecystectomy_anion_gap_copy1 b32
ON a.hadm_id=b32.hadm_id
LEFT JOIN
cholecystectomy_rdw_copy1 b33
ON a.hadm_id=b33.hadm_id
LEFT JOIN
cholecystectomy_sodium_copy1 b34
ON a.hadm_id=b34.hadm_id
LEFT JOIN
cholecystectomy_total_cholesterol_copy1 b35
ON a.hadm_id=b35.hadm_id
LEFT JOIN
cholecystectomy_magnesium_copy1 b36
ON a.hadm_id=b36.hadm_id
LEFT JOIN
cholecystectomy_basophils_copy1 b37
ON a.hadm_id=b37.hadm_id
LEFT JOIN
cholecystectomy_ammonia_copy1 b38
ON a.hadm_id=b38.hadm_id
LEFT JOIN
cholecystectomy_cholesterol_ratio_copy1 b39
ON a.hadm_id=b39.hadm_id
GROUP BY a.subject_id,a.hadm_id,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,b22.valuenum_rbc,b22.valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time


--to
CREATE TABLE cholecystectomy_tot
AS
SELECT a.subject_id,a.hadm_id,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,valuenum_rbc,valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii
FROM
cholecystectomy_lab a
LEFT JOIN
cholecystectomy_durg_t b1
ON a.hadm_id=b1.hadm_id
LEFT JOIN
cholecystectomy_jibin b2
ON a.hadm_id=b2.hadm_id
LEFT JOIN
cholecystectomy_7_copy1 b3
ON a.hadm_id=b3.hadm_id
GROUP BY a.subject_id,a.hadm_id,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,valuenum_rbc,valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii


CREATE TABLE cholecystectomy_ventilation
AS
SELECT a.subject_id,a.hadm_id,starttime,endtime,ventilation_status,DATETIME_DIFF(endtime,starttime, 'HOUR') as ventilation_time,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,valuenum_rbc,valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii
FROM
cholecystectomy_tot a
LEFT JOIN
mimiciv_derived.ventilation b
ON a.stay_id=b.stay_id
GROUP BY a.subject_id,a.hadm_id,starttime,endtime,ventilation_status,ventilation_time,valuenum_albumin,valueuom_albumin,valuenum_alt,valueuom_alt,valuenum_anion_gap,valueuom_anion_gap,valuenum_ast,valueuom_ast,valuenum_bicarbonate,valueuom_bicarbonate,valuenum_blood_urea_nitrogen,valueuom_blood_urea_nitrogen,valuenum_c_reactive_protein,valueuom_c_reactive_protein,valuenum_calcium,valueuom_calcium,valuenum_chloride,valueuom_chloride,valuenum_creatinine,valueuom_creatinine,valuenum_hdl,valueuom_hdl,valuenum_hematocrit,valueuom_hematocrit,valuenum_inr,valueuom_inr,valuenum_ldl,valueuom_ldl,valuenum_pco2,valueuom_pco2,valuenum_ph,valueuom_ph,valuenum_platelets,valueuom_platelets,valuenum_po2,valueuom_po2,valuenum_potassium,valueuom_potassium,valuenum_pt,valueuom_pt,valuenum_rbc,valueuom_rbc,valuenum_rdw,valueuom_rdw,valuenum_sodium,valueuom_sodium,valuenum_total_cholesterol,valueuom_total_cholesterol,valuenum_wbc,valueuom_wbc,valuenum_lactate,valueuom_lactate,valuenum_magnesium,valueuom_magnesium,valuenum_uric_acid,valueuom_uric_acid,valuenum_neutrophils,valueuom_neutrophils,valuenum_eosinophils,valueuom_eosinophils,valuenum_basophils,valueuom_basophils,valuenum_lymphocyte,valueuom_lymphocyte,valuenum_white_blood_cells,valueuom_white_blood_cells,valuenum_macrophage,valueuom_macrophage,valuenum_ammonia,valueuom_ammonia,valuenum_cholesterol_ratio,valueuom_cholesterol_ratio,valuenum_bleeding_time,valueuom_bleeding_time,dose_val_rx_propofol,dose_unit_rx_propofol,form_val_disp_propofol,form_unit_disp_propofol,dose_val_rx_midazolam,dose_unit_rx_midazolam,form_val_disp_midazolam,form_unit_disp_midazolam,dose_val_rx_fentanyl,dose_unit_rx_fentanyl,form_val_disp_fentanyl,form_unit_disp_fentanyl,dose_val_rx_dexmedetomidine,dose_unit_rx_dexmedetomidine,form_val_disp_dexmedetomidine,form_unit_disp_dexmedetomidine,dose_val_rx_amoxicillin_amoxicillin,dose_unit_rx_amoxicillin_amoxicillin,form_val_disp_amoxicillin_amoxicillin,form_unit_disp_amoxicillin_amoxicillin,dose_val_rx_atropine_sulfate,dose_unit_rx_atropine_sulfate,form_val_disp_atropine_sulfate,form_unit_disp_atropine_sulfate,dose_val_rx_ciprofloxacin,dose_unit_rx_ciprofloxacin,form_val_disp_ciprofloxacin,form_unit_disp_ciprofloxacin,dose_val_rx_diclofenac_sodiu,dose_unit_rx_diclofenac_sodiu,form_val_disp_diclofenac_sodiu,form_unit_disp_diclofenac_sodiu,dose_val_rx_ertapenem_sodium,dose_unit_rx_ertapenem_sodium,form_val_disp_ertapenem_sodium,form_unit_disp_ertapenem_sodium,dose_val_rx_indomethacin,dose_unit_rx_indomethacin,form_val_disp_indomethacin,form_unit_disp_indomethacin,dose_val_rx_levofloxacin,dose_unit_rx_levofloxacin,form_val_disp_levofloxacin,form_unit_disp_levofloxacin,dose_val_rx_meperidine,dose_unit_rx_meperidine,form_val_disp_meperidine,form_unit_disp_meperidine,dose_val_rx_penicillin,dose_unit_rx_penicillin,form_val_disp_penicillin,form_unit_disp_penicillin,icd_code_hypertension,seq_num_hypertension,icd_code_hyperlipidemia,seq_num_hyperlipidemia,icd_code_gout,seq_num_gout,icd_code_osteoporosis,seq_num_osteoporosis,icd_code_pulmonary_embolism,seq_num_pulmonary_embolism,icd_code_protein_calorie_malnutrition,seq_num_protein_calorie_malnutrition,icd_code_cholecystitis,seq_num_cholecystitis,icd_code_postprocedural_fever,seq_num_postprocedural_fever,icd_code_vomiting,seq_num_vomiting,icd_code_infection,seq_num_infection,icd_code_subcutaneous_emphysema,seq_num_subcutaneous_emphysema,a.icd_code,admittime,dischtime,race,hospital_expire_flag,marital_status,los_zhuyuan,long_title,gender,anchor_age,dod,os_time,a.stay_id,intime,outtime,los,myocardial_infarct,congestive_heart_failure,peripheral_vascular_disease,cerebrovascular_disease,dementia,chronic_pulmonary_disease,rheumatic_disease,peptic_ulcer_disease,mild_liver_disease,diabetes_without_cc,diabetes_with_cc,paraplegia,renal_disease,malignant_cancer,severe_liver_disease,metastatic_solid_tumor,aids,charlson_comorbidity_index,sofa,apsiii