title: Alternative negative and positive values
body: <p>I have a table called <code>PROD_COST</code> with 5 fields:</p>
  
  <p><code>(ID, Duration, Cost, COST_NEXT, COST_CHANGE)</code></p>
  
  <p>I need extra field called <code>Groups</code> for aggregation.</p>
  
  <ol>
  <li>Duration = number of days the price is valid (1 day = 1row).</li>
  <li>Cost = product price in this day.</li>
  <li>-Cost_next = lead(cost,1,0).</li>
  <li>Cost_change = Cost_next - Cost.</li>
  </ol>
  
  <p>.</p>
  
  <pre><code>+ ID + Duration + Cost + Next_Cost + Cost_change + Groups+
  |  1 | 1        | 10   | 8.5       | -1.5        | 1     |
  |  2 | 1        | 8.5  | 12.2      | 3.7         | 2     |
  |  3 | 1        | 12.2 | 5.3       | -6.9        | 3     |
  |  4 | 1        | 5.3  | 4.2       | 1.2         | 4     |
  |  5 | 1        | 4.2  | 6.2       | 2           | 4     |
  |  6 | 1        | 6.2  | 9.2       | 3           | 4     |
  |  7 | 1        | 9.2  | 7.5       | -2.7        | 5     |
  |  8 | 1        | 7.5  | 6.2       | -1.3        | 5     |
  |  9 | 1        | 6.2  | 6.3       | 0.1         | 6     |
  | 10 | 1        | 6.3  | 7.2       | 0.9         | 6     |
  | 11 | 1        | 7.2  | 7.5       | 0.3         | 6     |
  | 12 | 1        | 7.5  | 0         | 7.5         | 6     |
  +----+----------+------+-----------+-------------+-------+
  </code></pre>
  
  <p>I need to group <code>Groups</code> field by <code>Cost_change</code>. It can be positive, negative or zero.</p>
  
  <p>Some kind guys advised me to use this code:</p>
  
  <pre><code>Select 
      id
      , COST_CHANGE
      , sum(Groups) over (order by id asc) +1
  from 
      (
      select 
          pc.*,
          (case when sign(cost_change) - sign(lag(cost_change) over (order by id)) between -1 and 1
          then 0
          else 1 -- `NULL` intentionally goes here
          end) Groups
      from Prod_Cost
      ) pc
  </code></pre>
  
  <p>But there is one problem: if there are 0 values between two positive/negative or negative/positive values then it groups it together, for example:</p>
  
  <pre><code> Cost_change Groups
  | -5.279    | 33   |
  |  5.279    | 34   |
  |  0.000    | 34   |
  | -5.279    | 34   |
  |  0.000    | 34   |
  |  5.279    | 34   |
  | -8.769    | 35   |
  </code></pre>
  
  <p>I need to have:</p>
  
  <pre><code> Cost_change Groups 
  | -5.279    | 33   |
  |  5.279    | 34   |
  |  0.000    | 34   |
  | -5.279    | 35   |
  |  0.000    | 35   |
  |  5.279    | 36   |
  | -8.769    | 37   |
  </code></pre>
  
  <p>Second example:</p>
  
  <pre><code> Cost_change Groups
  |  7.574    | 68   |
  |  0.000    | 68   |
  | -5.279    | 68   |
  | -3.490    | 68   |
  </code></pre>
  
  <p>But I need:</p>
  
  <pre><code> Cost_change Groups
  |  7.574    | 68   |
  |  0.000    | 68   |
  | -5.279    | 69   |
  | -3.490    | 69   |
  </code></pre>
  
  <p>I would be very grateful for any help.</p>