Assign unique id each time two fields/columns are an exact match

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.

person_id	Datetime	unique combination_id
1234	2016-04-01T00:00:00	1
1234	2016-05-04T00:00:00	2
9102	2018-05-17T00:00:00	3
5678	2019-09-01T00:00:00	4
1234	2016-04-01T00:00:00	1

Answer

Using DENSE_RANK() should do the trick:

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

DEMO

Advertisement

Answer