title: SQL function to run a counter that increases when a flag changes?
body: <p>I have data that looks like the first three columns, and I'd like to add the fourth column for Cycle, which is null when the Flag is zero, and otherwise counts the occurrence of that flag for a given x, ordered by the date. I'm using db2 and I'm not sure how to modify a row_number() function to get this result.</p>
  
  <pre><code>  name  date           Flag        Cycle
      x     2014            0          Null
      x     2015            0          Null
      x     2016            1          1
      x     2017            1          1
      x     2018            0          Null
      x     2019            1          2
      x     2020            0          Null
      x     2021            1          3 
      y     2014            0          Null
      y     2016            1          1
      y     2017            0          Null
      y     2018            1          2
  </code></pre>
  
  <p>I'm looking for something that behaves like the Rank function, but allows a tie-ranking based on the duplicates in the partition statement and not the order by.</p>