【实践篇】分组查询

有如下的用户信息表 user_profile ，其中字段 active_days_within_30 表示用户 30 天内活跃次数；字段 question_cnt 为用户发帖数量；字段 answer_cnt 为用户回帖数量。

分组练习

下面，我们需要分性别、分学校检索用户人数、30 天内的平均活跃次数以及平均发帖数量。编写的 SQL 如下：

SELECT 
    gender,
    university,
    COUNT(*), 
    ROUND(AVG(active_days_within_30),1) AS avg_active_day,
    ROUND(AVG(question_cnt), 1) AS avg_question_cnt
FROM 
    user_profile
GROUP BY 
    gender, 
    university;

分析的维度为性别 gender 和学校 university ，为此这两个字段位于 GROUP BY 关键字之后，作为分组的依据。

分组是聚合的依据，因此出现在 GROUP BY 之后的字段，在 SELECT 中检索时不需要聚合操作，而其他未作为分组依据的字段出现在 SELECT 之后时，必须使用聚合操作。比如 active_days_within_30 和 question_cnt 进行了平均值的聚合计算。

COUNT(*) 对分组中的记录进行计数。分组检索结果如下：

分组排序

检索各个大学用户的平均发帖数量，并按平均发帖数正序排列，以便找出活跃度低的学校重点运营。

SQL 语句如下：

SELECT 
    university, 
    AVG(question_cnt) AS avg_question_cnt
FROM
    user_profile
GROUP BY 
    university
ORDER BY 
    avg_question_cnt;

维度为各个学校，因此根据学校进行分组；聚合的字段为发帖数量 question_cnt，聚合方式为 AVG 计算均值；最后使用 ORDER BY 字句指定排序的依据为 avg_question_cnt 即 AVG(question_cnt) 计算字段的别名。

分组过滤

注：行级别的过滤使用 WHERE 字句；使用分组聚合产生的字段进行过滤时，则使用 HAVING 指定过滤条件。

检索用户的平均发帖量小于 5 ，并且平均回帖量小于 20 的学校及其平均发帖、回帖的数量。

SQL 语句如下：

SELECT 
    university, 
    AVG(question_cnt) AS avg_question_cnt, 
    AVG(answer_cnt) AS avg_answer_cnt
FROM 
    user_profile
GROUP BY 
    university
HAVING 
    avg_question_cnt < 5 OR avg_answer_cnt < 20;

其中 avg_question_cnt 和 avg_answer_cnt 作为聚合计算字段，用于分组过滤条件时，需要使用 HAVING 关键字指定。