CODEWITHSUNDEEP
sqlgoogle-bigquery
Adiansyah
Adiansyah

Reputation: 353

Bigquery cumulative sUM between preceding and current row with condition

i've a problem with cumsum logic between specific column let say order time. I want to get the time spend between order. i already have table that contain user, spend time in second and order flag. how to do a cumulative sum between is_order is true?

user  | open_time   | spend_time_sec | is_order   | cumsum
001   | 2022-01-01  | 320            | FALSE      | 
001   | 2022-01-02  | 60             | TRUE       | 380
001   | 2022-01-04  | 100            | TRUE       | 100
001   | 2022-01-06  | 20             | FALSE      |
001   | 2022-01-08  | 60             | TRUE       | 80

i'm thinking about using below syntax but how to tweak the "unbounded preceding" only if is_order is TRUE? any idea?

SUM(spend_time_sec) OVER(PARTITION BY user ORDER BY open_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW)

Thanky you

Upvotes: 0

Views: 1166

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Consider below approach

select * except(grp), 
  if(is_order, sum(spend_time_sec) over(partition by user, grp), null) as cumsum
from (
  select *, countif(is_order) over win as grp
  from  your_table window win as (
    partition by user order by open_time 
    rows between unbounded preceding and 1 preceding
  )
)

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions