0

I need to calculate - let's say - item prices based on purchase price and date (FIFO/LIFO). So, let's say I've made theese purchases:

  • 3 x ABC for $100 at 2017-11-10
  • 2 x ABC for $80 at 2017-11-12
  • 5 x XYZ for $120 at 2017-11-11
  • 7 x XYZ for $110 at 2017-11-12

Items first bought determine final product price, so ABC will cost $100 + markup for first 3 items sold, and $80 + markup for another 2.

What I need now is a sort of cache table, that should look like so:

SKU  price  max_qty
ABC    100        3
ABC     80        5
XYZ    120        5
XYZ    110       12

Please note that third column contains not purchased quantity, but rather a sort of "cap" of quantity to which this price is referring. That way I can quickly estimate sell price for 4 items of ABC by doing a simple JOIN on max_qty >= basket_qty. This is important as there will be much more reads than writes to this table.

What i'm struggling with is generating such table in a manner regarding performance and resource cost. I can't do it in a "query per sku" manner, as there can be quite a lot of records to update at a time. I've tried using MySQL variables;

SELECT
    sku,
    price,
    @r := @r + qty AS max_qty
FROM
    (SELECT @r := 0) AS r
        JOIN
    warehouse_items
GROUP BY
    sku
ORDER BY
    sku, date_in

This resultset would be easly inserted into cache table, but @r doesn't reset for each new sku in the result.

Koval
  • 145
  • 8
  • While I am struggling to understand your business model or your descriptions - but "let's say" usually implies you are doing something different, so I guess suggestion different ideas would be out of place - you can reset @r with e.g. `@r := if(sku=@lastsku, @r + qty,qty) AS max_qty, @lastsku=sku` ... `select @r := 0, @lastsku = ''` (although technically, MySQL is allowed to evaluate the two statements in any order) – Solarflare Nov 23 '17 at 08:00
  • @Solarflare That's actually a good idea, but - as You said - statement order is very much in question here. And yes, other solutions are VERY welcome :) – Koval Nov 23 '17 at 08:36
  • That was just a remark and not supposed to prevent you from using it - practically, it will work and is the standard solution for groupwise running totals (for mysql <8.0; 8.0 has windows functions). If you don't like that: you will *definitely* don't like what MySQL is allowed to do to your price with your current usage of `group by` (which I didn't see before, and which has to be removed to get your sample output table): it's allowed to use a price of *any* random row - and in contrast to variable order, MySQL *will* actually do that (and fail to work in 5.7 with default settings). – Solarflare Nov 23 '17 at 09:32
  • And just for completeness: MySQL is technically not forced to evaluate the `join` with the init of `@r` before it evaluates the `@r := @r ...`-part. So `@r` could have some random init value (or `null`). Just as another example of how much variable usage rely on implicit "let's hope it works, as it has always worked that way"-expectations - so you should expect that the default groupwise running total code will work too. You could do it without variables, but your code will get longer and slower. Ultimately, you need a workaround for missing window functions - and variables are used for this. – Solarflare Nov 23 '17 at 09:52
  • @Solarflare Yep, You're absolutely correct. `GROUP BY` war originally by `sku` AND `price` since that's what we're after; this example is overly simplified for purposes of simplicity - hence "let's say" :) Yet another problem has risen i've just thought of; since we're grouping by sku and price, 3 batches with prices 100, 80 and 100 would be grouped incorectly. Back to the drawing board I guess :( – Koval Nov 23 '17 at 10:13
  • You could add a 3rd variable @price and a 4th variable @groupid (that you increase by 1 every time you start a new group). You will e.g. get rows like `(groupid, sku, price, qty, max_qty) (1 ABC 100 1 1),...,(1 ABC 100 1 3),(2 ABC 80 1 1)` and so on). Then use group by groupid on that subquery. Anyway, you may want to rethink your pricing model, it sounds a bit "unconventional": you may never sell any items anymore simply because you have one for 100 left - as everyone else can offer it for 20 less, and noone likes to be the one paying more to allow the next customer to pay less (=new normal). – Solarflare Nov 23 '17 at 10:55
  • @Solarflare This model is for calculating Your purchase price - accordingly to local laws. You can't sell anything below Your purchase price (anti-dumping laws). On top of that, clients can have both custom discounts and markups, so final client price has to be based on something. Anyway, I've decided to move some of the logic to the application layer, as it will be less complicated and probably more efficient in the long run. Thank You for Your help :) – Koval Nov 23 '17 at 14:11

0 Answers0