PostgreSQL中获取最新重复行并添加重复次数列的方法

现有一个PostgreSQL视图查询，通过按iid、usr、position分组并关联最大created_at值，获取每组的最新行。现在希望在结果中新增一列，显示该组的重复行数，询问是否可在原查询中实现，无需额外编写查询。

现有查询 #

select u.*
from users u
join(
      select iid, usr, position,max(created_at) as ca
      from users
      group by iid, usr, position
    ) j on j.ca = u.created_at

示例表数据 #

10001 User 1 intern 2022-10-14 00:00:00
10002 User 2 intern 2022-10-15 00:00:00
10003 User 3 intern 2022-10-13 00:00:00
10003 User 3 p.a    2022-10-15 00:00:00
10001 User 1 p.a    2022-10-16 00:00:00

当前查询结果 #

10001 User 1 p.a    2022-10-16 00:00:00
10003 User 3 p.a    2022-10-15 00:00:00
10002 User 2 intern 2022-10-15 00:00:00

预期结果 #

10001 User 1 p.a    2022-10-16 00:00:00 [2]
10003 User 3 p.a    2022-10-15 00:00:00 [2]
10002 User 2 intern 2022-10-15 00:00:00 [1]

可以直接在原查询中实现，只需修改子查询并调整关联逻辑：

select u.*, concat('[', j.group_count, ']') as row_count
from users u
join(
      select iid, usr, position, max(created_at) as ca, count(*) as group_count
      from users
      group by iid, usr, position
    ) j on j.iid = u.iid and j.usr = u.usr and j.position = u.position and j.ca = u.created_at

关键调整说明 #

1. 子查询新增count(*) as group_count，统计每个分组（iid、usr、position组合）的总行数
2. 主查询用concat('[', j.group_count, ']')将计数格式化为预期的[数字]样式
3. 补充完整关联条件（iid、usr、position匹配），避免仅通过created_at关联可能出现的跨分组匹配错误

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