CODEWITHSUNDEEP
sqlamazon-redshift
Gil Alpert
Gil Alpert

Reputation: 31

How to apply aggregate addition/arithmetic and multiplication/product based on condition in one column?

Let's assume we have this 'table' here:

Time_Order Logic Number Accumulated
1 Add 20 20
2 Add 30 50
3 Add 50 100
4 Multiply 0.8 80
5 Multiply 0.5 40
6 Add 10 50

Accumulated is the results of Adding or Multiplying based on all the previous records, so in Time_Order 3 we accumulated (50 + 30 + 20) = 100, then in Time_Order 4 I want to multiply by the 0.8 so I get 100 * 0.80 = 80, then Time_Order 5 I multiply the 80 by 0.5 and get 40. I go back to Add in Time Order 6 and get 40 + 10 = 50

I have something like:

Select a.*, case when Logic = 'Add' then sum(Number) over (Order by Time_Order)
            when Logic = 'Multiply' then Exp(Sum(ln(Accumulated * (1+Number)))) as Accumulated
from table a

The above won't work because I have 'Accumulated' within itself in the Multiply logic, so this is the exact problem, when faced with a conditional statement like this, how can i shift back and forth between 'add' and 'multiply' such that the accumulated number is remembered what it is from the previous row.

Upvotes: 1

Views: 74

Answers (2)

Kshitij
Kshitij

Reputation: 76

--Step 1: Identify "Add" and "Multiply" Operations

;WITH Calculation AS (
    SELECT 
        Time_Order,
        Logic,
        Number,
        CASE 
            WHEN Logic = 'Add' THEN SUM(Number) OVER (ORDER BY Time_Order)
            ELSE 1
        END AS Add_Cumulative
    FROM your_table
),
--Step 2: Handle Multiplication Using Logs

Multiplication AS (
    SELECT
        Time_Order,
        Logic,
        Number,
        Add_Cumulative,
        EXP(SUM(LOG(NULLIF(Number, 0))) OVER (ORDER BY Time_Order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS Multiply_Cumulative
    FROM Calculation
)
-- Step 3: Apply Conditions
-- For Add, accumulate using a running sum.
-- For Multiply, apply the cumulative product to the last "Add" total. 
SELECT
    Time_Order,
    Logic,
    Number,
    CASE 
        WHEN Logic = 'Add' THEN Add_Cumulative
        ELSE LAG(Add_Cumulative) OVER (ORDER BY Time_Order) * Multiply_Cumulative
    END AS Accumulated
FROM Multiplication
ORDER BY Time_Order;

--Why this works:
--Cumulative Sum for "Add" ensures we keep a running total.
--Cumulative Product for "Multiply" is calculated using the logarithmic transformation (EXP(SUM(LOG(x)))).
--Row-by-Row Calculation preserves the operation sequence.

Upvotes: 0

Drop_top
Drop_top

Reputation: 39

You can use recursive CTEs in SQL Follow the below example I write a query.

 WITH RECURSIVE Accumulation AS (
    -- Base case: The first row remains the same
    SELECT 
        Time_Order, 
        Logic, 
        Number, 
        Number AS Accumulated
    FROM Example_table
    WHERE Time_Order = 1

    UNION ALL

    -- Recursive step
    SELECT 
        t.Time_Order, 
        t.Logic, 
        t.Number, 
        CASE 
            WHEN t.Logic = 'Add' THEN a.Accumulated + t.Number
            WHEN t.Logic = 'Multiply' THEN a.Accumulated * t.Number
            ELSE a.Accumulated
        END AS Accumulated
    FROM Example_table t
    JOIN Accumulation a 
        ON t.Time_Order = a.Time_Order + 1
 )
 SELECT * FROM Accumulation;

I hope it will help you,Thanks.

Upvotes: 3

Related Questions