聚合

您可以对声明性查询使用以下五个聚合函数：sum()、count()、min()、max()、avg() 和 group_concat()。让我们看看使用这些函数的简单查询的一些示例。

101 组学生的总 GPA

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.courses.name)

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

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

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 模块中实现，可以从那里导入，无论是“按星号”，还是按名称导入。

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.students) > 4.5)

如果我们使用 Pony 的 属性提升 功能，此查询可以更短。

select(g for g in Group if avg(g.students.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()

以下是聚合函数的列表。

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

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)

函数 count

聚合查询通常需要计算某事物的数量。以下是如何获取 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 column)。

条件 count

还有另一种使用 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)

虽然此查询将起作用，但它很长，而且效率不高 - 每个 count 将转换为一个单独的子查询。对于这种情况，Pony 提供了“条件 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() 函数中。此查询将没有子查询，这使其更有效。

注意

上面的查询并不完全等效：如果一个组没有任何学生，那么第一个查询将选择该组，并以零作为 count() 的结果；而第二个查询则根本不会选择该组。这是因为第二个查询从 Student 表中选择行，如果该组没有任何学生，那么 Student 表将不会为此组有任何行。

如果您想要获取包含零的行，那么有效的 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，并将被放置到 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 没有被聚合。因此，它将被添加到 GROUP BY 部分以满足 SQL 要求。

排序部分的聚合函数

聚合函数可以在 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)