de-aggregate for table columns in Greenplum

Question

I am using Greenplum, and I have data like:

 id | val 
----+-----
 12 |  12 
 12 |  23 
 12 |  34 
 13 |  23 
 13 |  34 
 13 |  45 
(6 rows)  

somehow I want the result like:

 id | step 
----+-----
 12 |  12 
 12 |  11 
 12 |  11 
 13 |  23 
 13 |  11 
 13 |  11 
(6 rows)  

How it comes:

First there should be a Window function, which execute a de-aggreagte function based on partition by id

the column val is cumulative value, and what I want to get is the step values.

Maybe I can do it like:

select deagg(val) over (partition by id) from table_name;

So I need the deagg function.

Thanks for your help!

P.S and Greenplum is based on postgresql v8.2

Answer 1

You can just use the LAG function:

SELECT id,
    val - lag(val, 1, 0) over (partition BY id ORDER BY val) as step
FROM yourTable

Note carefully that lag() has three parameters. The first is the column for which to find the lag, the second indicates to look at the previous record, and the third will cause lag to return a default value of zero.

Here is a table showing the table this query would generate:

 id | val | lag(val, 1, 0) | val - lag(val, 1, 0)
----+-----+----------------+----------------------
 12 | 12  |      0         |        12
 12 | 23  |      12        |        11
 12 | 34  |      23        |        11
 13 | 23  |      0         |        23
 13 | 34  |      23        |        11
 13 | 45  |      34        |        11

Second note: This answer assumes that you want to compute your rolling difference in order of val ascending. If you want a different order you can change the ORDER BY clause of the partition.