CODEWITHSUNDEEP
sqlgoogle-bigquery
i_am_cris
i_am_cris

Reputation: 627

Calculate a running total with a condition in BigQuery

Sorry bad topic… I need to calculate a running total but need to reset the total on a condition (when expected reached = 0). I have this table:

Date, Registrations, Expected Registrations, Expected reached
        2020-03-01, 5, 4,1
        2020-03-02, 7, 5,1
        2020-03-03, 8, 6,1
        2020-03-04, 2, 5,0
        2020-03-05, 5, 4,1
        2020-03-06, 7, 5,1
        2020-03-07, 8, 6,1
        2020-03-08, 2, 5,0

Expected result with running total - the condition is that while “Expected Reached” <> 0 running total should be calculated. If “Expected Reached” = 0 the running total should start over from 0:

Date, Registrations, Expected Registrations, Expected Reached, Running Total
            2020-03-01, 5, 4,1, 1
            2020-03-02, 7, 5,1, 2
            2020-03-03, 8, 6,1, 3
            2020-03-04, 2, 5,0, 0
            2020-03-05, 5, 4,1, 1
            2020-03-06, 7, 5,1, 2
            2020-03-07, 8, 6,1, 3 
            2020-03-08, 2, 5,0, 0

I don't know how to partition my window function to do this. Maybe I have to create an intermediate calculation before but Im unsure. Any suggestions?

Edit: edit2: removed my "on the fly question".

Upvotes: 2

Views: 2978

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

#standardSQL
SELECT * EXCEPT(grp), 
  SUM(Expected_reached) OVER(PARTITION BY grp ORDER BY `date`) Running_Total
FROM (
  SELECT *, COUNTIF(Expected_reached = 0) OVER(ORDER BY `date`) grp 
  FROM `project.dataset.table`
)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Use a cumulative sum to define "islands". Then use row_number():

select t.*,
       (case when expected_reached > 0
             then row_number() over (partition by grp, (expected_reached = 0) 
                                     order by date
                                    )
             else 0
        end) as running_total
from (select t.*,
             countif(expected_reached = 0) over (order by date) as grp
      from t
     ) t;

Upvotes: 0

Related Questions