Postgresql - 选择 'votes' 的计数，但只包括最新的

Question

我已经盯着这个看了 20 分钟，无法理解它。我以前做过，我只是不记得在哪个项目中(所以我可以引用代码)。

我有这样一张表:

votes [table]
  id - serial , primary
  useraccount_id - id
  object_id - id
  vote - int
  timestamp_vote - timestamp

我需要能够为任何一个对象选择当前的总票数。

但是 - 此表也充当“时间轴” View 。请注意，我使用序列作为 id 列。 useraccount_id + object_id 可能有多条记录

不幸的是，这不是我需要的:

SELECT 
   vote , count(vote)
FROM 
   votes
WHERE
   object_id = 161 
GROUP BY 
   vote ;

我知道需要使用一个子查询和不同的查询，以及一个“按时间戳 desc 排序”，以限制给定用户的最新投票。我只是不记得如何将 SELECT 串在一起。

-- 添加示例数据--

示例数据

 id | object_id  | useraccount_id |     timestamp_created      | vote 
----+------------+----------------+----------------------------+------------
  2 |        153 |             57 | 2012-12-11 18:37:20.421013 |    1
  3 |        153 |             57 | 2012-12-11 18:48:39.963748 |    2
  4 |        153 |             57 | 2012-12-11 18:48:42.869945 |    3
  5 |        153 |             57 | 2012-12-11 18:48:48.991617 |    1
  6 |        153 |             57 | 2012-12-11 19:34:26.701351 |    2
  7 |        153 |             57 | 2012-12-11 19:35:52.449031 |    3
  8 |        153 |             57 | 2012-12-11 19:35:52.986822 |    1
  9 |        153 |             57 | 2012-12-11 19:35:53.850291 |    2
 10 |        153 |             58 | 2012-12-11 19:35:55.884202 |    3
 12 |        153 |             58 | 2012-12-11 19:35:56.803337 |    1
 13 |        153 |             59 | 2012-12-11 19:37:04.563519 |    3
 15 |        153 |             60 | 2012-12-11 19:37:40.618324 |    3
 16 |        153 |             61 | 2012-12-11 19:37:40.618324 |    3
 17 |        153 |             62 | 2012-12-11 19:37:40.618324 |    3
 18 |        153 |             63 | 2012-12-11 19:37:40.618324 |    2

预期的输出是:

vote | count
-----+------
1    | 1        # user 58 cast a vote of `3` , then `1`.  only the latest vote of `1` counted.
2    | 2        # user 57 switched votes many times. only their last vote of `2` counted. user 63 cast a vote of `2` as well.
3    | 4        # users 59,60,61,62 all voted `3`

Answer 1

select vote, 
       count(vote) as cnt
from (
   select vote, 
          row_number() over (partition by vote order by timestamp_created desc) as rn
   from votes
)
where rn = 1
group by vote;