最近需要在接口调用记录表统计各区间内的数据,刚开始使用存储过程来实现,发现查询效率不高;后来查询相关资料后,找到一个不错的解决方法,主要使用 etl 和 INTERVAL 函数来实现,举例如下:

1. 建立一个 class 表

分别插入每个分数段的多条数据,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * FROM class;
+--------+-------------+
| name | score
+--------+-------------+
| MinHow | 100 |
| Jack | 80 |
| Tom | 60 |
| Howe | 64 |
| Albutt | 33 |
| Bonnie | 50 |
| Carmen | 90 |
| Connie | 75 |
| Irene | 66 |
| June | 88 |
+--------+-------------+
10 rows in set (0.00 sec)

2. 统计各分数区间数据

现在要统计:<50、50-60、60-70、70-80、80-90、90-100、>=100分数区间的人数;
利用 INTERVAL 划出7个区间;
再利用 elt 函数将7个区间分别返回一个列名,如下SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100') as score_level, count(name) as counts
-> FROM class
-> GROUP BY elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100');
+-------------+--------+
| score_level | counts |
+-------------+--------+
| 50-60 | 1 |
| 60-70 | 3 |
| 70-80 | 1 |
| 80-90 | 2 |
| 90-100 | 1 |
| <50 | 1 |
| >=100 | 1 |
+-------------+--------+
7 rows in set (0.00 sec)

SQL语句分析:

  • 按分数(score列)区间分组, 通过INTERVAL(score, 0, 50, 60, 70, 80, 90, 100 ...)函数,将分数按固定区间分割为: '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100' ...; 函数 INTERVAL 将返回对应的下标值, 起始值为1;INTERVAL函数详情
  • 使用elt(score, 0, 50, 60, 70, 80, 90, 100 ...)函数将对应区间转换为指定值, 即: '<50', '50-60', '60-70', '70-80', '80-90', '90-100', '>=100' ...ELT函数详情
3. 对各分数区间进行排序

如果需要按从小到大排序的话,可以在列名定义时加一个首字符,对各分数区间进行排序,如下SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '1/<50', '2/50-60', '3/60-70', '4/70-80', '5/80-90', '6/90-100', '7/>=100') as score_level, count(name) as counts
-> FROM class
-> GROUP BY elt(INTERVAL(score, 0, 50, 60, 70, 80, 90, 100), '1/<50', '2/50-60', '3/60-70', '4/70-80', '5/80-90', '6/90-100', '7/>=100');
+-------------+--------+
| score_level | counts |
+-------------+--------+
| 1/<50 | 1 |
| 2/50-60 | 1 |
| 3/60-70 | 3 |
| 4/70-80 | 1 |
| 5/80-90 | 2 |
| 6/90-100 | 1 |
| 7/>=100 | 1 |
+-------------+--------+
7 rows in set (0.01 sec)

最后更新: 2018年01月06日 19:07

原始链接: http://blog.minhow.com/2017/08/15/database/mysql-interval-group/

× 请我吃糖~
打赏二维码