Skip to content

Latest commit

 

History

History
243 lines (207 loc) · 12.2 KB

README.md

File metadata and controls

243 lines (207 loc) · 12.2 KB
Raw

Link to CommonDataModel

Source Tables(mimic)

transfers

admissions

  • the room or bed movement have been removed so that only ward change are logged in the table
  • the emergency stays have been added in has conventional stays (may introduce strange gap in times)
  • visit_type_concept_id is equal to 2000000006 (Ward and physical location) and localize the patient
  • visit_detail_concept_id is equal to 9201 (Inpatient visit) for a non emergency stay
  • visit_detail_concept_id is equal to 9203 (Emergency room) for a emergency stay
  • visit_detail_concept_id is equal to 32037 (Intensive Care Unit) for an ICU stay
  • the callout delay has been added in the table has an omop contrib derived variable

services

  • the services table populates the visit_detail too

  • visit_type_concept_id is equal to 45770670 (Services and care) and lists services that a patient was admitted/transferred under

  • visit_detail_concept_id is equal to 45763735 to identify medical patients

  • visit_detail_concept_id is equal to 4149152 to identify surgical patients

  • visit_detail_concept_id is equal to 4237225 to identify newborn patients

  • visit_detail_concept_id is equal to 4150859 to identigy psychiatric patients (only one!)

  • it is then possible to know both where the patient is (from transfers) and whose take care of him (from services)

visit_detail_id assigning

  • Be careful when joining patient data based on visit_detail_id, there is no guaranty the link is actual
  • WARNING : any link to visit_detail_id in the database is calculated (measurement, observation, drug_exposure...)
  • this is actually the case for mimiciii and icustays
  • the algorith is below:
    • if the data has a visit_occurrence value
    • if the data has a precise timestamp
      • then assign to the visit_detail that cover that date and is linked to that visit_occurrence
    • if the timestamp is before/after the first/last visit_detail
      • then assign to the first/last visit_detail instance

Mapping used

visit_source_concept_id

  • it maps mimic admission type to omop admission type

admitting_source_concept_id

  • it maps mimic location admission type to omop location admission type

discharge_type_to_concept

  • it maps mimic location discharge type to omop location discharge type

Example

explanation of visit_type_concept_id

SELECT distinct concept_name, visit_type_concept_id  as concept_id
FROM visit_detail v
JOIN concept c ON v.visit_type_concept_id = c.concept_id;
concept_name concept_id
Services and care 45770670
Ward and physical location 2000000006

explanation of visit_detail_concept_id, distribution of type of care

SELECT concept_name, concept_id, count(1)
FROM visit_detail
JOIN concept ON visit_detail_concept_id = concept_id
WHERE visit_type_concept_id = 45770670                          -- concept.concept_name = 'Services and care'
GROUP BY 1, 2 ORDER BY count(1) desc;
concept_name concept_id count
General medical service 45763735 88209
Surgical service 4149152 73729
Newborn care service 4237225 16718
Psychiatry service 4150859 1

explanation of visit_detail_concept_id, distribution of ward types (physical location)

SELECT concept_name, concept_id, count(1)
FROM visit_detail
JOIN concept ON visit_detail_concept_id = concept_id
WHERE visit_type_concept_id = 2000000006                      -- concept.concept_name = 'Ward and physical location'
GROUP BY 1, 2 ORDER BY count(1) desc;
concept_name concept_id count
No matching concept 0 87766
Intensive Care 32037 71570
Emergency Room Visit 9203 30877
Inpatient Visit 9201 8237

explanation of care_site_id, transfers table in non omop mimic

-- transfers table (mimic)
SELECT place_of_service_source_value, count (1)
FROM visit_detail JOIN care_site c USING (care_site_id)
WHERE visit_type_concept_id = 2000000006                        -- concept.concept_name = 'Ward and physical location'
GROUP BY 1 ORDER BY count(1) DESC;
place_of_service_source_value count
Unknown Ward 87766
Emergency Room Critical Care Facility 30877
Medical intensive care unit 24289
Cardiac surgery recovery unit 11818
Surgical intensive care unit 10688
Coronary care unit 9005
Neonatal intensive care unit 8472
Neonatal ward 8237
Trauma/surgical intensive care unit 7298

explanation of care_site_id, service table in non omop table

SELECT place_of_service_source_value, count (1)
FROM visit_detail JOIN care_site c USING (care_site_id)
WHERE visit_type_concept_id = 45770670                           -- concept.concept_name = 'Services and care'
GROUP BY 1 ORDER BY count(1) DESC;
place_of_service_source_value count
Medical - general service for internal medicine 57032
Cardiac Surgery - for surgical cardiac admissions 23775
Cardiac Medical - for non-surgical cardiac related admissions 20663
Surgical - general surgical service not classified elsewhere 16600
Newborn - infants born at the hospital 16258
Neurologic Surgical - surgical, relating to the brain 10574
Trauma - injury or damage caused by physical harm from an external source 7034
Neurologic Medical - non-surgical, relating to the brain 6395
Vascular Surgical - surgery relating to the circulatory system 5422
Orthopaedic medicine - non-surgical, relating to musculoskeletal system 4119
Thoracic Surgical - surgery on the thorax, located between the neck and the abdomen 3974
Orthopaedic - surgical, relating to the musculoskeletal system 2911
Genitourinary - reproductive organs/urinary system 1144
Plastic - restortation/reconstruction of the human body (including cosmetic or aesthetic) 775
Gynecological - female reproductive systems and breasts 674
Ear, nose, and throat - conditions primarily affecting these areas 614
Newborn baby - infants born at the hospital 460
Obstetrics - conerned with childbirth and the care of women giving birth 218
Dental - for dental/jaw related admissions 14
Psychiatric - mental disorders relating to mood, behaviour, cognition, or perceptions 1

Number of patients in ICU

SELECT COUNT(distinct visit_detail_id) AS num_totalstays_count
FROM visit_detail
WHERE visit_detail_concept_id = 32037                             -- concept.concept_name = 'Intensive Care'
AND visit_type_concept_id = 2000000006;                            -- concept.concept_name = 'Ward and physical location'
num_totalstays_count
71570

Number of dead patients in ICU

SELECT count(distinct visit_detail_id) AS dead_hospital_count
FROM visit_detail
JOIN concept ON visit_detail_concept_id = concept_id
WHERE visit_detail_concept_id = 32037                             -- concept.concept_name = 'Intensive Care'
AND visit_type_concept_id = 2000000006                             -- concept.concept_name = 'Ward and physical location'
AND discharge_to_concept_id = 4216643;                             -- concept.concept_name = 'Patient died'
dead_hospital_count
4559

% of dead patients in ICU

WITH tmp AS
(
  SELECT COUNT(distinct d.visit_detail_id) AS dead
       , COUNT(distinct t.visit_detail_id) AS total
  FROM visit_detail t
  LEFT JOIN
  (
        SELECT visit_detail_id
        FROM visit_detail
	WHERE visit_detail_concept_id = 32037                             -- concept.concept_name = 'Intensive Care'
	AND visit_type_concept_id = 2000000006                    -- concept.concept_name = 'Ward and physical location'
	AND discharge_to_concept_id = 4216643                     -- concept.concept_name = 'Patient died'
  ) d USING (visit_detail_id)
  WHERE t.visit_detail_concept_id = 32037
  AND t.visit_type_concept_id = 2000000006

)
SELECT dead, total, dead * 100 / total as percentage FROM tmp;
dead total percentage
4559 71570 6

Distribution of length of stay in ICU

SELECT percentile_25
       , median
       , percentile_75
       , MIN( EXTRACT(EPOCH FROM visit_end_datetime  - visit_start_datetime)/60.0/60.0/24.0    )    AS minimum
       , MAX( EXTRACT(EPOCH FROM visit_end_datetime  - visit_start_datetime)/60.0/60.0/24.0  )    AS maximum
       , CAST(AVG(  EXTRACT(EPOCH FROM visit_end_datetime  - visit_start_datetime)/60.0/60.0/24.0  ) AS INTEGER)   AS mean
       , STDDEV( EXTRACT(EPOCH FROM visit_end_datetime  - visit_start_datetime)/60.0/60.0/24.0    ) AS stddev
  FROM
  (SELECT MAX( CASE WHEN( percentile = 1    ) THEN los END    ) AS percentile_25
        , MAX( CASE WHEN( percentile = 2    ) THEN los END    ) AS median
        , MAX( CASE WHEN( percentile = 3    ) THEN los END    ) AS percentile_75
    FROM
       ( SELECT counter.los, counter.nb_los
              , FLOOR( CAST( SUM( nb_los    ) OVER( ORDER BY los ROWS UNBOUNDED PRECEDING    ) AS DECIMAL    )
                     / CAST( SUM( nb_los   ) OVER( ORDER BY los ROWS BETWEEN UNBOUNDED PRECEDING
                                                                        AND UNBOUNDED FOLLOWING    )  AS DECIMAL    )
                     * 4
                        ) + 1
          as percentile
          FROM
             ( SELECT EXTRACT(EPOCH FROM visit_end_datetime  - visit_start_datetime)/60.0/60.0/24.0 as los, count(*) AS nb_los
                FROM visit_detail
		WHERE visit_detail_concept_id = 32037                             -- concept.concept_name = 'Intensive Care'
                AND visit_type_concept_id = 2000000006            -- concept.concept_name = 'Ward and physical location' 
                GROUP BY EXTRACT(EPOCH FROM visit_end_datetime - visit_start_datetime)/60.0/60.0/24.0
               ) as counter
         ) as p
     WHERE percentile <= 3
  ) as percentile_table, visit_detail
  WHERE visit_detail_concept_id = 32037                             -- concept.concept_name = 'Intensive Care'
  AND visit_type_concept_id = 2000000006                          -- concept.concept_name = 'Ward and physical location' 
  GROUP BY percentile_25, median, percentile_75;
percentile_25 median percentile_75 minimum maximum mean stddev
0.958622685185185 1.8746412037037 3.87761574074074 1.15740740740741e-05 171.622650462963 4 8.67445796806052