Search code examples
sqlvertica

Creating a Nested/Loop Calculation in Vertica (?)

So maybe I'm just way over-thinking things, but is there any way to replicate a nested/loop calculation in Vertica with just SQL syntax.

Explanation -

In Column AP I have remaining values per month by an attribute key, in column CHANGE_1M I have an attribution value to apply.

The goal is for future values to calculate the preceding Row partition AP*CHANGE_1M, by the subsequent row partition CHANGE_1M to fill in the future AP values.

For reference I have 15,000 Keys Per Period and 60 Periods Per Year in the full-data set.

Sample Calculation

Period 5 = (Period4_AP * Period5_CHANGE_1M)+Period4_AP

Period 6 = (((Period4_AP * Period5_CHANGE_1M)+Period4_AP)*Period6_CHANGE_1M) + ((Period4_AP * Period5_CHANGE_1M)+Period4_AP)

ect.

Sample Data on Top

Expected Results below

Sample Data

Result Set

Solution

  • Long time no see - I should have returned to answer this question earlier.

    I got so stuck on thinking of the programmatic way to solve this issue, I inherently forgot it is a math equation, and where you have math functions you have solutions.

    Basically this question revolves around doing table multiplication.

    The solution is to simply use LOG/LN functions to multiply and convert back using EXP.

    Snippet of the simple solve.

    Hope this helps other lost souls, don't forget your math background and spiral into a whirlpool of self-defeat.

    EXP(SUM(LN(DEGREDATION)) OVER (ORDER BY PERIOD_NUMBER ASC ROWS UNBOUNDED PRECEDING)) AS DEGREDATION_RATE

    ** Controlled by what factors/attributes you need the data stratified by with a PARTITION

    Basically instead of starting at the retention PX/P0, I back into with the degradation P1/P0 - P2/P1 ect.

    PERIOD_NUMBER DEGRADATION DEGREDATION_RATE DEGREDATION_RATE x 100000
    0 100.00% 100.00% 100000.00
    1 57.72% 57.72% 57715.18
    2 60.71% 35.04% 35036.59
    3 70.84% 24.82% 24820.66
    4 76.59% 19.01% 19009.17
    5 79.29% 15.07% 15071.79
    6 83.27% 12.55% 12550.59
    7 82.08% 10.30% 10301.94
    8 86.49% 8.91% 8910.59
    9 89.60% 7.98% 7984.24
    10 86.03% 6.87% 6868.79
    11 86.00% 5.91% 5907.16
    12 90.52% 5.35% 5347.00
    13 91.89% 4.91% 4913.46
    14 89.86% 4.41% 4414.99
    15 91.96% 4.06% 4060.22
    16 89.36% 3.63% 3628.28
    17 90.63% 3.29% 3288.13
    18 92.45% 3.04% 3039.97
    19 94.95% 2.89% 2886.43
    20 92.31% 2.66% 2664.40
    21 92.11% 2.45% 2454.05
    22 93.94% 2.31% 2305.32
    23 89.66% 2.07% 2066.84
    24 94.12% 1.95% 1945.26
    25 95.83% 1.86% 1864.21
    26 92.31% 1.72% 1720.81
    27 96.97% 1.67% 1668.66
    28 90.32% 1.51% 1507.18
    29 90.00% 1.36% 1356.46
    30 94.44% 1.28% 1281.10
    31 94.12% 1.21% 1205.74
    32 100.00% 1.21% 1205.74
    33 90.91% 1.10% 1096.13
    34 90.00% 0.99% 986.52
    35 94.44% 0.93% 931.71
    36 100.00% 0.93% 931.71