筛选system_id出现次数为1的数据行问题求助

现有如下数据集：

org   system_id     punch_start_tb1         punch_start_tb2
CG    100242        2022-08-16T00:08:00Z    2022-08-16T03:08:00Z
LA    250595        2022-08-16T00:00:00Z    2022-08-16T03:00:00Z
LB    300133        2022-08-15T04:00:00Z    2022-08-16T04:00:00Z
LB    300133        2022-08-16T04:00:00Z    2022-08-15T04:00:00Z
MO    400037        2022-08-15T14:00:00Z    2022-08-15T23:00:00Z
MO    400037        2022-08-15T23:00:00Z    2022-08-15T14:00:00Z

需要筛选出system_id仅出现1次的行，预期结果：

org   system_id     punch_start_tb1         punch_start_tb2
CG    100242        2022-08-16T00:08:00Z    2022-08-16T03:08:00Z
LA    250595        2022-08-16T00:00:00Z    2022-08-16T03:00:00Z

尝试用GROUP BY和HAVING子句未成功，求解决方法。

解决方法 #

直接用GROUP BY+HAVING会丢失原表的其他列数据，因为GROUP BY后只能保留分组字段或通过聚合函数处理其他列，因此可以用以下两种方式实现需求：

方法1：子查询筛选目标system_id #

先统计每个system_id的出现次数，再关联原表取出对应行数据：

SELECT t.*
FROM your_table t
WHERE t.system_id IN (
    SELECT system_id
    FROM your_table
    GROUP BY system_id
    HAVING COUNT(*) = 1
);

方法2：窗口函数（推荐） #

用COUNT()窗口函数计算每行对应的system_id出现次数，再筛选次数为1的行：

SELECT org, system_id, punch_start_tb1, punch_start_tb2
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY system_id) AS id_count
    FROM your_table
) temp
WHERE id_count = 1;

窗口函数无需额外关联操作，一次扫描即可完成统计与筛选，效率更高，且能完整保留所有原始列数据。

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