4. SQL基础回顾（二）：聚合函数与GROUP BY在交易汇总中的应用

在反洗钱特征样本提取中，我们经常需要回答这样的问题：“某个客户在过去30天内总共发生了多少笔交易？”、“这些交易的总金额是多少？”、“平均单笔交易金额是否异常？”。这些问题无法通过逐行查看原始交易记录来回答，必须借助SQL的聚合函数与GROUP BY子句对数据进行分组汇总。

4.1 五大聚合函数速览

聚合函数用于对一组值执行计算并返回单个汇总值。在反洗钱场景中，最常用的五个聚合函数如下：

函数 语法示例 返回结果 反洗钱典型应用
COUNT COUNT(transaction_id) 非空值的行数 统计客户交易笔数、账户往来对手数量
SUM SUM(amount) 数值列的总和 计算交易总金额、累计流入/流出量
AVG AVG(amount) 数值列的平均值 计算平均单笔交易金额，识别拆分交易
MAX MAX(amount) 数值列的最大值 找出单笔最大交易金额，识别大额可疑交易
MIN MIN(amount) 数值列的最小值 找出单笔最小交易金额，辅助判断交易规律
避坑提示：COUNT(*) 会统计包括 NULL 在内的所有行；COUNT(column_name) 只统计该列非 NULL 的行数。在反洗钱统计中，建议使用 COUNT(transaction_id) 或 COUNT(1) 来确保统计准确性。

4.2 GROUP BY：按维度分组汇总

单独使用聚合函数只能返回一个全局汇总值。要按客户、按账户、按交易类型等维度分别汇总，必须配合 GROUP BY 子句。GROUP BY 将结果集按一个或多个列分组，然后对每个组分别应用聚合函数。

基本语法结构：

SELECT
    grouping_column,
    AGGREGATE_FUNCTION(measure_column) AS alias_name
FROM table_name
WHERE filter_condition
GROUP BY grouping_column
ORDER BY alias_name DESC;
执行逻辑顺序：FROM → WHERE → GROUP BY → SELECT（含聚合） → ORDER BY。理解这个顺序有助于排查查询错误。

4.3 反洗钱实战：按客户汇总交易特征

假设我们有一张交易明细表 txn_detail，包含字段：customer_id、txn_date、txn_type（'IN' 为流入，'OUT' 为流出）、amount。以下查询提取每个客户的交易汇总特征：

SELECT
    customer_id,
    COUNT(txn_id) AS txn_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    MAX(amount) AS max_amount,
    MIN(amount) AS min_amount
FROM txn_detail
WHERE txn_date >= '2025-01-01'
GROUP BY customer_id
ORDER BY total_amount DESC;

这个查询的结果集就是一份客户级交易特征样本，可以直接用于后续的异常评分或规则筛选。例如，txn_count 过高可能暗示频繁交易型洗钱，avg_amount 接近整数阈值可能暗示拆分交易。

4.4 多维度分组：按客户+交易类型

反洗钱分析经常需要同时按多个维度分组。例如，分别统计每个客户的流入总额和流出总额：

SELECT
    customer_id,
    txn_type,
    COUNT(txn_id) AS txn_count,
    SUM(amount) AS total_amount
FROM txn_detail
WHERE txn_date >= '2025-01-01'
GROUP BY customer_id, txn_type
ORDER BY customer_id, txn_type;

这种多维度分组可以揭示客户资金流向的结构特征，是构建资金净流量、流入流出比等高级特征的基础。

4.5 聚合与GROUP BY的执行流程

聚合与GROUP BY执行流程 原始交易明细 逐行记录 WHERE 过滤 筛选符合条件的行 GROUP BY 分组 按维度列划分组 聚合计算 COUNT/SUM/AVG等 结果集输出 每个组一行汇总 执行顺序：FROM → WHERE → GROUP BY → 聚合函数 → SELECT → ORDER BY 理解此顺序有助于排查“列不在GROUP BY中”等常见错误

4.6 HAVING：对聚合结果进行过滤

WHERE 子句在分组前过滤行，无法用于过滤聚合后的结果（如“交易笔数大于10的客户”）。此时需要使用 HAVING 子句：

SELECT
    customer_id,
    COUNT(txn_id) AS txn_count,
    SUM(amount) AS total_amount
FROM txn_detail
WHERE txn_date >= '2025-01-01'
GROUP BY customer_id
HAVING COUNT(txn_id) >= 10
   AND SUM(amount) > 500000
ORDER BY total_amount DESC;

上述查询只返回交易笔数≥10笔且总金额超过50万的客户，这类客户在反洗钱规则中通常需要进一步排查。

常见错误：在 WHERE 子句中引用聚合列，例如 WHERE COUNT(txn_id) > 10。这是非法的，因为 WHERE 执行时聚合尚未计算。必须使用 HAVING 来过滤聚合结果。

4.7 聚合中的NULL值处理

在反洗钱数据中，NULL 值经常出现。聚合函数对 NULL 的处理规则如下：

  • COUNT(column)：忽略 NULL 值，只统计非 NULL 的行数。
  • SUM、AVG、MAX、MIN：忽略 NULL 值，只对非 NULL 值进行计算。
  • COUNT(*)：统计所有行，包括全为 NULL 的行。
  • 如果所有值都为 NULL，则 SUM/AVG 返回 NULL，COUNT 返回 0。

在提取特征样本时，建议使用 COALESCE 函数将 NULL 转换为 0，避免下游分析出现意外结果：

SELECT
    customer_id,
    COUNT(txn_id) AS txn_count,
    COALESCE(SUM(amount), 0) AS total_amount,
    COALESCE(AVG(amount), 0) AS avg_amount
FROM txn_detail
GROUP BY customer_id;

4.8 聚合函数与GROUP BY的常见反洗钱应用场景

场景 SQL关键逻辑 提取的特征
客户交易活跃度分析 GROUP BY customer_id + COUNT(txn_id) 交易笔数、日均交易量
大额交易识别 GROUP BY customer_id + MAX(amount) 单笔最大金额、大额交易占比
拆分交易检测 GROUP BY customer_id, txn_date + COUNT(txn_id) + SUM(amount) 单日交易笔数、单日累计金额
资金净流量计算 GROUP BY customer_id, txn_type + SUM(amount) 流入总额、流出总额、净流量
对手方集中度分析 GROUP BY customer_id, counterparty + COUNT(txn_id) 与单一对手交易笔数、交易金额占比

4.9 多表关联后的分组汇总

在实际反洗钱系统中，客户信息与交易记录通常分表存储。以下示例展示如何关联客户表与交易表后进行分组汇总：

SELECT
    c.customer_id,
    c.customer_name,
    c.risk_level,
    COUNT(t.txn_id) AS txn_count,
    SUM(t.amount) AS total_amount,
    AVG(t.amount) AS avg_amount
FROM customer_info c
LEFT JOIN txn_detail t ON c.customer_id = t.customer_id
WHERE t.txn_date >= '2025-01-01'
GROUP BY c.customer_id, c.customer_name, c.risk_level
HAVING COUNT(t.txn_id) > 0
ORDER BY total_amount DESC;
注意：当 SELECT 中包含非聚合列时，这些列必须出现在 GROUP BY 子句中（或作为聚合函数的参数）。上述查询中 customer_name 和 risk_level 都出现在 GROUP BY 中，这是标准 SQL 的要求。

4.10 聚合结果在反洗钱特征样本中的典型输出

聚合结果在反洗钱特征样本中的典型输出 聚合结果 → 反洗钱特征样本 原始交易表 customer_id | amount C001 | 50,000 C001 | 30,000 C002 | 200,000 C002 | 150,000 C003 | 10,000 ... 聚合处理 GROUP BY customer_id COUNT(txn_id) SUM(amount) AVG(amount) 客户级特征样本 customer_id | txn_count | total_amt | avg_amt C002 | 2 | 350,000 | 175,000 C001 | 2 | 80,000 | 40,000 C003 | 1 | 10,000 | 10,000 ... 每客户一行，直接用于规则评分 聚合函数 + GROUP BY 将海量交易明细压缩为每客户一行的特征样本，是反洗钱特征工程的核心操作

4.11 本章小结

  • COUNT、SUM、AVG、MAX、MIN 是反洗钱特征提取中最常用的五个聚合函数，分别用于统计笔数、总额、均值、最大值和最小值。
  • GROUP BY 按维度列分组，使聚合函数能够输出每个组的汇总值，是生成客户级、账户级特征样本的关键语法。
  • HAVING 用于过滤聚合后的结果，弥补 WHERE 不能引用聚合列的局限。
  • 理解 FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY 的执行顺序，是写出正确聚合查询的基础。
  • 多表关联后的分组汇总，可以生成包含客户属性与交易特征的复合样本，为后续的异常检测模型提供更丰富的输入。
← 上一章 📖 返回目录 下一章 →