CODEWITHSUNDEEP
sqlvertica
Jalal.Hassan
Jalal.Hassan

Reputation: 47

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

Upvotes: 0

Views: 482

Answers (2)

Jalal.Hassan
Jalal.Hassan

Reputation: 47

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

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

Vertica does not have (yet?) the RECURSIVE WITH clause, which you would need for the recursive calculation you seem to be needing here.

Only possible workaround would be tedious: write (or generate, using perl or Python, for example) as many nested queries as you need iterations.

I'll only want to detail this if you want to go down that path.

Upvotes: 1

Related Questions