Question 319008 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql substract two columns and substract remainder

I have a test CTE as follows:

with test_cte (month, company, probability, expected, actual) as (values
('2024-03','CompA',100,4,13),
('2024-03','CompA',80,7,13),
('2024-03','CompA',50,7,13)
)
select * from test_cte

What I would like to do is add a final column called ‘expected_revised’ which would be ‘expected’ reduced by the amount in ‘actual’ (which itself gets depleted row after row).
The partition should be:

partition by month, company order by probability DESC

The column ‘expected-revised’ should return:

0 (greatest(0,4-13))
0 (greatest(0,7-(13-4)))
5 (greatest(0,7-(13-4-7)))

Any thoughts? I feel i should use a window function for this but can’t figure out how to go about it.

Tags:

2

Answers


  1. 0

    To achieve this, we need to calculate a cumulative sum of actual and then subtract it from expected in a way that the subtraction does not go beyond the total actual value available

    WITH test_cte (month, company, probability, expected, actual) AS (VALUES
    ('2024-03', 'CompA', 100, 4, 13),
    ('2024-03', 'CompA', 80, 7, 13),
    ('2024-03', 'CompA', 50, 7, 13)
),
cumulative AS (
    SELECT
        month,
        company,
        probability,
        expected,
        actual,
        SUM(actual) OVER (PARTITION BY month, company ORDER BY probability DESC) as cumulative_actual
    FROM test_cte
)
SELECT
    month,
    company,
    probability,
    expected,
    actual,
    GREATEST(0, expected - GREATEST(cumulative_actual - SUM(actual) OVER (PARTITION BY month, company ORDER BY probability DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)) AS expected_revised
FROM cumulative;
    Login or Signup to reply.
  2. 0 
    0 (greatest(0,4-13)) => 4 - 13
0 (greatest(0,7-(13-4))) => 7+4 - 13
5 (greatest(0,7-(13-4-7))) => 7+4+7 - 13

    You need to substract actual value from the running total of the expected values :

    WITH test_cte (month, company, probability, expected, actual) AS (VALUES
    ('2024-03', 'CompA', 100, 4, 13),
    ('2024-03', 'CompA', 80, 7, 13),
    ('2024-03', 'CompA', 50, 7, 13)
)
SELECT *,
        GREATEST(0, SUM(expected) OVER (PARTITION BY month, company ORDER BY probability DESC) - actual) AS expected_revised
FROM test_cte;

    Results :

    month   company probability expected    actual  expected_revised
2024-03 CompA   100         4           13      0
2024-03 CompA   80          7           13      0
2024-03 CompA   50          7           13      5

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search