WITH 
department_stats AS (
    SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary
    FROM employees GROUP BY department_id
),
salary_analysis AS (
    SELECT 
        department_id,
        emp_count,
        avg_salary,
        RANK() OVER (ORDER BY avg_salary DESC) as salary_rank
    FROM department_stats
)
SELECT * FROM salary_analysis WHERE salary_rank <= 3;

-- 分析患者檢查頻率模式
WITH exam_patterns AS (
    SELECT 
        patient_id,
        exam_date,
        exam_type,
        -- 計算與上一次檢查的時間間隔
        DATEDIFF(exam_date, LAG(exam_date) OVER (
            PARTITION BY patient_id ORDER BY exam_date
        )) as days_since_last_exam,
        -- 檢查頻率排名
        NTILE(4) OVER (PARTITION BY patient_id ORDER BY exam_date) as frequency_quartile
    FROM medical_exams
)
SELECT 
    patient_id,
    AVG(days_since_last_exam) as avg_days_between_exams,
    COUNT(*) as total_exams
FROM exam_patterns
GROUP BY patient_id
HAVING COUNT(*) > 1;

-- 醫(yī)院月度業(yè)務(wù)分析
WITH monthly_stats AS (
    SELECT 
        hospital_id,
        DATE_FORMAT(exam_date, '%Y-%m') as exam_month,
        COUNT(*) as exam_count,
        SUM(cost) as monthly_revenue,
        -- 月度排名
        RANK() OVER (PARTITION BY hospital_id ORDER BY SUM(cost) DESC) as revenue_rank,
        -- 月度增長
        LAG(SUM(cost)) OVER (PARTITION BY hospital_id ORDER BY DATE_FORMAT(exam_date, '%Y-%m')) as prev_month_revenue
    FROM medical_exams
    GROUP BY hospital_id, DATE_FORMAT(exam_date, '%Y-%m')
)
SELECT 
    hospital_id,
    exam_month,
    exam_count,
    monthly_revenue,
    ROUND(monthly_revenue / NULLIF(prev_month_revenue, 0) * 100, 2) as growth_rate
FROM monthly_stats;

-- 限制分區(qū)數(shù)據(jù)量
WITH recent_exams AS (
    SELECT * FROM medical_exams 
    WHERE exam_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
),
ranked_data AS (
    SELECT 
        patient_id,
        exam_date,
        exam_type,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY exam_date DESC) as rn
    FROM recent_exams
)
SELECT * FROM ranked_data WHERE rn = 1; -- 最近一次檢查

-- ? 錯誤：在WHERE中使用窗口函數(shù)結(jié)果
SELECT exam_id, ROW_NUMBER() OVER () as rn
FROM medical_exams
WHERE rn = 1; -- 錯誤！rn在WHERE時不可用

-- ? 正確：使用子查詢或CTE
WITH numbered_exams AS (
    SELECT exam_id, ROW_NUMBER() OVER () as rn
    FROM medical_exams
)
SELECT exam_id FROM numbered_exams WHERE rn = 1;

-- 定義可重用的窗口
SELECT 
    patient_id,
    exam_date,
    cost,
    SUM(cost) OVER w as running_total,
    AVG(cost) OVER w as moving_avg
FROM medical_exams
WINDOW w AS (PARTITION BY patient_id ORDER BY exam_date ROWS UNBOUNDED PRECEDING);

SELECT *
FROM JSON_TABLE(
    '[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;

name  | age
John  | 30
Jane  | 25

SELECT 
    exam_id,
    JSON_EXTRACT(patient_info, '$.name') as patient_name,
    JSON_EXTRACT(patient_info, '$.age') as patient_age,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.insurance')) as insurance_type
FROM medical_records
WHERE JSON_CONTAINS_PATH(patient_info, 'one', '$.chronic_diseases');

CREATE TABLE medical_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    exam_id VARCHAR(20) NOT NULL,
    patient_info JSON NOT NULL,
    exam_data JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO medical_records (exam_id, patient_info, exam_data) VALUES
('EXAM001', '{
    "name": "張三",
    "age": 45,
    "gender": "男",
    "insurance": "城鎮(zhèn)職工醫(yī)保",
    "chronic_diseases": ["高血壓", "糖尿病"],
    "contact": {
        "phone": "13800138000",
        "emergency_contact": "李四"
    },
    "medical_history": {
        "allergies": ["青霉素"],
        "surgeries": ["闌尾切除術(shù)-2015"]
    }
}', '{
    "exam_type": "CT",
    "body_part": "胸部",
    "results": {
        "diagnosis": "肺部結(jié)節(jié)",
        "size": "5mm",
        "location": "右上肺",
        "urgency": "常規(guī)隨訪"
    },
    "radiologist": "王醫(yī)生",
    "cost": 680.00
}'),

('EXAM002', '{
    "name": "李四",
    "age": 32,
    "gender": "女", 
    "insurance": "新農(nóng)合",
    "chronic_diseases": [],
    "contact": {
        "phone": "13900139000",
        "emergency_contact": "王五"
    },
    "medical_history": {
        "allergies": [],
        "surgeries": []
    }
}', '{
    "exam_type": "MRI",
    "body_part": "頭部",
    "results": {
        "diagnosis": "正常",
        "findings": "未見明顯異常",
        "urgency": "常規(guī)"
    },
    "radiologist": "趙醫(yī)生",
    "cost": 1200.00
}'),

('EXAM003', '{
    "name": "王五",
    "age": 68,
    "gender": "男",
    "insurance": "離退休干部醫(yī)保",
    "chronic_diseases": ["冠心病", "高血壓", "糖尿病"],
    "contact": {
        "phone": "13700137000", 
        "emergency_contact": "趙六"
    },
    "medical_history": {
        "allergies": ["阿司匹林"],
        "surgeries": ["冠狀動脈搭橋術(shù)-2020", "膽囊切除術(shù)-2018"]
    }
}', '{
    "exam_type": "超聲",
    "body_part": "腹部",
    "results": {
        "diagnosis": "膽囊息肉",
        "size": "8mm",
        "recommendation": "定期復(fù)查"
    },
    "radiologist": "孫醫(yī)生",
    "cost": 350.00
}');

-- 提取患者基本信息
SELECT 
    exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as patient_name,
    JSON_EXTRACT(patient_info, '$.age') as patient_age,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.gender')) as gender,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.insurance')) as insurance_type,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.exam_type')) as exam_type,
    JSON_EXTRACT(exam_data, '$.cost') as cost
FROM medical_records;

exam_id | patient_name | patient_age | gender | insurance_type    | exam_type | cost
EXAM001 | 張三         | 45          | 男     | 城鎮(zhèn)職工醫(yī)保      | CT        | 680.00
EXAM002 | 李四         | 32          | 女     | 新農(nóng)合           | MRI       | 1200.00
EXAM003 | 王五         | 68          | 男     | 離退休干部醫(yī)保    | 超聲      | 350.00

-- 查找有慢性病的患者
SELECT 
    exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as patient_name,
    JSON_EXTRACT(patient_info, '$.age') as age,
    JSON_EXTRACT(patient_info, '$.chronic_diseases') as chronic_diseases
FROM medical_records
WHERE JSON_CONTAINS_PATH(patient_info, 'one', '$.chronic_diseases')
  AND JSON_LENGTH(JSON_EXTRACT(patient_info, '$.chronic_diseases')) > 0;

exam_id | patient_name | age | chronic_diseases
EXAM001 | 張三         | 45  | ["高血壓", "糖尿病"]
EXAM003 | 王五         | 68  | ["冠心病", "高血壓", "糖尿病"]

-- 查找有特定過敏史的高齡患者
SELECT 
    exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as name,
    JSON_EXTRACT(patient_info, '$.age') as age,
    JSON_EXTRACT(patient_info, '$.medical_history.allergies') as allergies
FROM medical_records
WHERE JSON_EXTRACT(patient_info, '$.age') >= 60
  AND JSON_CONTAINS(JSON_EXTRACT(patient_info, '$.medical_history.allergies'), '"阿司匹林"');

-- 展開慢性病數(shù)組為多行
SELECT 
    mr.exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(mr.patient_info, '$.name')) as patient_name,
    diseases.disease_name
FROM medical_records mr,
JSON_TABLE(
    JSON_EXTRACT(mr.patient_info, '$.chronic_diseases'),
    '$[*]' COLUMNS (
        disease_name VARCHAR(50) PATH '$'
    )
) AS diseases
WHERE JSON_LENGTH(JSON_EXTRACT(mr.patient_info, '$.chronic_diseases')) > 0;

exam_id | patient_name | disease_name
EXAM001 | 張三         | 高血壓
EXAM001 | 張三         | 糖尿病
EXAM003 | 王五         | 冠心病
EXAM003 | 王五         | 高血壓
EXAM003 | 王五         | 糖尿病

-- 按保險類型統(tǒng)計費(fèi)用
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.insurance')) as insurance_type,
    COUNT(*) as exam_count,
    ROUND(AVG(JSON_EXTRACT(exam_data, '$.cost')), 2) as avg_cost,
    ROUND(SUM(JSON_EXTRACT(exam_data, '$.cost')), 2) as total_cost
FROM medical_records
GROUP BY JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.insurance'))
ORDER BY total_cost DESC;

-- 分析檢查結(jié)果的緊急程度
SELECT 
    exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as patient_name,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.exam_type')) as exam_type,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.results.diagnosis')) as diagnosis,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.results.urgency')) as urgency_level,
    CASE 
        WHEN JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.results.urgency')) = '緊急' THEN '高危'
        WHEN JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.results.urgency')) = '常規(guī)隨訪' THEN '中危'
        ELSE '低危'
    END as risk_level
FROM medical_records
ORDER BY 
    CASE 
        WHEN urgency_level = '緊急' THEN 1
        WHEN urgency_level = '常規(guī)隨訪' THEN 2
        ELSE 3
    END;

SELECT 
    exam_id,
    -- 基本信息
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as name,
    JSON_EXTRACT(patient_info, '$.age') as age,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.gender')) as gender,
    
    -- 聯(lián)系信息
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.contact.phone')) as phone,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.contact.emergency_contact')) as emergency_contact,
    
    -- 醫(yī)療信息
    JSON_EXTRACT(patient_info, '$.chronic_diseases') as chronic_diseases,
    JSON_EXTRACT(patient_info, '$.medical_history.allergies') as allergies,
    JSON_EXTRACT(patient_info, '$.medical_history.surgeries') as surgeries,
    
    -- 檢查信息
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.exam_type')) as exam_type,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.body_part')) as body_part,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.results.diagnosis')) as diagnosis,
    JSON_EXTRACT(exam_data, '$.cost') as cost,
    
    -- 計算字段
    CASE 
        WHEN JSON_EXTRACT(patient_info, '$.age') >= 65 THEN '老年患者'
        WHEN JSON_EXTRACT(patient_info, '$.age') >= 45 THEN '中年患者'
        ELSE '青年患者'
    END as age_group,
    
    CASE 
        WHEN JSON_LENGTH(JSON_EXTRACT(patient_info, '$.chronic_diseases')) >= 2 THEN '多病共存'
        WHEN JSON_LENGTH(JSON_EXTRACT(patient_info, '$.chronic_diseases')) = 1 THEN '單一慢性病'
        ELSE '無慢性病'
    END as chronic_status
    
FROM medical_records;

-- 為常用查詢字段創(chuàng)建索引
ALTER TABLE medical_records 
    ADD INDEX idx_patient_name ((JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name'))));

ALTER TABLE medical_records
    ADD INDEX idx_patient_age ((JSON_EXTRACT(patient_info, '$.age')));

ALTER TABLE medical_records
    ADD INDEX idx_exam_type ((JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.exam_type'))));

-- 創(chuàng)建簡化視圖提高查詢性能
CREATE VIEW patient_summary AS
SELECT 
    record_id,
    exam_id,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.name')) as patient_name,
    JSON_EXTRACT(patient_info, '$.age') as age,
    JSON_UNQUOTE(JSON_EXTRACT(patient_info, '$.insurance')) as insurance,
    JSON_UNQUOTE(JSON_EXTRACT(exam_data, '$.exam_type')) as exam_type,
    JSON_EXTRACT(exam_data, '$.cost') as cost,
    created_at
FROM medical_records;