CODEWITHSUNDEEP
sqlgoogle-bigquery
ajor
ajor

Reputation: 1634

BigQuery SQL - Increment ID based on condition

I have a sorted table with one field containing true and false values. I want to add an ID type field which increments each time a true value occurs:

enter image description here

How would I go about doing this? Can I store some kind of variable for Group ID or would I need to somehow back query the previous row?

Upvotes: 1

Views: 2202

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

to eliminate relatively heavy sum(case when ...) - you can use countif as in below example 

#standardSQL
SELECT *, COUNTIF(condition) OVER(ORDER BY pos) AS groupid
FROM `yourTable`

you can play with this using your dummy data as in below . 

#standardSQL
WITH `yourTable` AS (
  SELECT TRUE AS condition, 1 AS `group`, 1 AS pos UNION ALL
  SELECT FALSE, 1, 2 UNION ALL
  SELECT FALSE, 1, 3 UNION ALL
  SELECT TRUE, 2, 4 UNION ALL
  SELECT FALSE, 2, 5 UNION ALL
  SELECT FALSE, 2, 6 UNION ALL
  SELECT FALSE, 2, 7 UNION ALL
  SELECT TRUE, 3, 8 UNION ALL
  SELECT FALSE, 3, 9 UNION ALL
  SELECT TRUE, 4, 10 UNION ALL
  SELECT TRUE, 5, 11 UNION ALL
  SELECT FALSE, 5, 12 UNION ALL
  SELECT FALSE, 5, 13 UNION ALL
  SELECT FALSE, 5, 14  
)
SELECT *, COUNTIF(condition) OVER(ORDER BY pos) AS groupid
FROM `yourTable`

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the sum window function with a case expression to do this. 

select t.*,sum(case when condition='TRUE' then 1 else 0 end) over(order by somecolumn) as groupid
from tbl t

Upvotes: 1

Related Questions