logoChat01.ai

WITH basic AS ( WITH --纳入的诊断 --房颤 Atr...

Criado em: 19 de junho de 2025

Respondido usando GPT-4.5 por Chat01

Pergunta

WITH
basic AS (
WITH
--纳入的诊断
--房颤 Atrial fibrillation
includedDiagnose1 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42731')
),
--房扑 Atrial flutter
includedDiagnose2 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42732')
),
--阵发性心房颤动 Paroxysmal atrial fibrillation
includedDiagnose3 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I480')
),
--持续性心房颤动 Persistent atrial fibrillation
includedDiagnose4 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I481')
),
--其他持续性心房颤动 Other persistent atrial fibrillation
includedDiagnose5 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4819')
),
--慢性心房颤动 Chronic atrial fibrillation
includedDiagnose6 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I482')
),
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
includedDiagnose7 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4820')
),
--永久性心房颤动 Permanent atrial fibrillation
includedDiagnose8 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4821')
),
--未特指的心房颤动 Unspecified atrial fibrillation
includedDiagnose9 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4891')
),
--未特指的心房扑动 Unspecified atrial flutter
includedDiagnose10 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4892')
),
--心房颤动和扑动 Atrial fibrillation and flutter
includedDiagnose11 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I48')
),
--未特指的心房颤动和心房扑动 Unspecified atrial fibrillation and atrial flutter
includedDiagnose12 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I489')
),
--典型心房扑动 Typical atrial flutter
includedDiagnose13 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I483')
)
SELECT
DISTINCT ON (icu.subject_id)
icu.subject_id,
icu.stay_id,
icu.hadm_id,
stays.first_careunit,
stays.last_careunit,
icu.admittime AS admittime,
icu.dischtime AS dischtime,
icu.icu_intime AS icu_intime,
icu.icu_outtime AS icu_outtime
FROM mimiciv_derived.icustay_detail AS icu
JOIN mimiciv_derived.age AS age ON age.hadm_id = icu.hadm_id
JOIN mimiciv_icu.icustays AS stays ON stays.stay_id = icu.stay_id
WHERE
TRUE
AND age.age BETWEEN 0 AND 120
AND icu.first_icu_stay = true
--纳入诊断疾病
AND (
--房颤 Atrial fibrillation
icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose1)
--房扑 Atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose2)
--阵发性心房颤动 Paroxysmal atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose3)
--持续性心房颤动 Persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose4)
--其他持续性心房颤动 Other persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose5)
--慢性心房颤动 Chronic atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose6)
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose7)
--永久性心房颤动 Permanent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose8)
--未特指的心房颤动 Unspecified atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose9)
--未特指的心房扑动 Unspecified atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose10)
--心房颤动和扑动 Atrial fibrillation and flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose11)
--未特指的心房颤动和心房扑动 Unspecified atrial fibrillation and atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose12)
--典型心房扑动 Typical atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose13)
)
)
--SELECT * FROM basic;

,
YuHou_Final AS (
SELECT
subject.subject_id,
MAX(icu.admittime) AS admit_time,
MAX(icu.dischtime) AS disch_time,
MAX(icu.icu_intime) AS icu_intime,
MAX(icu.icu_outtime) AS icu_outtime,
MAX(admission.deathtime) AS dead_time,
MAX(icu.dod) AS icu_dod,
CASE WHEN MAX(admission.deathtime) IS NOT NULL OR MAX(icu.dod)IS NOT NULL THEN 1 ELSE NULL END AS is_dead,

text
            CASE 
                 WHEN MAX(admission.deathtime) IS NOT NULL 
                 AND MAX(admission.deathtime) NOT BETWEEN MAX(icu.icu_intime) AND MAX(icu.icu_outtime) THEN 1 
                 ELSE 0 
            END AS is_hosp_dead,
            CASE 
                WHEN MAX(admission.deathtime) BETWEEN MAX(icu.icu_intime) AND MAX(icu.icu_outtime) THEN 1 
                ELSE 0 
            END AS is_icu_dead
FROM 
    basic AS subject
LEFT JOIN mimiciv_derived.icustay_detail AS icu ON subject.subject_id = icu.subject_id
LEFT JOIN mimiciv_hosp.admissions AS admission ON admission.subject_id = icu.subject_id 
GROUP BY subject.subject_id

),

YuHou AS (
SELECT
subject.subject_id,
icu.stay_id,
icu.hadm_id,
icu.admittime AS admit_time,
icu.dischtime AS disch_time,
icu.icu_intime AS icu_intime,
icu.icu_outtime AS icu_outtime,
subject.first_careunit,
subject.last_careunit,
YuHou_Final.dead_time,
YuHou_Final.icu_dod,
YuHou_Final.is_dead,
CASE
WHEN adm.deathtime IS NOT NULL THEN 1
ELSE 0
END AS death_in_current_adm,

text
    YuHou_Final.is_hosp_dead,
    YuHou_Final.is_icu_dead,
    ROUND(EXTRACT(EPOCH FROM (icu.dischtime - icu.admittime)) / 86400, 2) AS hosp_day,
    ROUND(EXTRACT(EPOCH FROM (icu.icu_outtime - icu.icu_intime)) / 86400, 2) AS icu_day,
    ROUND(EXTRACT(EPOCH FROM (YuHou_Final.dead_time - icu.admittime)) / 86400, 2) AS hosp_survival_time,
    ROUND(EXTRACT(EPOCH FROM (YuHou_Final.dead_time - icu.icu_intime)) / 86400, 2) AS icu_survival_time,
    CASE
        WHEN YuHou_Final.dead_time IS NOT NULL AND
             (YuHou_Final.dead_time - icu.admittime) <= INTERVAL '28 days'
        THEN 1
        ELSE 0
    END AS death_within_hosp_28days,
    CASE
        WHEN YuHou_Final.dead_time IS NOT NULL AND
             (YuHou_Final.dead_time - icu.icu_intime) <= INTERVAL ' 28 days'
        THEN 1
        ELSE 0
    END AS death_within_icu_28days
FROM 
    basic AS subject
LEFT JOIN mimiciv_derived.icustay_detail AS icu 
    ON subject.subject_id = icu.subject_id
LEFT JOIN mimiciv_hosp.admissions AS adm 
    ON icu.hadm_id = adm.hadm_id  
LEFT JOIN YuHou_Final 
    ON YuHou_Final.subject_id = subject.subject_id
GROUP BY
    subject.subject_id,
    icu.stay_id,
    icu.hadm_id,
    icu.admittime,
    icu.dischtime,
    icu.icu_intime,
    icu.icu_outtime,
    YuHou_Final.dead_time,
    YuHou_Final.icu_dod,
    YuHou_Final.is_dead,
    YuHou_Final.is_hosp_dead,
    YuHou_Final.is_icu_dead,
    adm.deathtime,subject.last_careunit,subject.first_careunit

)

SELECT * FROM YuHou ORDER BY subject_id,admit_time

请帮我分析这个代码的作用

Resposta

This SQL query is a complex extraction of data related to ICU stays for patients who have been diagnosed with various forms of atrial fibrillation and atrial flutter. Here's a breakdown of the logic and what each part of the code does:

1. Basic CTE (Common Table Expression) – basic:

  • This CTE identifies the cohort of patients who have been diagnosed with any form of atrial fibrillation or atrial flutter from the mimiciv_hosp.diagnoses_icd table.
  • It includes diagnoses like:
    • Atrial fibrillation (ICD codes: '42731')
    • Atrial flutter (ICD codes: '42732')
    • Paroxysmal atrial fibrillation (ICD code: 'I480')
    • Persistent atrial fibrillation (ICD code: 'I481')
    • And other forms, including unspecified types and typical atrial flutter.

This section ensures that only patients with the relevant diagnoses (identified by the ICD codes) are selected.

  • It then uses the mimiciv_derived.icustay_detail table to fetch ICU stay data for these patients, including admission time, discharge time, ICU admission and discharge times, and the first and last care units they stayed in.

2. YuHou_Final CTE:

  • This section builds upon the data in the basic CTE and combines it with the admissions table (mimiciv_hosp.admissions) to extract data about patient outcomes, particularly death information.
  • The following columns are calculated or derived:
    • Admission time (admittime) and discharge time (dischtime).
    • ICU death time (icu_dod) and hospital death time (deathtime).
    • Flags are set based on whether a patient is dead:
      • is_dead: 1 if the patient died, NULL otherwise.
      • is_hosp_dead: 1 if the patient died during their hospital stay (but not necessarily in the ICU).
      • is_icu_dead: 1 if the patient died during their ICU stay.

This CTE provides an overall view of patient survival after ICU admission, including details on whether the death occurred within the ICU or hospital and the timing.

3. YuHou CTE:

  • This section combines the data from the basic CTE, the YuHou_Final CTE, and the admissions table (mimiciv_hosp.admissions).
  • Additional calculations for survival times are made, including:
    • hospital survival time: The time from ICU admission to hospital death (if any).
    • ICU survival time: The time from ICU admission to ICU death (if any).
  • Flags for death within 28 days of hospital stay and death within 28 days of ICU stay are also calculated, based on the deathtime and icu_intime.

This CTE is used to identify key survival metrics for patients, looking at whether they survived or died during their ICU or hospital stay, and the length of time they survived after their ICU admission.

4. Final Output:

  • The query returns all the relevant data for each patient (subject_id), including:
    • Admission and discharge times, both in the hospital and ICU.
    • Death-related information (e.g., if the patient died in the hospital or ICU, and if they survived beyond 28 days in the hospital/ICU).
    • Survival times (how long the patient survived during their hospital and ICU stay).
    • Flags indicating death and survival status.

Summary of the Query's Purpose:

The query is designed to generate a detailed report of ICU patients who were diagnosed with atrial fibrillation or atrial flutter, capturing their ICU admission/discharge details, survival status, and death-related information (whether they died in ICU, in the hospital, or survived). The query also calculates the time spent in the hospital and ICU, along with specific survival metrics like whether the patient survived beyond 28 days in either setting.

This can be particularly useful for analyzing the outcomes of patients with specific heart conditions (e.g., atrial fibrillation) in ICU settings, allowing for further research into patient survival and hospital/ICU stays.

Pergunta

"WITH
basic AS (
WITH
--纳入的诊断
--房颤 Atrial fibrillation
includedDiagnose1 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42731')
),
--房扑 Atrial flutter
includedDiagnose2 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42732')
),
--阵发性心房颤动 Paroxysmal atrial fibrillation
includedDiagnose3 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I480')
),
--持续性心房颤动 Persistent atrial fibrillation
includedDiagnose4 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I481')
),
--其他持续性心房颤动 Other persistent atrial fibrillation
includedDiagnose5 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4819')
),
--慢性心房颤动 Chronic atrial fibrillation
includedDiagnose6 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I482')
),
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
includedDiagnose7 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4820')
),
--永久性心房颤动 Permanent atrial fibrillation
includedDiagnose8 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4821')
),
--未特指的心房颤动 Unspecified atrial fibrillation
includedDiagnose9 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4891')
),
--未特指的心房扑动 Unspecified atrial flutter
includedDiagnose10 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4892')
),
--排除的诊断
--恶性肿瘤 CA
excludedDiagnose1 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('1629','185','1970','1976','1977','1983','1985','19889','C61','C786','C787','C7931','C7951','V1005','V1011','V103','V103','V1046','V1051','V1052','V1083','V160','V160','V163','V163','Z800','Z803','Z85038','Z85118','Z853','Z8546','Z8551','Z85828')
)
SELECT
DISTINCT ON (icu.subject_id)
icu.subject_id,
icu.stay_id,
icu.hadm_id,
stays.first_careunit,
stays.last_careunit,
icu.admittime AS admittime,
icu.dischtime AS dischtime,
icu.icu_intime AS icu_intime,
icu.icu_outtime AS icu_outtime
FROM mimiciv_derived.icustay_detail AS icu
JOIN mimiciv_derived.age AS age ON age.hadm_id = icu.hadm_id
JOIN mimiciv_icu.icustays AS stays ON stays.stay_id = icu.stay_id
WHERE
TRUE
AND age.age BETWEEN 0 AND 120
AND icu.first_icu_stay = true
--纳入诊断疾病
AND (
--房颤 Atrial fibrillation
icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose1)
--房扑 Atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose2)
--阵发性心房颤动 Paroxysmal atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose3)
--持续性心房颤动 Persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose4)
--其他持续性心房颤动 Other persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose5)
--慢性心房颤动 Chronic atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose6)
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose7)
--永久性心房颤动 Permanent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose8)
--未特指的心房颤动 Unspecified atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose9)
--未特指的心房扑动 Unspecified atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose10)
)
--排除诊断疾病
AND (
--恶性肿瘤 CA
icu.hadm_id NOT IN (SELECT hadm_id FROM excludedDiagnose1)
)
)
--SELECT * FROM basic;

,
--血管活性药物（升压药） Vasopressor
VP AS
(SELECT DISTINCT(prescriptions.hadm_id),
1 AS flag,
MAX(prescriptions.starttime) AS VP_prescriptions_starttime,
MAX(prescriptions.stoptime) AS VP_prescriptions_stoptime
FROM mimiciv_hosp.prescriptions AS prescriptions,
basic AS subject
WHERE prescriptions.hadm_id = subject.hadm_id
AND prescriptions.gsn IN ('004977','004985','004975','064575','062006','004939','066419','066452','004937','004931','065336','028633','003388','003389','003390','003385','052187','004934','003387','052188','003386','008022','008062','005068','063864','063863','066206','005066','074949','007764','008061','048541','060981','073081','006612','000141','064535','021502','064538')
GROUP BY prescriptions.hadm_id
),
VP_val AS
(SELECT DISTINCT(prescriptions.hadm_id),
SUM(CAST(prescriptions.dose_val_rx AS numeric)) AS dose_val_rx,
MAX(prescriptions.dose_unit_rx) AS dose_unit_rx
FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject
WHERE prescriptions.hadm_id = subject.hadm_id
AND prescriptions.gsn IN ('004977','004985','004975','064575','062006','004939','066419','066452','004937','004931','065336','028633','003388','003389','003390','003385','052187','004934','003387','052188','003386','008022','008062','005068','063864','063863','066206','005066','074949','007764','008061','048541','060981','073081','006612','000141','064535','021502','064538')
AND prescriptions.dose_val_rx ~ '^[0-9]+(.[0-9]+)?′GROUPBYprescriptions.hadmid),−−糖皮质激素GlucocorticoidsGCAS(SELECTDISTINCT(prescriptions.hadmid),1ASflag,MAX(prescriptions.starttime)ASGCprescriptionsstarttime,MAX(prescriptions.stoptime)ASGCprescriptionsstoptimeFROMmimicivhosp.prescriptionsASprescriptions,basicASsubjectWHEREprescriptions.hadmid=subject.hadmidANDprescriptions.gsnIN(′066110′,′006705′,′051558′,′006704′,′007544′,′023906′,′006696′,′006858′,′007545′,′007543′,′006724′,′006725′,′006753′,′007894′,′007892′,′006786′,′006784′,′006788′,′006776′,′006778′,′006789′,′006721′,′067556′,′047282′,′006745′,′060958′,′062053′,′006780′,′013701′,′006762′,′006758′,′006812′,′066112′,′026721′,′006749′,′006738′,′006742′,′006754′,′006748′,′006750′)GROUPBYprescriptions.hadmid),GCvalAS(SELECTDISTINCT(prescriptions.hadmid),SUM(CAST(prescriptions.dosevalrxASnumeric))ASdosevalrx,MAX(prescriptions.doseunitrx)ASdoseunitrxFROMmimicivhosp.prescriptionsASprescriptions,basicASsubjectWHEREprescriptions.hadmid=subject.hadmidANDprescriptions.gsnIN(′066110′,′006705′,′051558′,′006704′,′007544′,′023906′,′006696′,′006858′,′007545′,′007543′,′006724′,′006725′,′006753′,′007894′,′007892′,′006786′,′006784′,′006788′,′006776′,′006778′,′006789′,′006721′,′067556′,′047282′,′006745′,′060958′,′062053′,′006780′,′013701′,′006762′,′006758′,′006812′,′066112′,′026721′,′006749′,′006738′,′006742′,′006754′,′006748′,′006750′)ANDprescriptions.dosevalrx ′[0−9]+([˙0−9]+)?' GROUP BY prescriptions.hadm_id ), --糖皮质激素 Glucocorticoids GC AS (SELECT DISTINCT(prescriptions.hadm_id), 1 AS flag, MAX(prescriptions.starttime) AS GC_prescriptions_starttime, MAX(prescriptions.stoptime) AS GC_prescriptions_stoptime FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject WHERE prescriptions.hadm_id = subject.hadm_id AND prescriptions.gsn IN ('066110','006705','051558','006704','007544','023906','006696','006858','007545','007543','006724','006725','006753','007894','007892','006786','006784','006788','006776','006778','006789','006721','067556','047282','006745','060958','062053','006780','013701','006762','006758','006812','066112','026721','006749','006738','006742','006754','006748','006750') GROUP BY prescriptions.hadm_id ), GC_val AS (SELECT DISTINCT(prescriptions.hadm_id), SUM(CAST(prescriptions.dose_val_rx AS numeric)) AS dose_val_rx, MAX(prescriptions.dose_unit_rx) AS dose_unit_rx FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject WHERE prescriptions.hadm_id = subject.hadm_id AND prescriptions.gsn IN ('066110','006705','051558','006704','007544','023906','006696','006858','007545','007543','006724','006725','006753','007894','007892','006786','006784','006788','006776','006778','006789','006721','067556','047282','006745','060958','062053','006780','013701','006762','006758','006812','066112','026721','006749','006738','006742','006754','006748','006750') AND prescriptions.dose_val_rx ~ '^[0-9]+(\.[0-9]+)?'
GROUP BY prescriptions.hadm_id
)

SELECT
DISTINCT(subject.subject_id),
subject.stay_id,
subject.hadm_id,
subject.admittime,
subject.dischtime,
subject.icu_intime,
subject.icu_outtime,
subject.first_careunit,
subject.last_careunit,
VP.flag AS VP,VP_val.dose_val_rx AS VPtotalval, VP_val.dose_unit_rx AS VPunit,
VP.VP_prescriptions_starttime,
VP.VP_prescriptions_stoptime,
GC.flag AS GC,GC_val.dose_val_rx AS GCtotalval, GC_val.dose_unit_rx AS GCunit,
GC.GC_prescriptions_starttime,
GC.GC_prescriptions_stoptime
FROM basic AS subject
--血管活性药物（升压药） Vasopressor
LEFT JOIN VP ON subject.hadm_id = VP.hadm_id
LEFT JOIN VP_val ON subject.hadm_id = VP_val.hadm_id
--糖皮质激素 Glucocorticoids
LEFT JOIN GC ON subject.hadm_id = GC.hadm_id
LEFT JOIN GC_val ON subject.hadm_id = GC_val.hadm_id
ORDER BY subject.subject_id;
"
和“WITH
basic AS (
WITH
--纳入的诊断
--房颤 Atrial fibrillation
includedDiagnose1 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42731')
),
--房扑 Atrial flutter
includedDiagnose2 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('42732')
),
--阵发性心房颤动 Paroxysmal atrial fibrillation
includedDiagnose3 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I480')
),
--持续性心房颤动 Persistent atrial fibrillation
includedDiagnose4 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I481')
),
--其他持续性心房颤动 Other persistent atrial fibrillation
includedDiagnose5 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4819')
),
--慢性心房颤动 Chronic atrial fibrillation
includedDiagnose6 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I482')
),
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
includedDiagnose7 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4820')
),
--永久性心房颤动 Permanent atrial fibrillation
includedDiagnose8 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4821')
),
--未特指的心房颤动 Unspecified atrial fibrillation
includedDiagnose9 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4891')
),
--未特指的心房扑动 Unspecified atrial flutter
includedDiagnose10 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I4892')
),
--心房颤动和扑动 Atrial fibrillation and flutter
includedDiagnose11 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I48')
),
--未特指的心房颤动和心房扑动 Unspecified atrial fibrillation and atrial flutter
includedDiagnose12 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I489')
),
--典型心房扑动 Typical atrial flutter
includedDiagnose13 AS (
SELECT DISTINCT hadm_id
FROM mimiciv_hosp.diagnoses_icd
WHERE icd_code IN ('I483')
)
SELECT
DISTINCT ON (icu.subject_id)
icu.subject_id,
icu.stay_id,
icu.hadm_id,
stays.first_careunit,
stays.last_careunit,
icu.admittime AS admittime,
icu.dischtime AS dischtime,
icu.icu_intime AS icu_intime,
icu.icu_outtime AS icu_outtime
FROM mimiciv_derived.icustay_detail AS icu
JOIN mimiciv_derived.age AS age ON age.hadm_id = icu.hadm_id
JOIN mimiciv_icu.icustays AS stays ON stays.stay_id = icu.stay_id
WHERE
TRUE
AND age.age BETWEEN 0 AND 120
AND icu.first_icu_stay = true
--纳入诊断疾病
AND (
--房颤 Atrial fibrillation
icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose1)
--房扑 Atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose2)
--阵发性心房颤动 Paroxysmal atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose3)
--持续性心房颤动 Persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose4)
--其他持续性心房颤动 Other persistent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose5)
--慢性心房颤动 Chronic atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose6)
--未特指的慢性心房颤动 Chronic atrial fibrillation, unspecified
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose7)
--永久性心房颤动 Permanent atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose8)
--未特指的心房颤动 Unspecified atrial fibrillation
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose9)
--未特指的心房扑动 Unspecified atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose10)
--心房颤动和扑动 Atrial fibrillation and flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose11)
--未特指的心房颤动和心房扑动 Unspecified atrial fibrillation and atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose12)
--典型心房扑动 Typical atrial flutter
OR icu.hadm_id IN (SELECT hadm_id FROM includedDiagnose13)
)
)
--SELECT * FROM basic;

,
--血管活性药物（升压药） Vasopressor
VP AS
(SELECT DISTINCT(prescriptions.hadm_id),
1 AS flag,
MAX(prescriptions.starttime) AS VP_prescriptions_starttime,
MAX(prescriptions.stoptime) AS VP_prescriptions_stoptime
FROM mimiciv_hosp.prescriptions AS prescriptions,
basic AS subject
WHERE prescriptions.hadm_id = subject.hadm_id
AND prescriptions.gsn IN ('004977','004985','004975','064575','062006','004939','066419','066452','004937','004931','065336','028633','003388','003389','003390','003385','052187','004934','003387','052188','003386','008022','008062','005068','063864','063863','066206','005066','074949','007764','008061','048541','060981','073081','006612','000141','064535','021502','064538')
GROUP BY prescriptions.hadm_id
),
VP_val AS
(SELECT DISTINCT(prescriptions.hadm_id),
SUM(CAST(prescriptions.dose_val_rx AS numeric)) AS dose_val_rx,
MAX(prescriptions.dose_unit_rx) AS dose_unit_rx
FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject
WHERE prescriptions.hadm_id = subject.hadm_id
AND prescriptions.gsn IN ('004977','004985','004975','064575','062006','004939','066419','066452','004937','004931','065336','028633','003388','003389','003390','003385','052187','004934','003387','052188','003386','008022','008062','005068','063864','063863','066206','005066','074949','007764','008061','048541','060981','073081','006612','000141','064535','021502','064538')
AND prescriptions.dose_val_rx ~ '^[0-9]+(.[0-9]+)?′GROUPBYprescriptions.hadmid),−−糖皮质激素GlucocorticoidsGCAS(SELECTDISTINCT(prescriptions.hadmid),1ASflag,MAX(prescriptions.starttime)ASGCprescriptionsstarttime,MAX(prescriptions.stoptime)ASGCprescriptionsstoptimeFROMmimicivhosp.prescriptionsASprescriptions,basicASsubjectWHEREprescriptions.hadmid=subject.hadmidANDprescriptions.gsnIN(′066110′,′006705′,′051558′,′006704′,′007544′,′023906′,′006696′,′006858′,′007545′,′007543′,′006724′,′006725′,′006753′,′007894′,′007892′,′006786′,′006784′,′006788′,′006776′,′006778′,′006789′,′006721′,′067556′,′047282′,′006745′,′060958′,′062053′,′006780′,′013701′,′006762′,′006758′,′006812′,′066112′,′026721′,′006749′,′006738′,′006742′,′006754′,′006748′,′006750′)GROUPBYprescriptions.hadmid),GCvalAS(SELECTDISTINCT(prescriptions.hadmid),SUM(CAST(prescriptions.dosevalrxASnumeric))ASdosevalrx,MAX(prescriptions.doseunitrx)ASdoseunitrxFROMmimicivhosp.prescriptionsASprescriptions,basicASsubjectWHEREprescriptions.hadmid=subject.hadmidANDprescriptions.gsnIN(′066110′,′006705′,′051558′,′006704′,′007544′,′023906′,′006696′,′006858′,′007545′,′007543′,′006724′,′006725′,′006753′,′007894′,′007892′,′006786′,′006784′,′006788′,′006776′,′006778′,′006789′,′006721′,′067556′,′047282′,′006745′,′060958′,′062053′,′006780′,′013701′,′006762′,′006758′,′006812′,′066112′,′026721′,′006749′,′006738′,′006742′,′006754′,′006748′,′006750′)ANDprescriptions.dosevalrx ′[0−9]+([˙0−9]+)?' GROUP BY prescriptions.hadm_id ), --糖皮质激素 Glucocorticoids GC AS (SELECT DISTINCT(prescriptions.hadm_id), 1 AS flag, MAX(prescriptions.starttime) AS GC_prescriptions_starttime, MAX(prescriptions.stoptime) AS GC_prescriptions_stoptime FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject WHERE prescriptions.hadm_id = subject.hadm_id AND prescriptions.gsn IN ('066110','006705','051558','006704','007544','023906','006696','006858','007545','007543','006724','006725','006753','007894','007892','006786','006784','006788','006776','006778','006789','006721','067556','047282','006745','060958','062053','006780','013701','006762','006758','006812','066112','026721','006749','006738','006742','006754','006748','006750') GROUP BY prescriptions.hadm_id ), GC_val AS (SELECT DISTINCT(prescriptions.hadm_id), SUM(CAST(prescriptions.dose_val_rx AS numeric)) AS dose_val_rx, MAX(prescriptions.dose_unit_rx) AS dose_unit_rx FROM mimiciv_hosp.prescriptions AS prescriptions, basic AS subject WHERE prescriptions.hadm_id = subject.hadm_id AND prescriptions.gsn IN ('066110','006705','051558','006704','007544','023906','006696','006858','007545','007543','006724','006725','006753','007894','007892','006786','006784','006788','006776','006778','006789','006721','067556','047282','006745','060958','062053','006780','013701','006762','006758','006812','066112','026721','006749','006738','006742','006754','006748','006750') AND prescriptions.dose_val_rx ~ '^[0-9]+(\.[0-9]+)?'
GROUP BY prescriptions.hadm_id
)

SELECT
DISTINCT(subject.subject_id),
subject.stay_id,
subject.hadm_id,
subject.admittime,
subject.dischtime,
subject.icu_intime,
subject.icu_outtime,
subject.first_careunit,
subject.last_careunit,
VP.flag AS VP,VP_val.dose_val_rx AS VPtotalval, VP_val.dose_unit_rx AS VPunit,
VP.VP_prescriptions_starttime,
VP.VP_prescriptions_stoptime,
GC.flag AS GC,GC_val.dose_val_rx AS GCtotalval, GC_val.dose_unit_rx AS GCunit,
GC.GC_prescriptions_starttime,
GC.GC_prescriptions_stoptime
FROM basic AS subject
--血管活性药物（升压药） Vasopressor
LEFT JOIN VP ON subject.hadm_id = VP.hadm_id
LEFT JOIN VP_val ON subject.hadm_id = VP_val.hadm_id
--糖皮质激素 Glucocorticoids
LEFT JOIN GC ON subject.hadm_id = GC.hadm_id
LEFT JOIN GC_val ON subject.hadm_id = GC_val.hadm_id
ORDER BY subject.subject_id;
”有什么区别？麻烦用中文回答

Resposta

这两个 SQL 查询的结构和逻辑几乎是相同的，区别主要体现在以下几个方面：

1. 诊断的范围

  • 第一个查询：仅包含了与 房颤（Atrial fibrillation） 和 房扑（Atrial flutter） 相关的 ICD 诊断代码（ICD 代码为 42731、42732、I480、I481 等）。其中，还明确列出了多种类型的房颤和房扑的诊断。
  • 第二个查询：与第一个查询类似，但有一个不同之处，即它还添加了 Atrial fibrillation and flutter（ICD 代码为 I48）和 Unspecified atrial fibrillation and atrial flutter（ICD 代码为 I489）等更广泛的诊断。因此，第二个查询涵盖了更广泛的房颤和房扑相关的诊断。

2. 排除的诊断

  • 第一个查询：对排除的诊断进行了一定筛选，排除了那些患有 恶性肿瘤（CA） 的患者（ICD 代码为：1629、185、1970、1976 等）。
  • 第二个查询：没有类似的排除条件，因此它没有排除任何与恶性肿瘤相关的患者诊断。

3. 查询的范围

  • 第一个查询：在查询 basic CTE 的基础上，明确限制了与房颤和房扑相关的诊断和排除了恶性肿瘤的患者。
  • 第二个查询：查询的逻辑与第一个查询几乎相同，但它没有排除恶性肿瘤患者，意味着查询结果可能包含有恶性肿瘤的患者。

4. 结果集的含义

  • 第一个查询：通过筛选房颤、房扑等相关疾病并排除恶性肿瘤患者，目的是对特定患者群体进行分析，评估这些患者在 ICU 中的治疗情况以及死亡率等。
  • 第二个查询：通过包含更广泛的房颤和房扑类型以及不排除恶性肿瘤的患者，分析的患者范围更广，可能会导致更广泛的结论，但也可能包含一些恶性肿瘤患者的影响。

总结：

  • 第一个查询 更加专注于排除恶性肿瘤的患者，确保仅分析房颤和房扑患者的 ICU 治疗和结果。
  • 第二个查询 在分析房颤和房扑患者时，包含了更广泛的诊断类型，并没有排除恶性肿瘤的患者，因此结果集可能会更广泛。

Compartilhar esta Q&A