MySQL 中只统计周一到周五的到访数据(案例演示)_Mysql

在医院信息系统中，我们经常需要统计各种业务数据，例如门诊到访人数、挂号量、就诊量等。
但一个常见需求是：

只统计工作日（周一 ~ 周五），周六周日不算！

乍一看很简单，但在实际数据库中，如果处理不好，很容易出现：

查询很慢（全表扫描 all）
索引用不上（在字段上套函数）
数据量大时 cpu 飙高
报表卡顿

一、业务背景

某医院有一张到访记录表：

create table if not exists t_visit (
    id          bigint primary key auto_increment,
    hospital_id int,
    creatime    datetime,
    visit_num   int
);

每天有大量患者到访，后台需要统计：

每家医院在最近一个月的工作日到访总人数（排除周六周日）

二、初级写法（反面案例）

select
    sum(visit_num)
from t_visit
where weekday(creatime) < 5;

这看似正确，但这会导致：

问题 1：索引用不上

因为你对 creatime 使用了函数：

weekday(creatime)

使得 mysql 无法利用索引，explain 一般是：

type: all
rows: 全表行数

问题 2：数据量大时性能灾难

医院一天几十万条，一年几千万条，
全表扫 + 函数计算，直接拖垮服务器。

所以这不是企业级可接受的写法。

三、基础优化写法（适合中小数据量）

加上时间范围，利用 creatime 索引进行范围扫描

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;

优势：

mysql 会先利用时间范围做索引扫描
再对扫描结果执行 weekday 过滤
比全表扫好很多

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

四、企业实战解决方案

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

医院数据往往需要长期统计、同比环比、报表分析，
企业里最常用的做法是：

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

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. 查询变得非常快

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;

企业大部分系统都这么做。

方案 2：数据仓库 / 报表系统的标准：日期维表 dim_date

大型医院（尤其三甲）数据量很大，
通常会有数据仓库（dw）或 bi 系统。

在 dw 里几乎必建：

日期维表（dim_date）

例子：

create table dim_date (
    date_key    date primary key,
    weekday     tinyint,
    is_workday  tinyint,
    is_holiday  tinyint,
    holiday_name varchar(20)
);

再加关联字段：

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;

优势：

查询速度快（走索引）
节假日/补班规则可随时调整
所有报表口径统一
信息科、财务、运营都能复用这个维表

五、医院真实案例演示

需求：统计 2025 年 1 月各医院工作日的到访人数

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;

输出示例：