Sum over partition by (with changing variables instead of rows)

Database
N/A

Sum over partition by (with changing variables instead of rows)

Hi all, 

I would like to create cummulative sums of "mp_tpv_guest" for the 12 months preceeding each specific month. 

However (and as you can see), my data set only incorporates months when there is data recorded, so the past 12 months does not match with the past 12 rows.

could you help me out with this one?

Best, 

Ferran

mth_id customer_id mp_tpv_guest
1357 3000000000146728503 20.63
1358 3000000000146728503 0
1359 3000000000146728503 0
1360 3000000000146728503 2.31
1365 3000000000146728503 0
1367 3000000000146728503 0
1368 3000000000146728503 57.24
1369 3000000000146728503 0
1370 3000000000146728503 0
1372 3000000000146728503 0
1373 3000000000146728503 4.04
1379 3000000000146728503 4.68
1380 3000000000146728503 54.84
1382 3000000000146728503 76.08
1393 3000000000146728503 19.24

and the output would be:

mth_id customer_id mp_tpv_guest 12m_mp_tpv_guest
1357 3000000000146728503 20.63 20.63
1358 3000000000146728503 0 20.63
1359 3000000000146728503 0 20.63
1360 3000000000146728503 2.31 22.94
1365 3000000000146728503 0 22.94
1367 3000000000146728503 0 22.94
1368 3000000000146728503 57.24 80.18
1369 3000000000146728503 0 59.55
1370 3000000000146728503 0 59.55
1372 3000000000146728503 0 59.55
1373 3000000000146728503 4.04 63.59
1379 3000000000146728503 4.68 68.27
1380 3000000000146728503 54.84 123.11
1382 3000000000146728503 76.08 139.64
1393 3000000000146728503 19.24 150.16
1 REPLY
Senior Apprentice

Re: Sum over partition by (with changing variables instead of rows)

Hi Ferran,

this would be a perfect task for RANGE 12 PRECEDING, but unfortunately Teradata doesn't support it...

Is this for a large number of rows?

As it's a maximum of 12 rows/months you could do a brute force approach like this:

mp_tpv_guest +
CASE WHEN MIN(mth_id) OVER (PARTITION BY customer_id ORDER BY mth_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) >= mth_id -12
THEN MIN(mp_tpv_guest) OVER (PARTITION BY customer_id ORDER BY mth_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
ELSE 0
END +
... +
... +
... +
CASE WHEN MIN(mth_id) OVER (PARTITION BY customer_id ORDER BY mth_id ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING) >= mth_id -12
THEN MIN(mp_tpv_guest) OVER (PARTITION BY customer_id ORDER BY mth_id ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING)
ELSE 0
END