How to get the size of a partition (window) in PostgreSQL?

Question

When I use a window, how can I get the size (number of rows) of the current partition?

For instance let's say I have a table that holds the comments of posts in a blog. I would like to know for each post what is the first comment, second comment, last comment and number of comments (without having another sub-query where I group by post and do COUNT(*)).

The query should look something like:

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
);

What should SOME_FUNCTION be?

Answer 1

It's right there in your question, once you realise you can use it with windows: COUNT(*) OVER wnd AS number_of_comments will do the job.

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
);