代码之家 › 专栏 › 技术社区 › Alfred Balle

PostgreSQL与多行匹配

postgresql sql

Alfred Balle · 技术社区 · 7 年前

我正在做一个汽车统计解决方案,我需要收费每公里行驶。

table: cars
columns: car_id, km_driven

table: pricing
columns: from, to, price

我的内容 cars 表可以是:

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

我的内容 pricing 表可以是:

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

汽车 PostgreSQL ?

car 开价201,那么价格是 100x2 + 100x1 + 0.5 ,不简单 201x0.5 .

5 回复 | 直到 7 年前

Gordon Linoff 7 年前

我将把查询写成:

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;

db<>fiddle .

kurkle 7 年前

修改自@sean johnston的答案:

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

保留原始范围
其中km\u driven>=开始

再摆弄一点,在适当的地方可以省略case

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

Sean Johnston 7 年前

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;

说明:

开放式范围在第一个case子句中处理,非常简单。
对于封闭范围,我们需要一个内部case子句,因为我们只希望旅程的一部分在该范围内。

如果您不想(或不能)使您的范围保持一致,那么您需要为起始范围添加第三个外壳。

gpeche 7 年前

我会的 一定地 使用过程来实现这一点,因为可以使用循环以非常简单的方式来实现。但是,您应该能够执行类似的操作:

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

这个查询比需要的要复杂一些,我尝试了一些最终不需要的窗口函数。此处的简化版本应等同于:

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

Zaynul Abadin Tuhin 7 年前

    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