rank a column by condition in another column-CodePudding

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? Ty

CodePudding user response：

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

CodePudding user response：

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)

CodePudding user response：

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;