10. 汇总

10. 汇总

你可以使用以下五个聚合函数进行声明性查询：sum()、count()、min()、max()、avg()和group_concat()。

明明是6个嘛......不过最后那个不应该算是聚合函数，只用来进行字符串拼接——gthank

让我们来看一些使用这些函数进行简单查询的例子：

101组的学生的总GPA：

sum(sum(s.gpa for s in Student if s.group.number ==101)

GPA超过3的学生人数：

Count(s for s in Student if s.gpa > 3)

学习哲学、按字母顺序排序的学生的第一个名字：

min(s.name for s in Student if "Philosophy" in s.course.name)

101组中最年轻的学生的出生日期：

max(s.dob for s in Student if s.group.number ==101)

文档中说要返回101班中最年轻的学生的出生日期，但SQL语句返回的应该是最大的出生日期——gthank存疑

44系的平均GPA：

avg(s.gpa for s in Student if s.group.dept.number ==44)

用逗号连接101组的学生名字：

group_concat(s.name for s in Student if s.group.number ==101)

尽管 Python 已经有了标准的函数 sum()、count()、min()和 max()，但 Pony 还是添加了自己的函数，并以相同的名字命名。
此外，Pony 还添加了自己的 avg() 和 group_concat() 函数。
这些函数在pony.orm模块中实现，它们可以从那里 "by the star"或其名称导入。

在 Pony 中实现的函数扩展了 Python 中标准函数的行为，因此，如果在程序中以标准的方式使用这些函数，导入不会影响它们的行为。
但它还允许在函数内部指定一个声明性的查询。

如果忘记了从 pony.orm 包中导入这些函数，那么在使用 Python 标准函数 sum()、count()、min()和 max()时，会出现一个错误，并以声明性查询作为参数。

TypeError: Use a declarative query in order to iterate over entity

聚类函数也可以在查询中使用，例如，如果你不仅需要查找组中最年轻的学生的出生日期，还需要查找学生本人，你可以这么查询：

select(s for s in Student if s.group.number == 101
           and s.dob == max(s.dob for s in Student
                                        if s.group.number == 101))

或者，例如，得到所有平均GPA高于4.5的组：

select(g for g in Group if avg(s.gpa for s in g.s in g.s students) > 4.5)

如果我们使用Pony属性提升功能，这个查询可以更短：

select(g for g in Group if avg(g.schools.gpa) > 4.5)

而这个查询可以显示文章的所有标签：

select((article, group_concat(article.tags)) for article in Aricle)

查询对象聚合函数

你可以调用Query对象的聚合方法：

select(sum(s.gpa) for s in Student)

等于下面的查询：

select(s.gpa for s in Student).sum()

下面是聚合函数的列表：

• Query.avg()
• Query.count()
• Query.min()
• Query.max()
• Query.sum()
• Quety.group_concat()

在一个查询中使用多个聚合函数

SQL允许你在同一个查询中包含多个聚合函数。

例如，我们可能想同时接收每组的最低和最高GPA。在SQL中，这样的查询会是这样的。

SELECT s.group_number, MIN(s.gpa), MAX(s.gpa)
FROM Student s
GROUP BY s.group_number

这个查询将返回每个组的最低和最高GPA，在Pony中，你可以使用同样的方法。

select((s.group, min(s.gpa), max(s.gpa)) for s in Student)

计数函数

聚类查询经常需要计算出某物的数量，下面是我们如何得到101组中的学生人数。

count(s for s in Student if s.group.number == 101)

每组中与系部相关的学生人数44：

select((g, count(g.students)) for g in Group if g.dept.number == 44)

或者是这样：

select((s.group, count(s)) for s in Student if s.group.dept.number == 44)

在第一个例子中，聚合函数count()接收到一个集合，Pony会将其转化为一个子查询，实际上，这个子查询会被Pony优化，并被替换成left join。

在第二个例子中，函数count()接收的是一个单一对象，而不是一个集合，在这种情况下，Pony将在SQL查询中添加一个GROUP BY部分，并在s.group属性上进行分组。

如果你使用count()函数没有参数，这将被翻译成SQL COUNT(*)，如果你指定了参数，将被翻译成COUNT(DISTINCT列)。

有条件的计数

还有一种方法可以使用count()函数，假设我们想得到每组的三个数字：

• GPA小于3的学生数
• GPA在3至4之间的学生人数；
• GPA大于4的学生人数；

该查询可以这样构造。

select((g, count(s for s in g.students if s.gpa <= 3),
           count(s for s in g.students if s.gpa > 3 and s.gpa <= 4),
           count(s for s in g.students if s.gpa > 4)) for g in Group)

虽然这个查询可以工作，但它相当长，而且效率不高--每个计数都会被翻译成一个单独的子查询。

对于这种情况，Pony提供了一个"conditional COUNT "语法。

select((s.group, count(s.gpa <= 3),
                 count(s.gpa > 3 and s.gpa <= 4),
                 count(s.gpa > 4)) for s in Student)

这样一来，我们把条件放到count()函数中，这个查询不会有子查询，这使得它更有效。

上面的查询并不完全等同：如果一个组没有学生，那么第一个查询将把0作为count()的结果，而第二个查询根本不会选择该组。
出现这种情况是因为第二个查询从表 Student 中选择了记录，如果组中没有学生，那么表 Student 中就没有这个组的记录。

如果你想得到带0的记录，那么有效的SQL查询应该使用left_join()函数：

left_join((g, count(s.gpa <= 3),
       count(s.gpa > 3 and s.gpa <= 4),
       count(s.gpa > 4)) for g in Group for s in g.students)

更复杂的聚合查询

使用Pony，你可以进行更复杂的分组，例如，你可以通过属性部分进行分组：

select((s.dob.year, avg(s.gpa)) for s in Student)

在这种情况下，出生年份不是一个独立的属性--它是dob属性的一部分。

你可以在聚合函数中使用表达式。

select((item.order, sum(item.price * item.quantity))
        for item in OrderItem if item.order.id == 123)

下面是另一种相同的查询方式。

select((order, sum(order.items.price * order.items.quantity))
        for order in Order if order.id == 123)

在第二种情况下，我们使用属性提升的概念，表达式order. items.price会产生一个价格数组，而order. items.quantity会产生一个数量数组。
结果，在这个例子中，我们将得到每个订单项目的数量之和乘以价格。

使用HAVING进行查询

SELECT语句有两个不同的部分WHERE和HAVING。
WHERE部分用得比较多，包含将应用于每一行的条件。

如果查询包含聚合函数，如MAX或SUM，SELECT语句也可以包含GROUP BY和HAVING部分。HAVING部分的条件是在对SQL查询结果进行分组后应用的。

通常情况下，HAVING部分的条件总是包含聚合函数，而WHERE部分的条件可能只包含子查询中的聚合函数。

当你写一个包含聚合函数的查询时，Pony 需要确定结果的 SQL 是否包含GROUP BY和 HAVING部分，以及它应该把 Python 查询中的每个条件放在哪里。

如果一个条件包含聚合函数，Pony 将条件放在HAVING部分。否则，它将把条件放在WHERE部分。

考虑一下下面的查询，它返回的是(Group, count_of_students)的元组。

select((s.group, count(s)) for s in Student
       if s.group.dept.number == 44 and avg(s.gpa) > 4)

在这个查询中，我们有两个条件。第一个条件是s.group.dept.number ==44，因为它不包含聚合函数，所以Pony将把这个条件放在WHERE部分；第二个条件avg(s.gpa)>4包含了聚合函数avg，Pony将把这个条件放在HAVING部分。

另一个问题是Pony应该在GROUP BY部分中添加什么列，根据SQL标准，任何放置在SELECT语句中的非聚合列也应该添加到GROUP BY部分，让我们考虑一下下面的查询。

SELECT A, B, C, SUM(D), MAX(E), COUNT(F)
FROM T1
WHERE ...
GROUP BY ...
HAVING ...

根据SQL标准，我们需要将列A、B和C包含在GROUP BY部分，因为这些列是在SELECT列表中出现的，并没有用任何聚合函数包装。

Pony正是这样做的，如果你的聚合的Pony查询返回一个有多个表达式的元组，任何非聚合的表达式都会被放入GROUP BY部分。

让我们再考虑一下同样的Pony查询：

select((s.group, count(s)) for s in Student
       if s.group.dept.number == 44 and avg(s.gpa) > 4)

这个查询返回元组（Group, count_of_students），元组的第一个元素，即Group实例，没有被聚合，所以它将被放置到GROUP BY部分。

SELECT "s"."group", COUNT(DISTINCT "s"."id")
FROM "Student" "s", "Group" "group-1"
WHERE "group-1"."dept" = 44
  AND "s"."group" = "group-1"."number"
GROUP BY "s"."group"
HAVING AVG("s"."gpa") > 4

将s.group表达式放入GROUP BY部分，将条件avg(s.gpa)>4放入查询的HAVING部分。

有时，应该放在HAVING部分的条件中的条件包含一些非汇总列，这样的列会被添加到GROUP BY部分，因为根据SQL标准，如果没有添加到GROUP BY列表中，那么在HAVING部分内禁止使用非汇总列。

再举一个例子：

select((item.order, item.order.total_price,
     sum(item.price * item.quantity))
     for item in OrderItem
     if item.order.total_price < sum(item.price * item.quantity))

这个查询有如下条件： item.order.total_price < sum(item.price * item.quantity)，其中包含一个聚合函数，应该添加到HAVING部分。

但是item.order.total_price这部分没有聚合，因此，为了满足SQL的要求，将其添加到GROUP BY部分中。

利用聚合函数排序

聚合函数可以在Query.order_by()函数内部使用，下面是一个例子：

select((s.group, avg(s.gpa)) for s in Student).order_by(lambda s: desc(avg(s.gpa)))

另一种按聚合值排序的方法是在Query.order_by()方法中指定位置号。

select((s.group, avg(s.gpa)) for s in Student).order_by(-2)