skip to Main Content

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

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search