---
# System prepended metadata

title: 院內資料庫SQL範例

---

# SQL 範例
## 範例一：健保資料庫轉換成OMOP格式
此範例的目標是將來自健保資料庫的原始數據轉換為OMOP（Observational Medical Outcomes Partnership）通用數據模型格式。OMOP CDM 是一種標準化的數據模型，用於在不同數據源之間進行一致的分析。每個SQL腳本負責填充OMOP CDM中的特定表格：

* **`location` (位置)**：將個人居住的郵遞區號插入到 `location` 表中，用於記錄地址、郵遞區號和經緯度等資訊。
* **`person` (個人)**：從內部 `ID` 表格中提取個人資訊，如性別、生日、種族和族群，並將其插入到 `person` 表中。
* **`visit_occurrence` (就診)**：記錄病患的就診開始和結束時間。數據主要來自 `CD` 和 `DD` 表格，用於區分門診和住院記錄。
* **`condition_occurrence` (疾病情況)**：記錄病患所患疾病及其發病時間（通常設為就診時間）。數據來源於 `CD` 和 `DD` 表格，並處理ICD-9和ICD-10疾病分類碼。
* **`measurement` (測量)**：將實驗室檢測結果（如血尿素氮、高密度脂蛋白、肌酐、預測腎小管過濾率等）從 `LAB_20231003` 表格中提取並插入到 `measurement` 表中。
* **`observation` (觀察)**：將觀察性數據（例如血型）從 `ID` 表格中提取並插入到 `observation` 表中。
* **`observation_period` (觀察期)**：根據病患的首次和最後一次就診日期來定義觀察期，這對於Atlas數據源的顯示至關重要。
* **`cost` (費用)**：將來自 `CD` 和 `DO` 表格的相關費用數據（如總費用、支付金額）插入到 `COST` 表中。
* **`death` (死亡)**：從 `ID` 表格中記錄病患的死亡日期和死亡類型。
* **`condition era & drug era` (疾病期與藥物期)**：這部分引用了OHDSI官方網站和GitHub上的SQL腳本，用於計算疾病持續時間和藥物使用期間，這在OMOP中用於提供時間背景分析。

### location
- 可紀錄地址、郵遞區號、經緯度等
- 目前主要紀錄院內`ID`表格中的個人居住之郵遞區號
- 此表格主要用於OMOP中`person`的`location_id`欄位
![image](https://hackmd.io/_uploads/By36Y8IEJe.png)

##### 插入郵遞區號數據
```sql=
INSERT INTO @target_schema.location (
    location_id,
    zip
)
SELECT
    ROW_NUMBER() OVER(ORDER BY zipcode),
    zipcode
FROM (
    SELECT
        id.zipcode
    FROM dblink(
        'dbname=@source_database host=@source_host port=@source_port user=@source_username password=@source_password',
        $$SELECT
            DISTINCT REPLACE(zipcode, ' ', '')
        FROM
            @source_schema."ID"
        WHERE
            zipcode IS NOT NULL AND
            LENGTH(REPLACE(zipcode, ' ', '')) <> 0$$
    ) AS id(
        zipcode VARCHAR(9)
    )
    UNION
    -- 若有些紀錄遺失郵遞區號，可用此值代替。
    SELECT 'UNKNOWN'
);

```

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![location](https://hackmd.io/_uploads/rkxVwuLEJg.png)

### person
- 紀錄個人資訊，包含生日、種族、族群等資料
- 資料來源為院內`ID`表格

#### SQL範例
```sql=
WITH unknown_zipcode AS (
    SELECT location_id
    FROM @target_schema.location
    WHERE zip = 'UNKNOWN'
),

gender AS (
    SELECT concept_id, concept_code
    FROM @target_schema.concept
    WHERE domain_id = 'Gender'
),

race AS (
    SELECT concept_id, concept_name
    FROM @target_schema.concept
    WHERE domain_id = 'Race'
),

ethnicity AS (
    SELECT concept_id, concept_code
    FROM @target_schema.concept
    WHERE domain_id = 'Ethnicity'
),

person_source AS (
    SELECT *
    FROM dblink(
        'dbname=@source_database host=@source_host port=@source_port user=@source_username password=@source_password',
        $$SELECT
            uuid,
            sex,
            birthday,
            REPLACE(COALESCE(zipcode, ''), ' ', '')
        FROM
            @source_schema."ID"$$
    ) AS t(
        uuid VARCHAR,
        sex VARCHAR,
        birthday VARCHAR,
        zipcode VARCHAR
    )
)

INSERT INTO @target_schema.person (
    person_id,
    gender_concept_id,
    year_of_birth,
    month_of_birth,
    day_of_birth,
    race_concept_id,
    ethnicity_concept_id,
    location_id,
    care_site_id,
    person_source_value,
    gender_source_value
)
SELECT
    ROW_NUMBER() OVER(),
    CASE sex
        WHEN '1' THEN (SELECT concept_id FROM gender WHERE concept_code = 'M') -- Male concept ID
        WHEN '2' THEN (SELECT concept_id FROM gender WHERE concept_code = 'F') -- Female concept ID
        ELSE (SELECT concept_id FROM gender WHERE concept_code = 'U')          -- UNKNOWN concept ID
    END,
    CAST(
        CASE
            WHEN omop_is_valid_date(birthday, 'YYYYMMDD') THEN SUBSTRING(birthday, 1, 4)
            ELSE '0001'
        END AS INT
    ),
    CAST(
        CASE
            WHEN omop_is_valid_date(birthday, 'YYYYMMDD') THEN SUBSTRING(birthday, 5, 2)
            ELSE '01'
        END AS INT
    ),
    CAST(
        CASE
            WHEN omop_is_valid_date(birthday, 'YYYYMMDD') THEN SUBSTRING(birthday, 7, 2)
            ELSE '01'
        END AS INT
    ),
    (SELECT concept_id FROM race WHERE concept_name = 'Unknown'),
    -- From OMOP offical website: Only use this field (ethnicity_concept_id) if you have US-based data and a source of this information.
    (SELECT concept_id FROM ethnicity WHERE concept_code = 'Not Hispanic'),
    COALESCE(l.location_id, (SELECT location_id FROM unknown_zipcode)),
    (
        SELECT care_site_id
        FROM @target_schema.care_site
        WHERE care_site_name = '臺北榮民總醫院'
    ),
    uuid,
    sex
FROM
    person_source
    LEFT JOIN @target_schema.location l ON person_source.zipcode = l.zip
```

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![person](https://hackmd.io/_uploads/SJ8xvuIVyg.png)


### visit_occurrence
- 紀錄就診開始和結束時間
- 資料來源為院內`CD`和`DD`表格
- 目前主要紀錄門診與住院
#### CD表格
![image](https://hackmd.io/_uploads/SyP65ErNJx.png)

筆記：
- 可能的`visit_concept_id`值：
1. 9201: Inpatient Visit(住院訪問)
2. 9202: Outpatient Visit(門診訪問)
3. 9203: Emergency Room Visit(急診訪問)
- `visit_type_concept_id`可設為32817 - EHR(電子健康紀錄)
- [參考資料](https://ohdsi.github.io/ETL-LambdaBuilder/CPRD/CPRD_Visit_Occurrence.html)
- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![visit_occurrence](https://hackmd.io/_uploads/ryBZDOUNke.png)


#### DD表格
![image](https://hackmd.io/_uploads/HJlN54SNyl.png)

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![visit_occurrence](https://hackmd.io/_uploads/rJaZvdL4yx.png)

#### 輸入資料
```sql=
WITH source AS (
    SELECT
        t.uuid,
        t.visit_concept_id,
        t.visit_start_date,
        t.visit_end_date,
        t.visit_type_concept_id,
        t.visit_source_value,
        t.visit_source_concept_id
    FROM
        dblink(
            'dbname=@source_database host=@source_host port=@source_port user=@source_username password=@source_password',
            'SELECT DISTINCT
            uuid,
            9202,
            CAST(func_date AS DATE),
            COALESCE(CAST(treat_end_date AS DATE), CAST(func_date AS DATE)),
            32834, -- EHR outpatient note
            acode_icd9_1,
            45880088 -- ICD-10
        FROM @source_schema."CD"
        WHERE CAST(func_date AS DATE) >= ''2016-01-01''
        GROUP BY uuid, func_date, treat_end_date, acode_icd9_1

        UNION ALL

        SELECT DISTINCT
            uuid,
            9202,
            CAST(func_date AS DATE),
            COALESCE(CAST(treat_end_date AS DATE), CAST(func_date AS DATE)),
            32834, -- EHR outpatient note
            acode_icd9_1,
            45880957 -- ICD-9
        FROM @source_schema."CD"
        WHERE CAST(func_date AS DATE) < ''2016-01-01''
        GROUP BY uuid, func_date, treat_end_date, acode_icd9_1

        UNION ALL

        SELECT DISTINCT
            uuid,
            9201,
            CAST(in_date AS DATE),
            COALESCE(CAST(out_date AS DATE), CAST(in_date AS DATE)),
            32829, -- EHR inpatient note
            icd9cm_code,
            45880088 -- ICD-10
        FROM @source_schema."DD"
        WHERE CAST(in_date AS DATE) >= ''2016-01-01''
        GROUP BY uuid, in_date, out_date, icd9cm_code

        UNION ALL

        SELECT DISTINCT
            uuid,
            9201,
            CAST(in_date AS DATE),
            COALESCE(CAST(out_date AS DATE), CAST(in_date AS DATE)),
            32829, -- EHR inpatient note
            icd9cm_code,
            45880957 -- ICD-9
        FROM @source_schema."DD"
        WHERE CAST(in_date AS DATE) < ''2016-01-01''
        GROUP BY uuid, in_date, out_date, icd9cm_code'
        ) AS t(
            uuid character varying(100),
            visit_concept_id integer,
            visit_start_date date,
            visit_end_date date,
            visit_type_concept_id integer,
            visit_source_value character varying(50),
            visit_source_concept_id integer
        )
)
INSERT INTO
    @target_schema.visit_occurrence (
        visit_occurrence_id,
        person_id,
        visit_concept_id,
        visit_start_date,
        visit_end_date,
        visit_type_concept_id,
        care_site_id,
        visit_source_value,
        visit_source_concept_id
    )
SELECT
    ROW_NUMBER() OVER (),
    p.person_id,
    r.visit_concept_id,
    r.visit_start_date,
    r.visit_end_date,
    r.visit_type_concept_id,
    (
        SELECT
            care_site_id
        FROM
            @target_schema.care_site
        WHERE
            care_site_name = '臺北榮民總醫院'
    ),
    r.visit_source_value,
    r.visit_source_concept_id
FROM
    source r
    LEFT JOIN @target_schema.person p ON r.uuid = p.person_source_value;
```

### condition_occurrence
- 紀錄病人患有之疾病與其發病時間（定為就診時間）
- 資料來源為CD與DD表格
- 筆記：須注意疾病分類號是否為ICD-9或是ICD-10
![image](https://hackmd.io/_uploads/S17hLllS1x.png)

```sql=
INSERT INTO
    @target_schema.condition_occurrence (
        condition_occurrence_id,
        person_id,
        condition_concept_id,
        condition_start_date,
        condition_end_date,
        condition_type_concept_id,
        visit_occurrence_id,
        condition_source_value,
        condition_source_concept_id
    )
SELECT
    ROW_NUMBER() OVER(),
    v.person_id,
    c.concept_id,
    v.visit_start_date,
    v.visit_end_date,
    v.visit_type_concept_id,
    v.visit_occurrence_id,
    v.visit_source_value,
    v.visit_source_concept_id
FROM
    @target_schema.visit_occurrence v
    LEFT JOIN @target_schema.concept c ON (
        CASE
            -- ICD-10 Code
            WHEN v.visit_source_concept_id = 45880088 THEN (
                CASE
                    WHEN LENGTH(v.visit_source_value) >= 4 THEN SUBSTRING(
                        v.visit_source_value
                        FROM
                            1 FOR 3
                    ) || '.' || SUBSTRING(
                        v.visit_source_value
                        FROM
                            4
                    )
                    ELSE v.visit_source_value
                END
            ) -- ICD-9 Code
            ELSE (
                CASE
                    WHEN v.visit_source_value LIKE 'E%'
                    AND LENGTH(v.visit_source_value) > 4 THEN SUBSTRING(
                        v.visit_source_value
                        FROM
                            1 FOR 4
                    ) || '.' || SUBSTRING(
                        v.visit_source_value
                        FROM
                            5
                    )
                    WHEN v.visit_source_value LIKE 'V%'
                    AND LENGTH(v.visit_source_value) > 3 THEN SUBSTRING(
                        v.visit_source_value
                        FROM
                            1 FOR 3
                    ) || '.' || SUBSTRING(
                        v.visit_source_value
                        FROM
                            4
                    )
                    WHEN LENGTH(v.visit_source_value) > 3 THEN SUBSTRING(
                        v.visit_source_value
                        FROM
                            1 FOR 3
                    ) || '.' || SUBSTRING(
                        v.visit_source_value
                        FROM
                            4
                    )
                    ELSE v.visit_source_value
                END
            )
        END
    ) = c.concept_code
WHERE
    c.concept_id IS NOT NULL;
```
### measurement
- 進度: 完成
- 缺: 
- 其他:
    - `measurement_id`, `measurement_type_concept_id`, 目前流水號
    - `measurement_concept_id`目前以`Athena API`腳本方式大量自動取得
```sql=
WITH measurement_base AS (
    SELECT 
        CAST("PERSON"."person_id" AS INTEGER) AS "person_id",
        "LAB_ITEM_MAPPING"."id" AS "measurement_concept_id",
        CAST("LAB_20231003"."sdate" AS DATE) AS "measurement_date",
        CASE 
            WHEN "LAB_20231003"."outcome" ~ '^[+-]?[0-9]*\.?[0-9]+$' THEN CAST("NEPHR"."LAB_20231003"."outcome" AS NUMERIC)
            ELSE NULL
        END AS "value_as_number",
        "VISIT_OCCURRENCE"."visit_occurrence_id"
    FROM 
        "LAB_20231003"
    LEFT JOIN "PERSON" 
        ON "LAB_20231003"."uuid" = "PERSON"."person_source_value"
    LEFT JOIN "LAB_ITEM_MAPPING"
        ON "LAB_ITEM_MAPPING"."item" = "NEPHR"."LAB_20231003"."item"
    LEFT JOIN "VISIT_OCCURRENCE"
    	ON "PERSON"."person_id" = "VISIT_OCCURRENCE"."person_id" AND "VISIT_OCCURRENCE"."visit_start_date" = "LAB_20231003"."sdate" AND "VISIT_OCCURRENCE"."visit_end_date" = "NEPHR"."LAB_20231003"."sdate"
)
INSERT INTO "measurement" (
    "measurement_id",
    "person_id",
    "measurement_concept_id",
    "measurement_date",
    "measurement_type_concept_id",
    "value_as_number",
    "visit_occurrence_id"
)
SELECT 
    ROW_NUMBER() OVER () + 375050204 AS "measurement_id",
    "person_id",
    "measurement_concept_id",
    "measurement_date",
    1 AS "measurement_type_concept_id",
    "value_as_number",
    "visit_occurrence_id"
FROM measurement_base
WHERE "person_id" IS NOT NULL
  AND "measurement_concept_id" IS NOT NULL
  AND "measurement_date" IS NOT NULL
  AND "value_as_number" IS NOT NULL
  AND "visit_occurrence_id" IS NOT NULL
```

- "item"欄位補充說明
HTN(CD or DD - icd) / 共病症
BUN / 血尿素氮 (Blood Urea Nitrogen)
HDL(HDLC) / 高密度脂蛋白 (High-Density Lipoprotein)
LDL(LDLC) / 低密度脂蛋白 (Low-Density Lipoprotein)
TG / 三酸甘油酯 (Triglycerides)
WBC / 白血球 (White Blood Cells)
Hb(HGB) / 血紅素 (Hemoglobin)
CREAT / 血清肌酐 (Creatinine)
eGFR / 預測腎小管過濾率 (estimated Glomerular Filtration Rate)
NA / 鈉 (Sodium)
K / 鉀 (Potassium)
Cl / 氯 (Chloride)
Ca / 鈣 (Calcium)
IP / 無機磷 (Inorganic Phosphate)
CHOL / 總膽固醇 (Cholesterol)
UA / 尿酸 (Uric Acid)
HCO3 / 重碳酸根 (Bicarbonate)
HbA1c / 糖化血紅素 (Hemoglobin A1c)
UPCR / 尿蛋白對肌酐比值 (Urine Protein to Creatinine Ratio)
UACR / 尿白蛋白對肌酐比值 (Urine Albumin to Creatinine Ratio)

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![measurement](https://hackmd.io/_uploads/HkPRUuUN1l.png)

### observation
- 進度: 完成
- 缺: 
- 其他:
    - `observatin_id`目前流水號
    - `observation_type_concept_id`, 目前全部帶`44814721`(`EHR/Electronic Health Record`的`concept_id`碼)
    - `observation_date`目前暫帶`ID.firstvisit`
```sql=
WITH observation_base AS (
    SELECT 
        "PERSON"."person_id" as "person_id",
        CASE 
            WHEN "ID"."bloodtype" = 'A' THEN 1576146
            WHEN "ID"."bloodtype" = 'A -' THEN 45910926
            WHEN "ID"."bloodtype" = 'A +' THEN 45930334
            WHEN "ID"."bloodtype" = 'AB' THEN 1576148
            WHEN "ID"."bloodtype" = 'AB -' THEN 45930337
            WHEN "ID"."bloodtype" = 'AB +' THEN 45934055
            WHEN "ID"."bloodtype" = 'B' THEN 1576147
            WHEN "ID"."bloodtype" = 'B-' THEN 45917006
            WHEN "ID"."bloodtype" = 'B +' THEN 45930336
            WHEN "ID"."bloodtype" = 'O' THEN 1576149
            WHEN "ID"."bloodtype" = 'O -' THEN 45917005
            WHEN "ID"."bloodtype" = 'O +' THEN 45930335
            ELSE NULL
        END AS "value_as_concept_id",
        "ID"."bloodtype" AS "value_as_string",
        CASE 
            WHEN "ID"."firstvisit" = '' OR "ID"."firstvisit" IS NULL OR "ID"."firstvisit" = '20201920' THEN NULL
            ELSE CAST("ID"."firstvisit" AS DATE)
        END AS "observation_date"
    FROM "ID"
    LEFT JOIN "PERSON" ON "ID"."uuid" = "PERSON"."person_source_value"
)
INSERT INTO "observation" (
    "observation_id",
    "person_id",
    "observation_concept_id",
    "observation_date",
    "observation_type_concept_id",
    "value_as_concept_id",
    "value_as_string"
)
SELECT 
    ROW_NUMBER() OVER () + 475050204 AS "observation_id",
    "person_id",
    4246053 AS "observation_concept_id",
    "observation_date",
    44814721 AS "observation_type_concept_id",
    "value_as_concept_id",
    "value_as_string"
FROM observation_base
WHERE "person_id" IS NOT NULL
  AND "observation_date" IS NOT NULL
  AND "value_as_concept_id" IS NOT NULL
--LIMIT 10000;
```

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![observation](https://hackmd.io/_uploads/HyG1wOL4Jx.png)


### observatin_period (Atlas Data Source顯示圖必要table, 不能空, [參考1](https://forums.ohdsi.org/t/broadsea-atlas-why-are-most-data-source-reports-empty-when-using-a-local-data-source/18858/3), [參考2](https://forums.ohdsi.org/t/atlas-now-load-info-properly/16522/2), [參考3](https://github.com/OHDSI/Achilles/blob/main/inst/sql/sql_server/analyses/505.sql)(Atlas顯示也用此Table做日期filter))
```sql=
WITH observation_period_base AS (
    SELECT 
        "PERSON"."person_id" as "person_id",
        CAST("ID"."firstvisit" AS DATE) AS "observation_period_start_date",
        CAST("ID"."lastvisit" AS DATE) AS "observation_period_end_date"
    FROM "ID"
    LEFT JOIN "PERSON" ON "ID"."uuid" = "PERSON"."person_source_value"
    WHERE "ID"."firstvisit" != '' 
    AND "ID"."lastvisit" != ''
    AND "ID"."lastvisit" NOT IN ('20182332', '20201920')
),
ranked_periods AS (
    SELECT 
        "person_id",
        "observation_period_start_date",
        "observation_period_end_date",
        ROW_NUMBER() OVER (PARTITION BY "person_id" ORDER BY "observation_period_start_date") AS row_num
    FROM observation_period_base
)
INSERT INTO "observation_period" (
    "observation_period_id",
    "person_id",
    "observation_period_start_date",
    "observation_period_end_date",
    "period_type_concept_id"
)
SELECT 
    ROW_NUMBER() OVER () + 29755 AS "observation_period_id",  -- Create unique ID
    "person_id",
    "observation_period_start_date",
    "observation_period_end_date",
    44814724 AS "period_type_concept_id"
FROM ranked_periods
WHERE row_num = 1
AND "person_id" IS NOT NULL
AND "observation_period_start_date" IS NOT NULL
AND "observation_period_end_date" IS NOT NULL;
```

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![ob](https://hackmd.io/_uploads/r1Z_A8AV1x.png)

### cost
- 進度: 完成
- 缺: `cost_event_id `, ` cost_domain_id`, 
- 其他: 
    - `cost_type_concept_id`目前帶`5032:Covered Charge`
    - `cost_event_id`，來自於`CD`帶`1`，來自於`DO`帶`2`
    - `cost_domain_id`目前暫帶`visit_temp`
```sql=
WITH cost_CD_base AS (
    SELECT
        CAST("CD"."t_amt" AS NUMERIC) AS "total_charge",
        CAST("CD"."t_appl_amt" AS NUMERIC) AS "total_cost",
        CAST("CD"."part_amt" AS NUMERIC) AS "paid_patient_copay",
        "CD"."case_pay_code" AS "drg_source_value",
        1 AS "cost_event_id"
    FROM "CD"
),
cost_DO_base AS (
    SELECT
        CAST("DO"."order_price" AS NUMERIC) AS "total_charge",
        CAST("DO"."order_amt" AS NUMERIC) AS "total_cost",
        CAST(NULL AS NUMERIC) AS "paid_patient_copay",
        CAST(NULL AS VARCHAR) AS "drg_source_value",
        2 AS "cost_event_id"
    FROM "DO"
)
INSERT INTO "COST" (
    "cost_id",
    "cost_event_id",
    "cost_domain_id",
    "cost_type_concept_id",
    "total_charge",
    "total_cost",
    "paid_patient_copay",
    "drg_source_value"
)
SELECT
    ROW_NUMBER() OVER () AS "cost_id",
    "cost_event_id",
    'visit_temp' AS "cost_domain_id",
    5032 AS "cost_type_concept_id",
    "total_charge",
    "total_cost",
    "paid_patient_copay",
    "drg_source_value"
FROM (
    SELECT * FROM cost_CD_base
    UNION ALL
    SELECT * FROM cost_DO_base
) AS combined_cost_data
 LIMIT 10000;
```
- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![cost](https://hackmd.io/_uploads/HysJDOI4kx.png)

### death
```sql=
WITH death_base AS (
    SELECT 
        "PERSON"."person_id" AS "person_id", 
        CAST("ID"."lastvisit" AS DATE) AS "death_date",
        32815 AS "death_type_concept_id"
    FROM 
        "ID"
    JOIN 
        "PERSON" ON "ID"."uuid" = "PERSON"."person_source_value"
    WHERE 
        "ID"."lastvisit" != '' 
        AND "ID"."lastvisit" NOT IN ('20182332', '20201920')
        AND "ID"."isdead" = 'true'
),
ranked_deaths AS (
    SELECT 
        "person_id",
        "death_date",
        "death_type_concept_id",
        ROW_NUMBER() OVER (PARTITION BY "person_id" ORDER BY "death_date" ASC) AS row_num
    FROM death_base
)
INSERT INTO "death" (
    "person_id", 
    "death_date",
    "death_type_concept_id"
)
SELECT 
    "person_id", 
    "death_date",
    "death_type_concept_id"
FROM ranked_deaths
WHERE row_num = 1;
```

- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
![d](https://hackmd.io/_uploads/BJjKAIA4kl.png)


### condition era & drug era

- [官方網站SQL](https://ohdsi.github.io/CommonDataModel/sqlScripts.html#drug_eras)
- [官方GithubSQL](https://github.com/OHDSI/ETL-CMS/tree/master/SQL)

## 範例二：TPMI
此範例主要處理TPMI（台灣精準醫療計畫）的HIS（醫院資訊系統）數據，專注於腎臟疾病的分析：

* **`HIS資料` (HIS Data)**：這部分SQL腳本用於：
* **計算年齡 (`count_age`)**：根據病患的生日計算其年齡。
* **計算肌酐值 (`count_scr`)**：從 `LAB_20231003` 表格中提取並處理血清肌酐（Scr）數據。
* **計算eGFR (`count_egfr`)**：利用肌酐值、年齡和性別等因素，根據特定的CKD-EPI公式計算預估腎小管過濾率（eGFR）。
* **識別持續性慢性腎臟病 (CKD)**：透過分析eGFR隨時間的變化，識別出符合持續性CKD定義的病患。
* **收集相關生理指標和實驗室數據**：在識別出CKD病患後，進一步收集其在特定時間範圍內的體重、BMI、血壓等生理指標，以及血清電解質、尿酸、膽固醇等實驗室數據。
* **判斷末期腎臟病 (ESRD)**：根據eGFR值和透析相關的ICD編碼，判斷病患是否已進入末期腎臟病。

### HIS資料
```sql=
-- 臨時資料表 1: 計算年齡
CREATE TEMP TABLE count_age AS
SELECT
    uuid,
    birthday,
    CASE
        WHEN birthday = '19470229' THEN NULL
        WHEN birthday ~ '^[0-9]{8}$' AND LENGTH(birthday) = 8 THEN
            CASE
                WHEN TO_DATE(birthday, 'YYYYMMDD') IS NOT NULL
                     AND EXTRACT(DOY FROM TO_DATE(birthday, 'YYYYMMDD')) IS NOT NULL
                     AND TO_DATE(birthday, 'YYYYMMDD') <= CURRENT_DATE THEN
                    DATE_PART('year', AGE(CURRENT_DATE, TO_DATE(birthday, 'YYYYMMDD')))
                ELSE NULL
            END
        ELSE NULL
    END AS age
FROM "NEPHR"."ID"
WHERE birthday ~ '^[0-9]{8}$'
      AND LENGTH(birthday) = 8
      AND (birthday <> '19470229'
           AND TO_DATE(birthday, 'YYYYMMDD') IS NOT NULL
           AND EXTRACT(DOY FROM TO_DATE(birthday, 'YYYYMMDD')) IS NOT NULL
           AND TO_DATE(birthday, 'YYYYMMDD') <= CURRENT_DATE);

-- 臨時資料表 2: 計算 Scr 值
CREATE TEMP TABLE count_scr AS
SELECT
    "NEPHR"."LAB_20231003".uuid,
    "NEPHR"."LAB_20231003".pfkey,
    "NEPHR"."LAB_20231003".Rdate,
    "NEPHR"."LAB_20231003".item,
    CASE
        WHEN "NEPHR"."LAB_20231003".outcome ~ '^[0-9]+(\.[0-9]+)?$' THEN
            NULLIF("NEPHR"."LAB_20231003".outcome, '')::numeric
        ELSE NULL
    END AS Scr
FROM "NEPHR"."LAB_20231003"
JOIN "NEPHR"."ID" ON "NEPHR"."ID".uuid = "NEPHR"."LAB_20231003".uuid
WHERE "NEPHR"."LAB_20231003".pfkey IN ('90111100', '90691100')
AND "NEPHR"."LAB_20231003".item = 'CREAT';

-- 臨時資料表 3: 計算 eGFR
CREATE TEMP TABLE count_egfr AS
SELECT
    count_scr.uuid,
    count_scr.pfkey,
    count_scr.Rdate,
    count_scr.Scr,
    "NEPHR"."ID".sex,
    count_age.age,
    CASE
        WHEN "NEPHR"."ID".sex = '1' AND count_scr.Scr > 0 THEN 
            142 * POWER(LEAST(count_scr.Scr / 0.9, 1), -0.302) * POWER(GREATEST(count_scr.Scr / 0.9, 1), -1.200)
        WHEN "NEPHR"."ID".sex = '2' AND count_scr.Scr > 0 THEN 
            142 * POWER(LEAST(count_scr.Scr / 0.7, 1), -0.241) * POWER(GREATEST(count_scr.Scr / 0.7, 1), -1.200)
        ELSE NULL
    END * 
    POWER(0.9938, count_age.age) *
    CASE
        WHEN "NEPHR"."ID".sex = '2' THEN 1.012
        ELSE 1.000
    END AS eGFR_
FROM count_scr
JOIN count_age ON count_scr.uuid = count_age.uuid
LEFT OUTER JOIN "NEPHR"."ID" ON "NEPHR"."ID".uuid = count_scr.uuid
WHERE count_scr.Scr IS NOT NULL
  AND count_age.age IS NOT NULL
  AND "NEPHR"."ID".sex IS NOT NULL
  AND (CASE
        WHEN "NEPHR"."ID".sex = '1' AND count_scr.Scr > 0 THEN 
            142 * POWER(LEAST(count_scr.Scr / 0.9, 1), -0.302) * POWER(GREATEST(count_scr.Scr / 0.9, 1), -1.200)
        WHEN "NEPHR"."ID".sex = '2' AND count_scr.Scr > 0 THEN 
            142 * POWER(LEAST(count_scr.Scr / 0.7, 1), -0.241) * POWER(GREATEST(count_scr.Scr / 0.7, 1), -1.200)
        ELSE NULL
    END IS NOT NULL);

WITH base_data AS (
    SELECT
        uuid,
        Rdate,
        CAST(eGFR_ AS INTEGER) AS eGFR,
        LAG(Rdate) OVER (PARTITION BY uuid ORDER BY Rdate) AS prev_rdate,
        LAG(eGFR_) OVER (PARTITION BY uuid ORDER BY Rdate) AS prev_egfr
    FROM count_egfr
),
accumulated_data AS (
    SELECT
        uuid,
        Rdate,
        eGFR,
        prev_rdate,
        CASE
            WHEN prev_rdate IS NULL OR eGFR >= 60 THEN 0
            ELSE DATE_PART('day', Rdate::timestamp - prev_rdate::timestamp)
        END AS days_since_last,
        LEAST(
            SUM(
                CASE
                    WHEN prev_rdate IS NULL OR eGFR >= 60 THEN 0
                    ELSE DATE_PART('day', Rdate::timestamp - prev_rdate::timestamp)
                END
            ) OVER (
                PARTITION BY uuid
                ORDER BY Rdate
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ),
            90
        ) AS accum_days,
        CASE
            WHEN LEAST(
                SUM(
                    CASE
                        WHEN prev_rdate IS NULL OR eGFR >= 60 THEN 0
                        ELSE DATE_PART('day', Rdate::timestamp - prev_rdate::timestamp)
                    END
                ) OVER (
                    PARTITION BY uuid
                    ORDER BY Rdate
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ),
                90
            ) >= 90 THEN 1 ELSE 0
        END AS has_persistent_ckd
    FROM base_data
),
persistent_ckd AS (
    SELECT
        uuid,
        MIN(Rdate) AS index_day,
        MIN(eGFR) FILTER (WHERE eGFR < 60) AS index_day_egfr
    FROM accumulated_data
    WHERE has_persistent_ckd = 1
    AND NOT EXISTS (
        SELECT 1
        FROM "NEPHR"."DD" d
        WHERE d.uuid = accumulated_data.uuid
          AND (
              d.icd9cm_code IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
              OR d.icd9cm_code_1 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
              OR d.icd9cm_code_2 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
              OR d.icd9cm_code_3 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
              OR d.icd9cm_code_4 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
          )
    )
    AND NOT EXISTS (
        SELECT 1
        FROM base_data e
        WHERE e.uuid = accumulated_data.uuid
          AND e.Rdate = accumulated_data.Rdate
          AND e.eGFR < 15
    )
    GROUP BY uuid
),
avg_vital AS (
    SELECT
        g.uuid,
        AVG(CAST(v.outcome AS NUMERIC)) FILTER (WHERE v.item = 'WT') AS BW,
        AVG(CAST(v.outcome AS NUMERIC)) FILTER (WHERE v.item = 'BMI') AS BMI,
        AVG(CAST(v.outcome AS NUMERIC)) FILTER (WHERE v.item = 'SBP') AS SBP,
        AVG(CAST(v.outcome AS NUMERIC)) FILTER (WHERE v.item = 'DBP') AS DBP
    FROM persistent_ckd g
    LEFT JOIN "NEPHR"."VITAL" v
        ON v.uuid = g.uuid
    WHERE TO_DATE(v.rdate, 'YYYY-MM-DD') BETWEEN g.index_day - INTERVAL '6 months' AND g.index_day
    GROUP BY g.uuid
),
filtered_lab_data_with_pfkey AS
(
    SELECT
        persistent_ckd.uuid,
        persistent_ckd.index_day,
        "NEPHR"."LAB_20231003".Rdate,
        "NEPHR"."LAB_20231003".item,
        NULLIF(outcome, '')::numeric AS value
    FROM persistent_ckd
    INNER JOIN "NEPHR"."LAB_20231003"
    ON persistent_ckd.uuid = "NEPHR"."LAB_20231003".uuid
    AND "NEPHR"."LAB_20231003".Rdate <= persistent_ckd.index_day
    AND "NEPHR"."LAB_20231003".Rdate > persistent_ckd.index_day - INTERVAL '1 year'
    WHERE item IN ('CREAT', 'NA', 'K', 'CL', 'UA', 'CA', 'ALB', 'UPCR', 'UACR', 'IP')
        AND pfkey IS NOT NULL
        AND outcome ~ '^[0-9]*\.?[0-9]+$'
        AND (
            (item = 'CREAT' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'NA' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'K' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'CL' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'UA' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'CA' AND pfkey IN ('90111100', '90611100')) OR
            (item = 'ALB' AND pfkey IN ('90111100', '90611100', '90109T38')) OR
            (item = 'UPCR' AND pfkey = '90149S00') OR
            (item = 'UACR' AND pfkey = '90142A11') OR
            (item = 'IP' AND pfkey IN ('90111100', '90611100'))
        )
),
filtered_lab_data_without_pfkey AS (
    SELECT
        persistent_ckd.uuid,
        persistent_ckd.index_day,
        "NEPHR"."LAB_20231003".Rdate,
        "NEPHR"."LAB_20231003".item,
        NULLIF(outcome, '')::numeric AS value
    FROM persistent_ckd
    INNER JOIN "NEPHR"."LAB_20231003"
    ON persistent_ckd.uuid = "NEPHR"."LAB_20231003".uuid
    AND "NEPHR"."LAB_20231003".Rdate <= persistent_ckd.index_day
    AND "NEPHR"."LAB_20231003".Rdate > persistent_ckd.index_day - INTERVAL '1 year'
    WHERE item IN ('CHOL', 'HCO3', 'HBA1C', 'BUN', 'HDLC', 'LDLC', 'TG', 'WBC', 'HGB')
		AND pfkey IS NOT NULL
        AND outcome ~ '^[0-9]*\.?[0-9]+$'
),
ranked_lab_data AS (
    SELECT
        uuid,
        item,
        value,
        Rdate,
        ROW_NUMBER() OVER (
            PARTITION BY uuid, item
            ORDER BY ABS(DATE_PART('day', index_day::timestamp - Rdate::timestamp)) ASC
        ) AS rn
    FROM (
        SELECT * FROM filtered_lab_data_with_pfkey
        UNION ALL
        SELECT * FROM filtered_lab_data_without_pfkey
    ) AS lab_data
),
final_output AS (
    SELECT
        uuid,
        MAX(value) FILTER (WHERE item = 'CREAT') AS CREAT,
        MAX(value) FILTER (WHERE item = 'NA') AS NA,
        MAX(value) FILTER (WHERE item = 'K') AS K,
        MAX(value) FILTER (WHERE item = 'CL') AS CL,
        MAX(value) FILTER (WHERE item = 'UA') AS UA,
        MAX(value) FILTER (WHERE item = 'CA') AS CA,
        MAX(value) FILTER (WHERE item = 'ALB') AS ALB,
        MAX(value) FILTER (WHERE item = 'UPCR') AS UPCR,
        MAX(value) FILTER (WHERE item = 'UACR') AS UACR,
        MAX(value) FILTER (WHERE item = 'IP') AS IP,
        MAX(value) FILTER (WHERE item = 'CHOL') AS CHOL,
        MAX(value) FILTER (WHERE item = 'HCO3') AS HCO3,
        MAX(value) FILTER (WHERE item = 'HBA1C') AS HBA1C,
        MAX(value) FILTER (WHERE item = 'BUN') AS BUN,
        MAX(value) FILTER (WHERE item = 'HDLC') AS HDL,
        MAX(value) FILTER (WHERE item = 'LDLC') AS LDL,
        MAX(value) FILTER (WHERE item = 'TG') AS TG,
        MAX(value) FILTER (WHERE item = 'WBC') AS WBC,
        MAX(value) FILTER (WHERE item = 'HGB') AS Hb
    FROM ranked_lab_data
    WHERE rn = 1
    GROUP BY uuid
),
eGFR_Check AS (
    SELECT uuid, 
           MIN(Rdate) AS ESRD_date
    FROM (
        SELECT persistent_ckd.uuid, 
               (base_data.Rdate::text)::date AS Rdate, 
               base_data.eGFR,
               (persistent_ckd.index_day::text)::date AS index_day,
               LAG((base_data.Rdate::text)::date) OVER (PARTITION BY persistent_ckd.uuid ORDER BY (base_data.Rdate::text)::date) AS prev_date,
               LAG(base_data.eGFR) OVER (PARTITION BY persistent_ckd.uuid ORDER BY (base_data.Rdate::text)::date) AS prev_egfr
        FROM persistent_ckd
        LEFT JOIN base_data ON persistent_ckd.uuid = base_data.uuid
        WHERE base_data.eGFR < 15
          AND (base_data.Rdate::text)::date > (persistent_ckd.index_day::text)::date
    ) AS eGFR_CTE
    WHERE prev_date IS NOT NULL
      AND AGE(Rdate, prev_date) > INTERVAL '3 months'
      AND prev_egfr < 15
    GROUP BY uuid
),
Dialysis_Check AS (
    SELECT uuid, 
           MIN(Rdate) AS ESRD_date
    FROM (
        SELECT persistent_ckd.uuid, 
               ("NEPHR"."DD".in_date::text)::date AS Rdate,
               LAG(("NEPHR"."DD".in_date::text)::date) OVER (PARTITION BY persistent_ckd.uuid ORDER BY ("NEPHR"."DD".in_date::text)::date) AS prev_date
        FROM persistent_ckd
        LEFT JOIN "NEPHR"."DD" ON persistent_ckd.uuid = "NEPHR"."DD".uuid
        WHERE (
            icd9cm_code IN ('58001C', '58002C', '58009B', '58010B', '58011B', '58011C', '58012B', '58017B', '58017C', '58018C', '58019C', '58020C', '58021C', '58022C', '58023C', '58024C', '58025C', '58026C', '58027C', '58028C', '58029C', '58001CA', '58002CB', '58010A', '58011A', '58011AB', '58012A', '58013C', '58014C', '58030B')
            OR icd9cm_code_1 IN ('58001C', '58002C', '58009B', '58010B', '58011B', '58011C', '58012B', '58017B', '58017C', '58018C', '58019C', '58020C', '58021C', '58022C', '58023C', '58024C', '58025C', '58026C', '58027C', '58028C', '58029C', '58001CA', '58002CB', '58010A', '58011A', '58011AB', '58012A', '58013C', '58014C', '58030B')
            OR icd9cm_code_2 IN ('58001C', '58002C', '58009B', '58010B', '58011B', '58011C', '58012B', '58017B', '58017C', '58018C', '58019C', '58020C', '58021C', '58022C', '58023C', '58024C', '58025C', '58026C', '58027C', '58028C', '58029C', '58001CA', '58002CB', '58010A', '58011A', '58011AB', '58012A', '58013C', '58014C', '58030B')
            OR icd9cm_code_3 IN ('58001C', '58002C', '58009B', '58010B', '58011B', '58011C', '58012B', '58017B', '58017C', '58018C', '58019C', '58020C', '58021C', '58022C', '58023C', '58024C', '58025C', '58026C', '58027C', '58028C', '58029C', '58001CA', '58002CB', '58010A', '58011A', '58011AB', '58012A', '58013C', '58014C', '58030B')
            OR icd9cm_code_4 IN ('58001C', '58002C', '58009B', '58010B', '58011B', '58011C', '58012B', '58017B', '58017C', '58018C', '58019C', '58020C', '58021C', '58022C', '58023C', '58024C', '58025C', '58026C', '58027C', '58028C', '58029C', '58001CA', '58002CB', '58010A', '58011A', '58011AB', '58012A', '58013C', '58014C', '58030B')
        )
        AND ("NEPHR"."DD".in_date::text)::date > (persistent_ckd.index_day::text)::date
    ) AS Dialysis_CTE
    WHERE prev_date IS NOT NULL
      AND AGE(Rdate, prev_date) > INTERVAL '3 months'
    GROUP BY uuid
),
Transplant_Check AS (
    SELECT uuid, 
           MIN(Rdate) AS ESRD_date
    FROM (
        SELECT persistent_ckd.uuid, 
               ("NEPHR"."DD".in_date::text)::date AS Rdate
        FROM  persistent_ckd
        LEFT JOIN "NEPHR"."DD" ON persistent_ckd.uuid = "NEPHR"."DD".uuid
        WHERE (
            icd9cm_code IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
            OR icd9cm_code_1 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
            OR icd9cm_code_2 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
            OR icd9cm_code_3 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
            OR icd9cm_code_4 IN ('V420', '99681', 'Z940', 'T8610', 'T8611', 'T8612', 'T8613', 'T8619')
        )
        AND ("NEPHR"."DD".in_date::text)::date > (persistent_ckd.index_day::text)::date
    ) AS Transplant_CTE
    GROUP BY uuid
),
esrd_final AS (
    SELECT DISTINCT persistent_ckd.uuid,
           CASE 
               WHEN EXISTS (SELECT 1 FROM eGFR_Check WHERE eGFR_Check.uuid = persistent_ckd.uuid) THEN 1
               WHEN EXISTS (SELECT 1 FROM Dialysis_Check WHERE Dialysis_Check.uuid = persistent_ckd.uuid) THEN 1
               WHEN EXISTS (SELECT 1 FROM Transplant_Check WHERE Transplant_Check.uuid = persistent_ckd.uuid) THEN 1
               ELSE 0
           END AS ESRD,
		   CASE 
               WHEN EXISTS (SELECT 1 FROM eGFR_Check WHERE eGFR_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM eGFR_Check WHERE eGFR_Check.uuid = persistent_ckd.uuid)
               WHEN EXISTS (SELECT 1 FROM Dialysis_Check WHERE Dialysis_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM Dialysis_Check WHERE Dialysis_Check.uuid = persistent_ckd.uuid)
               WHEN EXISTS (SELECT 1 FROM Transplant_Check WHERE Transplant_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM Transplant_Check WHERE Transplant_Check.uuid = persistent_ckd.uuid)
               ELSE NULL
           END AS ESRD_date,
           CASE 
               WHEN EXISTS (SELECT 1 FROM eGFR_Check WHERE eGFR_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM eGFR_Check WHERE eGFR_Check.uuid = persistent_ckd.uuid) - (persistent_ckd.index_day::date)
               WHEN EXISTS (SELECT 1 FROM Dialysis_Check WHERE Dialysis_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM Dialysis_Check WHERE Dialysis_Check.uuid = persistent_ckd.uuid) - (persistent_ckd.index_day::date)
               WHEN EXISTS (SELECT 1 FROM Transplant_Check WHERE Transplant_Check.uuid = persistent_ckd.uuid) THEN 
                   (SELECT MIN(ESRD_date) FROM Transplant_Check WHERE Transplant_Check.uuid = persistent_ckd.uuid) - (persistent_ckd.index_day::date)
               ELSE NULL
           END AS ESRD_time
    FROM persistent_ckd
),
latest_LVEF AS (
    SELECT DISTINCT ON (r.uuid)
        r.uuid,
        r.outcome AS LVEF,
        r.rdate::timestamp AS rdate
    FROM
        "NEPHR"."REPORT_ECHOCARDIO" r
    JOIN persistent_ckd p ON r.uuid::TEXT = p.uuid
    WHERE
        r.pfkey IN ('82018005', '82019001', '922CVECO')
        AND r.rdate::timestamp BETWEEN p.index_day::timestamp - INTERVAL '1 year' AND p.index_day::timestamp
    ORDER BY
        r.uuid,  -- 首先按 uuid 分組
        ABS(EXTRACT(EPOCH FROM (p.index_day::timestamp - r.rdate::timestamp)))  -- 然後按時間差排序
),
DiseaseFlags AS (
    SELECT
        persistent_ckd.uuid,
        persistent_ckd.index_day,
        -- 高血壓 Hypertension
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code IN ('401', '402', '403', '404', '405')
                     OR "NEPHR"."DD".icd9cm_code_1 IN ('401', '402', '403', '404', '405')
                     OR "NEPHR"."DD".icd9cm_code_2 IN ('401', '402', '403', '404', '405')
                     OR "NEPHR"."DD".icd9cm_code_3 IN ('401', '402', '403', '404', '405')
                     OR "NEPHR"."DD".icd9cm_code_4 IN ('401', '402', '403', '404', '405')
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I10%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I10%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I10%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I10%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I10%' THEN 1 ELSE 0 END) AS Hypertension,
        -- 糖尿病 DM
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code IN ('250', '648')
                     OR "NEPHR"."DD".icd9cm_code_1 IN ('250', '648')
                     OR "NEPHR"."DD".icd9cm_code_2 IN ('250', '648')
                     OR "NEPHR"."DD".icd9cm_code_3 IN ('250', '648')
                     OR "NEPHR"."DD".icd9cm_code_4 IN ('250', '648')
                     OR "NEPHR"."DD".icd9cm_code LIKE 'E10%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'E10%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'E10%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'E10%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'E10%' THEN 1 ELSE 0 END) AS DM,
        -- 冠狀動脈疾病 CAD
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code IN ('410', '411', '412', '413', '414')
                     OR "NEPHR"."DD".icd9cm_code_1 IN ('410', '411', '412', '413', '414')
                     OR "NEPHR"."DD".icd9cm_code_2 IN ('410', '411', '412', '413', '414')
                     OR "NEPHR"."DD".icd9cm_code_3 IN ('410', '411', '412', '413', '414')
                     OR "NEPHR"."DD".icd9cm_code_4 IN ('410', '411', '412', '413', '414')
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I21%' THEN 1 ELSE 0 END) AS CAD,
        -- 心衰竭 HeartFailure
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code = '428'
                     OR "NEPHR"."DD".icd9cm_code_1 = '428'
                     OR "NEPHR"."DD".icd9cm_code_2 = '428'
                     OR "NEPHR"."DD".icd9cm_code_3 = '428'
                     OR "NEPHR"."DD".icd9cm_code_4 = '428'
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I50%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I50%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I50%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I50%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I50%' THEN 1 ELSE 0 END) AS HeartFailure,
        -- 外周動脈閉塞病 PAOD
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code = '443'
                     OR "NEPHR"."DD".icd9cm_code_1 = '443'
                     OR "NEPHR"."DD".icd9cm_code_2 = '443'
                     OR "NEPHR"."DD".icd9cm_code_3 = '443'
                     OR "NEPHR"."DD".icd9cm_code_4 = '443'
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I73%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I73%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I73%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I73%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I73%' THEN 1 ELSE 0 END) AS PAOD,
        -- 中風 CVA
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code BETWEEN '430' AND '438'
                     OR "NEPHR"."DD".icd9cm_code_1 BETWEEN '430' AND '438'
                     OR "NEPHR"."DD".icd9cm_code_2 BETWEEN '430' AND '438'
                     OR "NEPHR"."DD".icd9cm_code_3 BETWEEN '430' AND '438'
                     OR "NEPHR"."DD".icd9cm_code_4 BETWEEN '430' AND '438'
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I60%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I60%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I60%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I60%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I60%' THEN 1 ELSE 0 END) AS CVA,
        -- 癌症 Cancer
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code BETWEEN '140' AND '208'
                     OR "NEPHR"."DD".icd9cm_code_1 BETWEEN '140' AND '208'
                     OR "NEPHR"."DD".icd9cm_code_2 BETWEEN '140' AND '208'
                     OR "NEPHR"."DD".icd9cm_code_3 BETWEEN '140' AND '208'
                     OR "NEPHR"."DD".icd9cm_code_4 BETWEEN '140' AND '208'
                     OR "NEPHR"."DD".icd9cm_code LIKE 'C%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'C%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'C%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'C%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'C%' THEN 1 ELSE 0 END) AS Cancer,
        -- 心肌梗塞 MI
        MAX(CASE WHEN "NEPHR"."DD".icd9cm_code = '410'
                     OR "NEPHR"."DD".icd9cm_code_1 = '410'
                     OR "NEPHR"."DD".icd9cm_code_2 = '410'
                     OR "NEPHR"."DD".icd9cm_code_3 = '410'
                     OR "NEPHR"."DD".icd9cm_code_4 = '410'
                     OR "NEPHR"."DD".icd9cm_code LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_1 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_2 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_3 LIKE 'I21%'
                     OR "NEPHR"."DD".icd9cm_code_4 LIKE 'I21%' THEN 1 ELSE 0 END) AS MI
    FROM persistent_ckd
    LEFT JOIN "NEPHR"."DD" ON persistent_ckd.uuid = "NEPHR"."DD".uuid
    WHERE "NEPHR"."DD".in_date <= persistent_ckd.index_day
    GROUP BY persistent_ckd.uuid, persistent_ckd.index_day
),
DeathInfo AS (
    SELECT
        persistent_ckd.uuid,
        persistent_ckd.index_day,
        "NEPHR"."ID".ISDEAD,
        MAX(CASE
                WHEN "NEPHR"."ID".ISDEAD = 'true' THEN
                    DATE_PART('day', "NEPHR"."DD".out_date::timestamp - persistent_ckd.index_day::timestamp)
                ELSE NULL
            END) AS Death_time,
        MAX(CASE
                WHEN "NEPHR"."ID".ISDEAD = 'true' THEN 1
                ELSE 0
            END) AS All_death,
        MAX(CASE
                WHEN "NEPHR"."ID".ISDEAD = 'true' THEN "NEPHR"."DD".out_date
                ELSE NULL
            END) AS All_death_date
    FROM persistent_ckd
    LEFT JOIN "NEPHR"."ID" ON persistent_ckd.uuid = "NEPHR"."ID".uuid
    LEFT JOIN "NEPHR"."DD" ON "NEPHR"."ID".uuid = "NEPHR"."DD".uuid
    GROUP BY persistent_ckd.uuid, persistent_ckd.index_day, "NEPHR"."ID".ISDEAD
),
ranked_data AS (
    -- 整合所有資料
    SELECT
        g.uuid,
        g.index_day,
        count_age.age,
        count_egfr.sex,
        avg_vital.BW,
        avg_vital.BMI,
        avg_vital.SBP,
        avg_vital.DBP,
        COALESCE(df.Hypertension::text, '0') AS HTN,
        COALESCE(df.DM::text, '0') AS DM,
        COALESCE(df.CAD::text, '0') AS CAD,
        COALESCE(df.HeartFailure::text, '0') AS CHF,
        COALESCE(df.PAOD::text, '0') AS PAOD,
        COALESCE(df.Cancer::text, '0') AS Cancer,
        COALESCE(df.CVA::text, '0') AS CVA,
        COALESCE(df.MI::text, '0') AS MI,
        final_output.CREAT,
        final_output.NA,
        final_output.K,
        final_output.CL,
        final_output.UA,
        final_output.CA,
        final_output.ALB,
        final_output.UPCR,
        final_output.UACR,
        final_output.IP,
        final_output.CHOL,
        final_output.HCO3,
        final_output.HBA1C,
        final_output.BUN,
        final_output.HDL,
        final_output.LDL,
        final_output.TG,
        final_output.WBC,
        final_output.Hb,
        e.ESRD,
        e.ESRD_date,
        e.ESRD_time,
        di.All_death,
        di.All_death_date,
        di.Death_time,
        latest_LVEF.LVEF,
        ROW_NUMBER() OVER (PARTITION BY g.uuid ORDER BY g.index_day) AS rn
    FROM persistent_ckd g
    LEFT JOIN esrd_final e ON g.uuid = e.uuid
    LEFT JOIN count_egfr ON g.uuid = count_egfr.uuid
    LEFT JOIN count_age ON g.uuid = count_age.uuid
    LEFT JOIN "NEPHR"."DD" ON g.uuid = "NEPHR"."DD".uuid
    LEFT JOIN "NEPHR"."LAB_20231003" ON g.uuid = "NEPHR"."LAB_20231003".uuid
    LEFT JOIN avg_vital ON g.uuid = avg_vital.uuid
    LEFT JOIN DiseaseFlags df ON g.uuid = df.uuid AND g.index_day = df.index_day
    LEFT JOIN DeathInfo di ON g.uuid = di.uuid AND g.index_day = di.index_day
    LEFT JOIN final_output ON g.uuid = final_output.uuid
    LEFT JOIN (
        SELECT
            persistent_ckd.uuid,
            "NEPHR"."REPORT_ECHOCARDIO".outcome AS LVEF,
            ROW_NUMBER() OVER (PARTITION BY persistent_ckd.uuid ORDER BY "NEPHR"."REPORT_ECHOCARDIO".rdate DESC) AS row_num
        FROM
            persistent_ckd
        LEFT JOIN "NEPHR"."REPORT_ECHOCARDIO" ON persistent_ckd.uuid = "NEPHR"."REPORT_ECHOCARDIO".uuid
        WHERE
            "NEPHR"."REPORT_ECHOCARDIO".pfkey IN ('82018005', '82019001', '922CVECO')
            AND "NEPHR"."REPORT_ECHOCARDIO".rdate::date BETWEEN persistent_ckd.index_day::date - INTERVAL '1 year' AND persistent_ckd.index_day::date
    ) latest_LVEF ON g.uuid = latest_LVEF.uuid AND latest_LVEF.row_num = 1
    WHERE avg_vital.uuid IS NOT NULL
)

SELECT
    uuid,
    index_day,
    age,
    sex,
    BW,
    BMI,
    SBP,
    DBP,
    HTN,
    DM,
    CAD,
    CHF,
    PAOD,
    Cancer,
    CVA,
    MI,
    CREAT,
    NA,
    K,
    CL,
    UA,
    CA,
    ALB,
    UPCR,
    UACR,
    IP,
    CHOL,
    HCO3,
    HBA1C,
    BUN,
    HDL,
    LDL,
    TG,
    WBC,
    Hb,
    LVEF,
    ESRD,
    ESRD_date,
    ESRD_time,
    All_death,
    All_death_date,
    Death_time
FROM ranked_data
WHERE rn = 1;
```
### Aldactone
### 抓取血液檢體報告
```sql=
SELECT *
FROM "NEPHR"."LAB_20231003"
WHERE pfkey IN ('90111100', '90691100');
```

### 抓取相關藥物資訊
```sql=
-- Aldactone
select *
from "NEPHR"."DRUG" d 
where LOWER(udname1) like '%spirotone%'
	or LOWER(udname1) like '%aldactone%'
	or LOWER(udname1) like '%spironolactone%'
	or LOWER(udname2) like '%spirotone%'
	or LOWER(udname2) like '%aldactone%'
	or LOWER(udname2) like '%spironolactone%';
    
-- Kalimate
select *
from "NEPHR"."DRUG"
where LOWER(udname1) like '%kalimate%' or LOWER(udname2) like '%kalimate%';
```

### 取得住院病人之日期與ICD碼
```sql=
select
    uuid as "UUID",
    in_date,
    array_to_string(
        array_remove(
            array[ICD9CM_CODE, ICD9CM_CODE_1, ICD9CM_CODE_2, ICD9CM_CODE_3, ICD9CM_CODE_4],
            ''
        ),
        ','
    ) as icd_codes
from "NEPHR"."DD";
```

### 抓取Hemorrhagic_stroke與Ischemic_stroke之病患
```sql=
-- Hemorrhagic_stroke
select
	uuid as "UUID",
	func_date::DATE AS "diagnosis_date",
	'H' as stroke_type,
    case
        when acode_icd9_1 >= '430' and acode_icd9_1 < '433' then acode_icd9_1
        when acode_icd9_2 >= '430' and acode_icd9_2 < '433' then acode_icd9_2
        when acode_icd9_3 >= '430' and acode_icd9_3 < '433' then acode_icd9_3
        when acode_icd9_1 >= 'I60' and acode_icd9_1 < 'I63' then acode_icd9_1
        when acode_icd9_2 >= 'I60' and acode_icd9_2 < 'I63' then acode_icd9_2
        when acode_icd9_3 >= 'I60' and acode_icd9_3 < 'I63' then acode_icd9_3
        else null
    end as icd_code
from "NEPHR"."CD"
where acode_icd9_1 >= '430' and acode_icd9_1 < '433'
	or acode_icd9_2 >= '430' and acode_icd9_2 < '433'
	or acode_icd9_3 >= '430' and acode_icd9_3 < '433'
	or acode_icd9_1 >= 'I60' and acode_icd9_1 < 'I63'
	or acode_icd9_2 >= 'I60' and acode_icd9_2 < 'I63'
	or acode_icd9_3 >= 'I60' and acode_icd9_3 < 'I63'
union all
select
	uuid as "UUID",
	in_date,
	'H' as stroke_type,
    case
        when icd9cm_code >= '430' and icd9cm_code < '433' then icd9cm_code
        when icd9cm_code_1 >= '430' and icd9cm_code_1 < '433' then icd9cm_code_1
        when icd9cm_code_2 >= '430' and icd9cm_code_2 < '433' then icd9cm_code_2
        when icd9cm_code_3 >= '430' and icd9cm_code_3 < '433' then icd9cm_code_3
        when icd9cm_code_4 >= '430' and icd9cm_code_4 < '433' then icd9cm_code_4
        when icd9cm_code >= 'I60' and icd9cm_code < 'I63' then icd9cm_code
        when icd9cm_code_1 >= 'I60' and icd9cm_code_1 < 'I63' then icd9cm_code_1
        when icd9cm_code_2 >= 'I60' and icd9cm_code_2 < 'I63' then icd9cm_code_2
        when icd9cm_code_3 >= 'I60' and icd9cm_code_3 < 'I63' then icd9cm_code_3
        when icd9cm_code_4 >= 'I60' and icd9cm_code_4 < 'I63' then icd9cm_code_4
        else null
    end as icd_code
from "NEPHR"."DD"
where  icd9cm_code >= '430' and icd9cm_code < '433'
	or icd9cm_code_1 >= '430' and icd9cm_code_1 < '433'
	or icd9cm_code_2 >= '430' and icd9cm_code_2 < '433'
	or icd9cm_code_3 >= '430' and icd9cm_code_3 < '433'
	or icd9cm_code_4 >= '430' and icd9cm_code_4 < '433'
	or icd9cm_code >= 'I60' and icd9cm_code < 'I63'
	or icd9cm_code_1 >= 'I60' and icd9cm_code_1 < 'I63'
	or icd9cm_code_2 >= 'I60' and icd9cm_code_2 < 'I63'
	or icd9cm_code_3 >= 'I60' and icd9cm_code_3 < 'I63'
	or icd9cm_code_4 >= 'I60' and icd9cm_code_4 < 'I63'
union all

-- Ischemic_stroke
select
	uuid as "UUID",
	func_date::DATE AS "diagnosis_date",
	'I' as stroke_type,
    case
        when acode_icd9_1 >= '433' and acode_icd9_1 < '439' then acode_icd9_1
        when acode_icd9_2 >= '433' and acode_icd9_2 < '439' then acode_icd9_2
        when acode_icd9_3 >= '433' and acode_icd9_3 < '439' then acode_icd9_3
        when acode_icd9_1 >= 'I63' and acode_icd9_1 < 'I70' then acode_icd9_1
        when acode_icd9_2 >= 'I63' and acode_icd9_2 < 'I70' then acode_icd9_2
        when acode_icd9_3 >= 'I63' and acode_icd9_3 < 'I70' then acode_icd9_3
        else null
    end as icd_code
from "NEPHR"."CD"
where acode_icd9_1 >= '433' and acode_icd9_1 < '439'
	or acode_icd9_2 >= '433' and acode_icd9_2 < '439'
	or acode_icd9_3 >= '433' and acode_icd9_3 < '439'
	or acode_icd9_1 >= 'I63' and acode_icd9_1 < 'I70'
	or acode_icd9_2 >= 'I63' and acode_icd9_2 < 'I70'
	or acode_icd9_3 >= 'I63' and acode_icd9_3 < 'I70'
union all
select
	uuid as "UUID",
	in_date,
	'I' as stroke_type,
    case
        when icd9cm_code >= '433' and icd9cm_code < '439' then icd9cm_code
        when icd9cm_code_1 >= '433' and icd9cm_code_1 < '439' then icd9cm_code_1
        when icd9cm_code_2 >= '433' and icd9cm_code_2 < '439' then icd9cm_code_2
        when icd9cm_code_3 >= '433' and icd9cm_code_3 < '439' then icd9cm_code_3
        when icd9cm_code_4 >= '433' and icd9cm_code_4 < '439' then icd9cm_code_4
        when icd9cm_code >= 'I63' and icd9cm_code < 'I70' then icd9cm_code
        when icd9cm_code_1 >= 'I63' and icd9cm_code_1 < 'I70' then icd9cm_code_1
        when icd9cm_code_2 >= 'I63' and icd9cm_code_2 < 'I70' then icd9cm_code_2
        when icd9cm_code_3 >= 'I63' and icd9cm_code_3 < 'I70' then icd9cm_code_3
        when icd9cm_code_4 >= 'I63' and icd9cm_code_4 < 'I70' then icd9cm_code_4
        else null
    end as icd_code
from "NEPHR"."DD"
where  icd9cm_code >= '433' and icd9cm_code < '439'
	or icd9cm_code_1 >= '433' and icd9cm_code_1 < '439'
	or icd9cm_code_2 >= '433' and icd9cm_code_2 < '439'
	or icd9cm_code_3 >= '433' and icd9cm_code_3 < '439'
	or icd9cm_code_4 >= '433' and icd9cm_code_4 < '439'
	or icd9cm_code >= 'I63' and icd9cm_code < 'I70'
	or icd9cm_code_1 >= 'I63' and icd9cm_code_1 < 'I70'
	or icd9cm_code_2 >= 'I63' and icd9cm_code_2 < 'I70'
	or icd9cm_code_3 >= 'I63' and icd9cm_code_3 < 'I70'
	or icd9cm_code_4 >= 'I63' and icd9cm_code_4 < 'I70'; 

```

### 填入資料
```sql=
-- Clear up values.
UPDATE aldactone.no_psm
SET "Aldactone_time" = NULL,
	"Aldactone_ratio" = NULL,
	"Aldactone75" = NULL,
	"Aldactone_mean_dose" = NULL,
	"Hemorrhagic_stroke" = NULL,
	"Ischemic_stroke" = NULL,
	"2nd_hyperK" = NULL,
	"2nd_hyperK_date" = NULL,
	"2nd_hyperK_time" = NULL,
	"Kalimate" = NULL,
	"Kalimate_date" = NULL,
	"Aldactone_stop" = NULL,
	"Aldactone_reduction" = NULL,
	"2nd_hyperK90" = NULL,
	"2nd_hyperK14" = NULL,
	"2nd_hyperK90_date" = NULL,
	"2nd_hyperK14_date" = NULL,
	"2nd_hyperK90_time" = NULL,
	"2nd_hyperK14_time" = NULL,
	"HyperK_death" = NULL,
	"Ischemic_stroke_icd" = NULL,
	"Hemorrhagic_stroke_icd" = NULL,
	"All_death_icd_1" = NULL,
	"All_death_icd_2" = NULL,
	"All_death_icd_3" = NULL,
	"All_death_icd_4" = NULL,
	"All_death_icd" = NULL;

UPDATE aldactone.psm
SET "Aldactone_time" = NULL,
	"Aldactone_ratio" = NULL,
	"Aldactone75" = NULL,
	"Aldactone_mean_dose" = NULL,
	"Hemorrhagic_stroke" = NULL,
	"Ischemic_stroke" = NULL,
	"2nd_hyperK" = NULL,
	"2nd_hyperK_date" = NULL,
	"2nd_hyperK_time" = NULL,
	"Kalimate" = NULL,
	"Kalimate_date" = NULL,
	"Aldactone_stop" = NULL,
	"Aldactone_reduction" = NULL,
	"2nd_hyperK90" = NULL,
	"2nd_hyperK14" = NULL,
	"2nd_hyperK90_date" = NULL,
	"2nd_hyperK14_date" = NULL,
	"2nd_hyperK90_time" = NULL,
	"2nd_hyperK14_time" = NULL,
	"HyperK_death" = NULL,
	"Ischemic_stroke_icd" = NULL,
	"Hemorrhagic_stroke_icd" = NULL,
	"All_death_icd_1" = NULL,
	"All_death_icd_2" = NULL,
	"All_death_icd_3" = NULL,
	"All_death_icd_4" = NULL,
	"All_death_icd" = NULL;

-- Calculate Aldactone_time, Aldactone_ratio and Aldactone75.
WITH drug_use AS (
	SELECT
		"ID",
		LEAST(SUM(drug_days), MAX("All_death_time")) AS "Aldactone_time",
		LEAST(SUM(drug_days), MAX("All_death_time"))::FLOAT / MAX("All_death_time") AS "Aldactone_ratio",
		(LEAST(SUM(drug_days), MAX("All_death_time"))::FLOAT / MAX("All_death_time") > 0.75)::INTEGER AS "Aldactone75"
	FROM (
		SELECT
		    p."ID",
		    GREATEST(
		    	LEAST(d.uddatee::DATE, p."All_death_date"::DATE) -
		    	GREATEST(d.uddates::DATE, p.index_day::DATE),
		    	0
		    ) AS drug_days,
		    p."All_death_time"
		FROM aldactone.no_psm p 
			LEFT JOIN aldactone.drug_aldactone d ON p."ID" = d.chartid::BIGINT
		WHERE d.chartid IS NOT NULL
	)
	GROUP BY "ID"
)
UPDATE aldactone.no_psm
SET "Aldactone_time" = d."Aldactone_time",
	"Aldactone_ratio" = d."Aldactone_ratio",
	"Aldactone75" = d."Aldactone75"
FROM drug_use d
WHERE aldactone.no_psm."ID" = d."ID";

UPDATE aldactone.no_psm
SET "Aldactone_time" = 0,
	"Aldactone_ratio" = 0,
	"Aldactone75" = 0
WHERE "Aldactone_time" IS NULL;

UPDATE aldactone.psm
SET "Aldactone_time" = np."Aldactone_time",
	"Aldactone_ratio" = np."Aldactone_ratio",
	"Aldactone75" = np."Aldactone75"
FROM aldactone.no_psm np 
WHERE aldactone.psm."ID" = np."ID";

-- Calculate Aldactone_mean_dose
WITH mean_dose AS (
	SELECT
		"ID",
		CASE
			WHEN SUM(drug_days) > 0 THEN SUM(aldactone_dose) / SUM(drug_days)
			ELSE 0
		END AS "Aldactone_mean_dose"
	FROM (
		SELECT
		    np."ID",
		    COALESCE(
		    	UDDOSUSE::FLOAT * fd.per_day * GREATEST(LEAST(d.uddatee::DATE, np."All_death_date"::DATE) - GREATEST(d.uddates::DATE, np.index_day::DATE), 0) * 25,
		    	0.0
		    ) AS aldactone_dose,
		    COALESCE(GREATEST(LEAST(d.uddatee::DATE, np."All_death_date"::DATE) - GREATEST(d.uddates::DATE, np.index_day::DATE), 0), 0) AS drug_days
		FROM aldactone.no_psm np
			LEFT JOIN aldactone.drug_aldactone d ON np."ID" = d.chartid::BIGINT
			LEFT JOIN aldactone.freq_decode fd ON d.udfreq = fd.freq
		WHERE d.chartid IS NOT NULL
	)
	GROUP BY "ID"
)
UPDATE aldactone.no_psm
SET "Aldactone_mean_dose" = m."Aldactone_mean_dose"
FROM mean_dose m
WHERE aldactone.no_psm."ID" = m."ID";

UPDATE aldactone.no_psm
SET "Aldactone_mean_dose" = 0
WHERE "Aldactone_mean_dose" IS NULL;

UPDATE aldactone.psm
SET "Aldactone_mean_dose" = np."Aldactone_mean_dose"
FROM aldactone.no_psm np
WHERE aldactone.psm."ID" = np."ID";

-- Hemorrhagic_stroke
UPDATE aldactone.no_psm
SET "Hemorrhagic_stroke" = 1, "Hemorrhagic_stroke_icd" = s.icd_code
FROM (
	SELECT
		np."UUID",
		s.icd_code,
		s.stroke_type,
		row_number() OVER (PARTITION BY np."UUID" ORDER BY ABS(np."Stroke_date"::DATE - s.diagnosis_date)) AS priority
	FROM aldactone.no_psm np LEFT JOIN stroke s USING ("UUID")
) s 
WHERE "Stroke" = 1
	AND aldactone.no_psm."UUID" = s."UUID"
	AND s.stroke_type = 'H'
	AND s.priority = 1;

--UPDATE aldactone.no_psm
--SET "Hemorrhagic_stroke" = 1, "Hemorrhagic_stroke_icd" = s.icd_code
--FROM (
--	SELECT
--		np."ID",
--		s.icd_code,
--		s.stroke_type,
--		row_number() OVER (PARTITION BY np."ID" ORDER BY ABS(np."Stroke_date"::DATE - s.diagnosis_date)) AS priority
--	FROM aldactone.no_psm np LEFT JOIN stroke_all s ON np."ID" = s."ID"::BIGINT
--	WHERE s."ID" <> ''
--) s 
--WHERE "Stroke" = 1
--	AND aldactone.no_psm."ID" = s."ID"
--	AND s.stroke_type = 'H'
--	AND s.priority = 1;

UPDATE aldactone.no_psm
SET "Hemorrhagic_stroke" = 0
WHERE "Hemorrhagic_stroke" IS NULL;

UPDATE aldactone.psm
SET "Hemorrhagic_stroke" = np."Hemorrhagic_stroke",
	"Hemorrhagic_stroke_icd" = np."Hemorrhagic_stroke_icd"
FROM aldactone.no_psm np 
WHERE aldactone.psm."ID" = np."ID";

-- Ischemic_stroke
UPDATE aldactone.no_psm
SET "Ischemic_stroke" = 1, "Ischemic_stroke_icd" = s.icd_code
FROM (
	SELECT
		np."UUID",
		s.icd_code,
		s.stroke_type,
		row_number() OVER (PARTITION BY np."UUID" ORDER BY ABS(np."Stroke_date"::DATE - s.diagnosis_date)) AS priority
	FROM aldactone.no_psm np LEFT JOIN stroke s USING ("UUID")
) s 
WHERE "Stroke" = 1 
	AND aldactone.no_psm."UUID" = s."UUID"
	AND s.stroke_type = 'I'
	AND s.priority = 1;

--UPDATE aldactone.no_psm
--SET "Ischemic_stroke" = 1, "Ischemic_stroke_icd" = s.icd_code
--FROM (
--	SELECT
--		np."ID",
--		s.icd_code,
--		s.stroke_type,
--		row_number() OVER (PARTITION BY np."ID" ORDER BY ABS(np."Stroke_date"::DATE - s.diagnosis_date)) AS priority
--	FROM aldactone.no_psm np LEFT JOIN stroke_all s ON np."ID" = s."ID"::BIGINT
--	WHERE s."ID" <> ''
--) s 
--WHERE "Stroke" = 1
--	AND aldactone.no_psm."ID" = s."ID"
--	AND s.stroke_type = 'I'
--	AND s.priority = 1;

UPDATE aldactone.no_psm
SET "Ischemic_stroke" = 0
WHERE "Ischemic_stroke" IS NULL;

UPDATE aldactone.psm
SET "Ischemic_stroke" = np."Ischemic_stroke",
	"Ischemic_stroke_icd" = np."Ischemic_stroke_icd"
FROM aldactone.no_psm np 
WHERE aldactone.psm."ID" = np."ID";

-- Kalimate and Kalimate_date
WITH drug_use AS (
	SELECT
		p."ID",
		MIN(dk.uddates::DATE) AS "Kalimate_date"
	FROM aldactone.no_psm p
		LEFT JOIN aldactone.drug_kalimate dk ON p."ID" = dk.chartid::BIGINT
	WHERE dk.chartid IS NOT NULL
		AND p."HyperK" = 1
		AND dk.uddates::DATE BETWEEN
			p."HyperK_date"::DATE AND p."HyperK_date"::DATE + INTERVAL '90 days'
	GROUP BY p."ID"
)
UPDATE aldactone.no_psm
SET "Kalimate" = 1, "Kalimate_date" = du."Kalimate_date"
FROM drug_use du
WHERE no_psm."ID" = du."ID";

UPDATE aldactone.no_psm
SET "Kalimate" = 0
WHERE "Kalimate" IS NULL;

UPDATE aldactone.psm
SET "Kalimate" = np."Kalimate", "Kalimate_date" = np."Kalimate_date"
FROM aldactone.no_psm np
WHERE psm."ID" = np."ID";

-- Aldactone_stop
WITH drug_dates AS (
	SELECT da.chartid::BIGINT AS "ID",
		da.uddatee::DATE AS last_date,
		row_number() OVER (PARTITION BY da.chartid ORDER BY da.uddatee DESC) AS priority
	FROM aldactone.drug_aldactone da
)
UPDATE aldactone.no_psm
SET "Aldactone_stop" = ("ID" IN (
	SELECT p."ID"
	FROM no_psm p LEFT JOIN drug_dates dd USING ("ID")
	WHERE dd.priority = 1
		AND p."HyperK" = 1
		AND dd.last_date BETWEEN p."HyperK_date"::DATE AND p."HyperK_date"::DATE + INTERVAL '90 days'
	GROUP BY p."ID")
)::INTEGER;

UPDATE aldactone.psm
SET "Aldactone_stop" = p."Aldactone_stop"
FROM aldactone.no_psm p
WHERE aldactone.psm."ID" = p."ID";

-- Aldactone_reduction
WITH target_ids AS (
	SELECT
		"ID"
	FROM (
		SELECT
			np."ID",
			LAG(da.uddosuse::FLOAT * fd.per_day) OVER (PARTITION BY np."ID" ORDER BY da.uddates::DATE) AS prev_drug_amount,
			da.uddosuse::FLOAT * fd.per_day AS drug_amount
		FROM aldactone.no_psm np
			LEFT JOIN aldactone.drug_aldactone da ON np."ID" = da.chartid::BIGINT
			LEFT JOIN aldactone.freq_decode fd ON da.udfreq = fd.freq
		WHERE np."HyperK" = 1
			AND da.uddates::DATE BETWEEN np."HyperK_date"::DATE AND np."HyperK_date"::DATE + INTERVAL '90 days'
	)
	WHERE prev_drug_amount > drug_amount
	GROUP BY "ID"
)
UPDATE aldactone.no_psm
SET "Aldactone_reduction" = ("ID" IN (SELECT "ID" FROM target_ids))::INTEGER;

UPDATE aldactone.psm
SET "Aldactone_reduction" = np."Aldactone_reduction"
FROM aldactone.no_psm np
WHERE aldactone.psm."ID" = np."ID";

-- 2nd_hyperK
UPDATE aldactone.no_psm
SET "2nd_hyperK" = 1,
	"2nd_hyperK_date" = r."2nd_hyperK_date",
	"2nd_hyperK_time" = r."2nd_hyperK_time"
FROM (
	SELECT
		np."ID",
		MIN(l.sdate) AS "2nd_hyperK_date",
		MIN(l.sdate) - MIN("HyperK_date")::DATE AS "2nd_hyperK_time"
	FROM aldactone.no_psm np LEFT JOIN aldactone.lab_k l ON np."UUID" = l.uuid
	WHERE np."HyperK" = 1
		AND l.sdate >= "HyperK_date"::DATE
		AND LENGTH(REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')) > 0
		AND REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')::FLOAT > 6.0
	GROUP BY np."ID"
) r
WHERE no_psm."ID" = r."ID";

UPDATE aldactone.no_psm
SET "2nd_hyperK" = 0
WHERE "2nd_hyperK" IS NULL;

UPDATE aldactone.psm
SET
	"2nd_hyperK" = p."2nd_hyperK",
	"2nd_hyperK_date" = p."2nd_hyperK_date",
	"2nd_hyperK_time" = p."2nd_hyperK_time"
FROM aldactone.no_psm p
WHERE aldactone.psm."ID" = p."ID";

-- 2nd_hyperK90
UPDATE aldactone.no_psm
SET "2nd_hyperK90" = 1,
	"2nd_hyperK90_date" = r."2nd_hyperK90_date",
	"2nd_hyperK90_time" = r."2nd_hyperK90_time"
FROM (
	SELECT
		np."ID",
		MIN(l.sdate) AS "2nd_hyperK90_date",
		MIN(l.sdate) - MIN("HyperK_date")::DATE AS "2nd_hyperK90_time"
	FROM aldactone.no_psm np LEFT JOIN aldactone.lab_k l ON np."UUID" = l.uuid
	WHERE np."HyperK" = 1
		AND l.sdate >= "HyperK_date"::DATE + INTERVAL '90 days'
		AND LENGTH(REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')) > 0
		AND REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')::FLOAT > 6.0
	GROUP BY np."ID"
) r
WHERE no_psm."ID" = r."ID";

UPDATE aldactone.no_psm
SET "2nd_hyperK90" = 0
WHERE "2nd_hyperK90" IS NULL;

UPDATE aldactone.psm
SET
	"2nd_hyperK90" = p."2nd_hyperK90",
	"2nd_hyperK90_date" = p."2nd_hyperK90_date",
	"2nd_hyperK90_time" = p."2nd_hyperK90_time"
FROM aldactone.no_psm p
WHERE aldactone.psm."ID" = p."ID";

-- 2nd_hyperK14
UPDATE aldactone.no_psm
SET "2nd_hyperK14" = 1,
	"2nd_hyperK14_date" = r."2nd_hyperK14_date",
	"2nd_hyperK14_time" = r."2nd_hyperK14_time"
FROM (
	SELECT
		np."ID",
		MIN(l.sdate) AS "2nd_hyperK14_date",
		MIN(l.sdate) - MIN("HyperK_date")::DATE AS "2nd_hyperK14_time"
	FROM aldactone.no_psm np LEFT JOIN aldactone.lab_k l ON np."UUID" = l.uuid
	WHERE np."HyperK" = 1
		AND l.sdate >= "HyperK_date"::DATE + INTERVAL '14 days'
		AND LENGTH(REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')) > 0
		AND REGEXP_REPLACE(l.outcome, '[^0-9.]', '', 'g')::FLOAT > 6.0
	GROUP BY np."ID"
) r
WHERE no_psm."ID" = r."ID";

UPDATE aldactone.no_psm
SET "2nd_hyperK14" = 0
WHERE "2nd_hyperK14" IS NULL;

UPDATE aldactone.psm
SET
	"2nd_hyperK14" = p."2nd_hyperK14",
	"2nd_hyperK14_date" = p."2nd_hyperK14_date",
	"2nd_hyperK14_time" = p."2nd_hyperK14_time"
FROM aldactone.no_psm p
WHERE aldactone.psm."ID" = p."ID";

-- HyperK_death
UPDATE aldactone.no_psm
SET "HyperK_death" = r."HyperK_death"
FROM (
    SELECT
        np."ID",
        (REGEXP_REPLACE(lk.outcome, '[^0-9.]', '', 'g')::FLOAT > 6.0)::INTEGER AS "HyperK_death"
    FROM aldactone.no_psm np
    	LEFT JOIN aldactone.lab_k lk ON np."UUID" = lk."uuid"
    WHERE np."All_death" = 1
      AND LENGTH(REGEXP_REPLACE(lk.outcome, '[^0-9.]', '', 'g')) > 0
      AND SUBSTRING(np."All_death_date", 1, 10)::DATE = lk.sdate
) r
WHERE aldactone.no_psm."ID" = r."ID";

UPDATE aldactone.no_psm
SET "HyperK_death" = 0
WHERE "HyperK_death" IS NULL;

UPDATE aldactone.psm
SET "HyperK_death" = np."HyperK_death"
FROM aldactone.no_psm np
WHERE aldactone.psm."ID" = np."ID";

-- All_death_icd
WITH inpatient_dates AS (
	SELECT
		p."UUID",
		ROW_NUMBER() OVER (PARTITION BY p."UUID" ORDER BY ABS(p."All_death_date"::DATE - i.in_date)) AS priority,
		(STRING_TO_ARRAY(i.icd_codes, ','))[1] AS "All_death_icd",
		(STRING_TO_ARRAY(i.icd_codes, ','))[2] AS "All_death_icd_1",
		(STRING_TO_ARRAY(i.icd_codes, ','))[3] AS "All_death_icd_2",
		(STRING_TO_ARRAY(i.icd_codes, ','))[4] AS "All_death_icd_3",
		(STRING_TO_ARRAY(i.icd_codes, ','))[5] AS "All_death_icd_4"
	FROM
		aldactone.no_psm p LEFT JOIN aldactone.inpatient i USING ("UUID")
)
UPDATE aldactone.no_psm
SET "All_death_icd" = i."All_death_icd",
	"All_death_icd_1" = i."All_death_icd_1",
	"All_death_icd_2" = i."All_death_icd_2",
	"All_death_icd_3" = i."All_death_icd_3",
	"All_death_icd_4" = i."All_death_icd_4"
FROM inpatient_dates i
WHERE "All_death" = 1
	AND aldactone.no_psm."UUID" = i."UUID"
	AND i.priority = 1;

UPDATE aldactone.psm
SET "All_death_icd" = p."All_death_icd",
	"All_death_icd_1" = p."All_death_icd_1",
	"All_death_icd_2" = p."All_death_icd_2",
	"All_death_icd_3" = p."All_death_icd_3",
	"All_death_icd_4" = p."All_death_icd_4"
FROM aldactone.no_psm p
WHERE aldactone.psm."ID" = p."ID";
```
### eGFR & index day
```sql=
WITH count_scr AS (
    SELECT
        lab.uuid,
        lab.Rdate,
        CASE
            WHEN lab.outcome ~ '^[0-9]+(\.[0-9]+)?$' THEN lab.outcome::numeric
            ELSE NULL
        END AS Scr
    FROM "NEPHR"."LAB_20231003" lab
    WHERE lab.pfkey IN ('90111100', '90691100') AND lab.item = 'CREAT'
),
count_age AS (
    SELECT
        uuid,
        CASE
            WHEN birthday = '19470229' THEN NULL
            WHEN birthday ~ '^[0-9]{8}$' THEN
                CASE WHEN TO_DATE(birthday, 'YYYYMMDD') <= CURRENT_DATE THEN
                    DATE_PART('year', AGE(CURRENT_DATE, TO_DATE(birthday, 'YYYYMMDD')))
                ELSE NULL END
            ELSE NULL
        END AS age
    FROM "NEPHR"."ID"
),
count_egfr AS (
    SELECT
        cs.uuid,
        cs.Rdate,
        cs.Scr,
        id.sex,
        ca.age,
        CASE
            WHEN id.sex = '1' AND cs.Scr > 0 THEN
                142 * POWER(LEAST(cs.Scr / 0.9, 1), -0.302) * POWER(GREATEST(cs.Scr / 0.9, 1), -1.2)
            WHEN id.sex = '2' AND cs.Scr > 0 THEN
                142 * POWER(LEAST(cs.Scr / 0.7, 1), -0.241) * POWER(GREATEST(cs.Scr / 0.7, 1), -1.2)
            ELSE NULL
        END * POWER(0.9938, ca.age) * CASE WHEN id.sex = '2' THEN 1.012 ELSE 1.000 END AS eGFR_
    FROM count_scr cs
    JOIN count_age ca ON cs.uuid = ca.uuid
    JOIN "NEPHR"."ID" id ON cs.uuid = id.uuid
    WHERE cs.Scr IS NOT NULL AND ca.age IS NOT NULL AND id.sex IS NOT NULL
),
base_data AS (
    SELECT
        uuid,
        Rdate,
        CAST(eGFR_ AS INTEGER) AS eGFR,
        LAG(Rdate) OVER (PARTITION BY uuid ORDER BY Rdate) AS prev_rdate
    FROM count_egfr
),
accumulated_data AS (
    SELECT
        uuid,
        Rdate,
        eGFR,
        prev_rdate,
        CASE
            WHEN prev_rdate IS NULL OR eGFR >= 60 THEN 0
            ELSE (Rdate - prev_rdate)
        END AS days_since_last,
        LEAST(
            SUM(
                CASE
                    WHEN prev_rdate IS NULL OR eGFR >= 60 THEN 0
                    ELSE (Rdate - prev_rdate)
                END
            ) OVER (
                PARTITION BY uuid
                ORDER BY Rdate
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ),
            90
        ) AS accum_days
    FROM base_data
),
persistent_ckd AS (
    SELECT
        uuid,
        TO_CHAR((MIN(Rdate) + INTERVAL '90 days'), 'YYYY-MM-DD') AS index_day
    FROM accumulated_data
    WHERE accum_days >= 90
    GROUP BY uuid
)

SELECT uuid, index_day FROM persistent_ckd;
```