---
# System prepended metadata

title: SQL 教學

---

# SQL 教學

## 1
```
SELECT *
FROM mimiciv_hosp.patients;
```

## 2
```
SELECT COUNT(*)
FROM mimiciv_hosp.patients;
```

## 3
```
SELECT DISTINCT(gender)
FROM mimiciv_hosp.patients;
```

## 4
```
SELECT COUNT(*)
FROM mimiciv_hosp.patients
WHERE gender = 'F';
```

## 5
```
SELECT gender, COUNT(*)
FROM mimiciv_hosp.patients
GROUP BY gender;
```

## 6
```
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag
FROM mimiciv_hosp.admissions a
INNER JOIN mimiciv_hosp.patients p
ON p.subject_id = a.subject_id;
```

## 7
```
SELECT p.subject_id, p.dob, a.hadm_id,
    a.admittime, p.expire_flag,
    MIN (a.admittime) OVER (PARTITION BY p.subject_id) AS first_admittime
FROM mimiciv_hosp.admissions a
INNER JOIN mimiciv_hosp.patients p
ON p.subject_id = a.subject_id
ORDER BY a.hadm_id, p.subject_id;
```

## 8
```
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM mimiciv_hosp.patients p
  INNER JOIN mimiciv_hosp.admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
SELECT
    subject_id, dob, gender
    , first_admittime, first_admit_age
    , CASE
        -- all ages > 89 in the database were replaced with 300
        WHEN first_admit_age > 89
            then '>89'
        WHEN first_admit_age >= 14
            THEN 'adult'
        WHEN first_admit_age <= 1
            THEN 'neonate'
        ELSE 'middle'
        END AS age_group
FROM first_admission_time
ORDER BY subject_id
```

## 9 實作
```
找到labevents的測量項目 (labevents and d_labitems)，限制搜尋100筆
```


## 10 實作
```
1. icu patients的年紀，從patients和icustays的年紀
2. 區分三種情況，小於65歲 (adult)，65到89 (old)，等於91 (>89)
3. 計算三種情況的人術
```


Reference: https://mimic.mit.edu/docs/iii/tutorials/intro-to-mimic-iii/