title: Can window function LAG reference the column which value is being calculated?
body: <p>I need to calculate value of some column X based on some other columns of the current record and the value of X for the previous record (using some partition and order). Basically I need to implement query in the form</p>
  
  <pre><code>SELECT &lt;some fields&gt;, 
    &lt;some expression using LAG(X) OVER(PARTITION BY ... ORDER BY ...) AS X
  FROM &lt;table&gt;
  </code></pre>
  
  <p>This is not possible because only existing columns can be used in window function so I'm looking way how to overcome this.</p>
  
  <p>Here is an example. I have a table with events. Each event has <code>type</code> and <code>time_stamp</code>.</p>
  
  <pre><code>create table event (id serial, type integer, time_stamp integer);
  </code></pre>
  
  <p>I wan't to find "duplicate" events (to skip them). By duplicate I mean the following. Let's order all events for given <code>type</code> by <code>time_stamp</code> ascending. Then</p>
  
  <ol>
  <li>the first event is not a duplicate</li>
  <li>all events that follow non duplicate and are within some time frame  after it (that is their <code>time_stamp</code> is not greater then <code>time_stamp</code> of the previous non duplicate plus some constant <code>TIMEFRAME</code>) are duplicates</li>
  <li>the next event which <code>time_stamp</code> is greater than previous non duplicate by more than <code>TIMEFRAME</code> is not duplicate</li>
  <li>and so on</li>
  </ol>
  
  <p>For this data </p>
  
  <pre><code>insert into event (type, time_stamp) 
   values 
    (1, 1), (1, 2), (2, 2), (1,3), (1, 10), (2,10), 
    (1,15), (1, 21), (2,13), 
    (1, 40);
  </code></pre>
  
  <p>and <code>TIMEFRAME=10</code> result should be</p>
  
  <pre><code>time_stamp | type | duplicate
  -----------------------------
          1  |    1 | false
          2  |    1 | true     
          3  |    1 | true 
         10  |    1 | true 
         15  |    1 | false 
         21  |    1 | true
         40  |    1 | false
          2  |    2 | false
         10  |    2 | true
         13  |    2 | false
  </code></pre>
  
  <p>I could calculate the value of <code>duplicate</code> field based on current <code>time_stamp</code> and <code>time_stamp</code> of the previous non-duplicate event like this:</p>
  
  <pre><code>WITH evt AS (
    SELECT 
      time_stamp, 
      CASE WHEN 
        time_stamp - LAG(current_non_dupl_time_stamp) OVER w &gt;= TIMEFRAME
      THEN 
        time_stamp
      ELSE
        LAG(current_non_dupl_time_stamp) OVER w
      END AS current_non_dupl_time_stamp
    FROM event
    WINDOW w AS (PARTITION BY type ORDER BY time_stamp ASC)
  )
  SELECT time_stamp, time_stamp != current_non_dupl_time_stamp AS duplicate
  </code></pre>
  
  <p>But this does not work because the field which is calculated cannot be referenced in <code>LAG</code>:</p>
  
  <pre><code>ERROR:  column "current_non_dupl_time_stamp" does not exist.
  </code></pre>
  
  <p>So the question: can I rewrite this query to achieve the effect I need?</p>