OferBr OferBr - 3 months ago 11
SQL Question

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

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?

hvd hvd
Answer

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.

Comments