CODEWITHSUNDEEP
postgresqlwindow-functions
franco_b
franco_b

Reputation: 898

Data partition in Postgresql using Window-Functions

I have table 'Z':

id|zone|name
------------
1 | 34 |  a
2 | 14 |  b
3 | 99 |  c
4 | 99 |  d
5 | 90 |  e
6 | 99 |  c

query

SELECT *,dense_rank() (OVER ORDER BY zone) FROM Z

return:

id|zone|name|dense_rank
-----------------------
1 | 34 |  a | 2
2 | 14 |  b | 1
3 | 99 |  c | 4
4 | 99 |  d | 4               
5 | 90 |  e | 3
6 | 99 |  c | 4

First (less important) question is: is it possible have dense_rank sorted by id:

id|zone|name|dense_rank
-----------------------
1 | 34 |  a | 1
2 | 14 |  b | 2
3 | 99 |  c | 3
4 | 99 |  d | 3               
5 | 90 |  e | 4
6 | 99 |  c | 3

But my final target is change the data partition when field 'zone' change. I need a table like this:

id|zone|name|window-function?
-----------------------
1 | 34 |  a | 1
2 | 14 |  b | 2
3 | 99 |  c | 3
4 | 99 |  d | 3               
5 | 90 |  e | 4
6 | 99 |  c | 5

Any idea?

Thanks a lot

Upvotes: 1

Views: 37

Answers (1)

krokodilko
krokodilko

Reputation: 36127

The query for the "final target" is:

SELECT *,
       SUM( x ) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM ( 
    SELECT *,
       CASE WHEN zone = lag( zone ) over ( order by id )
                 THEN 0 ELSE 1 END As x
    FROM Z
) x

For the "less important" question the query is

SELECT z.*, p.dense_rank
FROM z
JOIN (
   SELECT ZONE, row_number() over (order by ID ) as dense_rank
   FROM (
    SELECT zone, min ( id ) as id
    FROM z
    GROUP BY zone
   ) o
) p ON z.zone = p.zone
ORDER BY id

Upvotes: 2

Related Questions