title: Add new column as the difference between two rows
body: <p>I work with daily time series in PostgreSQL.
  My goal is to add a new variable to my table, which computes the difference between the value of a certain column for two adjacent dates for the same company. (You can e.g. think of a growth rate)</p>
  
  <p>We can think of the following example data:</p>
  
  <pre><code>  date company value
  
    2012-01-01       A  5
    2012-01-01       B  2
    2012-01-01       C  3
    2012-01-02       A  4
    2012-01-02       B  2
    2012-01-02       C  4
    2012-01-03       A  7
    2012-01-03       B  2
    2012-01-03       C  2
  </code></pre>
  
  <p>The optimal result I want to achieve is:</p>
  
  <pre><code>   date      company value  diff
    2012-01-01       A  5     NULL
    2012-01-01       B  2     NULL
    2012-01-01       C  3     NULL
    2012-01-02       A  4     -1
    2012-01-02       B  2      0
    2012-01-02       C  4      1
    2012-01-03       A  7      3
    2012-01-03       B  2      0 
    2012-01-03       C  2     -2
  </code></pre>
  
  <p>I tried the following (probably very cumbersome) approach:</p>
  
  <ol>
  <li><p>Added an autoincrement primary key to identify each row</p>
  
  <pre><code>CREATE SEQUENCE id_seq;
  ALTER TABLE mytable ADD id INT UNIQUE;
  ALTER TABLE mytable ALTER COLUMN id SET DEFAULT NEXTVAL('id_seq');          
  UPDATE mytable SET id = NEXTVAL('id_seq');
  ALTER TABLE mytable ADD PRIMARY KEY (id);
  </code></pre></li>
  <li><p>Computed the differences in the following way:</p>
  
  <pre><code>CREATE TABLE change AS WITH d AS (SELECT value, id FROM temp)
  SELECT d.value - d2.value AS change FROM d d
  JOIN d d2 ON d.id = d2.id + 1;
  </code></pre></li>
  </ol>
  
  <p>Unfortunately that code doesn't manage to distinguish between the different companies and computes the differences between all adjacent rows. Furthermore I don't know how to add it to the existing table, since it doesn't fill in the first value with NULL but skips it and starts with the second row, such that the resulting table is one row too short.</p>
  
  <p>Can you think of any other effective approach to find a solution?</p>