Блог

У меня есть два столбца, которые определяют person — passport_id и driver_license_id. И passport_id и driver_license_id однозначно определяют человека.

На данный момент времени существует только одна уникальная комбинация passport_id driver_license_id. Но у одного человека может быть несколько passport_id и несколько driver_license_id.

Существует таблица с этими двумя столбцами, содержащая все комбинации:

 passport_id | driver_license_id
111         | aaa
222         | aaa
333         | bbb
111         | bbb
  

Как я могу получить уникальный person_id для каждого passport_id и driver_license_id?

В приведенном выше примере это один человек, потому что 111 соответствует aaa и bbb, и это означает, что у этого человека есть 3 passport_id и 2 driver_license_id.

Ниже приведен пример для стандартного SQL BigQuery со сценариями

 #standardSQL
DECLARE rows_count, run_away_stop INT64 DEFAULT 0;

CREATE TEMP TABLE input AS (
  select '111' passport_id, 'aaa' driver_license_id union all
  select '222', 'aaa' union all
  select '333', 'bbb' union all
  select '111', 'bbb' union all
  select '444', 'ccc' union all
  select '444', 'ddd' union all
  select '555', 'ddd' union all
  select '666', 'eee' union all
  select '777', 'fff' 
);

CREATE TEMP TABLE initial_grouping AS 
SELECT ARRAY_AGG(driver_license_id ORDER BY driver_license_id) arr 
FROM input
GROUP BY passport_id;

LOOP
  SET rows_count = (SELECT COUNT(1) FROM initial_grouping);
  SET run_away_stop = run_away_stop   1;

  CREATE OR REPLACE TEMP TABLE initial_grouping AS
  SELECT ANY_VALUE(arr) arr FROM (
    SELECT ARRAY(SELECT DISTINCT val FROM UNNEST(arr) val ORDER BY val) arr
    FROM (
      SELECT ANY_VALUE(arr1) arr1, ARRAY_CONCAT_AGG(arr) arr    
      FROM (
        SELECT t1.arr arr1, t2.arr arr2, ARRAY(SELECT DISTINCT val FROM UNNEST(ARRAY_CONCAT( t1.arr, t2.arr)) val ORDER BY val) arr 
        FROM initial_grouping t1, initial_grouping t2 
        WHERE (SELECT COUNT(1) FROM UNNEST(t1.arr) val JOIN UNNEST(t2.arr) val USING(val)) > 0
      ) GROUP BY FORMAT('%t', arr1)
    )
  ) GROUP BY FORMAT('%t', arr);

  IF (rows_count = (SELECT COUNT(1) FROM initial_grouping) AND run_away_stop > 1) OR run_away_stop > 10 THEN BREAK; END IF;
END LOOP;

SELECT passport_id, driver_license_id, final_grouping FROM input 
JOIN (SELECT ROW_NUMBER() OVER() final_grouping, arr FROM initial_grouping) 
ON driver_license_id IN UNNEST(arr) 
ORDER BY passport_id, driver_license_id; 
  

с выводом

Чтобы применить приведенный выше пример к вашим реальным данным — оператор remove CREATE TEMP TABLE input (...) и оператор replace input in CREATE TEMP TABLE initial_grouping AS ... со ссылкой на вашу фактическую таблицу project.dataset.table

Кроме того, убедитесь, что вы установили соответствующий max для run_away_stop (в приведенном выше сценарии это 10 — см. Последний оператор в цикле — вам может потребоваться увеличить его, чтобы убедиться, что преобразование будет завершено)