Home > Back-end >  How to create new count column based on adjacent combinations in existing table
How to create new count column based on adjacent combinations in existing table

Time:09-22

I have a simple table I've already built in BigQuery and all I want to do is what feels like a simple count of the number of times the combination of the person_id and the specific activity in the activity column has appeared in that table and create as a new column with a value/count of the adjacent combination in every row: 'combination_count' There are thousands of rows in the table so it's no good creating a filter or wheres etc.

It feels really simple but it's driving me mad. I've tried using counts and partitions but it doesn't work for me.

desired result:

person_id activity combination_count
1234 activity_1 1
1234 activity_1 2
1234 activity_2 1
5678 activity_1 1

and so on...

CodePudding user response:

You can use row_number():

select t.*,
       row_number() over (partition by person_id, activity order by person_id)
from t;
  • Related