在不将内部列包含在group by中的情况下使用FIRST_VALUE

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

相反，出现了以下错误：

'错误：列"table.eventName" 必须出现在 GROUP BY 子句中或在聚合函数中使用'

是否有一种方法可以在不将其包含在 group by 函数中或创建子查询的情况下获取该值？ 我正在使用 PostgreSQL。

如果在 group by 子句中包含它，则会返回

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

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

- Cyborgcanoe

Answer 1

使用select distinct代替group by：

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没有“first”聚合函数，但这个方法效果非常好。

- Gordon Linoff

---

虽然这很有用，并且完全按照预期工作，但我的表格实际上还包括更多的列，而我的查询在这些列上包括max（）和其他聚合函数。这意味着我确实需要在末尾加上group by。是否有其他解决方案？ - Cyborgcanoe

@Cyborgcanoe . . . 这个答案提供了两种解决方案。第二种方法使用了 group by。 - Gordon Linoff

谢谢，第二个答案很好。不幸的是，我实际上正在使用Vertica Analytic Database v9.1.1-5，这是Postgres的一个分支，不包括array_agg函数。无论如何还是感谢您！ - Cyborgcanoe

@Cyborgcanoe...你应该正确地标记你的问题。 - Gordon Linoff

Answer 2

我认为PostgreSQL的DISTINCT ON可以解决这个问题：

SELECT DISTINCT ON (userid)
       userid, eventdate, eventname
FROM "table"
ORDER BY (eventdate);

这将为您提供每个userid最早eventdate的行。 最初的回答：

- Laurenz Albe

Answer 3

FIRST_VALUE 不是聚合函数，而是分析窗口函数。因此，您的基本查询不需要 GROUP BY 子句。应该重写为：

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

从您之前的评论中可以听出，您正在使用其他函数，包括MAX等聚合函数。为了实现您想要做的事情，您需要将上面的查询用作子查询。这将允许您使用聚合函数并从基本查询中获取唯一值。查询可能看起来像这样（我添加了一个价格列作为示例）。

SELECT userID, firstBought, MAX(price)
FROM (
        SELECT userID, price, FIRST_VALUE(eventName) over (partition by userID order by eventDate ASC) as firstBought 
        from test
) x
GROUP BY userId, firstBought;

这应该能解决问题！您可以在外部查询中使用其他聚合函数，并在子查询中使用附加的窗口函数。

- A. Saunders

Answer 4

我同意A. Saunders的观点。

你需要一个外部查询。

除了实际上将所有SELECT列表的列归为GROUP BY的SELECT DISTINCT之外，你不能在同一个SELECT中混合使用OLAP和GROUP BY函数。

因此，如果你确实有MAX()，你必须：

WITH -- your input data ...
input(userID,eventDate,eventName) AS (
          SELECT 1,DATE '2019-01-01','buySoup'
UNION ALL SELECT 2,DATE '2019-01-01','buyEggs'
UNION ALL SELECT 2,DATE '2019-01-03','buyMilk'
UNION ALL SELECT 2,DATE '2019-01-04','buyMilk'
UNION ALL SELECT 3,DATE '2019-01-02','buyBread'
UNION ALL SELECT 3,DATE '2019-01-03','buyBread'
)
,
getfirstbought AS (
  SELECT 
    userid
  , eventdate
  , FIRST_VALUE(eventname) OVER (
      PARTITION BY userid ORDER BY eventdate
   ) AS firstbought
  FROM input
)
SELECT
  userid
, firstbought
, MAX(eventdate) AS maxdt
FROM getfirstbought
GROUP BY 1,2;
-- out  userid | firstbought |   maxdt    
-- out --------+-------------+------------
-- out       2 | buyEggs     | 2019-01-04
-- out       3 | buyBread    | 2019-01-03
-- out       1 | buySoup     | 2019-01-01
-- out (3 rows)
-- out 
-- out Time: First fetch (3 rows): 22.157 ms. All rows formatted: 22.208 ms

- marcothesane