# 患者主表
CREATE TABLE PATIENT (
PATIENT_ID VARCHAR(32) NOT NULL,
MEDICAL_RECORD_NO VARCHAR(50),
ID_CARD VARCHAR(18),
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(10),
BIRTH_DATE DATE,
AGE INTEGER,
MARITAL_STATUS VARCHAR(10),
NATIONALITY VARCHAR(50),
ETHNICITY VARCHAR(50),
OCCUPATION VARCHAR(100),
ADDRESS VARCHAR(200),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
EMERGENCY_CONTACT VARCHAR(100),
EMERGENCY_PHONE VARCHAR(20),
ALLERGY_HISTORY CLOB,
PATIENT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PATIENT PRIMARY KEY (PATIENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_PATIENT_MRNO ON PATIENT(MEDICAL_RECORD_NO);
CREATE INDEX IDX_PATIENT_IDCARD ON PATIENT(ID_CARD);
CREATE INDEX IDX_PATIENT_NAME ON PATIENT(NAME);
CREATE INDEX IDX_PATIENT_STATUS ON PATIENT(PATIENT_STATUS);

# 就诊记录表
CREATE TABLE VISIT (
VISIT_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_TYPE VARCHAR(20) NOT NULL,
VISIT_DATE DATE NOT NULL,
VISIT_TIME TIMESTAMP NOT NULL,
DEPT_ID VARCHAR(20),
DEPT_NAME VARCHAR(100),
DOCTOR_ID VARCHAR(20),
DOCTOR_NAME VARCHAR(50),
CHIEF_COMPLAINT VARCHAR(500),
PRESENT_ILLNESS CLOB,
PAST_HISTORY CLOB,
PHYSICAL_EXAM CLOB,
DIAGNOSIS VARCHAR(500),
TREATMENT_PLAN CLOB,
VISIT_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_VISIT PRIMARY KEY (VISIT_ID)
) ORGANIZE BY ROW;

# 住院记录表
CREATE TABLE INPATIENT (
INPATIENT_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
ADMISSION_NO VARCHAR(50) NOT NULL,
ADMISSION_DATE TIMESTAMP NOT NULL,
DISCHARGE_DATE TIMESTAMP,
DEPT_ID VARCHAR(20),
DEPT_NAME VARCHAR(100),
WARD_ID VARCHAR(20),
WARD_NAME VARCHAR(100),
BED_NO VARCHAR(20),
ATTENDING_DOCTOR_ID VARCHAR(20),
ATTENDING_DOCTOR_NAME VARCHAR(50),
ADMISSION_DIAGNOSIS VARCHAR(500),
DISCHARGE_DIAGNOSIS VARCHAR(500),
TREATMENT_SUMMARY CLOB,
INPATIENT_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_INPATIENT PRIMARY KEY (INPATIENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_VISIT_PATIENT ON VISIT(PATIENT_ID, VISIT_DATE DESC);
CREATE INDEX IDX_VISIT_DEPT ON VISIT(DEPT_ID, VISIT_DATE DESC);
CREATE INDEX IDX_VISIT_DOCTOR ON VISIT(DOCTOR_ID, VISIT_DATE DESC);
CREATE INDEX IDX_INPATIENT_PATIENT ON INPATIENT(PATIENT_ID, ADMISSION_DATE DESC);
CREATE INDEX IDX_INPATIENT_STATUS ON INPATIENT(INPATIENT_STATUS, ADMISSION_DATE DESC);

# 医嘱主表
CREATE TABLE ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_ID VARCHAR(32),
INPATIENT_ID VARCHAR(32),
ORDER_TYPE VARCHAR(20) NOT NULL,
ORDER_CATEGORY VARCHAR(20),
ORDER_CONTENT CLOB,
START_TIME TIMESTAMP NOT NULL,
END_TIME TIMESTAMP,
FREQUENCY VARCHAR(50),
DOCTOR_ID VARCHAR(20),
DOCTOR_NAME VARCHAR(50),
NURSE_ID VARCHAR(20),
NURSE_NAME VARCHAR(50),
ORDER_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;

# 处方表
CREATE TABLE PRESCRIPTION (
PRESCRIPTION_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_ID VARCHAR(32),
PRESCRIPTION_TYPE VARCHAR(20),
DOCTOR_ID VARCHAR(20),
DOCTOR_NAME VARCHAR(50),
PHARMACIST_ID VARCHAR(20),
PHARMACIST_NAME VARCHAR(50),
PRESCRIPTION_DATE TIMESTAMP NOT NULL,
TOTAL_AMOUNT DECIMAL(10, 2),
PRESCRIPTION_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRESCRIPTION PRIMARY KEY (PRESCRIPTION_ID)
) ORGANIZE BY ROW;

# 处方明细表
CREATE TABLE PRESCRIPTION_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
PRESCRIPTION_ID VARCHAR(32) NOT NULL,
DRUG_ID VARCHAR(32) NOT NULL,
DRUG_NAME VARCHAR(200) NOT NULL,
DRUG_SPEC VARCHAR(100),
DOSAGE VARCHAR(100),
FREQUENCY VARCHAR(50),
DURATION VARCHAR(50),
QUANTITY DECIMAL(10, 2),
UNIT VARCHAR(20),
UNIT_PRICE DECIMAL(10, 2),
TOTAL_PRICE DECIMAL(10, 2),
USAGE VARCHAR(200),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRESCRIPTION_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;

# 用药记录表
CREATE TABLE MEDICATION_RECORD (
RECORD_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
PATIENT_ID VARCHAR(32) NOT NULL,
ORDER_ID VARCHAR(32),
PRESCRIPTION_ID VARCHAR(32),
DRUG_ID VARCHAR(32),
DRUG_NAME VARCHAR(200),
DOSAGE VARCHAR(100),
ADMINISTRATION_TIME TIMESTAMP NOT NULL,
ADMINISTRATOR_ID VARCHAR(20),
ADMINISTRATOR_NAME VARCHAR(50),
REMARK VARCHAR(500),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEDICATION_RECORD PRIMARY KEY (RECORD_ID)
) PARTITION BY RANGE (ADMINISTRATION_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);

# 创建索引
CREATE INDEX IDX_ORDER_PATIENT ON ORDER(PATIENT_ID, START_TIME DESC);
CREATE INDEX IDX_PRESCRIPTION_PATIENT ON PRESCRIPTION(PATIENT_ID, PRESCRIPTION_DATE DESC);
CREATE INDEX IDX_PRESCRIPTION_DETAIL ON PRESCRIPTION_DETAIL(PRESCRIPTION_ID);
CREATE INDEX IDX_MEDICATION_PATIENT ON MEDICATION_RECORD(PATIENT_ID, ADMINISTRATION_TIME DESC) PARTITIONED;

# 检查申请表
CREATE TABLE EXAM_REQUEST (
REQUEST_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_ID VARCHAR(32),
INPATIENT_ID VARCHAR(32),
EXAM_TYPE VARCHAR(20) NOT NULL,
EXAM_ITEM VARCHAR(200) NOT NULL,
EXAM_DEPT_ID VARCHAR(20),
EXAM_DEPT_NAME VARCHAR(100),
APPLY_DOCTOR_ID VARCHAR(20),
APPLY_DOCTOR_NAME VARCHAR(50),
CLINICAL_DIAGNOSIS VARCHAR(500),
APPLY_TIME TIMESTAMP NOT NULL,
REQUEST_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EXAM_REQUEST PRIMARY KEY (REQUEST_ID)
) ORGANIZE BY ROW;

# 检查结果表
CREATE TABLE EXAM_RESULT (
RESULT_ID VARCHAR(32) NOT NULL,
REQUEST_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
EXAM_DATE TIMESTAMP NOT NULL,
EXAM_DEPT_ID VARCHAR(20),
EXAM_DEPT_NAME VARCHAR(100),
EXAM_DOCTOR_ID VARCHAR(20),
EXAM_DOCTOR_NAME VARCHAR(50),
EXAM_RESULT_TEXT CLOB,
EXAM_IMAGE BLOB,
EXAM_REPORT CLOB,
DIAGNOSIS_SUGGESTION VARCHAR(500),
RESULT_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EXAM_RESULT PRIMARY KEY (RESULT_ID)
) ORGANIZE BY ROW;

# 检验申请表
CREATE TABLE LAB_REQUEST (
REQUEST_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_ID VARCHAR(32),
INPATIENT_ID VARCHAR(32),
LAB_TYPE VARCHAR(20) NOT NULL,
SAMPLE_TYPE VARCHAR(20),
SAMPLE_NO VARCHAR(50),
APPLY_DEPT_ID VARCHAR(20),
APPLY_DEPT_NAME VARCHAR(100),
APPLY_DOCTOR_ID VARCHAR(20),
APPLY_DOCTOR_NAME VARCHAR(50),
CLINICAL_DIAGNOSIS VARCHAR(500),
APPLY_TIME TIMESTAMP NOT NULL,
SAMPLE_COLLECT_TIME TIMESTAMP,
REQUEST_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_LAB_REQUEST PRIMARY KEY (REQUEST_ID)
) ORGANIZE BY ROW;

# 检验结果表
CREATE TABLE LAB_RESULT (
RESULT_ID VARCHAR(32) NOT NULL,
REQUEST_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
TEST_DATE TIMESTAMP NOT NULL,
LAB_DEPT_ID VARCHAR(20),
LAB_DEPT_NAME VARCHAR(100),
TESTER_ID VARCHAR(20),
TESTER_NAME VARCHAR(50),
AUDITOR_ID VARCHAR(20),
AUDITOR_NAME VARCHAR(50),
TEST_REPORT CLOB,
RESULT_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_LAB_RESULT PRIMARY KEY (RESULT_ID)
) ORGANIZE BY ROW;

# 检验结果明细表
CREATE TABLE LAB_RESULT_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
RESULT_ID VARCHAR(32) NOT NULL,
TEST_ITEM_ID VARCHAR(32) NOT NULL,
TEST_ITEM_NAME VARCHAR(200) NOT NULL,
TEST_VALUE VARCHAR(100),
UNIT VARCHAR(20),
REFERENCE_RANGE VARCHAR(100),
RESULT_FLAG VARCHAR(10),
TEST_METHOD VARCHAR(100),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_LAB_RESULT_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_EXAM_REQUEST_PATIENT ON EXAM_REQUEST(PATIENT_ID, APPLY_TIME DESC);
CREATE INDEX IDX_EXAM_RESULT_REQUEST ON EXAM_RESULT(REQUEST_ID);
CREATE INDEX IDX_LAB_REQUEST_PATIENT ON LAB_REQUEST(PATIENT_ID, APPLY_TIME DESC);
CREATE INDEX IDX_LAB_RESULT_REQUEST ON LAB_RESULT(REQUEST_ID);
CREATE INDEX IDX_LAB_RESULT_DETAIL ON LAB_RESULT_DETAIL(RESULT_ID);

# 电子病历表
CREATE TABLE EMR (
EMR_ID VARCHAR(32) NOT NULL,
PATIENT_ID VARCHAR(32) NOT NULL,
VISIT_ID VARCHAR(32),
INPATIENT_ID VARCHAR(32),
EMR_TYPE VARCHAR(20) NOT NULL,
EMR_TITLE VARCHAR(200),
EMR_CONTENT XML,
DOCTOR_ID VARCHAR(20),
DOCTOR_NAME VARCHAR(50),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EMR PRIMARY KEY (EMR_ID)
) ORGANIZE BY ROW;

# 插入XML电子病历
INSERT INTO EMR (EMR_ID, PATIENT_ID, EMR_TYPE, EMR_TITLE, EMR_CONTENT)
VALUES (
‘EMR001’,
‘PAT001’,
‘门诊病历’,
‘2026-04-08门诊病历’,
XMLPARSE(DOCUMENT ‘


张三
男
35

发热、咳嗽3天
患者3天前受凉后出现发热，体温最高38.5℃，伴咳嗽、咳痰

37.8
88
20
120/80

上呼吸道感染

阿莫西林
0.5g tid

‘
)
);

# 查询XML内容
SELECT
EMR_ID,
XMLCAST(XMLQUERY(‘$e/EMR/PatientInfo/Name/text()’ PASSING EMR_CONTENT AS “e”) AS VARCHAR(100)) AS PATIENT_NAME,
XMLCAST(XMLQUERY(‘$e/EMR/Diagnosis/text()’ PASSING EMR_CONTENT AS “e”) AS VARCHAR(500)) AS DIAGNOSIS
FROM EMR
WHERE PATIENT_ID = ‘PAT001’;

# XML索引
CREATE INDEX IDX_EMR_PATIENT_NAME ON EMR(
XMLCAST(XMLQUERY(‘$e/EMR/PatientInfo/Name/text()’ PASSING EMR_CONTENT AS “e”) AS VARCHAR(100))
);

# XQuery查询
SELECT
XMLQUERY(‘
for $e in $doc/EMR
return
{data($e/PatientInfo/Name)}
{data($e/Diagnosis)}

‘ PASSING EMR_CONTENT AS “doc”) AS RESULT
FROM EMR;

# 更新XML
UPDATE EMR
SET EMR_CONTENT = XMLQUERY(‘
transform copy $new := $e
modify do insert {current timestamp} into $new/EMR
return $new
‘ PASSING EMR_CONTENT AS “e”)
WHERE EMR_ID = ‘EMR001’;

# 删除XML节点
UPDATE EMR
SET EMR_CONTENT = XMLQUERY(‘
transform copy $new := $e
modify do delete $new/EMR/UpdateTime
return $new
‘ PASSING EMR_CONTENT AS “e”)
WHERE EMR_ID = ‘EMR001’;

# 创建BLU列存储表
CREATE TABLE PATIENT_STAT (
STAT_DATE DATE NOT NULL,
DEPT_ID VARCHAR(20),
DEPT_NAME VARCHAR(100),
PATIENT_TYPE VARCHAR(20),
PATIENT_COUNT INTEGER,
AVERAGE_AGE DECIMAL(5, 2),
TOTAL_EXPENSE DECIMAL(18, 2),
AVERAGE_EXPENSE DECIMAL(10, 2),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
) ORGANIZE BY COLUMN;

# 创建索引
CREATE INDEX IDX_PATIENT_STAT_DATE ON PATIENT_STAT(STAT_DATE);

# 就诊统计MQT
CREATE TABLE MQT_VISIT_STAT AS (
SELECT
DATE(VISIT_DATE) AS STAT_DATE,
DEPT_ID,
DEPT_NAME,
COUNT(*) AS VISIT_COUNT,
COUNT(DISTINCT PATIENT_ID) AS PATIENT_COUNT
FROM VISIT
GROUP BY DATE(VISIT_DATE), DEPT_ID, DEPT_NAME
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_VISIT_STAT;

# 疾病统计分析
SELECT
DIAGNOSIS,
COUNT(*) AS CASE_COUNT,
COUNT(DISTINCT PATIENT_ID) AS PATIENT_COUNT,
ROUND(AVG(EXTRACT(YEAR FROM CURRENT DATE) – EXTRACT(YEAR FROM BIRTH_DATE)), 1) AS AVG_AGE
FROM VISIT v
INNER JOIN PATIENT p ON v.PATIENT_ID = p.PATIENT_ID
WHERE VISIT_DATE BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
AND DIAGNOSIS IS NOT NULL
GROUP BY DIAGNOSIS
ORDER BY CASE_COUNT DESC
FETCH FIRST 20 ROWS ONLY;

# 科室工作量统计
SELECT
DEPT_NAME,
DOCTOR_NAME,
COUNT(*) AS VISIT_COUNT,
COUNT(DISTINCT PATIENT_ID) AS PATIENT_COUNT
FROM VISIT
WHERE VISIT_DATE BETWEEN ‘2026-04-01’ AND ‘2026-04-30’
GROUP BY DEPT_NAME, DOCTOR_NAME
ORDER BY DEPT_NAME, VISIT_COUNT DESC;

# 药品使用统计
SELECT
d.DRUG_NAME,
COUNT(*) AS PRESCRIPTION_COUNT,
SUM(d.QUANTITY) AS TOTAL_QUANTITY,
SUM(d.TOTAL_PRICE) AS TOTAL_AMOUNT
FROM PRESCRIPTION_DETAIL d
INNER JOIN PRESCRIPTION p ON d.PRESCRIPTION_ID = p.PRESCRIPTION_ID
WHERE p.PRESCRIPTION_DATE BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
GROUP BY d.DRUG_NAME
ORDER BY TOTAL_AMOUNT DESC
FETCH FIRST 20 ROWS ONLY;

# 时间维度表
CREATE TABLE DIM_DATE (
DATE_KEY INTEGER NOT NULL,
FULL_DATE DATE NOT NULL,
YEAR INTEGER,
QUARTER INTEGER,
MONTH INTEGER,
MONTH_NAME VARCHAR(20),
WEEK INTEGER,
DAY_OF_WEEK INTEGER,
DAY_NAME VARCHAR(20),
DAY_OF_MONTH INTEGER,
IS_WEEKEND CHAR(1),
CONSTRAINT PK_DIM_DATE PRIMARY KEY (DATE_KEY)
) ORGANIZE BY ROW;

# 科室维度表
CREATE TABLE DIM_DEPARTMENT (
DEPT_KEY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
DEPT_ID VARCHAR(20) NOT NULL,
DEPT_NAME VARCHAR(100) NOT NULL,
PARENT_DEPT_ID VARCHAR(20),
DEPT_LEVEL INTEGER,
DEPT_TYPE VARCHAR(20),
EFFECTIVE_DATE DATE,
EXPIRY_DATE DATE,
IS_CURRENT CHAR(1),
CONSTRAINT PK_DIM_DEPARTMENT PRIMARY KEY (DEPT_KEY)
) ORGANIZE BY ROW;

# 患者维度表
CREATE TABLE DIM_PATIENT (
PATIENT_KEY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
PATIENT_ID VARCHAR(32) NOT NULL,
GENDER VARCHAR(10),
AGE_GROUP VARCHAR(20),
MARITAL_STATUS VARCHAR(10),
NATIONALITY VARCHAR(50),
ETHNICITY VARCHAR(50),
ADDRESS VARCHAR(200),
CITY VARCHAR(50),
EFFECTIVE_DATE DATE,
EXPIRY_DATE DATE,
IS_CURRENT CHAR(1),
CONSTRAINT PK_DIM_PATIENT PRIMARY KEY (PATIENT_KEY)
) ORGANIZE BY ROW;

# 就诊事实表
CREATE TABLE FACT_VISIT (
DATE_KEY INTEGER NOT NULL,
DEPT_KEY INTEGER NOT NULL,
PATIENT_KEY INTEGER NOT NULL,
VISIT_TYPE VARCHAR(20),
VISIT_COUNT INTEGER DEFAULT 1,
DIAGNOSIS_COUNT INTEGER DEFAULT 1,
CONSTRAINT PK_FACT_VISIT PRIMARY KEY (DATE_KEY, DEPT_KEY, PATIENT_KEY)
) ORGANIZE BY COLUMN;

# 创建索引
CREATE INDEX IDX_FACT_VISIT_DATE ON FACT_VISIT(DATE_KEY);
CREATE INDEX IDX_FACT_VISIT_DEPT ON FACT_VISIT(DEPT_KEY);
CREATE INDEX IDX_FACT_VISIT_PATIENT ON FACT_VISIT(PATIENT_KEY);

# 分析查询
SELECT
d.YEAR,
d.MONTH,
de.DEPT_NAME,
SUM(f.VISIT_COUNT) AS TOTAL_VISITS,
SUM(f.DIAGNOSIS_COUNT) AS TOTAL_DIAGNOSES
FROM FACT_VISIT f
INNER JOIN DIM_DATE d ON f.DATE_KEY = d.DATE_KEY
INNER JOIN DIM_DEPARTMENT de ON f.DEPT_KEY = de.DEPT_KEY
WHERE d.YEAR = 2026
GROUP BY d.YEAR, d.MONTH, de.DEPT_NAME
ORDER BY d.YEAR, d.MONTH, de.DEPT_NAME;