Как удалить повторяющиеся данные из Google BigQuery

Три способа очистки данных в BigQuery SQL

Google BigQuery — это мощное хранилище данных SaaS, которое позволяет быстро и эффективно анализировать большие наборы данных. Однако, если ваш набор данных содержит повторяющиеся данные, будь то ошибка в ваших данных или некоторые обстоятельства в ваших процессах интеграции данных, это, конечно, может повлиять на ваш анализ и исказить ваши результаты.

Давайте начнем с создания некоторых фиктивных данных с дубликатами. Вот пример кода, если вы хотите попробовать сами:

WITH data AS (
 SELECT 1 AS id, ‘John’ AS name, ‘Doe’ AS last_name, DATE(‘2022–01–01’) AS date
 UNION ALL
 SELECT 2 AS id, ‘Jane’ AS name, ‘Doe’ AS last_name, DATE(‘2022–02–01’) AS date
 UNION ALL
 SELECT 3 AS id, ‘John’ AS name, ‘Doe’ AS last_name, DATE(‘2022–03–01’) AS date
 UNION ALL
 SELECT 4 AS id, ‘Alice’ AS name, ‘Smith’ AS last_name, DATE(‘2022–04–01’) AS date
 UNION ALL
 SELECT 5 AS id, ‘Bob’ AS name, ‘Jones’ AS last_name, DATE(‘2022–05–01’) AS date
 UNION ALL
 SELECT 6 AS id, ‘John’ AS name, ‘Doe’ AS last_name, DATE(‘2022–06–01’) AS date
)

Самый простой способ сделать это — просто дважды выбрать данные.

SELECT * FROM data
UNION ALL
SELECT * FROM data

К счастью, BigQuery предоставляет несколько методов удаления дубликатов данных. Ниже я приведу три различных способа:

Вариант 1: Использование DISTINCT

Самый простой способ удалить повторяющиеся данные в BigQuery — использовать ключевое слово DISTINCT. Это ключевое слово возвращает только уникальные значения в наборе данных. Вот пример:

SELECT DISTINCT * FROM Data.test

Вариант 2: Использование предложений GROUP BY и HAVING

Другой способ удаления повторяющихся данных — использование предложений GROUP BY и HAVING. Строки групп предложения GROUP BY основаны на указанных столбцах, а фильтры предложения HAVING удаляют группы, которые не соответствуют указанному условию.

SELECT *,COUNT(*) 
FROM `triple-silo-282319.Data.test` 
GROUP BY id, name, last_name, date
Having COUNT(*) <= 2

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

Вариант 3: Использование функции ROW_NUMBER

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

SELECT * FROM (
 SELECT *, ROW_NUMBER() OVER(PARTITION BY id, name, last_name, date) as row_nu
 FROM `triple-silo-282319.Data.test`
)
WHERE row_num = 1

Предложение PARTITION BY указывает, по каким столбцам следует разбивать данные, а необязательное предложение ORDER BY указывает порядок нумерации строк. Наконец, выбираются только строки, в которых номер строки равен 1 (т. е. первое вхождение каждой уникальной строки).

Краткое содержание

В заключение, есть несколько способов удалить повторяющиеся данные из Google BigQuery. Предпочитаете ли вы функцию DISTINCT, предложения GROUP BY и HAVING или функцию ROW_NUMBER, BigQuery предлагает множество инструментов, которые помогут вам очистить данные и получить нужные сведения. Особенно инженеры данных должны быть довольны этим при очистке данных, но и специалисты по данным, которые могут даже захотеть найти дубликаты, например, счета-фактуры, могут действительно извлечь из этого пользу.