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

布满荆棘的人生 2023-06-22 05:28 7阅读 0赞

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

首先两个日期的加减涉及到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

查询出来的结果，如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk_size_16_color_FFFFFF_t_70 1]  
然后为了能更好看，表达的更清晰一点，可以使用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

查询出来的结果为：  
![在这里插入图片描述][20191214150852772.png]  
将查询结果，做成excel图表如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019121414402312.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191214145557217.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk,size_16,color_FFFFFF,t_70
[20191214150852772.png]: https://img-blog.csdnimg.cn/20191214150852772.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191214151158375.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnl1aHVhX2tleWJvYXJk,size_16,color_FFFFFF,t_70