对ORDER BY使用DISTINCT ON和COUNT()时的意外结果

Question

PostgreSQL查询返回不同的行数和投票数，由COUNT()函数产生

在vote_table帖子可以被支持，然后同一个选民可以撤销它的投票，但同一个选民不能投票两次:

post_id | voter_id | is_upvote | timestamp
---------+----------+-----------+-----------
440 |       28 | f         | timestamp
440 |      497 | f         | timestamp
440 |      959 | t         | timestamp
440 |      959 | f         | timestamp
440 |      959 | t         | timestamp
440 |      959 | f         | timestamp
440 |      959 | t         | timestamp

在这个查询中，我使用DISTINCT ON:

查询投票的最后状态

SELECT
post_id, voter_id, is_upvote
FROM (
SELECT DISTINCT ON (voter_id)
post_id, voter_id, is_upvote
FROM (
SELECT post_id, voter_id, is_upvote
FROM vote_table
ORDER BY timestamp DESC
) votes_ordered
) votes_distinct
WHERE is_upvote IS TRUE AND post_id = 5;

假设上面的查询返回了帖子5被支持的行数。然后我要求计算每个帖子的投票数

SELECT
post_id,
COUNT(*) AS votes_count
FROM (
SELECT
post_id, voter_id, is_upvote
FROM (
SELECT DISTINCT ON (voter_id)
post_id, voter_id, is_upvote
FROM (
SELECT post_id, voter_id, is_upvote
FROM vote_table
ORDER BY timestamp DESC
) votes_ordered
) votes_distinct
WHERE is_upvote IS TRUE;
) votes_approve
GROUP BY post_id
;

在结果表中，结果不正确，votes_count将小于第一次查询返回的行数。如果我重新添加AND post_id = 5到第二个查询，votes_count是正确的

这是怎么回事?

更新:根据回应者的建议，我添加了一个示例表。唯一的区别是，表有id而不是timestamp但它也是顺序的

创建临时表id SERIAL主键;post_id INT NOT NULL，voter_id INT NOT NULL，is_upvote BOOLEAN NOT NULL

);

INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 28, TRUE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 497, TRUE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 959, TRUE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 959, FALSE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 959, TRUE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 959, FALSE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 497, FALSE);
INSERT INTO vote_table (post_id, voter_id, is_upvote)
VALUES (440, 959, TRUE);

对于这个表，第二个查询将返回正确的结果(440, 2)，但是当向表中添加更多条目时，查询结果可能是错误的，因为ORDER BY可以被忽略，正如Greg Pavlik所说的

更新后的问题将是:如何编写一个类似的查询来选择每个帖子的赞数，知道单个选民只能点赞一次，也可以点赞一次?

Answer 1

问题(或至少a)问题)就在这里:

SELECT DISTINCT ON (voter_id)
post_id, voter_id, is_upvote
FROM (
SELECT post_id, voter_id, is_upvote
FROM vote_table
ORDER BY timestamp DESC

这是一个子查询。想想如果你是在此基础上创建一个表。当从表中进行选择时，DBMS可以自由地存储和返回任何它想要的行。对于子查询也是如此。

根据SQL规范，DMBS不需要尊重子查询中的ORDER BY子句，只需要尊重最终结果集或位置，如窗口分区和组表达式。这里的by子句顺序是在子查询中，所以不能保证PostgreSQL会保持这个顺序。事实上，作为性能优化，一些DBMS完全忽略了子查询中的order by子句。

如果您需要使用行排序来选择特定的行，您需要在窗口分区、组表达式等中使用order by。

如果你可以用更多的细节更新你的问题，特别是一个有预期输出的表将是有帮助的，这里的人肯定会帮助你提供一个适用的窗口函数或其他方法。