数据质量校验：筛选col1+col2组合下col3重复记录的查询方法

要筛选出col1和col2组合对应多个不同col3值的违规行，这里提供两种实用的SQL查询方式：

方法一：窗口函数（适合MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数统计每个col1+col2组合下不同col3的数量，直接筛选出数量大于1的行：

SELECT col1, col2, col3
FROM (
    SELECT 
        col1, col2, col3,
        COUNT(DISTINCT col3) OVER (PARTITION BY col1, col2) AS col3_count
    FROM your_table_name
) AS sub
WHERE col3_count > 1;

方法二：分组子查询（兼容绝大多数数据库） #

先找出存在多个col3值的col1+col2组合，再关联原表获取这些组合对应的所有行：

SELECT t.col1, t.col2, t.col3
FROM your_table_name t
JOIN (
    SELECT col1, col2
    FROM your_table_name
    GROUP BY col1, col2
    HAVING COUNT(DISTINCT col3) > 1
) AS invalid_groups ON t.col1 = invalid_groups.col1 AND t.col2 = invalid_groups.col2;

将结果导出到拒绝文件 #

不同数据库的导出语法略有差异，举两个常见例子：

MySQL导出到CSV #

SELECT col1, col2, col3
FROM (
    SELECT 
        col1, col2, col3,
        COUNT(DISTINCT col3) OVER (PARTITION BY col1, col2) AS col3_count
    FROM your_table_name
) AS sub
WHERE col3_count > 1
INTO OUTFILE '/path/to/your_reject_file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

PostgreSQL导出到CSV #

COPY (
    SELECT col1, col2, col3
    FROM (
        SELECT 
            col1, col2, col3,
            COUNT(DISTINCT col3) OVER (PARTITION BY col1, col2) AS col3_count
        FROM your_table_name
    ) AS sub
    WHERE col3_count > 1
) TO '/path/to/your_reject_file.csv' WITH (FORMAT csv, HEADER);

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