Как посчитать ROW_NUMBER() OVER() c условием пересечения по времени?
У меня есть таблица. Вот пример:
| profile_id | starts_at | ends_at |
|---|---|---|
| kevinliugrateful | 2010-04-01 00:00:00 UTC | 2013-01-01 00:00:00 UTC |
| kevinliugrateful | 2010-06-01 00:00:00 UTC | 2011-10-01 00:00:00 UTC |
| kevinliugrateful | 2014-06-01 00:00:00 UTC | 2015-06-01 00:00:00 UTC |
| kevinliugrateful | 2014-09-01 00:00:00 UTC | 2015-01-01 00:00:00 UTC |
| kevinliugrateful | 2018-09-01 00:00:00 UTC | 2020-01-01 00:00:00 UTC |
Выше - таблица рабочего опыта человека, с указанием даты начала и даты конца работы на рабочем месте. Это данные одного человека, но в реальнойтаблице есть данные по большому количеству пользоваталей.
Мне необходимо сделать нумерацию опытов, таким образом, чтобы пересекающиеся опыты имели один и тот же номер.
Т. е. на выходе должно быть что-то вроде:
| profile_id | starts_at | ends_at | exp_number |
|---|---|---|---|
| kevinliugrateful | 2010-04-01 00:00:00 UTC | 2013-01-01 00:00:00 UTC | 1 |
| kevinliugrateful | 2010-06-01 00:00:00 UTC | 2014-01-01 00:00:00 UTC | 1 |
| kevinliugrateful | 2014-06-01 00:00:00 UTC | 2015-06-01 00:00:00 UTC | 2 |
| kevinliugrateful | 2014-09-01 00:00:00 UTC | 2015-01-01 00:00:00 UTC | 2 |
| kevinliugrateful | 2018-09-01 00:00:00 UTC | 2020-01-01 00:00:00 UTC | 3 |
Я понимаю, что это надо делать с помощью ROW_NUMBER() OVER(), но не понимаю, как указать условие, чтобы номер менялся тогда, когда появляется опыт, который не пересекается с предыдущими.
Запрос я делаю в BigQuery, но реализация на уровне SQL мне тоже подойдет:)
Благодарю