SQL 窗口函数在金融风控中的应用

用ROW_NUMBER()按user_id分组排序标记最近三笔逾期订单，结合LAG()计算设备申请间隔识别黑产，滚动统计宜用ROWS而非RANGE避免稀疏数据偏差，hive/spark有版本和语法限制需适配。

如何用 ROW_NUMBER() 标记用户最近三笔逾期订单

风控中常需识别“短期内高频逾期”行为，不能只看是否逾期，还要看时间密度。直接 ORDER BY create_time DESC + LIMIT 3 会丢失用户粒度，必须按用户分组排序。

正确写法是搭配 PARTITION BY user_id：

SELECT user_id, order_id, overdue_days, create_time,        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM loan_orders  WHERE status = 'overdue' AND create_time >= CURRENT_DATE - INTERVAL '30 days'

注意点：

• ROW_NUMBER() 是严格递增序号，相同时间戳也会被强制区分；若需并列排名，改用 RANK()
• 子查询或 CTE 中先过滤再开窗，避免对全表排序拖慢性能
• postgresql 需显式加 AND create_time IS NOT NULL 防止 NULL 排在最前影响 rn = 1 判断

用 LAG() 计算两次申请间隔，识别团伙申贷模式

黑产常控制多个设备/身份证集中申贷，时间间隔极短（如秒级）。LAG() 可直接拿到上一笔申请时间，比自连接更轻量。

示例（计算同一设备 ID 下相邻申请的时间差）：

SELECT device_id, apply_time,        EXTRACT(EPOCH FROM (apply_time - LAG(apply_time) OVER (            PARTITION BY device_id ORDER BY apply_time        ))) AS sec_since_last FROM application_log WHERE apply_time >= CURRENT_DATE - INTERVAL '7 days'

关键细节：

• PostgreSQL 中 EXTRACT(EPOCH FROM ...) 返回秒数；mysql 用 TIMESTAMPDIFF(SECOND, ..., ...)
• LAG() 默认返回 NULL（首行无前值），需用 COALESCE(sec_since_last, 999999) 避免后续条件漏判
• 若设备 ID 可伪造，建议叠加 ip_address 或 fingerprint_hash 多维分组

为什么 AVG() OVER(... RANGE BETWEEN ...) 在滚动逾期率统计中容易出错

风控报表常需“过去 7 天平均逾期率”，但用 RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW 看似合理，实际在数据稀疏时会引入偏差——比如某天无放款，窗口仍强行纳入空值，拉低均值。

更稳的做法是用 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW，按行数而非时间范围滚动：

SELECT stat_date,        COUNT(*) Filter (WHERE is_overdue) * 1.0 / COUNT(*) AS daily_rate,        AVG(COUNT(*) FILTER (WHERE is_overdue) * 1.0 / COUNT(*))           OVER (ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg FROM daily_loan_stats GROUP BY stat_date ORDER BY stat_date

注意：

• 窗口函数无法直接对聚合结果再聚合，必须先 GROUP BY 得到日粒度指标，再开窗
• SQL Server 不支持 FILTER，需改用 SUM(CASE WHEN is_overdue THEN 1 ELSE 0 END)
• 如果日期有断层（如节假日无数据），ROWS 滚动会跳过缺失日，而 RANGE 会补空，二者业务含义不同，得看策略需求

在 Hive/Spark SQL 中调用窗口函数的三个硬限制

离线风控模型训练常用 Hive 或 Spark SQL，但它们对窗口函数的支持比 PostgreSQL 更保守，容易在上线时翻车。

典型问题与绕过方式：

• Hive 3.1+ 才支持 RANGE 窗口帧，旧版本写 RANGE BETWEEN ... 直接报错 Unsupported frame type，必须降级为 ROWS
• Spark SQL 的 LEAD()/LAG() 不支持指定默认值（即不能写 LAG(col, 1, 0)），只能靠 COALESCE(LAG(col), 0)
• 所有分区字段必须出现在 ORDER BY 子句中，否则报 Expression not in GROUP BY key；例如 PARTITION BY user_id, province，则 ORDER BY create_time 会失败，得写成 ORDER BY province, create_time

真正麻烦的不是语法，而是同一段逻辑在开发环境（本地 PostgreSQL）跑通后，迁到数仓就因这些细节失效——得在提数前确认目标引擎版本和方言差异。