MySQL(六) 基础查询操作-分组和排序

在日常进行数据分析的时候，分组和排序是比较常用的两种手段，就以超市售卖的物品为例，可以将食物，百货，生鲜等进行分组，可以对不同组的销售额进行统计排序，看看哪个种类的物品，具体哪一个物品的售卖情况最好，今天我就学习一下GROUP BY和ORDER BY操作吧。

1.分组

1.1 GROUP BY-分组

select accuracy_level from history group by accuracy_level;

可以明显看到分组之后的结果是无序的，这是GROUP BY的特征之一，一般要结合后面的排序来调整顺序，下面介绍一下聚合函数和GROUP BY的联用，这里需要注意的是GROUP BY操作不能对AS的别名进行操作，只能对原列名进行操作，但是这个规则在MySQL和PostgreSQL中并不适用。

select accuracy_level,count(accuracy_level) from history group by accuracy_level;

在这里count(accuracy_level)可以换成count(*)和count(1)，在指定列名没有NULL值的时候三者是一样的，在指定列名有NULL值得时候，前者会忽略NULL值，后两者并不会忽略NULL值，这三种表达形式主要是在查询的时候效率会不一样，如果指定列是主键的话，count(accuracy_level)这种查询的速度较快，其他的话count(1)和count(*)较快。到现在出现的子句已经越来越多了，但是还没有齐全，还有更多的字句，字句的排布是有顺序的，不能出错，现在出现的子句顺序是SELECT-->FROM-->WHERE-->GROUP BY。

1.2 GROUP BY-常见错误语句

在这里需要提到一个SQL的概念词聚合键和分组列，select column_A from table_name group by column_A，这里的column_A就被成为聚合键或者分组列，在使用聚合函数的时候，select后面跟着的元素只能是常数、聚合函数和聚合键，不能出现其他的列名称。

在WHERE字句中使用聚合函数也是典型错误。

select user_name,accuracy_level,count(*) from history group by accuracy_level;

select accuracy_level,count(*) from history where count(*) = '279' group by accuracy_level;

1.3 HAVING字句

WHERE子句只能对行记录进行筛选，不能对分组数据进行筛选，这时候想要对分组后的数据进行筛选的话需要用到HAVING字句。HAVING语句和WHERE语句都能够对数据进行筛选，但是二者有所区别，WHERE子句的元素不能使用聚合函数，而HAVING子句的元素可以使用聚合函数，还有常数和聚合键，HAVING子句要写在GROUP BY子句之后，具体的顺序为SELECT-->FROM-->WHERE-->GROUP BY-->HAVING。

select accuracy_level,count(*) from history group by accuracy_level;
select accuracy_level,count(*) from history group by accuracy_level HAVING COUNT(*)=312;

select avg(softB_coretime) from history group by user_name;
select avg(softB_coretime) from history group by user_name HAVING avg(softB_coretime) >= 20;

select avg(softB_coretime) from history group by user_name HAVING end_time >= '20210301';

以上的错误情况是HAVING子句出现了聚合函数，聚合键和常数之外的元素。

在如下情况下，WHERE和HAVING子句执行的结果是一致的，那在这样的情况下，我们应该选择哪个子句呢？

一、比较简单也更容易理解子句的理由就是按照WHERE和HAVING的作用区分，前者作用于行，后者作用于组。

二、另一个选择理由就需要了解一下DBMS的运行机理了，之前列举过一些SQL语句的格式顺序，但是实际的执行顺序是不一样的，实际执行的顺序是

FROM-->WHERE-->GROUP BY-->SELECT(COUNT/AVG/MAX/MIN)-->HAVING

在DBMS中使用聚合函数的时候会对数据进行排序，数据排序会提高机器的负担。因此，参与排序的数据越少越好，根据上面的执行顺序我们可以发现HAVING在SELECT(COUNT/AVG/MAX/MIN)这些触发排序操作之后，WHERE在触发排序之前，WHERE子句可以在排序前把不符合条件的数据行去除掉，速度上WHERE更快。

select avg(lyra_coretime) as where_clause from history where user_name in ('user01','user02') group by user_name;
select avg(lyra_coretime) as having_clause from history group by user_name having user_name in ('user01','user02');

我这里因为数据太少了，没有百万级，千万级的数据量，在查询时间上没有特别的明显，但是理解没有问题。

2.排序

2.1 ORDER BY-排序

ORDER BY理解起来比较简单，它是对最后结果的显示做了处理，所以加入ORDER BY后的语句执行顺序如下

FROM-->WHERE-->GROUP BY-->SELECT(COUNT/AVG/MAX/MIN)-->HAVING-->ORDER BY

在ORDER BY子句后选择是否加DESC可以实现升降序排列。

select accuracy_level,count(*) from history group by accuracy_level order by accuracy_level;
select accuracy_level,count(*) from history group by accuracy_level order by accuracy_level desc;

也可以指定多个排序键，从左往右优先级逐渐降低，如下先对accuracy_level进行排序，当accuracy_level一致的时候再对end_time进行排序。

select accuracy_level,end_time from history;
select accuracy_level,end_time from history order by accuracy_level;
select accuracy_level,end_time from history order by accuracy_level,end_time;

在对NULL进行排序的时候，之前比较运算符的时候提到过不能对NULL进行比较，所以NULL值的数据会统一放在头部或者结尾。

之前讲过GROUP BY是不能使用别名的，但是ORDER BY可以，从上面的执行顺序可以理解，在进行GROUP BY操作的时候还没有进行SELECT(AS)操作，ORDER BY在SELECT(AS)操作之后，所以可以对别名进行排序，这里我就不列举了。

除以上的情况之后，SELECT的元素还可以是非SELECT列元素和聚合函数，主要还是因为ORDER BY是最后对查询结果的操作，功能比较简单，限制较少。

【还有一个依旧在很久之前就被摒弃掉的格式，用1,2,3,4.....这样的常数表示SELECT中列元素来进行排序，这个了解一下就行，不是很好用，语句字面意思不太好理解】

发送评论 编辑评论

发送评论编辑评论