title: How to reset internal state value in aggregate function in PostgreSQL
body: <p>Right now I have an aggregate function like below:</p>
  <pre><code>create or replace function ema_func(state double precision, inval double precision, alpha double precision) 
      returns double precision as $body$
  begin
      return case
          when state is null then inval
          else alpha * inval + (1-alpha) * state end;
  end; $body$
  language plpgsql;
  create aggregate ema(double precision, double precision) (sfunc = ema_func, stype = double precision);
  </code></pre>
  <p>The desired result for each row is: alpha * <code>close price now</code> + (1-alpha) * <code>last result</code> (alpha is a constant).</p>
  <p>The function is working fine. However, I want to reset the <code>state</code> value in aggregate function when column <code>Valid_invalid</code> is <code>0</code>. Is this possible?</p>
  <p>For example,</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Group</th>
  <th>Close_price</th>
  <th>Time</th>
  <th>Valid_invalid</th>
  <th>Result</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>0.4</td>
  <td>2021-08-15 12:00</td>
  <td>0</td>
  <td>0.4</td>
  </tr>
  <tr>
  <td>1</td>
  <td>0.7</td>
  <td>2021-08-15 12:01</td>
  <td>1</td>
  <td><code>result1</code></td>
  </tr>
  <tr>
  <td>1</td>
  <td>0.2</td>
  <td>2021-08-15 12:02</td>
  <td>1</td>
  <td><code>result2</code></td>
  </tr>
  <tr>
  <td>1</td>
  <td>0.3</td>
  <td>2021-08-15 12:03</td>
  <td>0</td>
  <td>0.3</td>
  </tr>
  <tr>
  <td>1</td>
  <td>0.9</td>
  <td>2021-08-15 12:04</td>
  <td>1</td>
  <td><code>result3</code></td>
  </tr>
  <tr>
  <td>2</td>
  <td>0.5</td>
  <td>2021-08-15 12:00</td>
  <td>0</td>
  <td>0.5</td>
  </tr>
  <tr>
  <td>2</td>
  <td>0.8</td>
  <td>2021-08-15 12:01</td>
  <td>1</td>
  <td><code>result4</code></td>
  </tr>
  <tr>
  <td>2</td>
  <td>0.1</td>
  <td>2021-08-15 12:02</td>
  <td>1</td>
  <td><code>result5</code></td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected results (alpha is a constant):<br />
  <code>result1</code> = <code>alpha</code> * 0.7 + <code>1-alpha</code> * 0.4<br />
  <code>result2</code> = <code>alpha</code> * 0.2 + <code>1-alpha</code> * <code>result1</code><br />
  <code>result3</code> = <code>alpha</code> * 0.9 + <code>1-alpha</code> * 0.3<br />
  (with the aggregate function now: <code>result3</code> = <code>alpha</code> * 0.9 + <code>1-alpha</code> * <code>result2</code>)<br />
  <code>result4</code> = <code>alpha</code> * 0.8 + <code>1-alpha</code> * 0.5<br />
  <code>result5</code> = <code>alpha</code> * 0.1 + <code>1-alpha</code> * <code>result4</code></p>
  <p>I've tried the recursive query before (<code>with recursive</code>) and I can reset the state value perfectly, but the data keeps coming in each minute and right now it's running slow. I think the aggregate function is faster. Is there any way to reset the state value in aggregate function? Or is there another efficient way for recursive calculation? I'd appreciate any help.</p>
  <p>---- Edit: separation line ---- <br />
  My attempt so far (table name: <code>test</code>):</p>
  <pre><code>with tmp9 as (
  select &quot;Group&quot;, &quot;Close_price&quot;, &quot;Time&quot;, 
      ema(&quot;Close&quot;::double precision, (2.0/(12.0+1.0)) /* alpha */) 
      over (partition by &quot;Symbol&quot; order by &quot;Time&quot; asc) as result
  from test where &quot;Valid_invalid&quot;=1
  )
  update test set &quot;Result&quot; = tmp9.result from tmp9
  where tmp9.&quot;Group&quot; = test.&quot;Group&quot; and tmp9.&quot;Time&quot; = test.&quot;Time&quot;;
  </code></pre>
  <p>Right now I can't reset the internal value when the row's <code>Valid_invalid</code> is 0.</p>