4. SQL 基础回顾：SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY 在质量探查中的应用

在风控数据质量治理中，SQL 是探查数据质量最直接、最强大的工具。本章将系统回顾六大核心 SQL 子句——SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY，并逐一演示它们在数据质量探查（Profiling）中的典型应用场景。掌握这些基础，是后续构建自动化质量监控报表的基石。

4.1 六大子句与质量探查的对应关系

每个 SQL 子句在数据质量探查中都有其独特的定位。下表总结了它们各自解决的质量问题类型：

SQL 子句	核心作用	质量探查场景
SELECT	选择列、计算表达式、去重	探查字段空值率、唯一值计数、字段值分布
WHERE	过滤行记录	筛选异常记录、空值记录、超出业务阈值的脏数据
JOIN	关联多张表	探查跨表引用完整性、外键缺失、数据一致性
GROUP BY	按维度分组聚合	统计各分组的记录数、重复度、字段完整性
HAVING	对聚合结果进行过滤	定位重复记录组、低质量分组、异常聚合值
ORDER BY	对结果集排序	按质量指标排序，快速定位最差或最优的数据分区

4.2 SELECT：探查字段级质量

SELECT 是质量探查的起点。通过组合 COUNT、DISTINCT、SUM 等聚合函数，可以快速评估单个字段的健康状况。

-- 探查 loan_amount 字段的空值率与唯一值计数
SELECT
    COUNT(*) AS total_records,
    COUNT(loan_amount) AS non_null_count,
    COUNT(*) - COUNT(loan_amount) AS null_count,
    ROUND(100.0 * (COUNT(*) - COUNT(loan_amount)) / COUNT(*), 2) AS null_rate_pct,
    COUNT(DISTINCT loan_amount) AS unique_values
FROM loan_apply;

4.3 WHERE：精准定位脏数据

WHERE 子句用于从海量数据中快速筛选出不符合质量规则的记录，是数据清洗的前置步骤。

-- 筛选出风控业务中典型的脏数据
SELECT
    apply_id,
    id_card,
    phone,
    loan_amount,
    apply_time
FROM loan_apply
WHERE
    -- 身份证号长度不符合18位
    LENGTH(id_card) <> 18
    -- 手机号非11位数字
    OR phone NOT REGEXP '^1[0-9]{10}$'
    -- 贷款金额为负数或超过业务上限
    OR loan_amount <= 0
    OR loan_amount > 1000000
    -- 申请时间在未来
    OR apply_time > NOW();

4.4 JOIN：探查跨表引用完整性

风控数据通常分布在多张表中（如用户表、申请单表、黑名单表）。JOIN 操作可以探查外键缺失、孤儿记录等引用完整性问题。

-- 探查 loan_apply 表中的 user_id 在 user_info 表中是否存在
SELECT
    la.apply_id,
    la.user_id,
    la.loan_amount
FROM loan_apply la
LEFT JOIN user_info ui ON la.user_id = ui.user_id
WHERE ui.user_id IS NULL;  -- 孤儿记录：申请单关联的用户不存在

4.5 GROUP BY + HAVING：探查重复与异常分组

GROUP BY 按维度汇总数据，HAVING 则对聚合结果进行二次过滤。两者结合是探查重复记录、异常分组的黄金组合。

-- 探查身份证号下关联多个手机号的异常情况
SELECT
    id_card,
    COUNT(DISTINCT phone) AS phone_count,
    COUNT(*) AS apply_count,
    MIN(apply_time) AS first_apply,
    MAX(apply_time) AS last_apply
FROM loan_apply
WHERE id_card IS NOT NULL
GROUP BY id_card
HAVING COUNT(DISTINCT phone) > 2  -- 同一身份证关联超过2个手机号
   OR COUNT(*) > 5;               -- 同一身份证申请次数超过5次

4.6 ORDER BY：按质量指标排序

ORDER BY 用于对探查结果进行排序，帮助数据治理人员快速聚焦质量最差的区域。通常与 LIMIT 配合使用。

-- 按空值率降序排列，找出质量最差的字段
SELECT
    column_name,
    null_count,
    total_count,
    ROUND(100.0 * null_count / total_count, 2) AS null_rate
FROM (
    SELECT 'id_card' AS column_name, COUNT(*) - COUNT(id_card) AS null_count, COUNT(*) AS total_count FROM loan_apply
    UNION ALL
    SELECT 'phone', COUNT(*) - COUNT(phone), COUNT(*) FROM loan_apply
    UNION ALL
    SELECT 'loan_amount', COUNT(*) - COUNT(loan_amount), COUNT(*) FROM loan_apply
) stats
ORDER BY null_rate DESC;  -- 最差的排在最前面

4.7 综合实战：一次完整的数据质量探查

将六大子句组合使用，可以构建一个端到端的数据质量探查脚本。以下示例综合了字段完整性、引用完整性、重复性三个维度的检查：

-- 综合质量探查：风控申请数据质量报告
WITH
-- 1. 字段完整性探查
field_stats AS (
    SELECT
        COUNT(*) AS total,
        COUNT(id_card) AS id_card_non_null,
        COUNT(phone) AS phone_non_null,
        COUNT(loan_amount) AS amount_non_null
    FROM loan_apply
),
-- 2. 引用完整性探查
ref_integrity AS (
    SELECT COUNT(*) AS orphan_records
    FROM loan_apply la
    LEFT JOIN user_info ui ON la.user_id = ui.user_id
    WHERE ui.user_id IS NULL
),
-- 3. 重复性探查
dup_stats AS (
    SELECT id_card, COUNT(*) AS cnt
    FROM loan_apply
    WHERE id_card IS NOT NULL
    GROUP BY id_card
    HAVING COUNT(*) > 1
)
SELECT
    '字段完整性' AS check_type,
    ROUND(100.0 * (total - id_card_non_null) / total, 2) AS id_card_null_rate,
    ROUND(100.0 * (total - phone_non_null) / total, 2) AS phone_null_rate,
    ROUND(100.0 * (total - amount_non_null) / total, 2) AS amount_null_rate
FROM field_stats
UNION ALL
SELECT '引用完整性', orphan_records, 0, 0 FROM ref_integrity
UNION ALL
SELECT '重复记录数', COUNT(*), 0, 0 FROM dup_stats
ORDER BY check_type;