title: Emulate the window function of SQL in R
body: <p>I have a table as following:</p>
  
  <pre><code>id   name  amount  year
  001  A     10      2010
  001  A     10      2011
  001  A     12      2012
  -----------------------
  002  A     3       2012
  002  A     4       2013
  -----------------------
  003  B     20      2011
  003  B     20      2012
  </code></pre>
  
  <p>(Note two entities have the same name <code>A</code> but they are different, <code>id</code> is the unique identifier.)</p>
  
  <p>I want to compute the increment in <code>amount</code> over the previous year, i.e. the result should look like:</p>
  
  <pre><code>id   name   increment   year
  001  A      0           2010
  001  A      0           2011
  001  A      2           2012
  ----------------------------
  002  A      0           2012
  002  A      1           2013
  ----------------------------
  003  B      0           2011
  003  B      0           2012
  </code></pre>
  
  <p>Note that the increment of the first year is considered "0".</p>
  
  <hr>
  
  <p>In MSSQL, it can implemented by:</p>
  
  <pre><code>SELECT id,
         name,
         amount - LAG(amount, 1, amount) OVER (PARTITION BY id ORDER BY YEAR) as increment,
         year
  FROM table
  </code></pre>
  
  <hr>
  
  <p>I am trying to accomplish the task in R with data.table. I found an succinct example <a href="https://stackoverflow.com/questions/14689424/use-a-value-from-the-previous-row-in-an-r-data-table-calculation">here</a>:<br>
  <code>DT[, increment := amount - shift(amount, 1), by=id]</code>. But error was prompted: <code>could not find function "shift"</code>. </p>
  
  <p>The versions are:</p>
  
  <ul>
  <li>R: 3.2.0_1</li>
  <li>data.table: 1.9.4</li>
  </ul>
  
  <p>The questions are:</p>
  
  <ol>
  <li>I found the <code>shift</code> function implemented on data.table's Github, why I failed to invoke the function?</li>
  <li>I think that <code>by</code> in data.table is equivalent to <code>PARTITION BY</code> in SQL, then what is the counterpart of <code>ORDER BY</code> in R? Do I have to set the key of data.table before carrying out any aggregation so the data.table is ordered?</li>
  </ol>