BigQuery - оператор DELETE для удаления дубликатов

Question

Существует множество отличных постов по SQL, которые выбирают уникальные строки и записывают (усекают) таблицу, чтобы удалить dus. например

WITH ev AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC) AS rowNum
  FROM `duplicates`
 )
SELECT
  * EXCEPT(rowNum)
FROM
  ev
WHERE rowNum = 1

Я пытался исследовать это немного по-другому, используя DML и DELETE (например, если вы не хотите использовать BQ saveQuery, просто выполните SQL). Что я хочу сделать, это примерно:

WITH dup_events AS (
  SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC) AS rowNum
      FROM `duplicates`
 )
DELETE FROM
  dup_events
WHERE rowNum > 1

но получил эту ошибку в консоли:

Syntax error: Expected "(" or keyword SELECT but got keyword DELETE at [10:1]

Может ли это быть достигнуто (StandardSQL) с помощью DELETE?

Спасибо!

Answer 1

Из синтаксической документации аргумент DELETE должна быть таблица, и нет никаких условий для использования WITH пункт. Это имеет смысл, учитывая, что вы не можете удалить то, что по сути является логическим представлением (CTE). Вы можете выразить, что вы хотите, поместив логику внутри фильтра, например,

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;

Answer 2

Ниже собственно: о) работает

#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)  

Примечание: предполагается, что loadTime также уникален - это означает, что если для данного идентификатора существует более одной записи с последним loadTime - все они будут сохранены

Answer 3

Это должен быть самый простой способ:

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

Если у вас есть тип данных массива, попробуйте следующее:

-- 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
  )
);

Answer 4

Эти ответы выше работают только для таблицы небольшого размера. Если у вас есть таблица разделов большого размера и вы хотите удалить дубликаты только в заданном диапазоне, используйте приведенный ниже SQL:

-- WARNING: back up the table before this operation
-- FOR large size timestamp partitioned table 
-- -------------------------------------------
-- -- To de-duplicate rows of a given range of a partition table, using surrage_key as unique id
-- -------------------------------------------

DECLARE dt_start DEFAULT TIMESTAMP("2019-09-17T00:00:00", "America/Los_Angeles") ;
DECLARE dt_end DEFAULT TIMESTAMP("2019-09-22T00:00:00", "America/Los_Angeles");

MERGE INTO `gcp_project`.`data_set`.`the_table` AS INTERNAL_DEST
USING (
  SELECT k.*
  FROM (
    SELECT ARRAY_AGG(original_data LIMIT 1)[OFFSET(0)] k 
    FROM `gcp_project`.`data_set`.`the_table` AS original_data
    WHERE stamp BETWEEN dt_start AND dt_end
    GROUP BY surrogate_key
  )

) AS INTERNAL_SOURCE
ON FALSE

WHEN NOT MATCHED BY SOURCE
  AND INTERNAL_DEST.stamp BETWEEN dt_start AND dt_end -- remove all data in partiion range
    THEN DELETE

WHEN NOT MATCHED THEN INSERT ROW

кредит: https://gist.github.com/hui-zheng/f7e972bcbe9cde0c6cb6318f7270b67a