CODEWITHSUNDEEP
google-bigquerypartition
idan
idan

Reputation: 2170

Consecutive values bigquery

I got the "type" and "previous row" fileds and trying to produce "consecutive values" filed

I tried to use count () over (partition by type , previouse row ) but the value does not reset after "previous row" change

type previous row Consecutive values
X 1 2
X 1 1
X 0 3
X 0 2
X 0 1
X 1 2
X 1 1

How can I solve this issue?

Upvotes: 1

Views: 1208

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

In order to achieve your goal - you need to have some indication of how rows to be ordered. Usually some sort of date or timestamp or any other means that allows to order on. so let's assume you have an extra column available called ts that define that order - like in example below

enter image description here

so in this case consider below solution

select * except(change, grp),
  count(1) over(partition by type, grp order by ts desc) Consecutive_values
from (
  select *, countif(change) over(partition by type order by ts) grp
  from (
    select *, 
      ifnull(value != lag(value) over(partition by type order by ts), true) change
    from data
  )
)
# order by ts

if apply to sample data - output is

enter image description here

Upvotes: 4

Related Questions