Как удалить повторяющиеся строки на основе значений нескольких столбцов в Amazon Athena?
У меня есть большая таблица в Athena (200 ГБ+), в которой есть несколько столбцов и столбец идентификатора на основе комбинации значений разных столбцов, пример ниже:
ID col1 col2 col3 timestamp ... coln
v1v2TSvn v1 v2 v3 TS vn
v1v2TSvn v1 v2 v3 TS vn
v1v2TSvn v1 v2 v3 TS vn
.
.
Я заметил, что у меня есть уникальные повторяющиеся строки, но есть 1 единственное отличие, которое представляет собой столбец Timestamp (это те же данные, но для разных временных меток)
Пример:
v1v2TSvn v1 v2 v3 9AM01 vn
v1v2TSvn v1 v2 v3 9AM02 vn
Я хочу удалить лишние повторяющиеся строки и оставить только первое появление строки данных (я хочу сохранить строку 9AM01)
Я пытался использовать выбор отдельных и потерпел неудачу в других попытках
Но смог получить хотя бы номер повторяющейся строки для каждой уникальной строки с помощью этого запроса:
SELECT "col1", "col2","col8","col13",
COUNT(*) AS CNT
FROM "mydatabase"."myTable"
GROUP BY "col1", "col2","col8","col13"
HAVING COUNT(*) > 1;
Результаты:
ID col1 col2 col8 coln CNT
v1v2TSvn v1 v2 v3 vn 3
v1v2TSvn v1 v2 v3 vn 7
v1v2TSvn v1 v2 v3 vn 2
.
.
и это фактически дало мне ожидаемые результаты подсчета дубликатов каждой строки
моя конечная цель - получить строки, в которых нет дубликатов, на основе значений, например, этих 4 столбцов.
Любая помощь, пожалуйста?
ПРИМЕЧАНИЕ: большинство моих значений строки — это STRINGS даже временная метка!
Ответы 1
Если у вас есть только один столбец, который вы хотите «исключить» из группировки, вы можете использовать агрегатную функцию min. Что-то в этом роде:
SELECT "col1", "col2","col8","col13",
min(timestamp) timestamp
FROM "mydatabase"."myTable"
GROUP BY "col1", "col2","col8","col13";
Если у вас есть только несколько столбцов, определяющих уникальность, а остальные должны быть «первыми», то самым простым вариантом будет использование функции row_number windows (хотя для 1-2 дополнительных столбцов предыдущий подход можно обновить, чтобы использовать min_by - select ..., min(timestamp) timestamp, min_by(colN, timestamp) colN, ...). Что-то вроде следующего:
select col1, col2, col8, col13, timestamp ....
from (
SELECT *,
row_number() over(partition by col1, col2, col8, col13 order by timestamp) rn
FROM "mydatabase"."myTable")
where rn = 1