title: Running total of "matches" using a window function in SQL
body: <p>I want to create a window function that will count how many times the value of the field in the current row appears in the part of the ordered partition coming <em>before</em> the current row. To make this more concrete, suppose we have a table like so: </p>
  
  <pre><code>| id| fruit  | date |
  +---+--------+------+
  | 1 | apple  |   1  |
  | 1 | cherry |   2  |
  | 1 | apple  |   3  |
  | 1 | cherry |   4  |
  | 2 | orange |   1  |
  | 2 | grape  |   2  |
  | 2 | grape  |   3  |
  </code></pre>
  
  <p>And we want to create a table like so (omitting the date column for clarity): </p>
  
  <pre><code>| id| fruit  | prior |  
  +---+--------+-------+
  | 1 | apple  |   0   |
  | 1 | cherry |   0   |
  | 1 | apple  |   1   |
  | 1 | cherry |   1   |
  | 2 | orange |   0   |
  | 2 | grape  |   0   |
  | 2 | grape  |   1   |
  </code></pre>
  
  <p>Note that for <code>id = 1</code>, moving along the ordered partition, the first entry 'apple' doesn't match anything (since the implied set is empty), the next fruit, 'cherry' also doesn't match. Then we get to 'apple' again, which is a match and so on. I'm imagining the SQL looks something like this:  </p>
  
  <pre><code>SELECT
  id, fruit, 
  &lt;some kind of INTERSECT?&gt; OVER (PARTITION BY id ORDER by date) AS prior
  FROM fruit_table; 
  </code></pre>
  
  <p>But I cannot find anything that looks right. FWIW, I'm using PostgreSQL 8.4.</p>