如何在PostgreSQL中获取分区（窗口）的大小？

Question

如何在PostgreSQL中获取分区（窗口）的大小？

6

当我使用一个窗口时，如何获取当前分区的大小（行数）？

例如，假设我有一个表存储博客中帖子的评论。我想知道每篇帖子的第一条评论、第二条评论、最后一条评论和评论总数（不需要另外一个子查询来按帖子分组并执行COUNT(*)）。

查询应该看起来像：

SELECT DISTINCT
    post_id.
    first_value(comment_text) OVER wnd AS first_comment,
    nth_value(comment_text, 2) OVER wnd AS second_comment,
    last_value(comment_text) OVER wnd AS last_comment,
    SOME_FUNCTION(comment_text) OVER wnd AS number_of_comments
FROM comments
WINDOW wnd AS (
    PARTITION BY post_id
    ORDER BY comment_created_at ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

SOME_FUNCTION应该是什么？

- OferBr

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user743382 · Accepted Answer

在你的问题中已经提到了，一旦你意识到可以与Windows一起使用它：COUNT(*) OVER wnd AS number_of_comments 就可以完成任务。

SELECT DISTINCT
    post_id.
    first_value(comment_text) OVER wnd AS first_comment,
    nth_value(comment_text, 2) OVER wnd AS second_comment,
    last_value(comment_text) OVER wnd AS last_comment,
    COUNT(*) OVER wnd AS number_of_comments
FROM comments
WINDOW wnd AS (
    PARTITION BY post_id
    ORDER BY comment_created_at ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);