CODEWITHSUNDEEP
sql
Tanakorn Taweepoka
Tanakorn Taweepoka

Reputation: 197

How to aggregrate over time elasped in Netezza sql?

I use Netezza and I have table like this.

cert_id       date        value  
--------   ------------   ------
01          2018-01-01     2            
01          2018-01-02     1
01          2018-01-03     3 
02          2018-02-06     2
02          2018-02-07     1
02          2018-02-08     4
02          2018-02-09     6

And I want this aggregate(over time) table to be like this.

cert_id       date        value  
--------   ------------   ------
01          2018-01-01     2            
01          2018-01-02     3
01          2018-01-03     6 
02          2018-02-06     2
02          2018-02-07     3
02          2018-02-08     7
02          2018-02-09     13

Upvotes: 0

Views: 42

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

One approach uses a correlated subquery to find the rolling sums:

SELECT
    cert_id,
    date,
    "value",
    (SELECT SUM(t2."value") FROM yourTable t2
     WHERE t1.cert_id = t2.cert_id AND t2.date <= t1.date) rolling_sum
FROM yourTable t1
ORDER BY
    cert_id,
    date;

enter image description here

Demo

If Netezza supports analytic functions, then here is an even simpler query:

SELECT
    cert_id,
    date,
    "value",
    SUM(value) OVER (PARTITION BY cert_id ORDER BY date) rolling_sum
FROM yourTable;

Upvotes: 1

Related Questions