🩺 3 คำถามจริงบน healthinfo.db

🔁 สูตรคิด 6 ขั้น (ขยายจาก wk09)

-- Scalar: เทียบค่าเฉลี่ยทั้ง รพ.
SELECT hn, hba1c
FROM patients
WHERE hba1c > (SELECT AVG(hba1c) FROM patients);

-- List: คนที่มียา Metformin (จาก prescriptions)
SELECT hn FROM patients
WHERE patient_id IN (
  SELECT patient_id FROM prescriptions
  WHERE drug='Metformin'
);

Table subquery (FROM)

-- หา avg ต่อ drug ก่อน แล้วค่อยกรอง
SELECT drug, avg_cost FROM (
  SELECT drug, AVG(cost_thb) AS avg_cost
  FROM prescriptions GROUP BY drug
) WHERE avg_cost > 50;

-- อยากได้: คนที่ "ไม่เคย" ได้ยา
-- ⚠️ เสี่ยง 0 แถวถ้าลิสต์มี NULL
SELECT hn FROM patients
WHERE patient_id NOT IN (
  SELECT patient_id FROM prescriptions
); -- ถ้า prescriptions.patient_id มี NULL → 0 แถว!

-- ✅ ปลอดภัย
SELECT hn FROM patients p
WHERE NOT EXISTS (
  SELECT 1 FROM prescriptions r
  WHERE r.patient_id = p.patient_id
);

เทียบ 3 แบบ

-- anti-join เทียบ wk07 สไลด์ 04
SELECT p.hn FROM patients p
LEFT JOIN prescriptions r ON r.patient_id=p.patient_id
WHERE r.rx_id IS NULL;

-- Correlated: ค่าใช้จ่าย > ค่าเฉลี่ยของตัวเองต่อ drug
SELECT p.hn, r.drug, r.cost_thb
FROM prescriptions r
JOIN patients p ON p.patient_id=r.patient_id
WHERE r.cost_thb > (
  SELECT AVG(cost_thb) FROM prescriptions
  WHERE drug = r.drug   -- ← อ้าง r ข้างนอก
);

Rewrite เป็น JOIN + GROUP BY

WITH avg_by_drug AS (
  SELECT drug, AVG(cost_thb) avg_c FROM prescriptions
  GROUP BY drug
)
SELECT p.hn, r.drug, r.cost_thb
FROM prescriptions r
JOIN avg_by_drug a USING(drug)
JOIN patients p ON p.patient_id=r.patient_id
WHERE r.cost_thb > a.avg_c;

SELECT hn, hba1c,
  CASE
    WHEN hba1c IS NULL THEN 'ยังไม่ได้วัด'
    WHEN hba1c < 6.5 THEN 'ปกติ'
    WHEN hba1c < 7.0 THEN 'เฝ้าระวัง'
    WHEN hba1c < 9.0 THEN 'คุมไม่ได้'
    ELSE 'วิกฤต ≥9'
  END AS a1c_bucket
FROM patients LIMIT 5;

-- ใช้ใน GROUP BY ได้เลย
SELECT a1c_bucket, COUNT(*) n
FROM (SELECT CASE WHEN … END AS a1c_bucket FROM patients)
GROUP BY a1c_bucket;

BP staging (AHA)

CASE
  WHEN systolic_bp >= 180 THEN 'วิกฤต'
  WHEN systolic_bp >= 140 THEN 'สูง'
  WHEN systolic_bp >= 120 THEN 'ค่อนข้างสูง'
  ELSE 'ปกติ'
END AS bp_stage

WITH clean AS (
  SELECT patient_id, hn, gender, hba1c
  FROM patients
  WHERE hba1c IS NOT NULL
    AND birth_date LIKE '____-__-__'
), spend AS (
  SELECT patient_id,
         SUM(cost_thb) total, COUNT(*) n_rx
  FROM prescriptions GROUP BY patient_id
)
SELECT c.hn, c.hba1c, s.total
FROM clean c
LEFT JOIN spend s USING(patient_id)
ORDER BY s.total DESC NULLS LAST LIMIT 5;

เทียบ Subquery vs CTE

-- Top-3 ค่าใช้จ่ายต่อคน "ต่อเพศ"
WITH spend AS (
  SELECT patient_id, SUM(cost_thb) total
  FROM prescriptions GROUP BY patient_id
)
SELECT p.gender, p.hn, s.total,
  ROW_NUMBER() OVER(PARTITION BY p.gender
                    ORDER BY s.total DESC) rn,
  RANK()       OVER(PARTITION BY p.gender
                    ORDER BY s.total DESC) rk
FROM spend s JOIN patients p USING(patient_id)
-- กรอง Top-3 ต่อเพศ
-- WHERE rn <=3  ❌ ใช้ใน outer query!
;
SELECT * FROM (
  SELECT …, ROW_NUMBER() OVER(…) rn FROM …
) WHERE rn <= 3;

-- เทียบรายจ่ายยาต่อคน vs ค่าเฉลี่ยเพศเดียวกัน
WITH spend AS (
  SELECT patient_id, SUM(cost_thb) total FROM prescriptions
  GROUP BY patient_id
)
SELECT p.hn, p.gender, s.total,
  AVG(s.total) OVER(PARTITION BY p.gender) avg_by_gender,
  s.total - AVG(s.total) OVER(PARTITION BY p.gender) diff,
  AVG(s.total) OVER() avg_all   -- ทั้ง รพ.
FROM spend s JOIN patients p USING(patient_id)
ORDER BY diff DESC LIMIT 5;

GROUP BY vs Window

WITH base AS (
  SELECT patient_id, hn, gender,
    CAST((julianday('now')-julianday(birth_date))/365.25 AS INT) age,
    hba1c, systolic_bp
  FROM patients
  WHERE gender='หญิง' AND hba1c >= 7
    AND systolic_bp >= 140
    AND birth_date LIKE '____-__-__'
)
SELECT b.hn, b.age, b.hba1c, b.systolic_bp
FROM base b
WHERE NOT EXISTS (
  SELECT 1 FROM prescriptions r
  WHERE r.patient_id=b.patient_id
    AND r.drug='Metformin'
)
ORDER BY b.hba1c DESC LIMIT 10;

เทคนิคที่ใช้

• อายุ: julianday (wk06) + กรอง LIKE '____-__-__' ตัดวันที่ผิดรูป
• หลายเงื่อนไข: AND + วงเล็บ (wk06 สไลด์ 04)
• ไม่มี Metformin: NOT EXISTS (สไลด์ 03) — ปลอดภัยกว่า NOT IN
• CTE: base ให้อ่านเป็นขั้น — ขยายต่อเป็น CASE/window ได้

-- เทรนด์ผู้ป่วยใหม่ต่อเดือน (จาก birth_date เป็น proxy)
SELECT strftime('%Y-%m', birth_date) AS ym,
       COUNT(*) n_new,
       AVG(hba1c) avg_a1c
FROM patients
WHERE birth_date LIKE '____-__-__'
GROUP BY ym
ORDER BY ym LIMIT 6;

-- ผูกกับ bucket
SELECT ym,
  SUM(CASE WHEN a1c_bucket='วิกฤต ≥9' THEN 1 ELSE 0 END) n_critical
FROM (SELECT strftime('%Y-%m',birth_date) ym,
  CASE WHEN hba1c>=9 THEN 'วิกฤต ≥9' ELSE 'อื่น' END a1c_bucket
  FROM patients WHERE birth_date LIKE '____-__-__')
GROUP BY ym;

ใช้กับ prescriptions (มีวันที่จริง)

ถ้าเพิ่มคอลัมน์ rx_date TEXT ISO8601 ใน prescriptions (HW wk07) →

SELECT strftime('%Y-%m', rx_date) ym,
       drug, SUM(cost_thb) spend
FROM prescriptions
WHERE rx_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY ym, drug
ORDER BY ym, spend DESC;

WITH clean AS (
  SELECT patient_id, hn, gender, hba1c
  FROM patients WHERE hba1c IS NOT NULL
)
SELECT gender,
  CASE WHEN hba1c>=9 THEN 'วิกฤต' ELSE 'อื่น' END bucket,
  COUNT(*) n,
  AVG(hba1c) avg_a1c,
  RANK() OVER(PARTITION BY gender ORDER BY AVG(hba1c) DESC) rnk
FROM clean
GROUP BY gender, bucket
HAVING COUNT(*) > 1
ORDER BY gender, rnk LIMIT 5;

C: CLI

scoop install sqlite
sqlite3 healthinfo.db
sqlite> SELECT sqlite_version();
 -- ≥3.25 window OK, ≥3.39 FULL JOIN OK
sqlite> .quit

📋 ภารกิจ ↔ สไลด์

✅ Self-check + HW

• IN vs EXISTS ให้ count เท่ากัน
• CASE bucket รวม COUNT = 103 คนครบ
• CTE รันได้ไม่ error (SQLite ≥3.25)
• WindowTop-3 ต่อเพศ ได้ 6 แถว (3+3) — RN 1–3 ต่อกลุ่ม

📘 SQLite

• SELECT · Subquery · EXISTS · CASE
• WITH (CTE) · Window Functions · Date/Time
• PARTITION BY · Release history (window ≥3.25)

🐍 Python

• sqlite3 · pandas groupby vs SQL

🛠️ เสริม

• W3Schools Subquery · CASE · Window · CTE
• Schema จริง: MIMIC-IV hosp · OMOP CDM

📬 ติดต่อ

ดร.วิชิต สมบัติ — wichit.s@ubu.ac.th

คาบหน้า: สอบกลางภาค (Week 9) — ทบทวน wk05–08 + cheat sheets!