Cardinality ที่เจอในโรงพยาบาล

• 👤 patient → visits : 1:N (มาหลายครั้ง)
• 💊 prescription → drugs : N:M (ผ่าน junction table)
• 🩺 doctor → doctor : self (referral)

import sqlite3
con = sqlite3.connect("healthinfo.db")
cur = con.cursor()

# 🔑 เปิด enforce — ทำ "ต่อ connection"
cur.execute("PRAGMA foreign_keys = ON")
print(cur.execute("PRAGMA foreign_keys").fetchone())

cur.execute("""
CREATE TABLE prescriptions(
  rx_id      INTEGER PRIMARY KEY,
  patient_id INTEGER NOT NULL
             REFERENCES patients(patient_id),
  drug       TEXT NOT NULL,
  dose_mg    REAL, cost_thb REAL
)""")

FK ป้องกันอะไร?

• INSERT patient_id 99999 ที่ไม่มีจริง → error (ถ้า enforce)
• DELETE patients ที่มี rx → FOREIGN KEY constraint failed
• ไม่มี orphan records = รายงานไม่บิดเบี้ยว

-- ลบแบบ cascade (ใช้อย่างระวัง)
CREATE TABLE t(…
  REFERENCES patients(patient_id)
  ON DELETE CASCADE);

SELECT p.hn, r.drug, r.cost_thb
FROM patients AS p
INNER JOIN prescriptions AS r
        ON r.patient_id = p.patient_id;

-- ON คือเงื่อนไขจับคู่
-- WHERE กรองหลัง join ได้เหมือนกัน
… WHERE r.drug = 'Metformin'

SELECT p.patient_id, p.hn, r.drug
FROM patients p
LEFT JOIN prescriptions r
       ON r.patient_id = p.patient_id
ORDER BY p.patient_id LIMIT 8;

-- ⭐ Anti-JOIN: คนที่ "ไม่มี" rx
SELECT p.hn
FROM patients p
LEFT JOIN prescriptions r
       ON r.patient_id=p.patient_id
WHERE r.rx_id IS NULL;

-- RIGHT: ขวาครบ (เห็น orphan!)
SELECT r.rx_id, r.drug, p.hn
FROM patients p
RIGHT JOIN prescriptions r
        ON r.patient_id = p.patient_id;
-- rx_id=6 → hn=NULL ✓

-- FULL: สองฝั่งครบ
SELECT … FROM patients p
FULL OUTER JOIN prescriptions r
  ON r.patient_id=p.patient_id;

Venn เทียบ 3 แบบ

-- Emulate RIGHT บน SQLite เก่า:
SELECT r.*, p.*
FROM prescriptions r
LEFT JOIN patients p
  ON r.patient_id=p.patient_id; -- สลับข้าง

SELECT a.rx_from,
       pa.hn AS from_hn,   -- alias ฝั่งต้น
       a.rx_to,
       pb.hn AS to_hn      -- alias ฝั่งปลาย
FROM referrals a
JOIN patients pa
     ON pa.patient_id = a.rx_from
JOIN patients pb
     ON pb.patient_id = a.rx_to;

-- ❌ ambiguous:
SELECT patient_id FROM patients p
JOIN prescriptions r USING(patient_id);
-- → Error: ambiguous column

-- ✅ dot notation:
SELECT p.patient_id, p.hn,
       r.rx_id, r.drug
FROM patients p
JOIN prescriptions r
     ON r.patient_id = p.patient_id;

-- suffixes แบบ pandas (เทียบ)
pd.merge(p, r, on='patient_id',
         suffixes=('_p','_r'))

Convention ที่นิยม

• ตารางยาว → alias ตัวเดียว: patients p, prescriptions r
• SELECT ระบุ dot เสมอ — อ่านรู้ทันทีว่าคอลัมน์ไหนมาจากไหน
• ON ใช้ dot ทั้งคู่: r.patient_id = p.patient_id

SELECT hn, 'มียา' AS status
FROM patients p
JOIN prescriptions r
  ON r.patient_id=p.patient_id
UNION                       -- ตัดซ้ำ
SELECT hn, 'ไม่มียา'
FROM patients p
LEFT JOIN prescriptions r
       ON r.patient_id=p.patient_id
WHERE r.rx_id IS NULL;

-- ยอดต่อผู้ป่วย (JOIN + GROUP BY)
SELECT p.hn,
       COUNT(r.rx_id)   AS n_rx,
       SUM(r.cost_thb)  AS spend,
       AVG(r.cost_thb)  AS avg_cost
FROM patients p
LEFT JOIN prescriptions r
       ON r.patient_id=p.patient_id
GROUP BY p.patient_id
ORDER BY spend DESC NULLS LAST;

-- ต่อชนิดยา
SELECT drug, COUNT(*) n, SUM(cost_thb) s
FROM prescriptions GROUP BY drug;

Diagram

-- ผู้ป่วยที่จ่าย > 1 ใบ
SELECT p.hn, COUNT(r.rx_id) n_rx,
       SUM(r.cost_thb) total
FROM patients p
LEFT JOIN prescriptions r
  ON r.patient_id=p.patient_id
GROUP BY p.patient_id
HAVING COUNT(r.rx_id) > 1
ORDER BY total DESC;

-- WHERE + HAVING ร่วม
WHERE drug='Metformin'   -- pre-group
GROUP BY drug
HAVING AVG(cost_thb)>30  -- post-group

Pipeline ตำแหน่ง HAVING

SELECT p.gender,
  COUNT(DISTINCT p.patient_id) n_patients,
  SUM(r.cost_thb) spend
FROM patients p
LEFT JOIN prescriptions r
  ON r.patient_id=p.patient_id
WHERE p.birth_date LIKE '____-__-__'
GROUP BY p.gender
HAVING SUM(r.cost_thb)>0
ORDER BY spend DESC LIMIT 5;

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

✅ Self-check + HW

• INNER=5, LEFT=6 (orphan ต่างกัน)
• anti-join เจอคนไม่มียา (NULL row)
• DELETE patient มี rx + FK on → IntegrityError
• HAVING COUNT>1 ได้ 2 คน (HN-54001, HN-54003)

📘 SQLite

• Foreign Key Support · PRAGMA fk
• JOIN clause · UNION · GROUP BY · HAVING
• FK constraints (CREATE)

🐍 Python

• sqlite3 · Connection.execute

🛠️ เสริม

• W3Schools All JOINs · GROUP BY · HAVING
• ER tool: draw.io · Schema จริง: MIMIC-IV · OMOP CDM

📬 ติดต่อ

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

คาบหน้า: สอบกลางภาค — ทบทวน wk05–07 + cheat sheets!