Удаляем дубликаты из таблиц Google BigQuery

Задача удаления дубликатов из таблицы BigQuery может быть решена разными способами. Рассмотрим несколько из них.

Используем ROW_NUMBER

Один из вариантов предполагает использование оконной функции ROW_NUMBER для удаления дубликатов.

Функция ROW_NUMBER присваивает каждой строке таблицы уникальный номер, который можно использовать для выборки только уникальных записей. Вот как выглядит код для удаления дубликатов с использованием ROW_NUMBER:

WITH cte AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY column1, column2, column3 ORDER BY column4) row_num
  FROM
    `mydataset.mytable`
)
DELETE FROM cte WHERE row_num > 1;

В этом коде используется Обобщенные табличные выражения (CTE), чтобы сначала присвоить каждой строке таблицы уникальный номер через функцию ROW_NUMBER. А затем удаляются все строки, у которых значение row_num больше 1, то есть все дубликаты.

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

Запрос с оператором DISTINCT

Другой вариант — это применение оператора DISTINCT, с помощью которого выбираются только уникальные записи из таблицы. Это проще, чем использование оконной функции ROW_NUMBER, но может быть менее эффективно для больших таблиц.

Кроме того, этот вариант не подойдет, если ваша таблица разбита на партиции.

Пример удаления дубликатов с использованием DISTINCT выглядит следующим образом:

CREATE OR REPLACE TABLE `mydataset.mytable_unique` AS
SELECT DISTINCT *
FROM `mydataset.mytable`;

В этом коде создается новая таблица mytable_unique которая содержит только уникальные записи из исходной таблицы mytable.

С функцией ARRAY_AGG

Еще один способ удаления дубликатов из таблицы BigQuery — это использование функции агрегирования ARRAY_AGG.

Предположим у нас есть таблица mydataset.mytable, и нам необходимо уникализировать ее по колонкам column1 и column2. Выполним следующий запрос:

SELECT a.* FROM (
  SELECT ARRAY_AGG(
    t ORDER BY t.column3 DESC LIMIT 1
  )[OFFSET(0)]  a
  FROM `mydataset.mytable` t 
  GROUP BY column1, column2
);

В этом запросе выполняется агрегация данных в таблице mytable, используя функцию ARRAY_AGG. При этом создается массив значений из столбцов таблицы t.

Затем эти массивы группируются по значениям в колонках column1 и column2 и выбирается один элемент из каждой группы, имеющий наибольшее значение в колонке column3. Это происходит с помощью комбинации ORDER BY t.column3 DESC LIMIT 1 и [OFFSET(0)].

Наконец, результаты этой группировки и выбора наибольшего значения в column3 объединяются в выходные данные, которые возвращаются в качестве результата запроса. В этом случае выбираются все столбцы из таблицы t и возвращаются в результате запроса с помощью SELECT a.*.

Остаётся только сохранить результаты запроса в новую таблицу.