select后有非聚合列的解决方法

SQL标准及MySQL的发展变化

SQL标准的发展

​ 在SQL教程或教材中一般有关于group by的黄金定律——select列表中的任何非聚合字段都必须包含在group by表达式中。即select列表的值要么是聚合函数的结果、要么来自group by列表。这条定律遵守SQL92标准。这基于安全假设：因为非聚合列可能有多个值，如果不分组，引擎很难知道取哪个值。

​ SQL99标准这条定律有修改。select列表中的任何非聚合列都必须在**功能上依赖**group by列表。

MySQL的变化

​ 在以前，MySQL并未强求严格按照SQL92标准编写查询命令。MySQL中的ONLY_FULL_GROUP_BY模式遵守SQL92标准的规定，默认情况下是关闭的。

​ 在MySQL5.7及之后，MySQL中的ONLY_FULL_GROUP_BY模式遵守SQL99标准的规定，默认情况下是开启的，该模式的含义更加宽泛。

解决方法

MySQL解决方法

如果想在select列表中根据业务内容需要非group by后的字段，有以下解决方法：

1. 最早的解决方法

   删掉该字段

   将该列聚合列参加到gropy by中

2. 使用函数绕过限制，any_value(非聚合列)或其他函数

   any_value()会选择 被分到同一组数据中 第一条数组的列值 作为返回数据。

   注意：

   • 一定要清楚，SELECT 后面出现的非分组列一定是直接依赖分组列，并且在每个组内的值是唯一的

     此情况尤其适用为该非聚合列与gropy by后字段具有对应关系的情况,如gropu by学号，select any_value(姓名)。(ps：如果学号是主键可以看3特殊情况)

   • 或者是业务上根本不在乎这个值具体是什么

3. 特殊情况

   虽然select列表是非聚合列

   ，但是如果group by后接

   • 主键，或
   • 唯一非空索引 unique NOT NULL

   在select后是可以查询非聚合列的。

   原因：此时分组的key是主键，则每个分组只有一条数据，可以查询非聚合列。

4. 将only_full_group_by参数设置为关闭状态

Hive解决办法(补充)

在hive中，select查询的列要么是聚合函数、要么是group by后的列。解决方法如下：

1. 较早的解决方法

   删除该列

   将该列添加到group by后

2. 使用窗口函数

​ 显示所有列的值

1. collect_set()函数

   作用：返回该分组下的列的集合,如[值1,值2,…,值n]，

​ 可以通过collect_set()[n]中的n来指定选择集合中的元素。