SQL GROUP BY 数据分组

更新时间：2021-08-19 20:43:49 标签：sql groupby

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

GROUP BY 语句是把数据按一定的维度（数据表中的字段或者计算后的分组依据）分组，结合一些聚合函数来计算出分组特征数据。

理解分组

一个列中为分类数据（有限重复，离散的）时，为了了解这些类别的数据表现情况，将这个列的数据分组，分组后表格的索引（可以理解为首列，多列分组为前几列）就变成了这些分组列，且不重复（或者组合不重复）。一些非分组列就需要进行聚合，使用计算方法如求和、求平均等方法进行计算，当然同一个列可以同时有多个不同的聚合方法。如图：

sql-group-by

因此，在 SQL 中，分组依据的列直接可以输出，非分组列需要聚合计算才能输出。

语法

分组的语法如下：

SELECT AGG_FUNC(column_or_expression) AS aggregate_description
FROM mytable
WHERE constraint_expression
GROUP BY column1, column2;

说明：

GROUP BY 在数据筛选后进行分组，分组可以按一个字段，也可以多个字段组合（笛卡尔积）分组
GROUP BY 分组依据可以使用 case 进行计算
分组后需要对要返回的字段或者数据使用聚合函数进行计算，因为分组后无法返回所有值，需要给定一定的算法返回唯一值
如果不指定聚合函数，会显示分组里的第一个值
分组依据字段不用聚合函数
在 hive 中，使用 Group By 时，非 Group By 的字段必须使用聚合函数，只有 Group By 的字段才能原值取出

用序号代表字段

在用 Group By 按一定的字段分组时，如果你不想写字段名，可以用 Select 中的序号来代码这些字段，如：

SELECT item_id, uesr_id
         ^^^^        ^^^^
          1           2
FROM tab
GROUP BY 1;

在上面的查询中 GROUP BY 1 指的是 select 语句中的第一列，那就是 item_id。这个用法，还可以用在 ORDER BY 子句中。

注意：ORDER BY 和GROUP BY 中的数字始终以 1 开头，而不是以 0 开头。

案例

本文例子中使用的数据是筛选指定字段中的数据内容。

select class as class, -- 班级
       avg(2020 - b_year) as avg_age, -- 平均年龄
       avg(math) as avg_math -- 数学平均成绩
from students
where gender == '男'
group by class
'''
class|avg_age|avg_math|
-----+-------+--------+
    1|   51.0|    71.5|
    2|   31.5|    71.0|
    3|   31.5|    77.5|
'''

以上的逻辑为将男生按班级分组，给出每班的平均年龄和平均数学成绩。

select class as class,
       gender as gender,
       avg(2020 - b_year) as avg_age,
       avg(math) as avg_math
from students
group by class, gender
'''
class|gender|avg_age|avg_math|
-----+------+-------+--------+
    1|女     |   28.0|    66.5|
    1|男     |   51.0|    71.5|
    2|女     |   10.0|    88.0|
    2|男     |   31.5|    71.0|
    3|男     |   31.5|    77.5|
'''

以上同时按班级和性别进行分组。

select (case
            when b_year >= 2000 then '00后'
            when b_year >= 1990 then '90后'
            else '其他'
    end)         as gap,
       avg(math) as avg_math
from students
group by (case
              when b_year >= 2000 then '00后'
              when b_year >= 1990 then '90后'
              else '其他'
    end)
'''
gap|avg_math|
---+--------+
00后|    73.0|
90后|    55.0|
其他|    77.4|
'''

以上按年龄分组再进行聚合。

HAVING

HAVING 可以在聚合后对聚合的结果进行条件筛选，因为聚合后的字段不是数据表里的真实字段。

select class as class, -- 班级
       avg(2020 - b_year) as avg_age -- 平均年龄
from students
group by class
having avg_age > 30
order by avg_age
'''
class|avg_age|
-----+-------+
    3|   31.5|
    1|   39.5|
'''

以上，聚合后对返回的结果再将年龄大于 30 岁的筛选出来，再进行排序。

<< 常用聚合统计函数 Hive SQL 教程聚合统计函数 >>