如何在窗口上模拟array_agg（distinct x）？

I have a query that uses array_agg as a window function, i.e. with an over (...) clause. I'd like to get only the set of distinct values in this array aggregation, but this is not implemented as of Postgres 9.4:

我有一个查询使用array_agg作为窗口函数,即使用over(...)子句。我想在这个数组聚合中只获得一组不同的值,但是从Postgres 9.4开始实现这一点:

For the following (simplified) query,

对于以下(简化)查询,

with test_data(day, daytime, song) as (
      select 'MON', 'morning', 'A'
      union all 
      select 'MON', 'morning', 'B'
      union all
      select 'MON', 'afternoon', 'A'
      union all
      select 'TUE', 'afternoon', 'B'
)
select distinct 
    day, 
    first_value(daytime) over w as started,
    array_agg(distinct daytime) over w as daytimes,
    array_agg(distinct song) over w as songs 
from test_data
window w as (partition by day order by daytime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Postgres returns the following:

Postgres返回以下内容:

SQLERROR [0A00]: ERROR: distinct is not implemented for window functions

I am certain that I can't avoid the use of the window clause itself. How do I work around this limitation?

我确信我无法避免使用window子句本身。我该如何解决这个限制?

1 个解决方案

#1

I decided to use the function anyarray_uniq as provided in this GitHub Repo. I wrapped the initial select in another select, applying anyarray_uniq and reusing all other columns as-is. Since this does exactly what I was looking for and luckily I do not need to consider performance in this case.

我决定使用这个GitHub Repo中提供的函数anyarray_uniq。我将初始选择包装在另一个选择中,应用anyarray_uniq并按原样重复使用所有其他列。因为这完全符合我的要求,幸运的是,在这种情况下我不需要考虑性能。