Big Query:

I'm looking to assign a 'unique combination id' if a person and date are an exact match. So for each specific time a 'person_id' and a specific datetime are the same, they are assigned a number overall (time will always be 00:00:00 but just has to be there for records). This will be a unique number overall, not for a count for each individual. Hope that makes sense.

Desired result below. So you'll see row 1 and 5 are the same unique combination, so have the same unique_combination_id.

CodePudding user response：

Using DENSE_RANK() should do the trick:

SELECT *, 
   DENSE_RANK() OVER(ORDER by person_id, datetime) as unique_combination_id
FROM tbl;

DEMO