Search code examples
sqlcumulative-sumsumproduct

How calculate previous cumulative product in SQL (detail below)

I'm trying to estimate a new scrap rate (loss factor) in a production line using SQL.

Basically, there are several operation in one machine, with qty in and qty out for each one of the operation.

The following operation has, as qty in, the qty out of the previous operation.

And this scrap rate (loss factor) needs to be carry over the operation.

So, operation 1 will have qty out / qty in from operation 1 = scrap rate 1; operation 2 has qty out / qty in * scrap rate 1; and so on.

I know I can use "exp(sum(log(column)) OVER (ORDER BY column))" to get the overall, lets say machine scrap rate, but I need to have an cumulative per machine-operation level.

Hope the image attached can explain better the outcome.

I'm struggling to calculate the column G (OutFactorAccumulated) in the image. Hope someone can help me.

Data and expected results example

1

calculate cumulative product

Solution

  • I think you are most of the way there.

    The final step is simply to take the OutfactorAccumulated column, and do a similar windowed function over it to calculate the next column e.g., MIN(OutFactorAccumulated) OVER (PARTITION BY Machine).

    Note also that the other windowed function (the SUM) should also have a PARTITION BY Machine in the window to ensure that each machine only uses its own data.

    Here is a db<>fiddle with the example code below in SQL Server/T-SQL.

    • The last CTE 'MachineData_with_ExpQtyOut` is the one that has the windows MIN function to do the calculation.
    • In the Fiddle I have also added a second machine B1 with some data I made up - to demonstrate it works with multiple machines.

    (Note lots of CAST AS decimal(14,10) to match your data - there's probably a better way to do this).

    CREATE TABLE #MachData (Machine nvarchar(10), Operation int, QtyIn int, QtyOut int, PRIMARY KEY (Machine, Operation));
INSERT INTO #MachData (Machine, Operation, QtyIn, QtyOut) VALUES
(N'A1', 1, 100, 100),
(N'A1', 2, 100, 95),
(N'A1', 3, 95,  95),
(N'A1', 4, 95,  94),
(N'A1', 5, 94,  86),
(N'A1', 6, 86,  66),
(N'A1', 7, 66,  66),
(N'A1', 8, 66,  66),
(N'A1', 9, 66,  66);

WITH MachData_with_Factors AS
    (SELECT     Machine, 
                Operation, 
                QtyIn, 
                QtyOut, 
                CAST(1 - CAST(QtyOut AS decimal(14,10))/CAST(QtyIn AS decimal(14,10)) AS decimal(14,10)) AS LossFactor,
                CAST(CAST(QtyOut AS decimal(14,10))/CAST(QtyIn AS decimal(14,10)) AS decimal(14,10)) AS OutFactor
        FROM    #MachData   
    ),
    MachineData_with_Acc AS
    (SELECT     *,
                CAST(exp(SUM(log(OutFactor)) OVER (PARTITION BY Machine ORDER BY Operation)) AS decimal(14,10)) AS OutFactorAccumulated
        FROM    MachData_with_Factors
    ),
    MachineData_with_ExpQtyOut AS
    (SELECT     *,
                CAST(OutFactorAccumulated * 100.0 / MIN(OutFactorAccumulated) OVER (PARTITION BY machine) AS decimal(14,10)) AS NewExpectedQtyOut
        FROM    MachineData_with_Acc
    )
SELECT *
    FROM    MachineData_with_ExpQtyOut
    ORDER BY Machine, Operation;

    Results are as below

    Machine  Operation  QtyIn       QtyOut  LossFactor    OutFactor     OutFactorAccumulated  NewExpectedQtyOut
------------------------------- ---------------------------------------------------------------------------
A1       1          100         100     0.0000000000  1.0000000000  1.0000000000          151.5151515152
A1       2          100         95      0.0500000000  0.9500000000  0.9500000000          143.9393939394
A1       3          95          95      0.0000000000  1.0000000000  0.9500000000          143.9393939394
A1       4          95          94      0.0105263158  0.9894736842  0.9400000000          142.4242424242
A1       5          94          86      0.0851063830  0.9148936170  0.8600000000          130.3030303030
A1       6          86          66      0.2325581395  0.7674418605  0.6600000000          100.0000000000
A1       7          66          66      0.0000000000  1.0000000000  0.6600000000          100.0000000000
A1       8          66          66      0.0000000000  1.0000000000  0.6600000000          100.0000000000
A1       9          66          66      0.0000000000  1.0000000000  0.6600000000          100.0000000000