概念
GROUP BY allows you to separate data into groups, which can be aggregated independently of one another.
GROUP BY 语句作用于某一列或者多列(分组列),然后按照分组列的不同值对表格进行分组(分组=分区+去重)
为什么强调”去重“,因为窗口函数没有去重 ,只有分区功能。
如何理解去重?GROUP BY city
就是选出 City 的值作为分组对象,这个值必然是不重复的。
name | city |
---|---|
Robin | sh |
Bob | hz |
Lily | sh |
Alice | sz |
分组对象为
City |
---|
sh |
hz |
sz |
用法
-
字段去重
SELECT col_name FROM t WHERE condition GROUP BY col_name
-
分组聚合
GROUP BY 更常用的是聚合函数配合使用,将全局聚合变成分组聚合,结果由原来的一行,变为多行。
如:
SELECT city, COUNT(name) cnt
FROM t
GROUP BY city
结果则为:
name | cnt |
---|---|
sh | 2 |
hz | 1 |
sz | 1 |
聚合函数COUNT
统计的是在分组的情况下,分组以外字段的数量。
注意事项
-
不论语法还是实际执行顺序,
GROUP BY
分组操作 在 行过滤WHERE
之后进行 -
使用了
GROUP BY
之后对SELECT的限制:SELECT
之后的字段必须是分组字段或者聚合字段,不允许有其它多余字段。再强调一遍,所有SELECT
之后的字段不是分组字段,就必须在聚合函数中!
新手大忌:SELECT * FROM table_a GROUP BY col
牢记
GROUP BY
与通配符*
水火不容! -
如果包含分组对象的值含有
NULL
,那么NULL
也会作为最终分组结果之一。 -
如果需要对聚合项的聚合值进行行过滤要使用
HAVING
,而不是对特定行进行行过滤的WHERE
(WHERE
进行普通行过滤,而HAVING
分组行过滤) -
HAVING
可以使用SELECT 中字段的别名,虽然HAVING
在SELECT
之前执行(奇怪!)
例如:SELECT m.s_id, AVG(IFNULL(m.s_score, 0)) avg_score FROM ( SELECT st.s_id, sc.s_score FROM Student st LEFT JOIN Score sc ON st.s_id = sc.s_id ) m GROUP BY m.s_id -- HAVING AVG(IFNULL(m.s_score, 0)) < 60 HAVING avg_score < 60 # why this also OK ?