在醫(yī)院信息系統(tǒng)中，我們經(jīng)常需要統(tǒng)計(jì)各種業(yè)務(wù)數(shù)據(jù)，例如門診到訪人數(shù)、掛號(hào)量、就診量等。
但一個(gè)常見需求是：

只統(tǒng)計(jì)工作日（周一 ~ 周五），周六周日不算！

乍一看很簡單，但在實(shí)際數(shù)據(jù)庫中，如果處理不好，很容易出現(xiàn)：

• 查詢很慢（全表掃描 ALL）
• 索引用不上（在字段上套函數(shù)）
• 數(shù)據(jù)量大時(shí) CPU 飆高
• 報(bào)表卡頓

一、業(yè)務(wù)背景

某醫(yī)院有一張到訪記錄表：

CREATE TABLE IF NOT EXISTS t_visit (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    hospital_id INT,
    creatime    DATETIME,
    visit_num   INT
);

每天有大量患者到訪，后臺(tái)需要統(tǒng)計(jì)：

每家醫(yī)院在最近一個(gè)月的工作日到訪總?cè)藬?shù)（排除周六周日）

二、初級(jí)寫法（反面案例）

SELECT
    SUM(visit_num)
FROM t_visit
WHERE WEEKDAY(creatime) < 5;

這看似正確，但這會(huì)導(dǎo)致：

問題 1：索引用不上

因?yàn)槟銓?duì) creatime 使用了函數(shù)：

WEEKDAY(creatime)

使得 MySQL 無法利用索引，explain 一般是：

type: ALL
rows: 全表行數(shù)

問題 2：數(shù)據(jù)量大時(shí)性能災(zāi)難

醫(yī)院一天幾十萬條，一年幾千萬條，
全表掃 + 函數(shù)計(jì)算，直接拖垮服務(wù)器。

所以這不是企業(yè)級(jí)可接受的寫法。

三、基礎(chǔ)優(yōu)化寫法（適合中小數(shù)據(jù)量）

加上時(shí)間范圍，利用 creatime 索引進(jìn)行范圍掃描

CREATE INDEX idx_visit_creatime ON t_visit(creatime);

查詢：

EXPLAIN
SELECT
    SUM(visit_num)
FROM t_visit
WHERE 
    creatime >= '2025-01-01'
    AND creatime <  '2025-02-01'
    AND WEEKDAY(creatime) < 5;
		EXPLAIN
SELECT
    SUM(visit_num)
FROM t_visit FORCE INDEX (idx_visit_creatime)
WHERE 
    creatime >= '2025-01-01'
    AND creatime <  '2025-02-01'
    AND WEEKDAY(creatime) < 5;

優(yōu)勢：

• MySQL 會(huì)先利用時(shí)間范圍做索引掃描
• 再對(duì)掃描結(jié)果執(zhí)行 weekday 過濾
• 比全表掃好很多

但 WEEKDAY() 仍然不是最佳方案。

四、企業(yè)實(shí)戰(zhàn)解決方案

方案 1：生成列（STORED）+ 索引

醫(yī)院數(shù)據(jù)往往需要長期統(tǒng)計(jì)、同比環(huán)比、報(bào)表分析，
企業(yè)里最常用的做法是：

把星期幾提前算好，存在表里，并建立索引

1. 增加生成列

ALTER TABLE t_visit
  ADD COLUMN weekday TINYINT AS (WEEKDAY(creatime)) STORED,
  ADD COLUMN is_workday TINYINT(1) AS (
    CASE WHEN WEEKDAY(creatime) < 5 THEN 1 ELSE 0 END
  ) STORED,
  ADD INDEX idx_visit_workday (is_workday, creatime);

• weekday：0=周一，6=周日
• is_workday：1=工作日，0=周末

2. 查詢變得非?？?/h4>

SELECT
    hospital_id,
    SUM(visit_num)
FROM t_visit
WHERE 
    is_workday = 1        -- 索引用得上！
    AND creatime BETWEEN '2025-01-01' AND '2025-02-01'
GROUP BY hospital_id;

企業(yè)大部分系統(tǒng)都這么做。

方案 2：數(shù)據(jù)倉庫 / 報(bào)表系統(tǒng)的標(biāo)準(zhǔn)：日期維表 dim_date

大型醫(yī)院（尤其三甲）數(shù)據(jù)量很大，
通常會(huì)有數(shù)據(jù)倉庫（DW）或 BI 系統(tǒng)。

在 DW 里幾乎必建：

日期維表（dim_date）

例子：

CREATE TABLE dim_date (
    date_key    DATE PRIMARY KEY,
    weekday     TINYINT,
    is_workday  TINYINT,
    is_holiday  TINYINT,
    holiday_name VARCHAR(20)
);

再加關(guān)聯(lián)字段：

ALTER TABLE t_visit
  ADD COLUMN visit_date DATE AS (DATE(creatime)) STORED,
  ADD INDEX idx_visit_date (visit_date);

然后：

SELECT
    v.hospital_id,
    SUM(v.visit_num) AS total_visit
FROM t_visit v
JOIN dim_date d
    ON v.visit_date = d.date_key
WHERE 
    d.is_workday = 1
    AND v.visit_date BETWEEN '2025-01-01' AND '2025-02-01'
GROUP BY v.hospital_id;

優(yōu)勢：

• 查詢速度快（走索引）
• 節(jié)假日/補(bǔ)班規(guī)則可隨時(shí)調(diào)整
• 所有報(bào)表口徑統(tǒng)一
• 信息科、財(cái)務(wù)、運(yùn)營都能復(fù)用這個(gè)維表

五、醫(yī)院真實(shí)案例演示

需求：統(tǒng)計(jì) 2025 年 1 月各醫(yī)院工作日的到訪人數(shù)

SQL：

SELECT
    v.hospital_id,
    d.is_workday,
    SUM(v.visit_num) AS visit_total,
    COUNT(*) AS visit_times
FROM t_visit v
JOIN dim_date d
    ON v.visit_date = d.date_key
WHERE
    v.visit_date >= '2025-01-01'
    AND v.visit_date <  '2025-02-01'
    AND d.is_workday = 1       -- 只算工作日
GROUP BY v.hospital_id, d.is_workday
ORDER BY hospital_id;

輸出示例：

六、總結(jié)

能跑的是 SQL，能跑快的是架構(gòu)。
企業(yè)里處理“周一到周五統(tǒng)計(jì)”一般不會(huì)直接用 weekday 函數(shù)，而是通過生成列或日期維表實(shí)現(xiàn)高性能統(tǒng)計(jì)。

到此這篇關(guān)于MySQL 中只統(tǒng)計(jì)周一到周五的到訪數(shù)據(jù)(案例演示)的文章就介紹到這了,更多相關(guān)mysql統(tǒng)計(jì)周一到周五數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論