title: Reset counter on window functions
body: <p>I have a dataset like the below and I want to create a new column C that acts like a counter/row number which should get reset every time column B has 0 partitioned by column value of A</p>
  
  <p>Using SparkSQL / SQL only (I can do it using Pyspark)</p>
  
  <pre><code>&gt;&gt;&gt; rdd = sc.parallelize([
  ...     [1, 0], [1, 1],[1, 1], [1, 0], [1, 1],
  ...     [1, 1], [2, 1], [2, 1], [3, 0], [3, 1], [3, 1], [3, 1]])
  &gt;&gt;&gt; df = rdd.toDF(['A', 'B'])
  &gt;&gt;&gt; 
  &gt;&gt;&gt; df.show()
  +---+---+
  |  A|  B|
  +---+---+
  |  1|  0|
  |  1|  1|
  |  1|  1|
  |  1|  0|
  |  1|  1|
  |  1|  1|
  |  2|  1|
  |  2|  1|
  |  3|  0|
  |  3|  1|
  |  3|  1|
  |  3|  1|
  +---+---+
  </code></pre>
  
  <p>What I would like to achieve</p>
  
  <pre><code>+---+---+---+
  |  A|  B|  C|
  +---+---+---+
  |  1|  0|  1|
  |  1|  1|  2|
  |  1|  1|  3|
  |  1|  0|  1|
  |  1|  1|  2|
  |  1|  1|  3|
  |  2|  1|  1|
  |  2|  1|  2|
  |  3|  0|  1|
  |  3|  1|  2|
  |  3|  1|  3|
  |  3|  1|  4|
  +---+---+---+
  </code></pre>
  
  <p>What I have so far</p>
  
  <pre><code>&gt;&gt;&gt; spark.sql('''
  ...    select *, row_number() over(partition by A order by A) as C from df
  ...    ''').show()
  
      +---+---+---+
      |  A|  B|  C|
      +---+---+---+
      |  1|  0|  1|
      |  1|  1|  2|
      |  1|  1|  3|
      |  1|  0|  4|
      |  1|  1|  5|
      |  1|  1|  6|
      |  3|  0|  1|
      |  3|  1|  2|
      |  3|  1|  3|
      |  3|  1|  4|
      |  2|  1|  1|
      |  2|  1|  2|
      +---+---+---+
  </code></pre>