今天,遇到了一个问题,就是sql的分组的问题,当然,不是简单的分组了,我先描述下题目:
有这么一个表:
CREATE TABLE test.tb1(
ttime CHAR(14),
tcpu DECIMAL(8,2)
);
数据是如下:
INSERT INTO TEST.db1 VALUES('20170511000000',23.8);
INSERT INTO TEST.db1 VALUES('20170511000100',28.2);
INSERT INTO TEST.db1 VALUES('20170511000200',30.8);
INSERT INTO TEST.db1 VALUES('20170511000300',33.2);
.......(这里有很多大量且连续的sql,不列出,太多了,规律都是一样的)
INSERT INTO TEST.db1 VALUES('20170511235600',29.3);
INSERT INTO TEST.db1 VALUES('20170511235700',31.2);
INSERT INTO TEST.db1 VALUES('20170511235800',28.9);
INSERT INTO TEST.db1 VALUES('20170511235900',29.6);
很明显,表里就一个数值字段,那么这类型的待分组表就形成了这样的格式:
- 待分组字段--》该字段要满足一定的函数式的分组
也就是:这个字段,经过一定的运算之后,就会形成一个闭包合集
就是如下图:
![](https://i-blog.csdnimg.cn/blog_migrate/b3b85fba50bb81c5f5f9e5395589ca33.png)
最简单的例子,就是:列为自然数,然后取模为5,那么就会形成一些只有0,1,2,3,4了。再加上一些其他的信息,就会形成一个一个的片区(注意哦,一定要加上一些其他的信息,不然的话,模出来的闭包,估计会太小之类)
- 可以使用子查询,创建新的表,然后再在子查询上进行操作
好了,下面,我就对这个表:进行每五分钟平均CPU查询
SELECT
min5,
AVG(t1.tcpu) AS num
FROM
(SELECT
CONCAT(
SUBSTRING(ttime,9,2),
LPAD(
CAST(
(
FLOOR(
(
CAST(SUBSTRING(ttime,11,2) AS UNSIGNED)/5+1
)
)*5-1
) AS CHAR(2)
),
2,
'0'
)
) AS min5,
tcpu
FROM
TEST.db1
WHERE ttime >='20170511000000'
AND ttime <=20170511235900) AS t1
GROUP BY t1.min5 ;
综上:秘诀就是两个:
- 对分组列进行闭包取模处理
- 嵌套子查询,降解难度
当然,在这里,我不得不说的是:使用太多的子查询嵌套会降低Sql的查询效率,但是,我们得先实现功能,才能寻求优化!
这个sql的优化,我将继续深入!