5

Currently I have data like this,

CREATE TABLE foo (id, qty, unit_price, date, action) AS
    VALUES
    (1,  2000, 4.01235, '2015-10-10'::timestamp, 'in'),
    (2,   -30, NULL   , '2015-10-11'::timestamp, 'out'),
    (3,  1800, 4.9    , '2015-10-25'::timestamp, 'in'),
    (4, -1000, NULL   , '2015-11-12'::timestamp, 'out'),
    (5,  -980, NULL   , '2015-11-20'::timestamp, 'out');

I need to get calculated avg price for outgoing rows so that result look like this.

id |  qty  | unit_price |    date    | action
----------------------------------------------
1  |  2000 |    4.01235 | 2015-10-10 | in
2  |   -30 |    4.01235 | 2015-10-11 | out
3  |  1800 |        4.9 | 2015-10-25 | in
4  | -1000 |    4.01235 | 2015-11-12 | out
5  |  -980 |    4.02141 | 2015-11-20 | out

unit_price is calculated for outgoing in this way:

  1. for id=2, since there's 2000 unused from only one income the result unit_price is the same as first income row id=1
  2. for id=4, there's still 1970 unused and it's bigger than needed (-1000) - so result unit_price is still the same as first income row id=1
  3. for id=5, there's left only 970 with the first in action, but theres more with in action id=3 1800. So basically i could use -970 with price 4.01235 and -10 with price 4.9. So as result i get the avg price for this row like this ((-970*4.01235)+(-10*4.9)) / -980 = 4.02141 when rounding to 5 decimals.

All the price calculations have to consider date column. Basically it is FIFO calculation logic.

Does anyone have idea how to do this kind of calculation?

  • 1
    This is an interesting development in your previous question, but have you tried using the answer given there to solve this problem? What was your success? Could you include your attempts in this question? – Andriy M Nov 23 '16 at 12:44
  • 1
    I took your answer from prev question as template to achive current solution, but i'm little stuck with the idea that there's this date. I know that i should separate somehow in and out actions and then do calculations. I'll to write attempt that i have already. – Tiit Remmel Nov 23 '16 at 13:16
  • I've never seen a system or price like this, because most systems are concerned with average: so if you buy 10 units at $10 (for $100), and 10 units at $5 (for $50) your average price per unit is $7.5 ($150/20), and if you sell 10 for $10, you make $25. With your system, if you sell 10 for $10 you make 0. And if you do the same thing again, you double your money! I wonder where this system is useful. – Evan Carroll May 2 '18 at 20:34
6

There appear to be two key issues here.

One is finding a way to match an 'out' quantity with all relevant 'in' quantities. In your case, row 5 needs to be matched with rows 1 and 3 because it uses both rows' quantities, as follows from the order of ins and outs.

You could try using a method like this. Take the two subsets of the table, ins and outs, and calculate two running totals for each, one including the current value (call it qty_from) and the other excluding it (i.e. the running total of all preceding values). Let us call the former qty_to and the latter qty_from. You will get these results for each subset:

  • for ins:

    qty_from  qty_to
    --------  ------
    0         2000
    2000      3800
    
  • for outs:

    qty_from  qty_to
    --------  ------
    0         30
    30        1030
    1030      2010
    

Now join the two subsets using a well-known range matching method: A.from < B.to AND B.from < A.to. That will give you intersecting ranges – or, translating into our problem, matching ins and outs. For the example given, the resulting set will look like this:

ins.qty_from  ins.qty_to  outs.qty_from  outs.qty_to
------------  ----------  -------------  -----------
0             2000        0              30
0             2000        30             1030
0             2000        1030           2010
2000          3800        1030           2010

You can see that the first 'in' range repeats three times. That is because the first 'in' row lends its quantity to three 'out' rows. You can also see that the last 'out' range is repeated too, meaning it borrows from both 'in' rows.

Now that ins and outs are successfully matched, the big issue is to correctly determine the quantity that an 'out' row borrows from an 'in' row in case it matches more than one 'in'. There may be variations on the logic to use, here is one:

  • if ins.qty_to < outs.qty_to,
    • if ins.qty_from > outs.qty_from,
      • take the current ins.qty
    • otherwise,
      • take ins.qty_to - outs.qty_from;
  • otherwise,
    • if outs.qty_from > ins.qty_from,
      • take the current outs.qty
    • otherwise,
      • take outs.qty_to - ins.qty_from.

It only remains to apply your formula to the obtained values, which is essentially SUM(ins.unit_price * borrowed_qty) / outs.qty.

Translating all the above into SQL, we can get a query like this:

SELECT
  outs.id,
  outs.qty,
  ROUND(SUM(x.borrowed_qty * ins.unit_price) / outs.qty, 5) AS unit_price,
  outs.date,
  outs.action
FROM
  (
    SELECT
      *,
      COALESCE(SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'in'
  ) AS ins
  INNER JOIN
  (
    SELECT
      *,
      COALESCE(SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS qty_from,
               SUM(-qty) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS qty_to
    FROM
      yourtable
    WHERE
      action = 'out'
  ) AS outs
  ON  ins.qty_from < outs.qty_to
  AND outs.qty_from < ins.qty_to
  CROSS JOIN LATERAL
  (
    SELECT
      CASE
        WHEN ins.qty_to < outs.qty_to THEN
          CASE
            WHEN ins.qty_from > outs.qty_from THEN -ins.qty
            ELSE -(ins.qty_to - outs.qty_from)
          END
        ELSE
          CASE
            WHEN outs.qty_from > ins.qty_from THEN outs.qty
            ELSE -(outs.qty_to - ins.qty_from)
          END
      END
  ) AS x (borrowed_qty)
GROUP BY
  outs.id,
  outs.qty,
  outs.date,
  outs.action
;

A demo of this solution can be found at Rextester.

  • 1
    Well that's something i've never tried - even close, but i'll start to analyze this whole thing and then i'm going to take it in my context and see if it works in propper way. It will take some time to put all together. I'll let you know if and/or how it worked. – Tiit Remmel Nov 24 '16 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.