MemFire Cloud新特性介绍-PostgREST聚合函数

MemFire Cloud应用开发新版本中已支持PostgREST v12 版本，随之而来的是一个备受期待的功能：聚合函数。

先介绍一下背景知识，聚合函数是一种数据库特性，它允许你通过对一组行数据执行计算来汇总你的数据。以前，只能通过PostgREST间接使用聚合函数，例如，在视图(View)中使用它们。但是随着最新版本的发布，你现在可以通过PostgREST API直接、动态地使用聚合函数，对数据进行处理。

在这篇文章中，我们将通过一些例子来介绍你可以用这个新功能做的一些有趣的事情。我们还将讨论安全措施的重要性，以防止在使用聚合函数时可能出现的性能问题。

为了获取最完整的信息，请参考文档中有关聚合函数的部分。

聚合函数的基础知识

PostgREST 支持 PostgreSQL 中最常见的聚合函数：avg()、count()、max()、min()和sum()。这些函数的名字可以清晰地传达其功能和作用，可以查看 PostgreSQL 文档深入了解以获取更多信息。

让我们看一个例子。假设我们有一个名为movies的数据表，它包含以下列：name（电影名称）、release_year（发行年份）、genre（类型）和box_office_earnings（票房收入）；一张名为directors数据表，包含name（导演名称）、country（国家）。

CREATE TABLE directors(  
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  
  name TEXT NOT NULL,  --姓名
  country TEXT         --国家
);

create table movies(
  id int primary key generated always as identity,
  director_id int references directors(id),  --导演ID
  name text,            --电影名称
  release_year int,     --发布年份
  genre text,           --类型
  box_office_earnings DECIMAL(10, 2)   --票房收入
);

INSERT INTO directors (name, country)  
VALUES  
  ('John','USA'),  
  ('Jane', 'UK'),  
  ('Michel', 'France'),  
  ('Quentin', 'USA');
  
INSERT INTO movies (director_id, name, release_year, genre, box_office_earnings)  
VALUES  
  (1, 'Movie One', 2020, 'Action', 13000000.00),  
  (2, 'Movie Two', 2021, 'Comedy', 15000000.00),  
  (1, 'Movie Three', 2019, 'Drama', 9500000.00),  
  (2, 'Movie Four', 2008, 'Comedy', 19300000.00),  
  (1, 'Movie Five', 2012, 'Action', 7000000.00), 
  (2, 'Movie Six', 2007, 'Comedy', 15600000.00),  
  (1, 'Movie Seven', 2001, 'Drama', 8900000.00), 
  (3, 'Movie Eight', 1995, 'Sci-Fi', 12500000.00), 
  (1, 'Movie Nine', 1999, 'Drama', 8000500.00), 
  (3, 'Movie Ten', 2017, 'Sci-Fi', 17100000.00);

完成上述操作后，可以在表编辑器页面查看结果：

备注说明：默认情况下是禁用聚合函数的，需要首先进行开启操作。

假设我们想要获取我们数据集中所有电影的发行年份的最大值和最小值。这很容易实现：

curl 'http://postgrest/movies?select=release_year.max(),release_year.min()'

const { data, error } = await supabase
  .from('movies')
  .select('release_year.max(), release_year.min()')

[
  {
    "max": 2022,
    "min": 1995
  }
]

想要使用聚合函数，我们只需在select参数中的列名后面加上该函数即可，操作简单便捷。

现在，如果我们想要更进一步，获取数据集中每种类型的电影的发行年份的的最大值和最小值？如果你熟悉 SQL 中的聚合函数，你可能会立即想到使用 GROUP BY。在 PostgREST 中，无需显式指定 GROUP BY；相反，你可以直接将分组列添加到select参数中。在select列表中没有聚合函数的任何列都将用作分组列：

$ curl 'http://postgrest/movies?select=genre,release_year.max(),release_year.min()'

const { data, error } = await supabase
  .from('movies')
  .select('genre, release_year.max(), release_year.min()')

[
    {
        "genre": "Comedy",
        "max": 2021,
        "min": 2007
    },
    {
        "genre": "Drama",
        "max": 2019,
        "min": 1999
    },
    {
        "genre": "Sci-Fi",
        "max": 2022,
        "min": 1995
    },
    {
        "genre": "Action",
        "max": 2020,
        "min": 2012
    }
]

一般来说，聚合函数可以与你已经熟悉的其他 PostgREST 功能一起使用。例如，你可以使用垂直过滤将聚合应用于数据集的缩小版本，比如只应用于 2000 年后发行的电影，或者你可以使用列重命名来更改结果中聚合列的名称，比如将前面示例中的max和min列的名称更改为max_release_year和min_release_year。

聚合函数和嵌套资源

聚合函数还可以很好地与嵌入式资源配合使用，为潜在的用例打开了一扇大门。

在前面示例的基础上，我们有一个名为 directors 的表格，它与我们之前的 movies 表格有一对多的关系。在本节中，我们将使用 directors 表格中的几个列：name（姓名）和 country（国家）。

假设我们想要为每位导演获取他们最老和最新电影的发行年份。我们可以很轻松做到这一点：

$ curl 'http://postgrest/directors?select=name,movies(newest_movie_year:release_year.max(),oldest_movie_year:release_year.min())'

const { data, error } = await supabase.from('directors').select(`name,
                movies(
                        newest_movie_year:release_year.max(),
                        oldest_movie_year:release_year.min()
                )`)

[
    {
        "name": "John",
        "movies": [
            {
                "newest_movie_year": 2020,
                "oldest_movie_year": 1999
            }
        ]
    },
    {
        "name": "Jane",
        "movies": [
            {
                "newest_movie_year": 2021,
                "oldest_movie_year": 2007
            }
        ]
    },
    {
        "name": "Michel",
        "movies": [
            {
                "newest_movie_year": 2017,
                "oldest_movie_year": 1995
            }
        ]
    },
    {
        "name": "Quentin",
        "movies": [
            {
                "newest_movie_year": null,
                "oldest_movie_year": null
            }
        ]
    }
]

如上所示，你可以在嵌入资源的上下文中使用聚合函数：对于属于特定导演的每组电影，我们会使用聚合函数进行计算，在本例中对 release_year使用了 min() 和 max() 函数。

你还可以看到，我们使用了列重命名——以使结果更易让人理解。

请注意，我们在这里没有使用分组列，但我们可以使用它们来进一步细化：例如，我们可以通过将 genre 作为分组列，来获取每位导演按类型分类的 release_year 列的最早和最晚值。

让我们看另一个例子，但这次我们反其道而行之：我们将使用movies作为我们的顶级资源，并通过一对一的关系嵌入directors。

现在，我们想要按导演的country（国家）分组，获取我们电影的平均票房收入。为了做到这一点，我们可以使用以下 API 调用：

$ curl 'http://postgrest/movies?select=avg_earnings:box_office_earnings.avg(),...directors(country)'

const { data, error } = await supabase.from('movies').select(`
                avg_earnings:box_office_earnings.avg(),
    ...directors(country)
        `)

[
    {
        "avg_earnings": 14800000.000000000000,
        "country": "France"
    },
    {
        "avg_earnings": 16633333.333333333333,
        "country": "UK"
    },
    {
        "avg_earnings": 9280100.000000000000,
        "country": "USA"
    }
]

在本例中，我们使用了扩展嵌入资源列的能力，使用导演的country分组列，即使聚合函数avg()应用于 movies的列，而不是directors的列。

因为扩展列将它们提升到顶级，因此在聚合和分组时，它们会被视为顶层的列。这意味着对扩展资源的列使用的任何聚合函数，也都是在顶级的上下文中使用的。

安全使用聚合函数

通过上述示例我们知晓了如何使用聚合函数，接下来如何在你的应用程序中安全地使用聚合函数是非常重要的。由于聚合函数可能存在性能风险，默认情况下是禁用聚合函数的。只有在审查了风险并确保适当的安全措施到位后，你才应该启用此功能。在 MemFire Cloud 上，你可以通过修改 PostgREST 连接角色，然后重新加载服务器配置来启用它，如下所示：

ALTER ROLE memfire SET pgrst.db_aggregates_enabled = 'true';
NOTIFY pgrst, 'reload config';

现在你可能会想，“这有什么大不了的？”与 PostgREST 的其他部分相比，聚合函数似乎不太可能造成性能问题，但有一个关键的区别：聚合函数可以在不限数量的数据行上操作，而 PostgREST 的其他部分——得益于分页——可以被限制在只操作一定数量的行。

例如，假设我们之前提到的movies表有两千万行数据。如果我们想获得所有电影发行年份的最大值，并且release_year列上没有索引，那么将需要非常长的时间。

更糟糕的是，想象一下有人怀着恶意想要对你的服务器做坏事：攻击者相对容易地可以用昂贵的聚合查询来轰炸你的服务器，这会阻止你的服务器处理合法流量，这是一种拒绝服务攻击的形式。

防止潜在性能问题的一种策略是使用pg_plan_filter_module. 使用此扩展，你可以设置PostgREST 将运行的查询成本的上限。

ALTER USER authenticator SET plan_filter.statement_cost_limit = 1e7;

在 PostgreSQL 执行查询之前，它首先会制定一个执行计划，作为该计划的一部分，它会计算出一个成本。正如你可能想象的那样，更高的成本与更慢的查询相关联，因此可以设置一个上限值，來防止可能出現的性能问题，降低服务攻击的风险。pg_plan_filter_module 使你能够通过 PostgreSQL 配置轻松设置这个上限。

你甚至可以根据角色的不同更改这个限制，允许更具有权限的角色在他们运行的查询中拥有更多的自由，而权限较低的角色——例如，你的公共 API 的外部用户——可能会有更严格的限制。

-- anonymous users can only run cheap queries
ALTER
  USER anon
SET
  plan_filter.statement_cost_limit = 10000;

-- authenticated users can run more expensive queries
ALTER
  USER authenticated
SET
  plan_filter.statement_cost_limit = 1e6;

你可以在文档中查看使用 PostgREST 的按角色配置的示例。

总结

PostgREST v12 现在有了聚合函数，这让你在处理数据更加灵活。更棒的是，它与你已经熟悉的其他 PostgREST 功能深度集成，与现有功能完美契合。

虽然我们很高兴能为 PostgREST 带来聚合功能，但管理员和用户必须了解随之而来的风险，这也是为什么这个功能仅作为可选功能提供。在启用聚合函数之前，请确保有一个策略到位——比如使用 pg_plan_filter_module——以确保最大程度的保护。

原文地址：https://supabase.com/blog/postgrest-aggregate-functions

作者：Tim Abdulla