Search code examples
postgresqlwindow-functions

PostgreSQL - how do I know which partition I'm in when using window functions?

I've been using windows functions as they're very useful for my current requirement. row_number() is very handy for letting me know the row of the current partition. I've read the documentation on window functions and searched online, but can't find an answer to what I want. I'm hoping that someone here can tell if what I want is possible.

My question is this: is it possible to find out which partition within a window you are in, in a similar way that row_number() tells you which row within the partition you are in?

Basically, I want to know something like this, assuming 2 partitions in the window with 2 rows in each partition:

partition 1 row 1
partition 1 row 2
partition 2 row 1
partition 2 row 2

Any ideas, anyone?

Solution

  • We can try using DENSE_RANK and ROW_NUMBER:

    WITH yourTable AS (
    SELECT 'A' AS col1, 1 AS col2 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'A', 3 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 2 UNION ALL
    SELECT 'B', 3
)

SELECT *,
    DENSE_RANK() OVER (ORDER BY col1) partition1,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) partition2
FROM yourTable
ORDER BY
    col1, col2;

    enter image description here

    Demo

    The general pattern here, for an arbitrary number of partitions, would be to use DENSE_RANK for the first N-1 partitions, and then ROW_NUMBER at the last partition. ROW_NUMBER would ensure that the last partition has 1,2,3,... as the sequence.