Postgresql - postgres eliminating NULLS in partition by group

earbasher
May 23, 2023
143 views
1 vote
2 Answers

updated

I have the following table of updates which you will note contains nulls

refer	event_date	col1	col2	col3	cat
2	today	null	null	bbb	a
2	yesterday	null	null	null	b
2	yesterday	null	xyz	null	b
2	last week	abc3	null	null	z
2	two weeks ago	null	null	null	z
2	last month	null	def	null	a
2	last year	vwy	abc123	null	z

so for refer = 2

bbb is the latest update of col3 based on latest date for cat = a (today).
xyz is the latest update of col2 based on date for cat = b (yesterday).
abc3 is the latest update of col1 based on date for cat z (last week).

I had previously asked a similar question here and accepted the following code which was kindly provided by @GMB and works fine if the above table has no NULLS.

select refer,
    max(col1) filter(where rn3 = 1 and cat = 'a') detail,
    max(col1) filter(where rn2 = 1 and cat = 'b') detail2,
    max(col1) filter(where rn1 = 1 and cat = 'z') detail3
from (
    select t.*,
        row_number() over(partition by refer,col3 order by event_date desc) rn1,
        row_number() over(partition by refer,col2 order by event_date desc) rn2,
        row_number() over(partition by refer,col1 order by event_date desc) rn3
    from mytable t
) t
group by refer

expected output:

refer	col1 lastest	col2 latest	col3 latest
2	bbb	xyz	abc3

The thing is it doesn’t work if there are nulls in the columns and I cant figure out how to eliminate nulls from the window partition groups. If I could add is not null in the partition as shown below it would fix my problem eg:

select
row_number() over(partition by refer, col3 **(where col3 is not null)** order by event_date desc) rn1
,row_number() over(partition by refer, col2, ***(where col2 is not null)*** order by event_date desc) rn2
,row_number() over(partition by refer, col1, ***(where col1 is not null)*** order by event_date desc) rn3

Answers

To eliminate null values from being captured from all partitions, use the conditional order inside row_number(), Also partitions must be based on refer and cat:

select refer,
    MAX(col3) filter(where rn3 = 1 and cat = 'a') col1_lastest,
    MAX(col2) filter(where rn2 = 1 and cat = 'b') col2_lastest,
    MAX(col1) filter(where rn1 = 1 and cat = 'z') col3_lastest
from (
    select t.*,
      row_number() over(partition by refer,cat order by case when col3 is not null then 1 else 2 end, event_date desc) rn1,
      row_number() over(partition by refer,cat order by case when col2 is not null then 1 else 2 end, event_date desc) rn2,
      row_number() over(partition by refer,cat order by case when col1 is not null then 1 else 2 end, event_date desc) rn3
    from mytable t
) t
group by refer;

Result :

refer	col1_lastest	col2_lastest	col3_lastest
2	bbb	xyz	abc3

Demo here

No need to check for null values: they’re removed by the max. You can move all your conditions inside the ORDER BY clause of the ROW_NUMBER window functions, then you can apply conditional aggregation in an easier way.

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY refer ORDER BY cat='z' DESC, 
                                                         event_date DESC) rn1,
           ROW_NUMBER() OVER(PARTITION BY refer ORDER BY cat='b' DESC,
                                                         event_date DESC) rn2,
           ROW_NUMBER() OVER(PARTITION BY refer ORDER BY cat='a' DESC, 
                                                         event_date DESC) rn3
    FROM mytable
)
SELECT refer,
       MAX(col3) FILTER(WHERE rn3 = 1) detail,
       MAX(col2) FILTER(WHERE rn2 = 1) detail2,
       MAX(col1) FILTER(WHERE rn1 = 1) detail3
FROM cte
GROUP BY refer

Output:

refer	detail	detail2	detail3
2	bbb	xyz	abc3

Check the demo here.

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – postgres eliminating NULLS in partition by group

Answers