mysql把日期相减并按照区间范围统计

最新推荐文章于 2024-06-27 01:41:14 发布

原创最新推荐文章于 2024-06-27 01:41:14 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

47 篇文章

订阅专栏

本文介绍如何使用MySQL的DATEDIFF和INTERVAL函数计算两个时间字段的差，并将其按区间统计，最后通过ELT函数将数值转换为描述性文本，便于理解和展示。通过具体SQL语句的展示，详细说明了如何从数据表中提取所需信息，进行时间差的计算，分组统计以及结果的图表化处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据计算导入时间和审核时间之间的差并且按照时间区间，整理成excel画出图表。
在这里插入图片描述
关键的表字段如上图所示。

首先两个日期的加减涉及到mysql的函数datediff（），可以计算出两者之间的差值

SELECT
	fk_comp_id,
	datediff(
		LEFT (f_create_time, 8),
		LEFT (f_date, 8)
	)
FROM
	t_athlete_score
WHERE
	f_year = 2019
GROUP BY
	fk_comp_id

然后计算差值在什么范围之内，使用到mysql的函数INTERVAL()，INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。下面的代码是显示 INTERVAL()函数如何工作的一个简单的例子：

mysql>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10);
+---------------------------------------------------------+
| INTERVAL(6,1,2,3,4,5,6,7,8,9,10)                        |
+---------------------------------------------------------+
| 6                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

结果中，6是从零开始的索引，列表第一个值的值大于N. 在我们的例子中，6 属于 [6,7) ，所以返回第6个索引

sql可以演化为：


SELECT
	
		INTERVAL (
			datediff(
				LEFT (f_create_time, 8),
				LEFT (f_date, 8)
			),
			0,
			10,
			20,
			30,
			40,
			50,
			60,
			70,
			80,
			90,
			100,
			110,
			120
		) AS dayNumber,
	fk_comp_id,
	datediff(
		LEFT (f_create_time, 8),
		LEFT (f_date, 8)
	)
FROM
	t_athlete_score
WHERE
	f_year = 2019
GROUP BY
	fk_comp_id

查询出来的结果，如图所示：
在这里插入图片描述
然后为了能更好看，表达的更清晰一点，可以使用ELT()函数，将每个值赋予意义，ELT(n,str1,str2,str3,…) ：如果n=1，则返回str1,如果n=2，则返回str2,依次类推。如果n小于1或大于参数个数，返回NULL。

mysql> SELECT ELT(3,'hello','halo','test','world');
+--------------------------------------+
| ELT(3,'hello','halo','test','world') |
+--------------------------------------+
| test                                 |
+--------------------------------------+
1 row in set

此时，mysql的SQL语句演变为：

SELECT
	ELT(INTERVAL(datediff(LEFT (f_create_time, 8),LEFT (f_date, 8)),0,10,20,30,40,50,60,70,80,90,100,110,120),'<=10','10<x<=20','20<x<=30','30<x<=40','40<x<=50','50<x<=60','60<x<=70','70<x<=80','80<x<=90','90<x<=100','100<x<=110','110<x<=120','x>=120') as dayNumber,
	fk_comp_id,
datediff(LEFT (f_create_time, 8),LEFT (f_date, 8))
FROM
	t_athlete_score
WHERE
	f_year = 2019
GROUP BY
	fk_comp_id

然后统计每个区间的个数，sql演化为：

select m.dayNumber,count(fk_comp_id) from (
SELECT
	ELT(
		INTERVAL (
			datediff(
				LEFT (f_create_time, 8),
				LEFT (f_date, 8)
			),
			0,
			10,
			20,
			30,
			40,
			50,
			60,
			70,
			80,
			90,
			100,
			110,
			120
		),
		'<=10',
		'10<x<=20',
		'20<x<=30',
		'30<x<=40',
		'40<x<=50',
		'50<x<=60',
		'60<x<=70',
		'70<x<=80',
		'80<x<=90',
		'90<x<=100',
		'100<x<=110',
		'110<x<=120',
		'x>=120'
	) AS dayNumber,
	fk_comp_id,
	datediff(
		LEFT (f_create_time, 8),
		LEFT (f_date, 8)
	),INTERVAL (
			datediff(
				LEFT (f_create_time, 8),
				LEFT (f_date, 8)
			),
			0,
			10,
			20,
			30,
			40,
			50,
			60,
			70,
			80,
			90,
			100,
			110,
			120
		) as orderindex
FROM
	t_athlete_score
WHERE
	f_year = 2019
GROUP BY
	fk_comp_id
) as m GROUP BY m.dayNumber ORDER BY m.orderindex