PostgreSQL如何查询各账号最新状态并计算用户首次加入时间

解决方案 #

场景1：表中已存在first_join_time字段 #

优先推荐用PostgreSQL专属的DISTINCT ON语法实现，写法简洁且执行效率更高：

SELECT DISTINCT ON (account)
  first_join_time,
  account,
  status
FROM 你的表名
ORDER BY account, time DESC;

如果需要兼容标准SQL语法，也可以用窗口函数实现：

WITH ranked_data AS (
  SELECT 
    first_join_time,
    account,
    status,
    ROW_NUMBER() OVER (PARTITION BY account ORDER BY time DESC) AS rn
  FROM 你的表名
)
SELECT first_join_time, account, status
FROM ranked_data
WHERE rn = 1;

• 逻辑说明：按account分组后，每组内按状态更新时间time倒序排序，取排名为1的记录就是该账号最新的状态数据。

场景2：表中不存在first_join_time字段 #

用聚合窗口函数MIN()计算每个账号的首次加入时间，再结合上述取最新记录的逻辑即可：

SELECT DISTINCT ON (account)
  MIN(time) OVER (PARTITION BY account) AS first_join_time,
  account,
  status
FROM 你的表名
ORDER BY account, time DESC;

标准SQL版本：

WITH calculated_data AS (
  SELECT 
    MIN(time) OVER (PARTITION BY account) AS first_join_time,
    account,
    status,
    ROW_NUMBER() OVER (PARTITION BY account ORDER BY time DESC) AS rn
  FROM 你的表名
)
SELECT first_join_time, account, status
FROM calculated_data
WHERE rn = 1;

• 逻辑说明：MIN(time) OVER (PARTITION BY account)会为同一账号的所有行填充该账号最小的time值，也就是用户首次加入时间。

内容的提问来源于stack exchange，提问作者99kies