无法执行Postgres组的非ID列以获取包含最大值的ID

Question

我试图在连接表上执行GROUP BY。连接表基本上是这样的：

CREATE TABLE user_foos (
    id SERIAL PRIMARY KEY, 
    user_id INT NOT NULL, 
    foo_id INT NOT NULL, 
    effective_at DATETIME NOT NULL 
); 
ALTER TABLE user_foos 
    ADD CONSTRAINT user_foos_uniqueness 
    UNIQUE (user_id, foo_id, effective_at); 

我想查询该表中查找所有记录中，其中effective_at是给定的任何一对user_id, foo_id最大值。我已经试过如下：

SELECT "user_foos"."id", 
     "user_foos"."user_id", 
     "user_foos"."foo_id", 
     max("user_foos"."effective_at") 
FROM "user_foos" 
GROUP BY "user_foos"."user_id", "user_foos"."foo_id"; 

不幸的是，这会导致错误：

column "user_foos.id" must appear in the GROUP BY clause or be used in an aggregate function

据我所知，这个问题涉及到“ID”没有在聚合函数和数据库使用如果它找到多个具有不同ID的记录，就不知道该怎么办，但我知道由于这些列的三元主键（user_id,foo_id和effective_at），这绝不会发生。

要解决这个问题，我也尝试了一些其他变体，如在id使用first_value window function的：

SELECT first_value("user_foos"."id"), 
     "user_foos"."user_id", 
     "user_foos"."foo_id", 
     max("user_foos"."effective_at") 
FROM "user_foos" 
GROUP BY "user_foos"."user_id", "user_foos"."foo_id"; 

和：

SELECT first_value("user_foos"."id") 
FROM "user_foos" 
GROUP BY "user_foos"."user_id", "user_foos"."foo_id" 
HAVING "user_foos"."effective_at" = max("user_foos"."effective_at") 

不幸的是，这些都导致不同错误：

window function call requires an OVER clause

理想情况下，我的目标是获取所有匹配id's，以便我可以在子查询中使用它来从此表中提取合法的全行数据以匹配记录。任何人都可以提供有关如何让这项工作的见解？

Answer 1

尝试：

SELECT * 
FROM (
    SELECT t.*, 
     row_number() OVER(partition by user_id, foo_id ORDER BY effective_at DESC) x 
    FROM user_foos t 
) 
WHERE x = 1 

Answer 2

如果你不希望使用基于所有三个键，那么你需要创建一个订单ID的子集“密集等级”窗口功能领域的复合材料的子查询，user_id和foo_id生效日期与等级顺序字段。然后子查询并记录rank_order = 1的记录。由于排序是按生效日期排列的，因此您将为每个foo和用户获取记录的最高生效日期的所有字段。

DATSET 
1 1 1 01/01/2001 
2 1 1 01/01/2002 
3 1 1 01/01/2003 
4 1 2 01/01/2001 
5 2 1 01/01/2001 

DATSET WITH RANK ORDER PARTITIONED BY FOO_ID, USER_ID ORDERED BY DATE DESC 
1 3 1 1 01/01/2001 
2 2 1 1 01/01/2002 
3 1 1 1 01/01/2003 
4 1 1 2 01/01/2001 
5 1 2 1 01/01/2001 

SELECT * FROM QUERY ABOVE WHERE RANK_ORDER=1 
3 1 1 1 01/01/2003 
4 1 1 2 01/01/2001 
5 1 2 1 01/01/2001 

Answer 3

Postgres有一个非常不错的功能叫distinct on，可在这种情况下使用：

SELECT DISTINCT ON (uf."user_id", uf."foo_id") uf.* 
FROM "user_foos" uf 
ORDER BY uf."user_id", uf."foo_id", uf."effective_at" DESC; 

它返回的第一行一组，根据括号内的值。子句需要包含这些值以及第三列以确定哪些是组中的第一行。