JOIN on range between current and previous row

Question

could anyone help me please? I have two tables: Prices and Intervals:

   Prices:           Intervals:
   Price         Interval_bound  Category
    16                 5         cheap
    11                 10        cheap
    9                  15        median
    26                 20        median
    6                  25        expensive

I need to join the Category values to the Price according to their interval, where Interval_bound is the lowest bound of category:

Price  Category
 16      median
 11      cheap
 9       cheap
 26      expensive
 6       cheap

I've tried to do it with

select Price, Category from Prices 
left join Intervals on Prices.Price interpolate previous value Interval.Interval_bound

but it gives me only NULL for Category. How can I do it the easiest way? I'm using Vertica.

score 3 · Accepted Answer · answered Feb 08 '21 at 13:06

You can use lead() to get the next upper bound and then join:

select p.Price, i.Category
from Prices p left join
     (select i.*,
             lead(interval_bound) over (order by interval_bound) as next_interval_bound
      from Intervals i
     ) i
     on p.price >= i.interval_bound and
        (p.price < i.next_interval_bound or i.next_interval_bound is null);

score 1 · Answer 2 · answered Feb 08 '21 at 15:21

I'm puzzled - why would your version not work?

-- your input ..
WITH
prices(price) AS (
          SELECT 16
UNION ALL SELECT 11
UNION ALL SELECT  9
UNION ALL SELECT 26
UNION ALL SELECT  6
)
, 
-- your other input 
intervals(interval_bound,category) AS (
          SELECT  5,'cheap'
UNION ALL SELECT 10,'cheap'
UNION ALL SELECT 15,'median'
UNION ALL SELECT 20,'median'
UNION ALL SELECT 25,'expensive'
)
-- the way I would write it ...
SELECT
  p.price
, i.category
FROM prices p
LEFT JOIN intervals i
ON p.price INTERPOLATE PREVIOUS VALUE i.interval_bound
;
 price | category
-------+-----------
     6 | cheap
     9 | cheap
    11 | cheap
    16 | median
    26 | expensive

-- the way you wrote it ...
select Price, Category from Prices
left join Intervals on Prices.Price interpolate previous value Intervals.Interval_bound;
 Price | Category
-------+-----------
     6 | cheap
     9 | cheap
    11 | cheap
    16 | median
    26 | expensive

What went wrong in your case?

you know, I've just checked and ереу problem was in by stupid not null filter, which killed the correct results, so actually my version was also correct! thank you for pointing out :) — Polly, Feb 08 '21 at 18:45

JOIN on range between current and previous row

2 Answers2