CODEWITHSUNDEEP
sqlpostgresqlmath
Zlatko Gyuzelev
Zlatko Gyuzelev

Reputation: 11

i need postgresql mathematical select

I need idea for sql select on this. I have a table with this columns:

ID  type  quan price
 1     1    5     6.5
 1     1    4     7
 1     2    5     10
 1     1    5     6

I need to run a query with following condition:

fir i = 1 to 4
if type = 1
     rprice = (rprice*rquan + price*quan)/(rquan+quan)
     rquan  = rquan  + quan
else
 rquan  = rquan  - quan
end
next

The type can be 1 or 2 For ID 1 I need as result rquan and rprice The sumary result must be : rquan=9 rprice=6.32

Upvotes: 0

Views: 58

Answers (3)

araqnid
araqnid

Reputation: 133672

You can write a custom aggregate to do this. The syntax is rather strange in places, though.

create schema so32264410;

create table so32264410.data(seqno serial primary key, id int, type int, quan int, price numeric);
insert into so32264410.data(id, type, quan, price) values(1, 1, 5, 6.5),(1, 1, 4, 7),(1, 2, 5, 10),(1, 1, 5, 6);

-- define a type to hold the aggregation and a function to perform reduction
create type so32264410.calc_type as (quan int, price numeric);
create function so32264410.calc_sfunc(state so32264410.calc_type, type int, quan int, price numeric) returns so32264410.calc_type immutable language plpgsql as $$
declare
  rquan int;
  rprice numeric;
begin
  rquan := state.quan;
  rprice := state.price;
  if type = 1 then
    rprice := (rprice * rquan + price * quan) / (rquan + quan);
    rquan := rquan + quan;
  else
    rquan := rquan - quan;
  end if;
  state := row(rquan, rprice)::so32264410.calc_type;
  return state;
end
$$;
-- bind the reducing function, state type and initial state into an aggregate
create aggregate so32264410.calc(int, int, numeric) (sfunc = so32264410.calc_sfunc, stype = so32264410.calc_type, initcond = '(0,0)');

select so32264410.calc(type, quan, price) from so32264410.data where id = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Assuming you have a column that specifies the ordering of the rows, you can do this with cumulative sums and/or aggregation. For instance, for rquan:

select rquan
from (select t.*,
             sum(case when type = 1 then quan else - quan end) over (order by ??) as rquan_row        
      from table t
     ) t
order by ?? desc
limit 1;

This can actually be simplified to a single conditional aggregation, but you seem to want this quantity row-by-row.

I don't fully understand the calculation for rprice. I can see what the formula is, but it doesn't make sense. An average price for the data would be 6 not 6.32.

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51599

select 
 case when type = 1 
 then (rprice*rquan + price*quan)/(rquan+quan)
 else price
 end as "rprice"
,case when type = 1 
 then rquan  + quan
 else quan
 end as "rquan"
from "table_name"

Upvotes: 1

Related Questions