PostgresSQL: Fill values for null rows based on rows for which we do have values

user_15980093 0
Viewed 28

I have the following table:

   country year rank
1  Austria 2019    1
2  Austria 2018   NA
3  Austria 2017   NA
4  Austria 2016   NA
5    Spain 2019    2
6    Spain 2018   NA
7    Spain 2017   NA
8    Spain 2016   NA
9  Belgium 2019    3
10 Belgium 2018   NA
11 Belgium 2017   NA
12 Belgium 2016   NA

I want to fill in the NA values for 2018, 2017 and 2016 for each country with the value for 2019 (which we have). I want the output table to look like this:

   country year rank
1  Austria 2019    1
2  Austria 2018    1
3  Austria 2017    1
4  Austria 2016    1
5    Spain 2019    2
6    Spain 2018    2
7    Spain 2017    2
8    Spain 2016    2
9  Belgium 2019    3
10 Belgium 2018    3
11 Belgium 2017    3
12 Belgium 2016    3

I do not know where to get started with this question. I typically work with R but am now working on a platform which uses postgresSQL. I could do this in R but thought it would be worthwhile to figure out how it is done with postgres.

Any help with this would be greatly appreciated. Thank you.

postgresql
2 Answers

Using an update to join to find the non NULL rank value for each country:

UPDATE yourTable AS t1
SET "rank" = t2.max_rank
FROM
(
    SELECT country, MAX("rank") AS max_rank
    FROM yourTable
    GROUP BY country
) t2
WHERE t2.country = t1.country;
-- AND year IN (2016, 2017, 2018)

Add the commented out portion of the WHERE clause if you really only want to target certain years (your example seems to imply that you want to backfill all missing data).

If you just want to view your data in the format of the output, then use MAX as an analytic function:

SELECT country, year, MAX("rank") OVER (PARTITION BY country) AS "rank"
FROM yourTable
ORDER BY country, year DESC;
user_18632290

If you just want the output then try this,

with cte as (
select distinct on (country) * from test 
order by country, year desc
)
select 
t1.id,t1.country,t1.year,t2.rank 
from test t1 left join cte t2 on t1.country=t2.country

If you want to update your table then try this:

with cte as (
select distinct on (country) * from test 
order by country, year desc
)
update test set rank=cte.rank from cte
where test.country=cte.country

DEMO

user_77724830
Related