postgresql - postgres : join, 分组依据，排序依据。我的查询错了吗？

Question

我的 table

CREATE TABLE IF NOT EXISTS members(
    group_id BIGINT, 
    amount INT, 
    updated_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups_ref(
    group_id BIGINT PRIMARY KEY, 
    title TEXT DEFAULT NULL, 
    message_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups(
    group_id BIGINT PRIMARY KEY, 
    lang TEXT DEFAULT NULL, 
    last_date timestamp DEFAULT NULL)

成员中的数据示例

(3, 90, date)
(1, 15, date)
(2, 200, date)
(1, 28, date)
(2, 300, date)
(1, 80, date)

supergroups_ref 中的数据示例:

(1, 'title', date)
(2, 'title2', date)
(3, 'title3', date)

超组中的数据示例:

(1, 'en', date)
(2, 'it', date)
(3, 'it', date)

我想做一个查询从表 members 中只取最近的行，按 'updated_date' DESC 对它们进行排序，然后将它们左连接到 super 组中，以便 super 组中的每个 group_id 都具有正确的最新数量的成员。现在我想在 supergroups_ref 中添加另一个左连接的标题信息

我想按成员表的数量排序。

我尝试这样做，但我不确定我是否做对了:

SELECT sub.group_id, sub.amount, sub.title
FROM (
    SELECT s.group_id, m.amount, s_ref.title
    FROM  supergroups AS s
    LEFT OUTER JOIN members AS m
    ON m.group_id = s.group_id
    LEFT OUTER JOIN supergroups_ref AS s_ref
    ON s_ref.group_id = s.group_id
    ORDER BY m.updated_date DESC
) AS sub
GROUP BY sub.group_id, sub.amount, sub.title
ORDER BY sub.amount DESC

我担心:

1. 我在查询中做错了什么
2. 我把查询复杂化了很多

期望的结果将是:[(2, 200, 'title2'), (3, 90, 'title3'), (1, 15, 'title1')]每个元素的第二个元素是最近的成员数量，因为与数量一起存储的日期也存储在成员表中。元组是这样排序的

Answer 1

如果我明白了，这就是你需要的:

SELECT members.*,supergroups.lang,supergroups_ref.title
FROM
-- Window function to get only de last_date:
    (SELECT last_members.group_id,last_members.amount
    FROM
     (SELECT *,row_number() OVER (PARTITION BY group_id
     ORDER BY updated_date DESC) as row FROM members)last_members
    WHERE last_members.row=1)members
-- Joins with other tables
LEFT JOIN supergroups ON members.group_id=supergroups.group_id
LEFT JOIN supergroups_ref ON supergroups.group_id=supergroups_ref.group_id

这应该返回:

group_id |  amount  |   lang  | title
--------------------------------------
1        |  15      |   en    | title
2        |  200     |   it    | title2
3        |  90      |   it    | title3

要使此查询正常工作，您需要使用窗口函数，这里有一篇关于它们的好文章 Understanding Window Functions