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（国家）。
CREATETABLEdirectors(
idINTPRIMARYKEYGENERATEDALWAYSASIDENTITY,
nameTEXTNOTNULL,--姓名
countryTEXT--国家
);
createtablemovies(
idintprimarykeygeneratedalwaysasidentity,
director_idintreferencesdirectors(id),--导演ID
nametext,--电影称号
release_yearint,--发布年份
genretext,--类型
box_office_earningsDECIMAL(10,2)--票房收入
);
INSERTINTOdirectors(name,country)
VALUES
('John','USA'),
('Jane','UK'),
('Michel','France'),
('Quentin','USA');
INSERTINTOmovies(director_id,name,release_year,genre,box_office_earnings)
VALUES
(1,'MovieOne',2020,'Action',13000000.00),
(2,'MovieTwo',2021,'Comedy',15000000.00),
(1,'MovieThree',2019,'Drama',9500000.00),
(2,'MovieFour',2008,'Comedy',19300000.00),
(1,'MovieFive',2012,'Action',7000000.00),
(2,'MovieSix',2007,'Comedy',15600000.00),
(1,'MovieSeven',2001,'Drama',8900000.00),
(3,'MovieEight',1995,'Sci-Fi',12500000.00),
(1,'MovieNine',1999,'Drama',8000500.00),
(3,'MovieTen',2017,'Sci-Fi',17100000.00);
完成上述操作后，可以在表编辑器页面检查成果：
备注说明：默许情况下是禁用聚合函数的，需求首要进行开启操作。
假定咱们想要获取咱们数据会集所有电影的发行年份的最大值和最小值。这很容易完成：
curl'http://postgrest/movies?select=release_year.max(),release_year.min()'
const{data,error}=awaitsupabase
.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}=awaitsupabase
.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}=awaitsupabase.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作为咱们的尖端资源，并经过1对1的关系嵌入directors。
现在，咱们想要按导演的country（国家）分组，获取咱们电影的平均票房收入。为了做到这一点，咱们可以运用以下 API 调用：
$curl'http://postgrest/movies?select=avg_earnings:box_office_earnings.avg(),...directors(country)'
const{data,error}=awaitsupabase.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 连接人物，然后重新加载服务器装备来启用它，如下所示：
ALTERROLEmemfireSETpgrst.db_aggregates_enabled='true';
NOTIFYpgrst,'reloadconfig';
现在你或许会想，“这有什么大不了的？”与 PostgREST 的其他部分相比，聚合函数似乎不太或许形成功用问题，但有一个要害的区别：聚合函数可以在不限数量的数据行上操作，而 PostgREST 的其他部分——得益于分页——可以被限制在只操作一定数量的行。
例如，假定咱们之前提到的movies表有两千万行数据。假如咱们想获得所有电影发行年份的最大值，而且release_year列上没有索引，那么将需求非常长的时刻。
更糟糕的是，幻想一下有人怀着恶意想要对你的服务器做坏事：进犯者相对容易地可以用贵重的聚合查询来轰炸你的服务器，这会阻止你的服务器处理合法流量，这是一种拒绝服务进犯的形式。
避免潜在功用问题的一种战略是运用pg_plan_filter_module. 运用此扩展，你可以设置PostgREST 将运行的查询成本的上限。
ALTERUSERauthenticatorSETplan_filter.statement_cost_limit=1e7;
在 PostgreSQL 履行查询之前，它首要会制定一个履行方案，作为该方案的一部分，它会计算出一个成本。正如你或许幻想的那样，更高的成本与更慢的查询相关联，因此可以设置一个上限值，來避免或许出現的功用问题，下降服务进犯的危险。pg_plan_filter_module使你可以经过 PostgreSQL 装备轻松设置这个上限。
你甚至可以根据人物的不同更改这个限制，答应更具有权限的人物在他们运行的查询中拥有更多的自在，而权限较低的人物——例如，你的公共 API 的外部用户——或许会有更严厉的限制。
--anonymoususerscanonlyruncheapqueries
ALTER
USERanon
SET
plan_filter.statement_cost_limit=10000;
--authenticateduserscanrunmoreexpensivequeries
ALTER
USERauthenticated
SET
plan_filter.statement_cost_limit=1e6;
你可以在文档中检查运用 PostgREST 的按人物装备的示例。
总结
PostgREST v12 现在有了聚合函数，这让你在处理数据愈加灵敏。更棒的是，它与你现已了解的其他 PostgREST 功用深度集成，与现有功用完美符合。
虽然咱们很高兴能为 PostgREST 带来聚合功用，但管理员和用户有必要了解随之而来的危险，这也是为什么这个功用仅作为可选功用供给。在启用聚合函数之前，请保证有一个战略到位——比方运用pg_plan_filter_module——以保证最大程度的保护。
原文地址：supabase.com/blog/postgr…
作者：Tim Abdulla
MemFireDB
产品资料
MemFireCloud
平台进口：memfiredb.com
