用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）跑通后，迁到数仓就因这些细节失效——得在提数前确认目标引擎版本和方言差异。
