CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

PostgreSQL and matching row on multiple

I'm making a car statistic solution where I need to charge per kilometer driven.

I have the following table:

table: cars
columns: car_id, km_driven

table: pricing
columns: from, to, price

Content in my cars table can be:

car_id, km_driven
2, 430
3, 112
4, 90

Content on my pricing table can be:

from, to, price
0, 100, 2
101, 200, 1
201, null, 0.5

Meaning that the first 100 km cost 2USD per km, the next 100 km cost 1USD per km and everything above costs 0.5USD per km.

Is there a logic and simple way to calculate cost for my cars via PostgreSQL?

So if a car has driven ex. 201, then the price would be 100x2 + 100x1 + 0.5, not simply 201x0.5.

Upvotes: 2

Views: 123

Answers (5)

gpeche
gpeche

Reputation: 22514

I would definitely do this using a procedure, as it can be implemented in a very straightforward manner using loops. However, you should be able to do something similar to this:

select car_id, sum(segment_price)
from (
  select 
  car_id, 
  km_driven, 
  f, 
  t, 
  price, 
  driven_in_segment, 
  segment_price
  from (
      select 
      car_id, 
      km_driven, 
      f, 
      t, 
      price, 
      (coalesce(least(t, km_driven), km_driven) - f) driven_in_segment, 
      price * (coalesce(least(t, km_driven), km_driven) - f) segment_price
      from 
      -- NOTE: cartesian product here
      cars, 
      pricing
      where f < km_driven
  )
) data
group by car_id
order by car_id

I find that pretty less readable, though.

UPDATE:

That query is a bit more complex than necessary, I was trying out some things with window functions that were not needed in the end. A simplified version here that should be equivalent:

select car_id, sum(segment_price)
from (
  select 
  car_id, 
  km_driven, 
  f, 
  t, 
  price, 
  (coalesce(least(t, km_driven), km_driven) - f) driven_in_segment, 
  price * (coalesce(least(t, km_driven), km_driven) - f) segment_price
  from 
  -- NOTE: cartesian product here
  cars, 
  pricing
  where f < km_driven
) data
group by car_id
order by car_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I would write the query as:

select c.car_id, c.km_driven, 
   sum(( least(p.to_km, c.km_driven) - p.from_km + 1) * p.price) as dist_price
from cars c join
     pricing p
     on c.km_driven >= p.from_km
group by c.car_id, c.km_driven;

Here is a db<>fiddle.

Upvotes: 2

kurkle
kurkle

Reputation: 383

Modified from @sean-johnston's answer:

select 
  car_id, km_driven, 
  sum(case 
    when km_driven>=start then (least(finish,km_driven)-start+1)*price 
    else 0
  end) as dist_price
from cars,pricing
group by car_id,km_driven
  • Original ranges kept
  • where km_driven >= start omitted (its optional but might improve performance)

fiddling a bit more, case can be omitted when where is in place

select 
  car_id, km_driven, 
  sum((least(finish,km_driven)-start+1)*price) as dist_price
from cars,pricing
where km_driven >= start
group by car_id,km_driven

dbfiddle

Upvotes: 2

Sean Johnston
Sean Johnston

Reputation: 174

Judicious use of case/sum combinations. However, firstly need to make your ranges consistent. I'll choose to change the first range to 1,100. Given that then the following should give you want you're after. (I've also used start/finish as from/to are reserved words).

select
  car_id, km_driven,
  sum (case
    when finish is null and km_driven >= start
      then (km_driven-start+1) * price
    when km_driven >= start
      then (case
              when (km_driven - start + 1) > finish
                then (finish - start + 1)
              else (km_driven - start + 1)
              end) * price
    else 0
    end) as dist_price
from cars, pricing
where km_driven >= start
group by 1, 2;

Explanation:

  1. We join against any range where the journey is at least as far as the start of the range.
  2. The open ended range is handled in the first case clause and is fairly simple.
  3. We need an inner case clause for the closed ranges as we only want the part of the journey in that range.
  4. Then sum the results of that for the total journey price.

If you don't want to (or can't) make your ranges consistent then you'd need to add a third outer case for the start range.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use join and calculate your cost by using case when 

    select c.car_id, case when p.price=.5 
   then  100*2+100*1+(c.km_driven-200)*0.5 
    when   p.price=1 then 100*2+(c.km_driven-100)*1
    else c.km_driven*p.price as cost
   from cars c join pricing p
   on c.km_driven>=p.from and c.km_driven<=p.to

Upvotes: 0

Related Questions