title: BigQuery - How to create a new column where the calculation includes the new column itself?
body: <p><strong>---Summary---</strong></p>
  
  <p>I have three columns: [visitorID], [rank], [numbers].</p>
  
  <p>In BigQuery,
  I would like to create a new column [calculation],
  which is part of the summation of [numbers] and [calculation] itself, incluing specified conditions.</p>
  
  <p>The problem I an encountering now is that "In BigQuery, I cannot create a column that needs the calculation including the column I am creating".
  I am not sure whether my concept or idea is suitable or not,
  and I hope there are some better suggestions.</p>
  
  <p><strong>---Details---</strong></p>
  
  <p><strong>*The Table I have:</strong></p>
  
  <p>A table with three columns: [visitorID], [rank], [numbers].</p>
  
  <p><strong>*The New Column I need to create:</strong></p>
  
  <p>Need to create the column [calculation].</p>
  
  <p><strong>*The definition of calculation:</strong></p>
  
  <p>After ordering by [visitorID] and [rank],
  the [calculation] is </p>
  
  <p>(i) If [numbers] = 0, THEN [calculation] = 0
     (ii) If [numbers] &lt;> 0, THEN sum up the current [numbers] value and the previous [calculation] number.
     (iii) Based on (ii), if the summation is larger than 30, then [calculation] = 0, ELSE [calculation] remains the same summation value.</p>
  
  <p>See example as below.
  <a href="https://i.stack.imgur.com/MniUx.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MniUx.jpg" alt="enter image description here"></a></p>
  
  <p><strong>*The Problem I am encountering</strong></p>
  
  <p>I need to use BigQuery to do this kind of calculation.
  However, what I came up with is the "window sum function", which seemed not a good solution to this.
  I think the key point is that "In BigQuery, I cannot create a column that needs the calculation including the column I am creating".</p>
  
  <p>See example as below.
  <a href="https://i.stack.imgur.com/9uYNs.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9uYNs.jpg" alt="Tried window function, which was failed."></a></p>
  
  <p>That is, I always need the existed value to create a new column.
  I have my sample query as follow, which can not solve the problem.
  And you can also see the print-screen to understand what the problem is.</p>
  
  <p>See sample query as below.</p>
  
  <pre><code>SELECT 
    visitorID, 
    rank, 
    numbers, 
    SUM(numbers) OVER (PARTITION BY visitorID ORDER BY rank) AS window_sum_current, 
    SUM(numbers) OVER (PARTITION BY visitorID ORDER BY rank ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS window_sum_prec1     
  FROM sample_table
  </code></pre>
  
  <p><strong>*Seeking for suggestions</strong></p>
  
  <p>I would like to ask for suggestions.
  (1) In BigQuery, is this problem solvable or not ?
  (2) What method or concept I am lacking of ? 
  (3) What is a better way to solve the issue in BigQuery ?</p>
  
  <p>Thank you very much.</p>