如何在SparkSQL中去除重复行？附示例数据与现有代码

问题描述 #

原始数据存在完全重复的行（如Code=11111且Time=07/06/2022 06:45:42的两行、Code=22222的两行），需要去除这些重复项，保留各唯一列组合的单行记录。

原始数据 #

期望结果 #

现有代码 #

select * from 
(
  select
     code,
     Time,
     Model Type,
     Total Value,
     First Status,
     lead(First Status, 1, null) over(partition by code order by Time asc) as Second Status
  from file
  where Model Type = 'MXJ'
) t 
where First Status='Turn On' and Second='Turn Off'
limit 5

解决方案 #

针对全列重复的去重需求，以下几种方式可以实现目标：

方法1：使用DISTINCT关键字 #

这是最直接的去重方式，保留所有列的唯一组合：

SELECT DISTINCT
    code,
    Time,
    `Model Type`,
    `Total Value`,
    `First Status`,
    `Second Status`
FROM (
  select
     code,
     Time,
     `Model Type`,
     `Total Value`,
     `First Status`,
     lead(`First Status`, 1, null) over(partition by code order by Time asc) as `Second Status`
  from file
  where `Model Type` = 'MXJ'
) t 
where `First Status`='Turn On' and `Second Status`='Turn Off'
limit 5

注意：SparkSQL中列名包含空格时，必须用反引号`包裹，否则会触发语法错误。

方法2：使用GROUP BY所有列 #

通过对全部列分组，也能实现去重，适合需要同时执行聚合操作的场景（此处无需聚合，直接取列即可）：

SELECT
    code,
    Time,
    `Model Type`,
    `Total Value`,
    `First Status`,
    `Second Status`
FROM (
  select
     code,
     Time,
     `Model Type`,
     `Total Value`,
     `First Status`,
     lead(`First Status`, 1, null) over(partition by code order by Time asc) as `Second Status`
  from file
  where `Model Type` = 'MXJ'
) t 
where `First Status`='Turn On' and `Second Status`='Turn Off'
GROUP BY code, Time, `Model Type`, `Total Value`, `First Status`, `Second Status`
limit 5

方法3：先去重再计算窗口函数 #

如果重复行在原始数据中就存在，可以先对原始数据去重，再执行窗口函数计算，减少数据处理量：

select
     code,
     Time,
     `Model Type`,
     `Total Value`,
     `First Status`,
     lead(`First Status`, 1, null) over(partition by code order by Time asc) as `Second Status`
from (
    SELECT DISTINCT
        code,
        Time,
        `Model Type`,
        `Total Value`,
        `First Status`
    FROM file
    where `Model Type` = 'MXJ'
) t1
where `First Status`='Turn On' and lead(`First Status`, 1, null) over(partition by code order by Time asc)='Turn Off'
limit 5

关键提示 #

• 列名含空格必须用反引号包裹，这是SparkSQL的语法要求；
• 若仅需针对部分列组合去重，可调整DISTINCT或GROUP BY的列集合即可。

内容的提问来源于stack exchange，提问作者Yount Shi