聚合不在同一组中的所有值 [英] Aggregating all values not in the same group

问题描述

PostgreSQL 中有没有办法取这个表:

<头>

ID	国家	名称	值
1	美国	约翰·史密斯	{1,2,3}
2	美国	简·史密斯	{0,1,3}
3	美国	简·多伊	{1,1,1}
4	美国	John Doe	{0,2,4}

并从中生成带有 agg_values 列的表:

<头>

ID	国家	名称	值	agg_values
1	美国	约翰·史密斯	{1,2,3}	{0,1,3,1,1,1,0,2,4}
2	美国	简·史密斯	{0,1,3}	{1,2,3,1,1,1,0,2,4}
3	美国	简·多伊	{1,1,1}	{1,2,3,0,1,3,0,2,4}
4	美国	John Doe	{0,2,4}	{1,2,3,0,1,3,1,1,1}

其中每一行聚合所有 values，除了当前行及其对等项.
因此，如果 name = John Smith 那么 agg_values = name not = John Smith 的所有值的聚合.这可能吗?

解决方案

在 Postgres 11 或更高版本中，使用 窗口函数，带有自定义框架和frame_exclusion:

SELECT *, array_combine(values) OVER (ROWS BETWEEN UNBOUNDED PRECEDING和无限制的跟随排除当前行)作为 agg_values从 tbl;

如果 name 不是 UNIQUE，并且既然你问了:

<块引用>

name not = John Smith 的所有值

SELECT *, array_combine(values) OVER (ORDER BY name无界前行之间的行和无限制的跟随EXCLUDE GROUP) AS agg_values从 tbl;

db<>fiddle 这里

第一个(也)适用于任意行顺序，仅排除当前行.第二个需要 ORDER BY 来确定哪些行在同一组中.

手册:<块引用>

frame_exclusion 选项允许当前行周围的行从框架中排除，即使它们会根据帧开始和帧结束选项.排除当前行从框架中排除当前行.EXCLUDE GROUP 排除当前行及其来自框架的排序同行. EXCLUDE TIES从框架中排除当前行的任何对等点，但不排除当前行本身.[...]

粗体强调我的.

这使用自定义聚合函数 array_combine(anyarray) 由 a_horse 提供.
或者在这里:

• 将数据选入 Postgres 数组
• PostgreSQL 中是否有类似 zip() 函数的东西可以组合两个数组?

Is there a way in PostgreSQL to take this table:

ID	country	name	values
1	USA	John Smith	{1,2,3}
2	USA	Jane Smith	{0,1,3}
3	USA	Jane Doe	{1,1,1}
4	USA	John Doe	{0,2,4}

and generate this table from it with the column agg_values:

ID	country	name	values	agg_values
1	USA	John Smith	{1,2,3}	{0,1,3,1,1,1,0,2,4}
2	USA	Jane Smith	{0,1,3}	{1,2,3,1,1,1,0,2,4}
3	USA	Jane Doe	{1,1,1}	{1,2,3,0,1,3,0,2,4}
4	USA	John Doe	{0,2,4}	{1,2,3,0,1,3,1,1,1}

Where each row aggregates all values except from the current row and its peers.
So if name = John Smith then agg_values = aggregate of all values where name not = John Smith. Is that possible?

解决方案

In Postgres 11 or later, use a window function with a custom frame and a frame_exclusion:

SELECT *, array_combine(values) OVER (ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING
                                           EXCLUDE CURRENT ROW) AS agg_values
FROM   tbl;

If name is not UNIQUE, and since you asked:

all values where name not = John Smith

SELECT *, array_combine(values) OVER (ORDER BY name
                                      ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING
                                           EXCLUDE GROUP) AS agg_values
FROM   tbl;

db<>fiddle here

The first one (also) works with arbitrary order of rows, only excluding the current one. The second requires ORDER BY to establish which rows are in the same group.

The manual:

The frame_exclusion option allows rows around the current row to be excluded from the frame, even if they would be included according to the frame start and frame end options. EXCLUDE CURRENT ROW excludes the current row from the frame. EXCLUDE GROUP excludes the current row and its ordering peers from the frame. EXCLUDE TIES excludes any peers of the current row from the frame, but not the current row itself. [...]

Bold emphasis mine.

This uses the custom aggregate function array_combine(anyarray) provided by a_horse.
Or here:

• Selecting data into a Postgres array
• Is there something like a zip() function in PostgreSQL that combines two arrays?

这篇关于聚合不在同一组中的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！