6

I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number.

I have table a

CREATE TABLE a (id int, code text, qty numeric);

And data in it

INSERT INTO a (id,code,qty)
(1,'test',5),
(2,'test',3),
(3,'test',10);

1.sample - When input is 16 - Desired result would be:

id | qty
--------------
1  | 0
2  | 0
3  | 2

Calculation would be something like this

case when 16 > 5 then 16-5 else 5-16 end /* result 11, but displayed 0 */
case when 11 > 3 then 11-3 else 3-11 end /* result 8, but displayed 0 */
case when 8 > 10 then 8-10 else 10-8 end /* result 2 and displayed 2 */

2.sample - When input is 6 - Desired result would be:

id | qty
--------------
1  | 0
2  | 2
3  | 10 /* is optional to display */

Calculation would be something like this

case when 6 > 5 then 6-5 else 5-6 end /* result 1, but displayed 0 */
case when 1 > 3 then 1-3 else 3-1 end /* result 2 and displayed 2 */
case when ... end /* result 10, since input is fulfilled */
9

Fundamentally, this is a running total problem: for each row you are getting the running total of qty and subtracting a fixed value from it. More specifically, this is about how you want to use the running total. In this case you want to display a new qty value based on the result of the previous calculation. And there are essentially three cases here:

  1. The result of the subtraction is negative.
  2. The result of the subtraction exceeds the current row's qty.
  3. The result is a non-negative value not exceeding qty.

The first case means that the supplied fixed value is more than necessary to exhaust the current running total – in other words, it exhausts all qty values from the beginning till the current row (including the current row). Therefore, we substitute a 0 for qty.

In the second case we just keep the qty, because the running total now exceeds the fixed value so much that the difference is greater than the current qty – so the latter stays intact.

The last case is when the fixed value either exactly covers all the qty values so far or is slightly less than the total but not much less: up to the amount of the current qty. In this case we show the difference between the running total and the fixed value.

The SQL for the above is this:

SELECT
  id,
  CASE
    WHEN balance < 0   THEN 0
    WHEN balance > qty THEN qty
    ELSE balance
  END AS qty
FROM
  (
    SELECT
      id,
      qty,
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum AS balance
    FROM
      a
  ) AS derived
;

where @fixedSum is the fixed value you want to subtract from the running total.

The central part of the solution is SUM(qty) OVER (ORDER BY id ASC) – the window aggregate function to get the running total of qty.

I am calculating the running totals here in a nested SELECT because the result of the subtraction needs to be referenced several times in a CASE expression, and you cannot reference a calculated column on the same level, you need to use nesting for that. The nesting can be in the form of either a derived table or a common table expression (CTE). The above solution uses a derived table.

There is a way to avoid nesting, though, and do all the calculations in a single SELECT using the functions LEAST and GREATEST:

SELECT
  id,
  qty,
  LEAST(
    qty,
    GREATEST(
      SUM(qty) OVER (ORDER BY id ASC) - @fixedSum,
      0
    )
  ) AS balance
FROM
  a

This method is much less straightforward than the one using CASE, though. Performance-wise, it would likely work the same.

The method works like this. First, you choose the greater value between the subtraction result and 0 using the GREATEST function. So, in terms of the cases listed above, if we have Case 1, GREATEST will yield 0. A non-zero result would mean we have one of the other cases.

The obtained value is then compared to the current qty and this time the lesser one of the two is chosen using the LEAST function. If we obtained 0 previously, LEAST will keep it. If we had the subtraction result and it is now found to be greater than qty, the LEAST function will give us qty (meaning this is Case 3). And if the difference happens to be less than qty, that is what LEAST will yield (Case 2).

3
  • 1
    Your offered solution with derived table solves my case 100%. Nov 15 '16 at 11:33
  • I did some tests. These 2 offered solutions are bit different in their logic, but in my case the results are same - but not in timing. Seems that with second solution (with LEAST AND GREATES) is query faster. Of course it depends on select scenario. Summary - definitely queries without subquery are always more scalable and clearer. Nov 16 '16 at 7:16
  • 3
    I didn't expect that the LEAST/GREATEST method would be noticeably faster but in the end I'm not very surprised. As for the difference in logic, do you mean you found cases where you got different results using these two methods? I would appreciate it if you gave me an example. I mean I realise the logic may appear different but I thought both variations would ultimately be equivalent and give identical results.
    – Andriy M
    Nov 16 '16 at 9:05

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.