3. SQL基础回顾：SELECT、WHERE、JOIN、GROUP BY、HAVING、窗口函数在风控中的应用场景

在实时风控巡检中，SQL 是提取、聚合、比对异常交易的核心工具。本节将回顾六类最常用的 SQL 语法，并直接对应到风控场景中的典型问题，如“查询高频交易”、“关联黑名单”、“按用户分组统计异常次数”、“筛选超过阈值的群体”以及“计算滑动窗口内的交易金额排名”。

3.1 SELECT & WHERE：基础过滤与异常定位

SELECT 指定要返回的字段，WHERE 对原始行进行条件过滤。在风控中，这是最频繁使用的组合，用于快速定位可疑交易。

-- 示例：查询今日单笔金额超过 5 万且交易失败的记录
SELECT
    trans_id,
    user_id,
    amount,
    trans_time,
    status
FROM transaction_log
WHERE trans_date = CURRENT_DATE
  AND amount > 50000
  AND status = 'FAILED';

3.2 JOIN：关联多表构建风控特征

风控数据通常分散在多个表中：交易流水表、用户信息表、设备指纹表、黑名单表。JOIN 将这些表关联起来，形成完整的风险画像。

-- 示例：关联黑名单表，查询命中黑名单用户的交易
SELECT
    t.trans_id,
    t.user_id,
    t.amount,
    b.black_reason
FROM transaction_log t
INNER JOIN blacklist b ON t.user_id = b.user_id
WHERE t.trans_date = CURRENT_DATE;

3.3 GROUP BY & HAVING：聚合统计与群体异常筛选

GROUP BY 按维度（用户、设备、IP）聚合数据，HAVING 对聚合后的结果进行过滤。这是识别“群体性异常”的关键手段，例如同一 IP 下多个账户同时交易。

-- 示例：按 IP 分组，找出今日关联用户数超过 3 个的异常 IP
SELECT
    ip_address,
    COUNT(DISTINCT user_id) AS user_cnt,
    COUNT(trans_id) AS trans_cnt
FROM transaction_log
WHERE trans_date = CURRENT_DATE
GROUP BY ip_address
HAVING COUNT(DISTINCT user_id) > 3;

3.4 窗口函数：滑动窗口内的排名与累计计算

窗口函数在不改变行数的情况下，为每一行计算其所在窗口内的排名、累计值、前后行差值。在实时风控中，窗口函数用于检测“短时间内连续交易”、“金额突增”等时序异常。

-- 示例：使用 LAG 计算同一用户相邻两笔交易的时间间隔（秒）
SELECT
    trans_id,
    user_id,
    trans_time,
    LAG(trans_time) OVER (
        PARTITION BY user_id
        ORDER BY trans_time
    ) AS prev_trans_time,
    EXTRACT(EPOCH FROM (trans_time - LAG(trans_time) OVER (
        PARTITION BY user_id
        ORDER BY trans_time
    ))) AS interval_seconds
FROM transaction_log
WHERE trans_date = CURRENT_DATE;

3.5 六类 SQL 在风控中的协同关系

下图展示了六类 SQL 语法在实时风控巡检中的典型调用链路：从原始交易数据开始，经过过滤、关联、聚合、窗口计算，最终输出异常交易名单。

3.6 综合案例：实时风控巡检 SQL 脚本

以下脚本将上述六类语法组合使用，实现一个完整的实时风控巡检逻辑：筛选今日交易、关联黑名单、按 IP 聚合、使用窗口函数计算时间间隔，最终输出高风险交易。

-- 实时风控巡检综合脚本
WITH filtered_trans AS (
    -- 1. SELECT + WHERE：过滤今日交易且金额大于 1000
    SELECT *
    FROM transaction_log
    WHERE trans_date = CURRENT_DATE
      AND amount > 1000
),
joined_data AS (
    -- 2. JOIN：关联黑名单表
    SELECT
        f.*,
        b.black_reason,
        b.black_level
    FROM filtered_trans f
    LEFT JOIN blacklist b ON f.user_id = b.user_id
),
agg_ip AS (
    -- 3. GROUP BY + HAVING：按 IP 聚合，筛选关联用户数超过 3 的 IP
    SELECT
        ip_address,
        COUNT(DISTINCT user_id) AS user_cnt,
        COUNT(trans_id) AS trans_cnt
    FROM joined_data
    GROUP BY ip_address
    HAVING COUNT(DISTINCT user_id) > 3
),
window_rank AS (
    -- 4. 窗口函数：按用户分组，按时间排序，标记每笔交易的序号
    SELECT
        j.*,
        ROW_NUMBER() OVER (
            PARTITION BY j.user_id
            ORDER BY j.trans_time
        ) AS trans_seq,
        LAG(j.trans_time) OVER (
            PARTITION BY j.user_id
            ORDER BY j.trans_time
        ) AS prev_trans_time
    FROM joined_data j
    WHERE j.ip_address IN (SELECT ip_address FROM agg_ip)
)
-- 5. 最终输出：高风险交易
SELECT
    trans_id,
    user_id,
    amount,
    trans_time,
    ip_address,
    black_reason,
    trans_seq,
    EXTRACT(EPOCH FROM (trans_time - prev_trans_time)) AS interval_sec
FROM window_rank
WHERE black_level IS NOT NULL  -- 命中黑名单
   OR trans_seq >= 5          -- 同一用户今日第 5 笔及以上交易
   OR interval_sec < 10;      -- 与上一笔交易间隔小于 10 秒