I have two tables that I want to join with filling null values by previous (closest by date).

CREATE TABLE a
( a_value int,
  a_date DATE,
  a_id int
);

CREATE TABLE b
( b_id int,
  b_date DATE,
  b_value float
);

INSERT INTO a VALUES (1, '20130603', 1);
INSERT INTO a VALUES (1, '20130704', 1);
INSERT INTO a VALUES (3, '20130805', 1);
INSERT INTO a VALUES (1, '20130906', 1);
INSERT INTO a VALUES (2, '20131007', 1);

INSERT INTO b VALUES (1, '20130603', 3.00);
INSERT INTO b VALUES (1, '20130706', 4.00);
INSERT INTO b VALUES (1, '20130906', 5.00);

I need to join like this, but I get null values in some a_dates.

SELECT a.a_value,
a.a_date,
a.a_id,
b.b_value,
b.b_date
FROM b
RIGHT JOIN a on a.a_id = b.b_id
AND b.b_date <= a.a_date
AND b.b_date >= timestampadd(day, -25, a.a_date)
ORDER BY a.a_date;

Result:

But I would like to get with fill missing values by closest by date

I tried to use coalesce:

and coalesce(b.b_date, 
             (select b_date 
              from b as b1 
              where b1.b_date <a.a_date and b1.b_value is not null
              order by b1.b_date desc
             LIMIT 1)) <= a.a_date;

But get duplicates rows :(

How should I do it?

CodePudding user response：

select  a_value 
       ,a_date  
       ,a_id    
       ,b_id    
       ,b_date  
       ,b_value
from
(
select  *
       ,rank() over(partition by a_id, a_date, a_value order by datediff(a_date, b_date)) as rnk
from    a full join b
where   b_date <= a_date
) ab
where   rnk = 1

Fiddle