CODEWITHSUNDEEP
sqlpostgresqlwindow-functionsrankdense-rank
Dan
Dan

Reputation: 1891

PostgreSQL - rank over rows listed in blocks of 0 and 1

I have a table that looks like:

id     code      date1         date2         block
--------------------------------------------------
20     1234      2017-07-01    2017-07-31    1
15     1234      2017-06-01    2017-06-30    1
13     1234      2017-05-01    2017-05-31    0
11     1234      2017-03-01    2017-03-31    0
9      1234      2017-02-01    2017-02-28    1
8      1234      2017-01-01    2017-01-31    0
7      1234      2016-11-01    2016-11-31    0
6      1234      2016-10-01    2016-10-31    1
2      1234      2016-09-01    2016-09-31    1

I need to rank the rows according to the blocks of 0's and 1's, like:

id     code      date1         date2         block     desired_rank
-------------------------------------------------------------------
20     1234      2017-07-01    2017-07-31    1         1
15     1234      2017-06-01    2017-06-30    1         1
13     1234      2017-05-01    2017-05-31    0         2
11     1234      2017-03-01    2017-03-31    0         2
9      1234      2017-02-01    2017-02-28    1         3
8      1234      2017-01-01    2017-01-31    0         4
7      1234      2016-11-01    2016-11-31    0         4
6      1234      2016-10-01    2016-10-31    1         5
2      1234      2016-09-01    2016-09-31    1         5

I've tried to use rank() and dense_rank(), but the result I end up with is:

id     code      date1         date2         block     dense_rank()
-------------------------------------------------------------------
20     1234      2017-07-01    2017-07-31    1         1
15     1234      2017-06-01    2017-06-30    1         2
13     1234      2017-05-01    2017-05-31    0         1
11     1234      2017-03-01    2017-03-31    0         2
9      1234      2017-02-01    2017-02-28    1         3
8      1234      2017-01-01    2017-01-31    0         3
7      1234      2016-11-01    2016-11-31    0         4
6      1234      2016-10-01    2016-10-31    1         4
2      1234      2016-09-01    2016-09-31    1         5

In the last table, the rank doesn't care about the rows, it just takes all the 1's and 0's as a unit and sets an ascending count starting at the first 1 and 0. My query goes like this:

CREATE TEMP TABLE data (id integer,code text, date1 date, date2 date, block integer);

INSERT INTO data VALUES
(20,'1234', '2017-07-01','2017-07-31',1),
(15,'1234', '2017-06-01','2017-06-30',1),
(13,'1234', '2017-05-01','2017-05-31',0),
(11,'1234', '2017-03-01','2017-03-31',0),
(9, '1234', '2017-02-01','2017-02-28',1),
(8, '1234', '2017-01-01','2017-01-31',0),
(7, '1234', '2016-11-01','2016-11-30',0),
(6, '1234', '2016-10-01','2016-10-31',1),
(2, '1234', '2016-09-01','2016-09-30',1);

SELECT *,dense_rank() OVER (PARTITION BY code,block ORDER BY date2 DESC)
FROM data 
ORDER BY date2 DESC;

By the way, the database is in postgreSQL.

I hope there's a workaround... Thanks :)

Edit: Note that the blocks of 0's and 1's aren't equal.

Upvotes: 0

Views: 1044

Answers (1)

dnoeth
dnoeth

Reputation: 60502

There's no way to get this result using a single Window Function:

SELECT *,
   Sum(flag) -- now sum the 0/1 to create the "rank"
   Over (PARTITION BY code
         ORDER BY date2 DESC)
FROM
 (
   SELECT *,
      CASE
         WHEN Lag(block) -- check if this is the 1st row of a new block
              Over (PARTITION BY code
                    ORDER BY date2 DESC) = block 
         THEN 0 
         ELSE 1 
      END AS flag
   FROM DATA
 ) AS dt

Upvotes: 1

Related Questions