聚合函数

聚合函数允许您通过对行组执行计算来汇总数据。例如，如果您有一个名为 orders 的表，其中包含一个名为 amount 的列，您可以使用聚合函数来获取 amount 列的总和，无论是针对所有行，还是针对共享特定值的每一组行，例如所有共享相同 order_date 的行。

注意

在 PostgREST 中，聚合函数默认情况下是禁用的，因为如果没有适当的保护措施，聚合函数会导致性能问题。有关更多详细信息，请参阅 db-aggregates-enabled。

PostgREST 支持以下聚合函数：avg()、count()、max()、min() 和 sum()。有关这些函数的详细说明，请参阅 PostgreSQL 文档中关于聚合函数的部分。

要使用聚合函数，您需要将函数附加到 select 参数中的值，如下所示

curl "http://localhost:3000/orders?select=amount.sum()"

使用上面的查询，PostgREST 将返回一个包含单个名为 sum 的列的单行，该列包含 amount 列中所有值的总和。

[
  {
    "sum": 1234.56
  }
]

您可以通过在 select 参数中添加更多包含聚合函数的列来使用多个聚合函数。

要按其他列分组，只需将这些列添加到 select 参数中。例如

curl "http://localhost:3000/orders?select=amount.sum(),amount.avg(),order_date"

这将为 order_date 列中的每个唯一值返回一行，其中包含所有共享相同 order_date 的行的 amount 列的总和和平均值。

[
  {
    "sum": 1234.56,
    "avg": 123.45,
    "order_date": "2023-01-01"
  },
  {
    "sum": 2345.67,
    "avg": 234.56,
    "order_date": "2023-01-02"
  }
]

注意

聚合函数与其他 PostgREST 功能一起使用，例如 水平过滤、JSON 列 和 排序。请注意，目前聚合函数与 域表示 不兼容。此外，PostgreSQL 的 HAVING 子句和按聚合列排序尚不支持。

关于 count() 的情况

注意

在添加聚合函数之前，可以通过将 count（不带括号）添加到 select 参数来进行计数。虽然这种方法仍然支持，但可能会在将来被弃用，因此**不建议**使用此旧功能。请改用 count()（带括号）。

count() 被特殊对待，因为它可以在没有关联列的情况下使用。例如，以下查询

curl "http://localhost:3000/orders?select=count(),order_date"

这将为 order_date 列中的每个唯一值返回一行，以及共享相同 order_date 的所有行的计数。

[
  {
    "count": 4,
    "order_date": "2023-01-01"
  },
  {
    "count": 2,
    "order_date": "2023-01-02"
  }
]

当 count() 与关联列一起使用时，其行为略有不同：它将返回所有非 NULL 值的计数。这是由于 PostgreSQL 本身对 count() 函数的实现方式。

重命名和转换

重命名聚合

与其他列一样，您也可以重命名聚合列。有关详细信息，请参阅 重命名列。

在聚合函数的上下文中，重命名列特别有用，因为默认情况下，应用了聚合函数的列将采用应用的聚合函数的名称。您可能希望提供一个更有语义意义的名称，或者在使用多个相同类型的聚合函数时防止冲突。

转换聚合

当将聚合函数应用于列时，您可以转换输入到聚合函数的值以及从聚合函数输出的值。在这两种情况下，语法都与 转换列 中描述的一致，唯一的区别是转换的放置位置。

转换输入的值

例如，假设 orders 表有一个 JSON 列 order_details，此列包含一个 JSON 对象，该对象有一个键 tax_amount。假设您想获取每个订单的税额总和。您可以使用 -> 或 ->> 运算符提取具有此键的值（请参阅 JSON 列），但这些运算符将分别返回类型为 JSON 和 text 的值，而这两种类型都不能与 sum() 一起使用。

因此，您需要先将输入值转换为与 sum() 兼容的类型（例如 numeric）。转换输入值的方式与转换任何其他值的方式完全相同。

curl "http://localhost:3000/orders?select=order_details->tax_amount::numeric.sum()"

这样，您将获得转换后的 tax_amount 值的总和。

[
  {
    "sum": 1234.56
  }
]

转换输出值

现在让我们回到一个涉及 orders 表的 amount 列的示例。假设我们想要获得 amount 列的四舍五入后的平均值。一种方法是使用 avg() 聚合函数，然后将函数的输出值转换为 int。要转换函数输出值，我们只需将转换放在聚合函数之后。

curl "http://localhost:3000/orders?select=amount.avg()::int"

然后您将收到四舍五入后的平均值作为结果。

[
  {
    "avg": 201
  }
]

当然，如果您愿意，您可以同时使用输入和输出转换。

将聚合函数与资源嵌入一起使用

聚合函数可以与 资源嵌入 结合使用。您可以使用嵌入式资源作为分组列，在嵌入式资源的上下文中使用聚合函数，或者使用展开资源中的列作为分组列或作为聚合函数的输入。

使用嵌入式资源作为分组列

使用嵌入式资源作为分组列允许您使用关联中的数据对聚合结果进行分组。

例如，假设上面的示例中的 orders 表与 customers 表相关联。如果您想获得按 customers 表的 name 列分组的 amount 列的总和，您可以使用标准的 资源嵌入 语法包含客户姓名，并在 amount 列上执行求和。

curl "http://localhost:3000/orders?select=amount.sum(),customers(name)"

然后您将获得总金额以及嵌入的客户资源。

[
  {
    "sum": 100,
    "customers": {
      "name": "Customer A"
    }
  },
  {
    "sum": 200,
    "customers": {
      "name": "Customer B"
    }
  }
]

注意

前面的示例使用一对一关联来演示此功能，但您也可以使用一对多关联作为分组列，尽管这种情况下很少有明显的用例。

在嵌入式资源的上下文中使用聚合函数

在嵌入资源时，您可以对关联资源中的列应用聚合函数，以在嵌入资源的上下文中执行聚合。

继续以上一节中orders和customers之间的示例关系为例，假设您想要获取每个客户的name、city和state，以及客户订单金额的总和，按订单日期分组。这可以通过以下方式完成

curl "http://localhost:3000/customers?select=name,city,state,orders(amount.sum(),order_date)"

[
  {
    "name": "Customer A",
    "city": "New York",
    "state": "NY",
    "orders": [
      {
        "sum": 215.22,
        "order_date": "2023-09-01"
      },
      {
        "sum": 905.73,
        "order_date": "2023-09-02"
      }
    ]
  },
  {
    "name": "Customer B",
    "city": "Los Angeles",
    "state": "CA",
    "orders": [
      {
        "sum": 329.71,
        "order_date": "2023-09-01"
      },
      {
        "sum": 425.87,
        "order_date": "2023-09-03"
      }
    ]
  }
]

在这个例子中，amount列被求和并按order_date在嵌入资源的上下文中分组。也就是说，来自customers表的name、city和state对在orders关联的上下文中执行的聚合没有影响；相反，每个聚合可以被视为独立地对仅属于特定客户的订单执行，仅使用来自嵌入资源的数据进行分组和聚合。

使用来自展开资源的列

当您展开嵌入资源时，来自展开资源的列将被视为顶级资源的列，无论是在使用它们作为分组列还是在对它们应用聚合函数时。

使用来自展开资源的列进行分组

例如，假设您想要对orders表中的amount列求和，使用来自customers表的city和state列作为分组列。为了实现这一点，您可以从customers表中选择这两列并展开它们；然后它们将被用作分组列

curl "http://localhost:3000/orders?select=amount.sum(),...customers(city,state)

结果将与city和state是来自orders表的列时相同

[
  {
    "sum": 2000.29,
    "city": "New York",
    "state": "NY"
  },
  {
    "sum": 9241.21,
    "city": "Los Angeles",
    "state": "CA"
  }
]

使用来自展开资源的列的聚合函数

现在假设customers表有一个joined_date列，它表示客户加入的日期。您想要获取在每个不同的订单日期下订单的客户的最新和最旧的joined_date。这可以通过以下方式表达

curl "http://localhost:3000/orders?select=order_date,...customers(joined_date.max(),joined_date.min())

由于从扩展资源中提取的列被视为来自顶级资源的列，因此 max() 和 min() 应用于顶级资源的上下文，而不是像上一节中那样应用于嵌入式资源的上下文。

结果将与对顶级资源的列应用聚合相同。

[
  {
    "order_date": "2023-11-01",
    "max": "2023-10-15",
    "min": "2013-10-01"
  },
  {
    "order_date": "2023-11-02",
    "max": "2023-10-30",
    "min": "2016-02-11"
  }
]