Seleccionar un padre arbitrario de una fila

Supongamos que tengo la siguiente tabla

| `id` | `parent_id` |
|:------:| :-----:|
| 1    | `NULL` |
| 2    | 1 |
| 3    | 2 |
| 4    | 2 |

Cada uno parent_ides un puntero al padre id. ¿Cómo puedo seleccionar el parent_id más antiguo de id4 (que es 1, porque parent_ides NULL). En este ejemplo, id 2y 1es padre de primer y segundo nivel de id 4, respectivamente.

El número de padres que puede tener una fila es uno, pero su nivel es arbitrario.

El resultado debería verse como

id oldest_parent_id
1 NULL
2 1
3 1
4 1
Answer

Un enfoque podría ser iterar a través de los resultados de las filas. Aquí está la muestra de uso para lograrlo.

tabla de salida

CREATE TABLE `my-project-id.dataset.output_table`  (
  id INT64,
  oldest_parent_id INT64
);

consulta

DECLARE baseid int64;
DECLARE x int64;

FOR record IN
  (SELECT id,parentid from `my-project-id.dataset.table`)
DO 
  SET baseid = record.id;
  LOOP
    SET x = (SELECT parentid from `my-project-id.dataset.table` where id = baseid);
    IF x IS NULL THEN 
      LEAVE;
    ELSE 
      SET baseid = x;
    END IF;
  END LOOP;
  
  insert into `my-project-id.dataset.output_table` (id,oldest_parent_id) 
  select record.id, case when record.parentid is null then null else baseid end;

END FOR;

Probé diferentes enfoques para obtener este tipo de salida sin bucles, pero mirando la complejidad, no es algo que obtendrá fácilmente sin ensuciarse las manos. Mi recomendación para este tipo de escenarios es tener una columna adicional que proporcione el valor clave ya especificado cuando se inserta una nueva fila.

Se requiere mucho esfuerzo para obtener esta identificación específica cuando no debería ser así.

Para lograr el resultado anterior, he seguido los siguientes conceptos: