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
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
WINDOW wnd AS (
PARTITION BY post_id
ORDER BY comment_created_at ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
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.