sql - 使用 FIRST_VALUE 而不包括 group by 中的内部列

Question

我正在使用如下所示的表格:

userID, eventDate, eventName
1  2019-01-01  buySoup
2  2019-01-01  buyEggs
2  2019-01-03  buyMilk
2  2019-01-04  buyMilk
3  2019-01-02  buyBread
3  2019-01-03  buyBread

我当前的查询是:

SELECT
    userID,
    FIRST_VALUE(eventName) OVER (
        PARTITION BY userID ORDER BY eventDate ASC
    ) AS firstBought 
FROM table 
GROUP BY userID

我觉得应该返回:

userID, firstBought
1  buySoup
2  buyEggs
3  buyBread

相反，它给出了错误:

'ERROR: Column "table.eventName" must appear in the GROUP BY clause or be used in an aggregate function'

有没有办法在不将其包含在按功能分组或创建子查询的情况下获取该值？我正在使用 PostgreSQL。

如果我确实将它包含在 group by 子句中，它会返回

userID, firstBought
1  buySoup
2  buyEggs
2  buyEggs
2  buyEggs
3  buyBread
3  buyBread

我知道我可以将其设为子查询，然后按用户 ID、firstBought 进行分组，但我不想创建另一个子查询。

Answer 1

代替group by，使用select distinct:

select distinct userID,
       FIRST_VALUE(eventName) over (partition by userID order by eventDate ASC) as firstBought 
from table ;

或者，您可以使用数组:

select userId,
       (array_agg(eventName order by eventDate))[1] as firstBought
from table
group by userId;

Postgres 没有“第一个”聚合函数，但这工作得很好。