Oracle GROUP BY分组后遍历各分组行实现自定义指标计算
实现方案
在Oracle中完全不需要写游标、循环逐行遍历分组内的行,直接用分析窗口函数就能完成所有指标计算,集合处理的效率比逐行遍历高几个数量级,尤其适合数据量较大的场景。
指标逻辑拆解
所有计算逻辑都可以依托窗口函数的PARTITION BY分组能力实现,无需手动写遍历逻辑:
- *Day-day[0]*指标:同一
customer_id分组下,先找到基准day值(即分组内第一行对应的day值),用当前行的day数值减去这个基准值就得到偏移量。注意原始表中day是VARCHAR2类型,计算前必须先转成数值,否则会报类型不匹配的语法错误。 - total risk指标:先按客户、天维度聚合出单天的风险值总和,再按天的顺序把单天风险值逐行累计,得到每个时间点对应的累计总风险值,最后把这个累计值关联回同个客户、同天偏移的所有原始行即可。
可直接运行的SQL实现
WITH base_data AS ( -- 基础数据层:处理类型转换,计算分组基准日、天偏移量 SELECT id, customer_id, risk, day, CAST(day AS NUMBER) AS day_num, -- 取分组内按id排序的第一行day值作为基准日,和业务里"第一行"的定义完全匹配 FIRST_VALUE(CAST(day AS NUMBER)) OVER ( PARTITION BY customer_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS base_day, CAST(day AS NUMBER) - FIRST_VALUE(CAST(day AS NUMBER)) OVER ( PARTITION BY customer_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS day_offset FROM risk_test ), day_risk_agg AS ( -- 聚合层:按客户、天偏移量计算单天的risk总和 SELECT customer_id, day_offset, SUM(risk) AS single_day_risk_sum FROM base_data GROUP BY customer_id, day_offset ), risk_calc AS ( -- 计算层:按天偏移顺序累计求和,得到total risk值 SELECT customer_id, day_offset, SUM(single_day_risk_sum) OVER ( PARTITION BY customer_id ORDER BY day_offset ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_risk FROM day_risk_agg ) -- 最终输出层:关联回原始行,得到完整结果 SELECT b.id, b.customer_id, b.risk, b.day, b.day_offset AS "Day-day[0]", r.total_risk FROM base_data b INNER JOIN risk_calc r ON b.customer_id = r.customer_id AND b.day_offset = r.day_offset ORDER BY b.customer_id, b.day_num, b.id;
测试数据验证
拿提供的测试数据执行后,结果完全匹配预期:
- 客户102分组的基准日是第一行对应的day=1:
- day=1的两行,偏移量为0,单天risk合计15+16=31,对应total risk为31
- day=2的两行,偏移量为1,单天risk合计17+10=27,对应累计total risk为31+27=58
- day=3的一行,偏移量为2,单天risk合计13,对应累计total risk为58+13=71
- 客户104分组的基准日是第一行对应的day=1:
- day=1的两行,偏移量为0,单天risk合计11+17=28,对应total risk为28
- day=2的三行,偏移量为1,单天risk合计14+13+16=43,对应累计total risk为28+43=71
适配说明:如果业务中“分组第一行”的定义不是按id排序,而是按其他规则(比如按day从小到大排序、按更新时间排序)取第一行的day值,只需要修改
FIRST_VALUE函数里的ORDER BY子句的排序字段即可,其余逻辑不需要改动。
内容的提问来源于stack exchange,提问作者M_Gh
相关产品推荐
相关产品推荐

