title: Math with previous row in SQL, avoiding nested queries?
body: <p>I want to do some math on the previous rows in an SQL request in order to avoid doing it in my code.</p>
  
  <p>I have a table representing the sales of two entities (the data represented here is doesn't make much sense and it's just an excerpt) : </p>
  
  <pre><code>YEAR    ID      SALES             PURCHASE         MARGIN
  2009    1       10796820,57       2662369,19       8134451,38
  2009    2        2472271,53       2066312,34        405959,19
  2008    1        9641213,19       1223606,68       8417606,51
  2008    2        3436363,86       2730035,19        706328,67
  </code></pre>
  
  <p>I want to know how the sales, purchase, margin... have evolved and compare one year to the previous one.</p>
  
  <p>In short I want an SQL result with the evolutions pre-computed like this :</p>
  
  <pre><code>YEAR    ID    SALES         SALES_EVOLUTION    PURCHASE      PURCHASE_EVOLUTION  MARGIN        MARGIN_EVOLUTION
  2009    1     10796820,57   11,99              2662369,19    117,58              8134451,38     -3,36
  2009    2      2472271,53   -28,06             2066312,34    -24,31               405959,19    -42,53
  2008    1      9641213,19                      1223606,68                        8417606,51 
  2008    2      3436363,86                      2730035,19                         706328,67 
  </code></pre>
  
  <p>I could do some ugly stuff :</p>
  
  <pre><code>SELECT *, YEAR, ID, SALES , (SALES/(SELECT SALES FROM TABLE WHERE YEAR = OUTER_TABLE.YEAR-1 AND ID = OUTER_TABLE.ID) -1)*100 as SALES_EVOLUTION (...) 
  FROM TABLE as OUTER_TABLE 
  ORDER BY YEAR DESC, ID ASC
  </code></pre>
  
  <p>But I have arround 20 fields for which I would have to do a nested query, meaning I would have a very huge and ugly query.</p>
  
  <p>Is there a better way to do this, with less SQL ?</p>