INTERVAL(N,N1,N2,N3,…) 函数

INTERVAL()函数进行比较列表(N1，N2，N3等等)中的N值。该函数如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。如果N为NULL，它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作。

mysql> select INTERVAL(10,0,5,10,15);
+------------------------+
| INTERVAL(10,0,5,10,15) |
+------------------------+
|                      3 |
+------------------------+
1 row in set (0.00 sec)

INTERVAL(10,0,5,10,15) 会匹配区间0-5，5-10，10-15，15- 四个区间。索引分别为0，1，2，3。因为10位于第三区间（前闭后开），所以结果为3。

可以配合count函数，统计各个区间的分布情况。比如：

mysql> select INTERVAL(10,0,5,10,15) as i,count(*) from test group by i;
+---+----------+
| i | count(*) |
+---+----------+
| 3 |     10 |
+---+----------+
1 row in set (0.00 sec)

ELT(N,str1,str2,str3,…) 函数

如果N =1返回str1，如果N= 2返回str2，等等。返回NULL如果参数的数量小于1或大于N。ELT()是FIELD()的补集。

mysql> select ELT(INTERVAL(10,0,5,10,15),'interval1','interval2','interval3','interval4');
+-----------------------------------------------------------------------------+
| ELT(INTERVAL(10,0,5,10,15),'interval1','interval2','interval3','interval4') |
+-----------------------------------------------------------------------------+
| interval3                                                                   |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

实战

select ELT(INTERVAL(pfs.time_cost, 0,500,1000,60000, 90000, 120000), '0-0.5ms','0.5-1s','1-60s', '60-90s', '60-120s', '>=120s') as time_cost, count(1)
from ods_dbaas_cust_instance ci, ods_dbaas_task_queue tq, ods_dbaas_pengine p, ods_dbaas_pengine_finished_step pfs WHERE ci.db_type = 'polardb_mysql_ro' and tq.target_id = ci.origin_id and tq.region_code = ci.region_code and tq.task_key = 'docker_install_ins' and tq.gmt_created > '2020-06-24 11:35:19' and tq.region_code = p.region_code and tq.origin_id = p.task_id and pfs.pengine_id = p.origin_id and pfs.region_code = p.region_code and pfs.step_name = 'do_pull_image_needed' group by ELT(INTERVAL(pfs.time_cost, 0,500,1000,60000, 120000), '0-0.5ms','0.5-1s','1-60s', '60-90s', '60-120s', '>=120s');

+-----------+----------+
| time_cost | count(1) |
+-----------+----------+
| 0-0.5ms   |      138 |
| 0.5-1s    |      351 |
| 1-60s     |      363 |
| 60-90s    |        6 |
+-----------+----------+
4 rows in set (0.31 sec)