MySQL中with窗口函數(shù)說明及使用案例總結

更新時間：2025年11月13日 11:21:48 作者：Java搬碼工

這篇文章主要介紹了MySQL中with窗口函數(shù)說明及使用案例的相關資料,窗口函數(shù)允許在查詢結果的特定窗口上執(zhí)行計算,而不會改變結果集的行數(shù),文中通過代碼介紹的非常詳細,需要的朋友可以參考下

1.1 JSON_TABLE函數(shù)
1.2 JSON_EXTRACT和JSON_CONTAINS_PATH
2.1 創(chuàng)建醫(yī)療記錄表
2.2 插入測試數(shù)據(jù)
3.1 基礎信息提取
3.2 慢性病患者篩選
3.3 復雜條件查詢
3.4 使用JSON_TABLE展開數(shù)組數(shù)據(jù)
4.1 醫(yī)療費用分析
4.2 檢查結果嚴重程度分析
5.1 患者完整檔案查詢
6.1 創(chuàng)建函數(shù)索引
6.2 物化視圖模式

總結

前言

窗口函數(shù)(Window Function)是MySQL 8.0引入的重要功能，允許在查詢結果的特定"窗口"(數(shù)據(jù)子集)上執(zhí)行計算，而不改變結果集的行數(shù)。與聚合函數(shù)不同，窗口函數(shù)不會將多行合并為一行。MySQL的WITH窗口函數(shù)（也稱為公共表表達式CTE + 窗口函數(shù)）

1. ??基本語法結構??

WITH cte_name AS (
    SELECT 
        column1,
        column2,
        window_function() OVER (PARTITION BY ... ORDER BY ...) as window_column
    FROM table
)
SELECT * FROM cte_name;

cte_name：結果集的名稱，類似表名，可以當做是一張表，不過這個結果集不存在索引
OVER：定義窗口的范圍
window_function()：窗口函數(shù)，ROW_NUMBER()，RANK()等
partition by：將數(shù)據(jù)分成多個獨立的分區(qū)，類似group by子句，但是在窗口函數(shù)中，數(shù)據(jù)不會合并為一行
order by：order by和普通查詢語句中的order by沒什么不同

2.常用窗口函數(shù)分類

2.1 排名函數(shù)

函數(shù)	說明	示例
`ROW_NUMBER()`	連續(xù)編號（無重復）	1,2,3,4,5
`RANK()`	排名（允許并列）	1,2,2,4,5
`DENSE_RANK()`	密集排名	1,2,2,3,4

2.2 聚合函數(shù)

函數(shù)	說明
`SUM() OVER()`	窗口內(nèi)求和
`AVG() OVER()`	窗口內(nèi)平均值
`COUNT() OVER()`	窗口內(nèi)計數(shù)
`MAX() OVER()`	窗口內(nèi)最大值
`MIN() OVER()`	窗口內(nèi)最小值

2.3 分布函數(shù)

函數(shù)	說明
`NTILE(n)`	分成n組
`PERCENT_RANK()`	百分比排名
`CUME_DIST()`	累積分布

3.實際應用示例

示例數(shù)據(jù)準備

-- 醫(yī)療影像檢查記錄表
CREATE TABLE medical_exams (
    exam_id INT PRIMARY KEY,
    patient_id INT COMMENT '患者ID',
    exam_date DATE COMMENT  '檢查日期',
    exam_type VARCHAR(50) COMMENT  '檢查類型',
    cost DECIMAL(10,2) COMMENT  '費用',
    hospital_id INT COMMENT  '醫(yī)院ID'
);

INSERT INTO medical_exams VALUES
(1, 101, '2024-01-10', 'CT', 500.00, 1),
(2, 101, '2024-01-15', 'MRI', 800.00, 1),
(3, 102, '2024-01-12', 'X-Ray', 200.00, 1),
(4, 103, '2024-01-18', 'CT', 500.00, 2),
(5, 101, '2024-01-20', 'Ultrasound', 300.00, 1),
(6, 104, '2024-01-22', 'MRI', 800.00, 2);

4.詳細用法示例

4.1 基礎排名查詢

-- 每個患者的檢查記錄按時間排序，ROW_NUMBER()生成連續(xù)的行號
WITH patient_exams AS (
    SELECT 
        patient_id,
        exam_date,
        exam_type,
        cost,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY exam_date) as exam_sequence
    FROM medical_exams
)
SELECT * FROM patient_exams;

運行結果

4.2 累計統(tǒng)計

-- 計算每個患者的當前累計檢查費用，平均費用
WITH patient_costs AS (
    SELECT 
        patient_id,
        exam_date,
        exam_type,
        cost,
        SUM(cost) OVER (PARTITION BY patient_id ORDER BY exam_date) as cumulative_cost,
        AVG(cost) OVER (PARTITION BY patient_id) as avg_cost_per_exam
    FROM medical_exams
)
SELECT * FROM patient_costs;

運行結果

說明：id為101的第一次500，第二次累計500+800=1300，第三次500+800+300=1600

-- 獲取每個患者的最近一次檢查
WITH patient_exams AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY exam_date DESC) as rn
    FROM medical_exams
)
SELECT * FROM patient_exams WHERE rn = 1;
-- 等同于以前用的group by語句
SELECT *
FROM medical_exams
WHERE (patient_id, exam_date) IN (
    SELECT patient_id, MAX(exam_date)
    FROM medical_exams
    GROUP BY patient_id
)
ORDER BY patient_id;

運行結果

4.3 獲取前三的排名

-- 每個醫(yī)院內(nèi)檢查費用排名
WITH hospital_ranking AS (
    SELECT 
        exam_id,
        patient_id,
        hospital_id,
        exam_type,
        cost,
        RANK() OVER (PARTITION BY hospital_id ORDER BY cost DESC) as cost_rank,-- 排名
        ROW_NUMBER() OVER (PARTITION BY hospital_id ORDER BY cost DESC) as row_num -- 連續(xù)編號
    FROM medical_exams
)
SELECT * FROM hospital_ranking 
WHERE cost_rank <= 3; -- 每個醫(yī)院費用前三的檢查

運行結果

使用窗口函數(shù) RANK() OVER (PARTITION BY hospital_id ORDER BY cost DESC) 按醫(yī)院分組，按檢查費用降序排名

5.高級窗口函數(shù)用法

5.1 使用窗口框架

-- 計算移動平均（最近3次檢查）
WITH moving_avg AS (
    SELECT 
        patient_id,
        exam_date,
        cost,
        AVG(cost) OVER (
        PARTITION BY patient_id          -- 按患者分組
        ORDER BY exam_date               -- 按檢查日期排序
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 窗口范圍：當前行+前2行
) as avg_last_3_exams
    FROM medical_exams
)
SELECT * FROM moving_avg;

ROWS：按物理行數(shù)計算（不是按值）
2 PRECEDING：當前行之前的2行
CURRENT ROW：當前行
??- 合計??：當前行 + 前2行 = 3行?

這里的窗口可以加額外的條件，比如只計算最近一年的數(shù)據(jù)

...FROM medical_exams
    WHERE exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

計算過程

-- 第1行：只有當前行
窗口范圍：第1行
計算：(500) / 1 = 500.00

-- 第2行：前1行 + 當前行  
窗口范圍：第1-2行
計算：(500 + 800) / 2 = 650.00

-- 第3行：前2行 + 當前行
窗口范圍：第1-3行
計算：(500 + 800 + 300) / 3 = 533.33

-- 第4行：前2行 + 當前行（第2-4行）
窗口范圍：第2-4行
計算：(800 + 300 + 600) / 3 = 566.67

-- 第5行：前2行 + 當前行（第3-5行）
窗口范圍：第3-5行
計算：(300 + 600 + 400) / 3 = 433.33

運行結果

patient_id | exam_date  | cost  | avg_last_3_exams
101        | 2024-01-10 | 500.00 | 500.00
101        | 2024-01-15 | 800.00 | 650.00
101        | 2024-01-20 | 300.00 | 533.33
101        | 2024-01-25 | 600.00 | 566.67
101        | 2024-01-30 | 400.00 | 433.33

窗口框架的其他寫法?

-- 寫法1：明確指定
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- 寫法2：簡寫（MySQL 8.0+）
ROWS 2 PRECEDING

-- 寫法3：向后擴展
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING  -- 當前行+后2行

-- 寫法4：前后擴展  
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING   -- 前1行+當前行+后1行

-- 寫法5：無界窗口
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 從開始到當前行

ROWS vs RANGE 的區(qū)別

特性	ROWS（物理行）	RANGE（邏輯值）
計算方式	按行數(shù)計算	按值范圍計算
適用場景	固定行數(shù)移動平均	按時間范圍統(tǒng)計
示例	最近3行	最近30天

RANGE示例：

-- 計算最近30天內(nèi)的平均費用
AVG(cost) OVER (
    PARTITION BY patient_id
    ORDER BY exam_date
    RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
) as avg_last_30_days

5.2 前后值比較

-- 與上一次檢查比較
WITH exam_comparison AS (
    SELECT 
        patient_id,
        exam_date,
        exam_type,
        cost,
        LAG(cost) OVER (PARTITION BY patient_id ORDER BY exam_date) as prev_exam_cost,
        cost - LAG(cost) OVER (PARTITION BY patient_id ORDER BY exam_date) as cost_change,
        LEAD(exam_date) OVER (PARTITION BY patient_id ORDER BY exam_date) as next_exam_date
    FROM medical_exams
)
SELECT * FROM exam_comparison;

函數(shù)功能說明

函數(shù)	作用	示例
`LAG()`	獲取前一行的值	上次檢查的費用
`LEAD()`	獲取后一行的值	下次檢查的日期
`cost - LAG(cost)`	計算變化量	費用增減金額

計算過程

-- 患者101的記錄處理：
第1行：LAG(cost) = NULL（沒有前一行）
       cost_change = 500 - NULL = NULL
       LEAD(exam_date) = '2024-01-15'（下一行日期）

第2行：LAG(cost) = 500.00（前一行費用）
       cost_change = 800 - 500 = 300.00（增加300）
       LEAD(exam_date) = '2024-01-20'

第3行：LAG(cost) = 800.00
       cost_change = 300 - 800 = -500.00（減少500）
       LEAD(exam_date) = NULL（沒有下一行）

-- 患者102的記錄處理（重新開始）：
第4行：LAG(cost) = NULL（新患者，沒有前一行）
       cost_change = NULL
       LEAD(exam_date) = NULL

運行結果

LAG()和LEAD()的完整語法

LAG(column, offset, default_value) OVER (...)
LEAD(column, offset, default_value) OVER (...)

參數(shù)	說明	示例
`column`	要獲取的列	`cost`, `exam_date`
`offset`	偏移量（默認1）	`LAG(cost, 2)`獲取前2行的值
`default_value`	默認值（替代NULL）	`LAG(cost, 1, 0)`無前一行時返回0

高級用法示例：

-- 獲取前2次檢查的費用
LAG(cost, 2, 0) OVER (PARTITION BY patient_id ORDER BY exam_date) as cost_2_exams_ago,

-- 獲取后一次檢查的類型  
LEAD(exam_type, 1, '未知') OVER (PARTITION BY patient_id ORDER BY exam_date) as next_exam_type,

-- 計算與上上次檢查的變化
cost - LAG(cost, 2, cost) OVER (...) as change_from_2_exams_ago

5.3 百分比計算

-- 計算每項檢查費用在總費用中的占比
WITH cost_analysis AS (
    SELECT 
        exam_id,
        patient_id,
        exam_type,
        cost,
        SUM(cost) OVER (PARTITION BY patient_id) as total_patient_cost,
        cost / SUM(cost) OVER (PARTITION BY patient_id) * 100 as cost_percentage,
        PERCENT_RANK() OVER (PARTITION BY patient_id ORDER BY cost) as cost_percent_rank
    FROM medical_exams
)
SELECT * FROM cost_analysis;

PERCENT_RANK()的計算公式：(當前行的排名 - 1) / (總行數(shù) - 1)

6.多級窗口函數(shù)

6.1 復雜分析查詢

-- 多層分析：患者+醫(yī)院級別統(tǒng)計
WITH multi_level_analysis AS (
    SELECT 
        exam_id,
        patient_id,
        hospital_id,
        exam_type,
        cost,
        -- 患者級別統(tǒng)計
        SUM(cost) OVER (PARTITION BY patient_id) as patient_total,
        RANK() OVER (PARTITION BY patient_id ORDER BY exam_date) as patient_exam_seq,
        
        -- 醫(yī)院級別統(tǒng)計
        AVG(cost) OVER (PARTITION BY hospital_id) as hospital_avg_cost,
        COUNT(*) OVER (PARTITION BY hospital_id) as exams_per_hospital,
        
        -- 全局統(tǒng)計
        SUM(cost) OVER () as grand_total,
        RANK() OVER (ORDER BY cost DESC) as global_cost_rank
    FROM medical_exams
)
SELECT 
    exam_id,
    patient_id,
    hospital_id,
    exam_type,
    cost,
    ROUND(cost / patient_total * 100, 2) as patient_cost_percentage,
    ROUND(cost / hospital_avg_cost, 2) as cost_vs_hospital_avg
FROM multi_level_analysis;

也可以單獨拆開多個cte

WITH 
cte1 AS (SELECT ...),
cte2 AS (SELECT ...),
cte3 AS (SELECT ...)
SELECT ... FROM cte1 JOIN cte2 ...;

多CTE鏈式查詢

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;

7.實際應用場景

7.1 患者檢查頻率分析

-- 分析患者檢查頻率模式
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;

7.2 醫(yī)院業(yè)務量分析

-- 醫(yī)院月度業(yè)務分析
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;

8.性能優(yōu)化技巧

8.1 使用適當?shù)乃饕?/h3>

-- 為窗口函數(shù)創(chuàng)建索引
CREATE INDEX idx_patient_date ON medical_exams(patient_id, exam_date);
CREATE INDEX idx_hospital_cost ON medical_exams(hospital_id, cost DESC);

8.2 分區(qū)數(shù)據(jù)限制

-- 限制分區(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; -- 最近一次檢查

9.常見錯誤與解決方案

9.1 避免的陷阱

-- ? 錯誤：在WHERE中使用窗口函數(shù)結果
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;

10.MySQL 8.0+ 新特性

10.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);

10.2 JSON數(shù)據(jù)處理

1.1 JSON_TABLE函數(shù)

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

功能：將JSON數(shù)組轉換為關系型表格

輸入：JSON數(shù)組字符串
路徑：$[*] 表示遍歷數(shù)組所有元素
列映射：
- name VARCHAR(50) PATH '$.name'：提取name字段
- age INT PATH '$.age'：提取age字段

輸出結果：

name  | age
John  | 30
Jane  | 25

1.2 JSON_EXTRACT和JSON_CONTAINS_PATH

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');

函數(shù)說明：

JSON_EXTRACT(json_doc, path)：提取JSON字段值（返回JSON格式）
JSON_UNQUOTE()：去除JSON字符串的引號
JSON_CONTAINS_PATH(json_doc, 'one', path)：檢查是否存在指定路徑

2.1 創(chuàng)建醫(yī)療記錄表

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
);

2.2 插入測試數(shù)據(jù)

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": ["闌尾切除術-2015"]
    }
}', '{
    "exam_type": "CT",
    "body_part": "胸部",
    "results": {
        "diagnosis": "肺部結節(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": ["冠狀動脈搭橋術-2020", "膽囊切除術-2018"]
    }
}', '{
    "exam_type": "超聲",
    "body_part": "腹部",
    "results": {
        "diagnosis": "膽囊息肉",
        "size": "8mm",
        "recommendation": "定期復查"
    },
    "radiologist": "孫醫(yī)生",
    "cost": 350.00
}');

3.1 基礎信息提取

-- 提取患者基本信息
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

3.2 慢性病患者篩選

-- 查找有慢性病的患者
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  | ["冠心病", "高血壓", "糖尿病"]

3.3 復雜條件查詢

-- 查找有特定過敏史的高齡患者
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'), '"阿司匹林"');

3.4 使用JSON_TABLE展開數(shù)組數(shù)據(jù)

-- 展開慢性病數(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 | 王五         | 糖尿病

4.1 醫(yī)療費用分析

-- 按保險類型統(tǒng)計費用
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;

4.2 檢查結果嚴重程度分析

-- 分析檢查結果的緊急程度
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;

5.1 患者完整檔案查詢

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;

6.1 創(chuàng)建函數(shù)索引

-- 為常用查詢字段創(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'))));

6.2 物化視圖模式

-- 創(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;

總結

到此這篇關于MySQL中with窗口函數(shù)說明及使用案例的文章就介紹到這了,更多相關MySQL with窗口函數(shù)使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL的增刪查改語句用法示例總結
這篇文章主要介紹了MySQL的增刪查改語句用法示例總結,是對MySQL學習的基本知識點的一個歸納,需要的朋友可以參考下
2015-05-05
教你解決往mysql數(shù)據(jù)庫中存入漢字報錯的方法
這篇文章主要介紹了Mysql基礎之教你解決往數(shù)據(jù)庫中存入漢字報錯的方法,文中有非常詳細的代碼示例,對正在學習mysql的小伙伴們有非常好的幫助,需要的朋友可以參考下
2021-05-05
mysql 時間戳的用法
這篇文章主要介紹了mysql 時間戳的用法，文中講解非常細致，代碼幫助大家更好的理解和學習，感興趣的朋友可以了解下
2020-08-08
MySQL基于位點的主從復制完整部署指南
本文詳細介紹了MySQL主從復制的部署流程,包括前置準備、主從協(xié)同操作、復制狀態(tài)驗證和功能測試,并提供了關鍵步驟和注意事項,需要的朋友可以參考下
2026-02-02
一文搞懂Mysql的行級鎖到底是怎么加的
在MySQL中行級鎖是一種非常重要的鎖機制,用于在高并發(fā)環(huán)境下提高數(shù)據(jù)庫的性能和數(shù)據(jù)一致性,這篇文章主要介紹了Mysql的行級鎖到底是怎么加的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2026-04-04
Mysql注入中的outfile、dumpfile、load_file函數(shù)詳解
這篇文章主要介紹了Mysql注入中的outfile、dumpfile、load_file,需要的朋友可以參考下
2018-05-05
MySQL數(shù)據(jù)庫分庫分表的方案
隨著項目不斷迭代,使用人數(shù)的不斷增加,數(shù)據(jù)庫中某些表數(shù)據(jù)正在逐步膨脹,往單表千萬迅速靠攏,,所以最近也在考慮做一下分庫分表,本文就給大家詳細講解了什么分庫分表和分庫分表的方案,需要的朋友可以參考下
2023-11-11
Mysql中group by 使用中發(fā)現(xiàn)的問題
當使用MySQL的GROUP BY語句時,根據(jù)指定的列對結果進行分組,這種情況通常是由于在 GROUP BY 中選擇的字段與其他非聚合字段不兼容,或者在 SELECT 子句中沒有正確使用聚合函數(shù)所導致的,本文給大家介紹Mysql中group by 使用中發(fā)現(xiàn)的問題,感興趣的朋友跟隨小編一起看看吧
2024-06-06
一次Mysql使用IN大數(shù)據(jù)量的優(yōu)化記錄
這篇文章主要給大家介紹了關于Mysql使用IN大數(shù)據(jù)量的優(yōu)化的實戰(zhàn)記錄，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2020-09-09
MySQL性能優(yōu)化出題業(yè)務SQL優(yōu)化
根據(jù)用戶的作答結果出練習卷，題目的優(yōu)先級為：未做過的題目>只做錯的題目>做錯又做對的題目>只做對的題目。
2010-08-08

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL中with窗口函數(shù)說明及使用案例總結

目錄

前言

1. ??基本語法結構??

2.常用窗口函數(shù)分類

2.1 排名函數(shù)

2.2 聚合函數(shù)

2.3 分布函數(shù)

3.實際應用示例

示例數(shù)據(jù)準備

4.詳細用法示例

4.1 基礎排名查詢

4.2 累計統(tǒng)計

4.3 獲取前三的排名

5.高級窗口函數(shù)用法

5.1 使用窗口框架

5.2 前后值比較

5.3 百分比計算

6.多級窗口函數(shù)

6.1 復雜分析查詢

7.實際應用場景

7.1 患者檢查頻率分析

7.2 醫(yī)院業(yè)務量分析

8.性能優(yōu)化技巧

8.1 使用適當?shù)乃饕?/h3> -- 為窗口函數(shù)創(chuàng)建索引 CREATE INDEX idx_patient_date ON medical_exams(patient_id, exam_date); CREATE INDEX idx_hospital_cost ON medical_exams(hospital_id, cost DESC);

8.2 分區(qū)數(shù)據(jù)限制

9.常見錯誤與解決方案

9.1 避免的陷阱

10.MySQL 8.0+ 新特性

10.1 命名窗口

10.2 JSON數(shù)據(jù)處理

1.1 JSON_TABLE函數(shù)

1.2 JSON_EXTRACT和JSON_CONTAINS_PATH

2.1 創(chuàng)建醫(yī)療記錄表

2.2 插入測試數(shù)據(jù)

3.1 基礎信息提取

3.2 慢性病患者篩選

3.3 復雜條件查詢

3.4 使用JSON_TABLE展開數(shù)組數(shù)據(jù)

4.1 醫(yī)療費用分析

4.2 檢查結果嚴重程度分析

5.1 患者完整檔案查詢

6.1 創(chuàng)建函數(shù)索引

6.2 物化視圖模式

總結

相關文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

8.1 使用適當?shù)乃饕?/h3>
-- 為窗口函數(shù)創(chuàng)建索引 CREATE INDEX idx_patient_date ON medical_exams(patient_id, exam_date); CREATE INDEX idx_hospital_cost ON medical_exams(hospital_id, cost DESC);