# 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`欄位
##### 插入郵遞區號數據
```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`
### 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`
### visit_occurrence
- 紀錄就診開始和結束時間
- 資料來源為院內`CD`和`DD`表格
- 目前主要紀錄門診與住院
#### CD表格
筆記：
- 可能的`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`
#### DD表格
- [Broadsea Atlas](https://github.com/OHDSI/Broadsea/blob/7e61aaeff305d1ee621d278dc643e06d5793a2b6/docker-compose.yml#L95)示範`table`
#### 輸入資料
```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
```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`
### 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`
### 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`
### 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`
### 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`
### 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;
```
Sign in with Wallet
Connect another wallet