title: How can I calculate the rate of entries in a time series in PostgreSQL with a moving time window
body: <p>So I have data like this:</p>
  
  <pre><code>index                      price
  2015-01-08 07:01:18.601318  10
  2015-01-08 07:01:19.100645  10
  2015-01-08 07:01:19.600494  24
  2015-01-08 07:01:20.101432  22
  2015-01-08 07:01:20.102579  235
  2015-01-08 07:01:20.600370  1050
  2015-01-08 07:01:20.603521  100
  2015-01-08 07:01:20.800408  50
  2015-01-08 07:01:21.100629  10
  2015-01-08 07:01:21.601491  12
  2015-01-08 07:01:21.601663  97
  </code></pre>
  
  <p>What I want is to have a moving window of a specified time length behind each entry, say 1 second, and calculate how many entries are within that window. </p>
  
  <p>The result should look something like:</p>
  
  <pre><code>index                      price    rate
  2015-01-08 07:01:18.601318  10       1
  2015-01-08 07:01:19.100645  10       2
  2015-01-08 07:01:19.600494  24       3
  2015-01-08 07:01:20.101432  22       2
  2015-01-08 07:01:20.102579  235      3
  2015-01-08 07:01:20.600370  1050     4
  2015-01-08 07:01:20.603521  100      4
  2015-01-08 07:01:20.800408  50       5
  2015-01-08 07:01:21.100629  10       6
  2015-01-08 07:01:21.601491  12       4
  2015-01-08 07:01:21.601663  97       5
  </code></pre>
  
  <p>This is to calculate the rate at which these entries were entered into the database.</p>
  
  <p>It looks like I need some kind of sliding expanding window but I can't get it to work. </p>
  
  <p>If this is doable with a pandas DataFrame that would also work.
  I know I can group them by seconds using a TimeGrouper in pandas and calculate the count of each group but I'm having other issues there with index duplication and also this removes information:</p>
  
  <pre><code>df['rate'] = df.groupby(pd.TimeGrouper('1s'))['price'].transform('count')
  </code></pre>