rank a column by condition in another column

Question

Given

 id |   date   | condition
-----------------------
  1 | 01/01/17 | true
  1 | 02/01/17 | false
  1 | 03/01/17 | true
  1 | 04/01/17 | true
  1 | 05/01/17 | false
  2 | 01/01/17 | false
  2 | 02/01/17 | true
  2 | 03/01/17 | true
  2 | 04/01/17 | false
  2 | 05/01/17 | false
  2 | 06/01/17 | true

I want to obtain

 id |   date   | condition |  rank  | 
-------------------------------------
  1 | 01/01/17 | true      |    1
  1 | 02/01/17 | false     |   null
  1 | 03/01/17 | true      |    2
  1 | 04/01/17 | true      |    3
  1 | 05/01/17 | false     |   null
  2 | 01/01/17 | false     |   null
  2 | 02/01/17 | true      |    1
  2 | 03/01/17 | true      |    2
  2 | 04/01/17 | false     |   null
  2 | 05/01/17 | false     |   null
  2 | 06/01/17 | true      |    3

How can I do this in postgresql?
Is there a way to do this without union or join?
Ty

Answer 1

We can do this in one query, without CTE or sub-query, by combining CASE and RANK() OVER (PARTITION BY. As we include condition in the PARTITION BY the lines with false do not augment the counter.

create table table_name(
id int,
d date,
condition char(5));

insert into table_name values
(1,'2017-01-01','true'),
(1,'2017-01-02','false'),
(1,'2017-01-03','true'),
(1,'2017-01-04','true'),
(1,'2017-01-05','false'),
(2,'2017-01-01','false'),
(2,'2017-01-02','true'),
(2,'2017-01-03','true'),
(2,'2017-01-04','false'),
(2,'2017-01-05','false'),
(2,'2017-01-06','true');

select
id,
d "date",
condition,
case condition when 'true'
then rank() over (
   partition by id,condition order by d)
end "rank"
from table_name
order by id,d;

id | date       | condition | rank
-: | :--------- | :-------- | ---:
 1 | 2017-01-01 | true      |    1
 1 | 2017-01-02 | false     | null
 1 | 2017-01-03 | true      |    2
 1 | 2017-01-04 | true      |    3
 1 | 2017-01-05 | false     | null
 2 | 2017-01-01 | false     | null
 2 | 2017-01-02 | true      |    1
 2 | 2017-01-03 | true      |    2
 2 | 2017-01-04 | false     | null
 2 | 2017-01-05 | false     | null
 2 | 2017-01-06 | true      |    3

db<>fiddle here

Answer 2

If you actually want to a NULL rather than just excluding them, ROW_NUMBER() with UNION ALL should work given your sample data:

SELECT
    id
    ,date
    ,condition
    ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) as rank
FROM t1
WHERE condition = 'true'
UNION ALL
SELECT
    id
    ,date
    ,condition
    ,NULL AS rank
FROM t1
where condition = 'false'
ORDER BY id, date, rank

Answer 3

You can use a combination of RANK window function and CASE clause, for example:

CREATE TABLE example (id int, d date, condition boolean);

INSERT INTO example values 
  (1 , '01/01/17' , true),
  (1 , '02/01/17' , false),
  (1 , '03/01/17' , true),
  (1 , '04/01/17' , true),
  (1 , '05/01/17' , false),
  (2 , '01/01/17' , false),
  (2 , '02/01/17' , true),
  (2 , '03/01/17' , true),
  (2 , '04/01/17' , false),
  (2 , '05/01/17' , false),
  (2 , '06/01/17' , true);
  
  SELECT *, 
  CASE 
  WHEN condition THEN rank() OVER (partition by id order by d) 
  ELSE NULL
  END 
  FROM example;

the output:

id |     d      | condition | case 
----+------------+-----------+------
  1 | 2017-01-01 | t         |    1
  1 | 2017-01-02 | f         |     
  1 | 2017-01-03 | t         |    3
  1 | 2017-01-04 | t         |    4
  1 | 2017-01-05 | f         |     
  2 | 2017-01-01 | f         |     
  2 | 2017-01-02 | t         |    2
  2 | 2017-01-03 | t         |    3
  2 | 2017-01-04 | f         |     
  2 | 2017-01-05 | f         |     
  2 | 2017-01-06 | t         |    6
(11 rows)

UPDATE:

For the same output, then you need to use a combination of UNION ALL clasue and an ORDER BY

 SELECT * FROM (
  SELECT *, 
  rank() OVER (partition by id order by d) 
  FROM example
  WHERE condition
  
  union all 
  
  SELECT *, 
  null    
  FROM example
  WHERE NOT condition
  ) AS sub ORDER BY 1,2;

id |     d      | condition | rank 
----+------------+-----------+------
  1 | 2017-01-01 | t         |    1
  1 | 2017-01-02 | f         |     
  1 | 2017-01-03 | t         |    2
  1 | 2017-01-04 | t         |    3
  1 | 2017-01-05 | f         |     
  2 | 2017-01-01 | f         |     
  2 | 2017-01-02 | t         |    1
  2 | 2017-01-03 | t         |    2
  2 | 2017-01-04 | f         |     
  2 | 2017-01-05 | f         |     
  2 | 2017-01-06 | t         |    3
(11 rows)

Answer 4

Do the ranking just on the TRUE conditions and then UNION on the FALSE conditions:

WITH t1 AS (
      SELECT *
        , RANK()  OVER w AS r
      FROM  t
      WHERE condition
      WINDOW w AS (PARTITION BY id ORDER BY date)
      UNION ALL
      SELECT *
        , NULL AS r
      FROM  t
      WHERE NOT condition
  )
  SELECT    
    id
    , date
    , condition
    , r
  FROM  t1
  ORDER BY id, date;