如何在Amazon Athena中基于多列值删除重复行并保留首行？

核心思路 #

你要保留col1、col2、col8、col13组合下最早出现的行（按timestamp排序），直接用SELECT DISTINCT无效——因为timestamp列值不同，所有列的组合是唯一的，DISTINCT会保留所有行。正确做法是用窗口函数标记每个分组的首行，再筛选出这些行。

解决方案代码 #

基础版（直接查询去重结果） #

WITH ranked_records AS (
  SELECT 
    *,
    -- 按目标列分组，按timestamp升序标记行号，首行号为1
    ROW_NUMBER() OVER (
      PARTITION BY "col1", "col2", "col8", "col13"
      ORDER BY "timestamp" ASC
    ) AS row_rank
  FROM "mydatabase"."myTable"
)
-- 只保留每个分组的首行，去掉行号列
SELECT * EXCEPT (row_rank)
FROM ranked_records
WHERE row_rank = 1;

优化版（解决timestamp字符串排序不准确问题） #

如果你的timestamp字符串格式（如9AM01、10PM30）直接按字符串排序会出错（比如10AM会排在9AM前面），可以用DATE_PARSE转换为时间类型后排序：

WITH ranked_records AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY "col1", "col2", "col8", "col13"
      -- 根据你的timestamp格式调整格式符，%I是12小时制小时，%p是AM/PM，%M是分钟
      ORDER BY DATE_PARSE("timestamp", '%I%p%M') ASC
    ) AS row_rank
  FROM "mydatabase"."myTable"
)
SELECT * EXCEPT (row_rank)
FROM ranked_records
WHERE row_rank = 1;

高效版（创建去重后的新表） #

针对200GB+的大表，重复查询会消耗大量资源，建议直接创建去重后的新表（推荐用Parquet/ORC列式存储格式提升性能）：

CREATE TABLE "mydatabase"."myTable_deduplicated"
WITH (
  format = 'PARQUET', -- 替换为你原表的存储格式（如ORC）
  external_location = 's3://your-bucket/path/to/deduplicated-data/', -- 替换为你的S3路径
  -- 如果原表有分区，保留这里的配置，比如 partitioned_by = ARRAY['partition_col']
)
AS
WITH ranked_records AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY "col1", "col2", "col8", "col13"
      ORDER BY DATE_PARSE("timestamp", '%I%p%M') ASC
    ) AS row_rank
  FROM "mydatabase"."myTable"
)
SELECT * EXCEPT (row_rank)
FROM ranked_records
WHERE row_rank = 1;

关键说明 #

• PARTITION BY：指定用来判断重复的列（你的col1、col2、col8、col13）
• ORDER BY：确保每个分组中最早的timestamp行被标记为row_rank=1
• EXCEPT (row_rank)：移除窗口函数生成的行号列，返回原表的所有列
• 大表优化：创建新表后，后续查询直接使用去重表，避免重复扫描S3中的原始大表数据

内容的提问来源于stack exchange，提问作者Adem Youssef