代码之家 › 专栏 › 技术社区 › Don A.R.B.N

在表连接中使用聚合函数的最有效方法?

aggregate-functions left-join postgresql

0

Don A.R.B.N · 技术社区 · 8 年前

我试图连接两个表,其中表1中的所有记录都有唯一的ID,表2中的所有记录都有唯一的ID 包含表1中一个id的多条记录(表1中的id可能在表2中没有任何相应的记录)。

SELECT id, MAX(date) AS DATE FROM table_two

SELECT * FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id

上面返回连接的表,但也返回重复的表。

如何构造join语句以仅返回表2中最近的记录?

SELECT * FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id, MAX(t2.date) AS date GROUP BY date

上面返回一个错误: ERROR: aggregate functions are not allowed in functions in FROM

我知道我可以使用子查询来获取最近的记录,但不确定最有效的方法。

2 回复 | 直到 8 年前

1

2

sagar gholap 8 年前

您必须使用子查询。以下是两个可能的答案:

select * from(
    SELECT t1.*,row_number() over(partition by t2.id order by t2.date desc) as row_num
    FROM table_one t1
    LEFT JOIN table_two t2 ON t1.id = t2.id
)t
where t.row_num = 1

使用max和子查询

SELECT * FROM table_one t1 
LEFT JOIN 
(
    SELECT id, MAX(date) AS DATE FROM table_two group by id
)t2
ON t1.id = t2.id

2

1

xQbert 8 年前

Postgresql 9.2?我认为up使用 lateral 关键字。这基本上是针对表1运行select,然后针对表1中的每个记录运行select from table 2;从表2中返回各个ID的最新日期记录;但是,不必在循环中这样做,引擎可以以某种方式将连接作为“集”处理,因此不会受到循环/逐行性能影响。

SELECT * 
FROM table_one t1 
LEFT JOIN LATERAL (SELECT A.*
                   FROM table_two A
                   WHERE A.ID = T1.ID
                   ORDER BY A.Date desc
                   LIMIT 1) t2 
  ON TRUE

Grouped LIMIT in PostgreSQL: show the first N rows for each group?

--应适用于许多以前的版本,因为它不使用分析函数或横向。

SELECT T1.*, t2.*
FROM table_one t1 
LEFT JOIN (SELECT A.* 
           FROM table_two A
           INNER JOIN (SELECT MAX(date) AS date, ID 
                       FROM table_two
                       GROUP BY ID) B
              on B.ID = A.ID
             and B.Date = A.Date) t2
 ON t1.id = t2.id

上面的基本概念…获取表2中记录的最大日期和ID 将其连接回表2的基本集,以获取表2中具有每个ID的最大日期的记录的所有详细信息。然后连接到表1以获取t1和t2值。