replace null values in subsequent rows from previous row-CodePudding

I have this table about foods and rating and facing this scenario.

select * from foods

food_type	country	rating
Pizza	Italy	8
Pizza	Italy	null
Pizza	Italy	null
Pizza	Sweden	6
Pizza	Sweden	null
Pizza	Sweden	null
Calzone	France	7
Calzone	France	null
Calzone	France	null
Hero Sub	USA	10
Hero Sub	USA	null
Hero Sub	USA	null
Hero Sub	USA	null

How can I replace the nulls with the rating value of the same food_type and same country from the previous not null rating value? e.g. new_rating column

food_type	country	rating	new_rating
Pizza	Italy	8	8
Pizza	Italy	null	8
Pizza	Italy	null	8
Pizza	Sweden	6	6
Pizza	Sweden	null	6
Pizza	Sweden	null	6
Calzone	France	7	7
Calzone	France	null	7
Calzone	France	null	7
Hero Sub	USA	10	10
Hero Sub	USA	null	10
Hero Sub	USA	null	10
Hero Sub	USA	null	10

I am using postgreSQL 11.4!

CodePudding user response：

You can try a Subquery in the SELECT-Part - like this:

https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

select 
  master.*,
  (select 
    newfood.rating 
   from 
    foods newfood 
   where 
    newfood.food_type = master.food_type AND newfood.country = master.country
    AND newfood.rating is not NULL
   group by newfood.rating
  ) as new_rating
from 
  foods master
;

CodePudding user response：

Try a coalesce'd MAX OVER

SELECT food_type, country, rating
, COALESCE(rating, MAX(rating) OVER (PARTITION BY food_type, country)) AS new_rating
FROM foods