Удаление повторяющихся строк из таблицы BigQuery

У меня есть таблица s > 1M строками данных и 20 + столбцами.

В моей таблице (tableX) я идентифицировал повторяющиеся записи (~ 80k) в одном конкретном столбце (problemColumn).

Если возможно, я хотел бы сохранить исходное имя таблицы и удалить дубликаты записей из моего проблемного столбца, иначе я мог бы создать новую таблицу (tableXfinal) с той же схемой, но без дубликатов.

Я не владею SQL или любым другим языком программирования, поэтому, пожалуйста, извините мое невежество.

delete from Accidents.CleanedFilledCombined 
where Fixed_Accident_Index 
in(select Fixed_Accident_Index from Accidents.CleanedFilledCombined 
group by Fixed_Accident_Index 
having count(Fixed_Accident_Index) >1);

Ответ 1

Вы можете удалить дубликаты, выполнив запрос, который перезаписывает вашу таблицу (вы можете использовать ту же таблицу, что и пункт назначения, или создать новую таблицу, убедиться, что она имеет то, что вы хотите, а затем скопировать ее по старой таблице).

Запрос, который должен работать, находится здесь:

SELECT *
FROM (
  SELECT
      *,
      ROW_NUMBER()
          OVER (PARTITION BY Fixed_Accident_Index)
          row_number
  FROM Accidents.CleanedFilledCombined
)
WHERE row_number = 1

Ответ 2

Альтернатива ответу Джордана - этот лучше масштабируется при наличии слишком большого количества дубликатов:

#standardSQL
SELECT event.* FROM (
  SELECT ARRAY_AGG(
    t ORDER BY t.created_at DESC LIMIT 1
  )[OFFSET(0)]  event
  FROM 'githubarchive.month.201706' t 
  # GROUP BY the id you are de-duplicating by
  GROUP BY actor.id
)

Или более короткая версия (занимает любую строку вместо новой):

SELECT k.*
FROM (
  SELECT ARRAY_AGG(x LIMIT 1)[OFFSET(0)] k 
  FROM 'fh-bigquery.reddit_comments.2017_01' x 
  GROUP BY id
)

Чтобы удалить дубликаты строк в существующей таблице:

CREATE OR REPLACE TABLE 'deleting.deduplicating_table'
AS
# SELECT id FROM UNNEST([1,1,1,2,2]) id
SELECT k.*
FROM (
  SELECT ARRAY_AGG(row LIMIT 1)[OFFSET(0)] k 
  FROM 'deleting.deduplicating_table' row
  GROUP BY id
)

Ответ 3

Если ваша схема не имеет записей - ниже variation ответа Jordans будет достаточно хорошо работать с записью поверх той же таблицы или новой и т.д.

SELECT <list of original fields>
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Fixed_Accident_Index) AS pos,
  FROM Accidents.CleanedFilledCombined
)
WHERE pos = 1

В более общем случае - со сложной схемой с записями /netsed полями и т.д. - выше подход может быть проблемой.

Я бы предложил попробовать Tabledata: insertAll API с rows [ ].insertId установлен на соответствующий Fixed_Accident_Index для каждой строки. В этом случае повторяющиеся строки будут удалены BigQuery

Конечно, это будет связано с некоторым кодированием на стороне клиента - поэтому это может быть не актуально для данного конкретного вопроса. Я даже не пробовал этот подход самостоятельно, но думаю, что было бы интересно попробовать: o)

Ответ 4

Не уверен, почему никто не упомянул запрос DISTINCT.

Вот способ очистки повторяющихся строк:

CREATE OR REPLACE TABLE project.dataset.table
AS
SELECT DISTINCT * FROM project.dataset.table