复杂的Sql分组

今天，遇到了一个问题，就是sql的分组的问题，当然，不是简单的分组了，我先描述下题目：

有这么一个表：

1. CREATE TABLE test.tb1(
2. ttime CHAR(14),
3. tcpu DECIMAL(8,2)
4. );

数据是如下：

1. INSERT INTO TEST.db1 VALUES('20170511000000',23.8);
2. INSERT INTO TEST.db1 VALUES('20170511000100',28.2);
3. INSERT INTO TEST.db1 VALUES('20170511000200',30.8);
4. INSERT INTO TEST.db1 VALUES('20170511000300',33.2);
5. .......（这里有很多大量且连续的sql，不列出，太多了，规律都是一样的）
6. INSERT INTO TEST.db1 VALUES('20170511235600',29.3);
7. INSERT INTO TEST.db1 VALUES('20170511235700',31.2);
8. INSERT INTO TEST.db1 VALUES('20170511235800',28.9);
9. INSERT INTO TEST.db1 VALUES('20170511235900',29.6);

 

很明显，表里就一个数值字段，那么这类型的待分组表就形成了这样的格式：

• 待分组字段--》该字段要满足一定的函数式的分组

也就是：这个字段，经过一定的运算之后，就会形成一个闭包合集

就是如下图：

最简单的例子，就是：列为自然数，然后取模为5，那么就会形成一些只有0，1，2，3，4了。再加上一些其他的信息，就会形成一个一个的片区（注意哦，一定要加上一些其他的信息，不然的话，模出来的闭包，估计会太小之类）

• 可以使用子查询，创建新的表，然后再在子查询上进行操作

 

好了，下面，我就对这个表：进行每五分钟平均CPU查询

1. SELECT
2. min5,
3. AVG(t1.tcpu) AS num
4. FROM
5. (SELECT
6. CONCAT(
7. SUBSTRING(ttime,9,2),
8. LPAD(
9. CAST(
10. (
11. FLOOR(
12. (
13. CAST(SUBSTRING(ttime,11,2) AS UNSIGNED)/5+1
14. )
15. )*5-1
16. ) AS CHAR(2)
17. ),
18. 2,
19. '0'
20. )
21. ) AS min5,
22. tcpu
23. FROM
24. TEST.db1
25. WHERE ttime >='20170511000000'

26. AND ttime <=20170511235900) AS t1
27. GROUP BY t1.min5 ;

 

综上：秘诀就是两个：

• 对分组列进行闭包取模处理
• 嵌套子查询，降解难度

当然，在这里，我不得不说的是：使用太多的子查询嵌套会降低Sql的查询效率，但是，我们得先实现功能，才能寻求优化！

这个sql的优化，我将继续深入！