BigQuery: declaración DELETE para eliminar duplicados

Solución:

Debajo en realidad: o) funciona

#standardSQL
DELETE FROM `yourproject.yourdataset.duplicates`
WHERE STRUCT(id, loadTime) NOT IN (
        SELECT AS STRUCT id, MAX(loadTime) loadTime 
        FROM `yourproject.yourdataset.duplicates` 
        GROUP BY id)  

Nota: asume que loadTime también es único, lo que significa que si para la identificación dada hay más de un registro con el último loadTime, todos se conservarán

De la documentación de sintaxis, el argumento para DELETE debe ser una mesa, y no hay ninguna disposición para usar un WITH cláusula. Esto tiene sentido dado que no puede eliminar de lo que es esencialmente una vista lógica (un CTE). Puedes expresar lo que quieras poniendo la lógica dentro del filtro, p. Ej.

DELETE
FROM duplicates AS d
WHERE (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC)
       FROM `duplicates` AS d2
       WHERE d.id = d2.id AND d.loadTime = d2.loadTime) > 1;

Esta tiene que ser la forma más sencilla:

create or replace table `myproject.mydataset.duplicates` as (
select distinct *
from `myproject.mydataset.duplicates`)

Si tiene un tipo de datos de matriz, intente esto:

-- build a test table with a duplicate and an array datatype column --
create or replace table DW.pmoTest as (
select 1 as ID, 'peter' as firstname,ARRAY<INT64>[1, 2, 3]  as int_array, current_date as createdate
union all
select 1 as ID, 'peter' as firstname,ARRAY<INT64>[1, 7, 3] as int_array, current_date as createdate
union all
select 2 as ID, 'chamri' as firstname,ARRAY<INT64>[1, 2, 39, 4] as int_array, current_date as createdate
);

-- recreate table without duplicate row
create or replace table DW.pmoTest as (
SELECT col.* FROM (
  SELECT ARRAY_AGG(tbl ORDER BY createdate LIMIT 1)[OFFSET(0)]  col
  FROM DW.pmoTest tbl
  GROUP BY ID
  )
);

---

---

Utiliza Nuestro Buscador