mysql区间分组统计

场景：

表cases,有完成时间(finish_time)，id等字段
现在将finish_time按时间区间划分年份
例 2021-11-01 00:00:00 ~2022-10-31 23:59:59 为2022年
下面是模拟数据：

id	finish_time
1	2019-01-23 23:23:23
2	2019-02-28 12:12:12
3	2020-08-12 08:12:12
4	2021-05-12 22:12:43
5	2022-05-12 23:23:23

统计结果应该如下所示：

年份	数量
2019	2
2020	1
2021	1
2022	1

因为不能在cases表中添加字段，上网搜寻后找到两个方法:

1. 利用了mysql提供的interval和elt函数实现了效果。
2. 是建一张中间表用来分组

利用了mysql提供的interval和elt函数实现了效果

interval

interval(N,N1,N2,N3) ,比较列表中的N值，该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。

elt

elt(n,str1,str2,str3,…) 如果n=1，则返回str1,如果n=2，则返回str2,依次类推
两个函数结合，再加上group，实现了这种范围分组的效果

select elt(interval(finish_ime,'2019-11-01','2020-11-01','2021-11-01','2022-11-01'),2019,2020,2021,2022) as i,count(*)
from cases
group by i

但实际效果不尽人意，也许是因为mysql中interval也是处理日期的关键字吧，interval()函数对日期进行划分的时候每次都划分到最后一个区间，因此使用第二种办法即建一张中间表用来划分

建中间表

创建如下一张中间表（tmp）

start_date	end_date	year
2018-11-01 00:00:00	2019-10-31 23:59:59	2019
2019-11-01 00:00:00	2020-10-31 23:59:59	2020
2020-11-01 00:00:00	2021-10-31 23:59:59	2021
2021-11-01 00:00:00	2022-10-31 23:59:59	2022

cases表通过join连接这张表就可以实现分组效果

	select count(*) as '数量', t.`year` as "年份"
	from cases c left join tmp t on c.finish_time>=t.start_date and 
									c.finish_time<=t.end_date 
	group by t.`year`

这样就可以在不修改原有表结构的基础上实现区间分组统计。

通过函数来划分区间

创建函数get_year()

create function get_year(finishTime datetime) returns int
begin
    declare d_year int(4);
    set d_year = year(finishTime);
    if (finishTime >= concat(d_year, '-11-01 00:00:00')) then
        return d_year + 1;
    else
        return d_year;
    end if;
end;

	select count(*) as '数量',get_year(c.finish_ime) as "年份"
	from cases c 
	group by get_year(c.finish_ime)