SQL : automatically fill price between dates

user_12252327 0
Viewed 63

I'm trying to write a view from two tables, one referential table that contains products ID and weeks :

+------------+------+
| Product_id | week |   t1
+------------+------+
|          1 |    1 |
|          2 |    1 |
|          1 |    2 |
|          2 |    2 |
|          1 |    3 |
|          2 |    3 |
+------------+------+ etc...

the other one contains Products ID, weeks when the product's price changed and the price

+------------+------+-------+
| Product_id | week | price | t2
+------------+------+-------+
|          1 |    1 |    70 |
|          1 |    2 |    50 |
|          2 |    2 |    70 |
|          1 |    4 |    30 |
|          2 |    4 |    40 |
+------------+------+-------+

I know how to achieve easily this by joining the two tables :

+------------+------+-------+
| Product_id | week | price | 
+------------+------+-------+
|          1 |    1 |    70 |
|          1 |    2 |    50 |
|          1 |    3 |       |
|          1 |    4 |    30 |
|          1 |    5 |       |
|          2 |    1 |       |
|          2 |    2 |    70 |
|          2 |    3 |       |
|          2 |    4 |    40 |
|          2 |    5 |       |
+------------+------+-------+

But my goals would rather be to fill in the gaps and have the price for each week (without creating any new table), as such :

+------------+------+-------+
| Product_id | week | price |
+------------+------+-------+
|          1 |    1 |    70 |
|          1 |    2 |    50 |
|          1 |    3 |    50 |
|          1 |    4 |    30 |
|          1 |    5 |    30 |
|          2 |    1 |       |
|          2 |    2 |    70 |
|          2 |    3 |    70 |
|          2 |    4 |    40 |
|          2 |    5 |    40 |
+------------+------+-------+ (product 2 isn't sold yet at week 1, so it doesn't have a price).

I can't see how I would do this in SQL. I haven't used PARTITION BY or LAG yet, and it might be what I'm looking for. If anyone can push me in the right direction, I would appreciate it :)

window-functions teradata select sql
3 Answers

You can use window functions - the ignore nulls clause, which teradata supports, comes handy here:

select
    t1.product_id,
    t1.week,
    coalesce(
        t2.price,
        lag(t2.price ignore nulls) over(partition by t1.product_id order by t1.week)
    ) price
from t1
left join t2 
    on  t2.product_id = t1.product_id
    and t2.week = t1.week

Or better yet, as suggested by dnoeth, you can use last_value(), which avoids the need for coalesce():

select
    t1.product_id,
    t1.week,
    last_value(t2.price ignore nulls) over(partition by t1.product_id order by t1.week) price
from t1
left join t2 
    on  t2.product_id = t1.product_id
    and t2.week = t1.week
user_106767160

Use a cross join to generate the rows, then left join and window functions:

with weeks as (
      select row_number() over (order by product_id) as n
      from table1
     )
select t1.product_id, w.n as week,
       coalesce(t2.price, lag(t2.price ignore nulls) over (partition by p.product_id order by w.n)
               ) as price
from (select distinct product_id
      from table1 t1
     ) p cross join
     weeks w left join
     table2 t2
     on t2.product_id = p.product_id and t2.week = w.week
where w.n <= 5
user_11440350

You can do this with a LEFT JOIN.

SELECT t1.Product_id, t1.week, tmp.price
FROM t1
LEFT JOIN t2 tmp ON tmp.Product_id = t1.Product_id AND
                    tmp.week = (SELECT MAX(week) FROM t2
                                WHERE Product_id = tmp.Product_id AND week <= t1.week)
ORDER BY t1.Product_id, t1.week

I would argue it's cleaner yet with OUTER APPLY, but I don't know if that's supported by teradata.

SELECT t1.Product_id, t1.week, oa.price
FROM t1
OUTER APPLY (SELECT TOP 1 price FROM t2
             WHERE Product_id = t1.Product_id AND week <= t1.week
             ORDER BY week DESC) oa
ORDER BY t1.Product_id, t1.week
user_31940050
Related