聚合不在同一组中的所有值

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 提供.
或者在这里:

Is there a way in PostgreSQL to take this table:

and generate this table from it with the column agg_values:

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:

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