Postgresql中的分组函数（group by 和 having）

奥德赛F9

在通过了WHERE过滤器之后，生成的输出表可以继续用GROUP BY 子句进行分组，然后用HAVING子句删除一些分组行。

SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [, grouping_column_reference]...


GROUP BY 子句 用于把那些在表中所列出的列上共享相同值的行聚集在一起。 这些列的列出顺序并没有什么关系。 效果是把每组共享相同值的行缩减为一个组行，它代表该组里的所有行。 这样就可以删除输出里的重复和/或计算应用于这些组的聚集。 比如：

=> SELECT * FROM test1;
x | y
---+---
a | 3
c | 2
b | 5
a | 1
(4 rows)
=> SELECT x FROM test1 GROUP BY x;
x
---
a
b
c
(3 rows)


在第二个查询里，我们不能写成 SELECT * FROM test1 GROUP BY x， 因为字段 y 里没有哪个值可以和每个组相关联起来。 被分组的字段可以在选择列表中引用是因为它们每个组都有单一的数值。
通常，如果一个表被分了组，那么没有在分组中引用的字段都不能引用，除了在聚集表达式中以外。 一个带聚集表达式的例子是：

=> SELECT x, sum(y) FROM test1 GROUP BY x;
x | sum
---+-----
a |   4
b |   5
c |   2
(3 rows)


这里的 sum 是一个聚集函数，它在整个组上计算一个数值。 有关可用的聚集函数的更多信息可以在 Section 9.15 中找到。
    提示: 没有聚集表达式的分组实际上计算了一个字段中独立数值的集合。 我们也可以用 DISTINCT 子句实现（参阅Section 7.3.3）。
这里是另外一个例子：它计算每种产品的总销售额。（而不是所有产品的总销售额）。

SELECT pid, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING ( pid )
GROUP BY pid, p.name, p.price;


在这个例子里，字段pid， p.name，和p.price必须在GROUP BY子句里， 因为它们都在查询选择列表里被引用到。 （根据产品表具体的设置的不同，名字和价格可能和产品 ID 完全无关，因此理论上额外的分组可能是不必的， 但是这些尚未实现。） 字段s.units不必在GROUP BY列表里，因为它只是在一个聚集表达式（sum(...)） 里使用，它代表一组产品的销售额。对于每种产品，这个查询都返回一个该产品的所有销售额的总和。
在严格的 SQL 里，GROUP BY只能对源表的列进行分组，但 PostgreSQL 把这个扩展为也允许GROUP BY那些在选择列表中的字段。也允许对值表达式进行分组，而不仅是简单的字段．
如果一个表已经用GROUP BY子句分了组，然后你又只对其中的某些组感兴趣， 那么就可以用HAVING子句，它很象WHERE子句，用于删除一个分了组的表中的一些组。 语法是：

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression


在 HAVING 子句中的表达式可以引用分组的表达式和未分组的表达式（后者必须涉及一个聚集函数）。
例子：

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
x | sum
---+-----
a |   4
b |   5
(2 rows)
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
x | sum
---+-----
a |   4
b |   5
(2 rows)


然后是一个更现实的例子：

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;


在上面的例子里，WHERE子句用于那些非分组的字段选择数据行。 （表达式只是对那些最近四周发生的销售为真）。 而HAVING子句选择那些单价超过 5000 的组的行。 请注意聚集函数不需要在查询中的所有地方都一样。