title: SQL rolling window unique count
body: <p>Is there an SQL equivalent to this line of code in pandas?</p>
  
  <p>Assuming a is a DataFrame object and the index is a list of times (with hours, minutes, and seconds).</p>
  
  <p>x in this case would just be the other column in the DataFrame besides the index.</p>
  
  <pre><code>a.rolling('1h').apply(lambda x: len(np.unique(x))).astype(int)
  </code></pre>
  
  <p>Sample result: (Time formatted in HH:MM:SS)</p>
  
  <pre><code>                X
  05:20:19        4   &lt;- 1 (only 1 unique number)
  05:20:19        5   &lt;- 2 (4 and 5 are unique) * same time as before
  05:37:18        7   &lt;- 3 (4, 5 and 7 are unique)
  05:45:14        4   &lt;- 3 (4, 5, and 7)
  05:56:04        4   &lt;- 3 (4, 5, and 7)
  06:18:48        6   &lt;- 4 (now 4, 5, 6, and 7)
  06:48:34        3   &lt;- 3 (only checks past hour, so now 3, 4, 6)
  07:52:48        1   &lt;- 1 (only time in past hour, so only 1)
  </code></pre>
  
  <p>I'm just using vanilla SQL for this as well.</p>
  
  <p>Thanks so much!</p>