如何按M分组取对应最大serial行的P与Q进行除法运算？

安全阿岳

2026-5-8

解决方案

嘿，这个需求我熟！你需要的是在每个m分组里，分别抓出P不为空的行中serial最大的那个P值，以及Q不为空的行中serial最大的那个Q值，然后做除法运算。刚好给你写个适配Oracle的SQL方案，完全匹配你的预期结果：

WITH mytable as (
 select 1 as serial, 'm1' as m, 10 as p, null as q from dual
 union all -- 推荐用union all替代union，避免不必要的去重开销
 select 2, 'm1', 12, null from dual
 union all
 select 3, 'm1', null, 5 from dual
 union all
 select 4, 'm2', 20 , null from dual
 union all
 select 5, 'm1', null, 6 from dual
 union all
 select 6, 'm2', null, 4 from dual
),
-- 提取每个分组中P非空且serial最大的P值
max_p_per_m as (
    select m, p
    from (
        select m, p, serial,
               row_number() over(partition by m order by serial desc) rn
        from mytable
        where p is not null
    )
    where rn = 1
),
-- 提取每个分组中Q非空且serial最大的Q值
max_q_per_m as (
    select m, q
    from (
        select m, q, serial,
               row_number() over(partition by m order by serial desc) rn
        from mytable
        where q is not null
    )
    where rn = 1
)
-- 关联两个子结果，计算分组的P/Q值
select mp.m, mp.p / mq.q as calculated_value
from max_p_per_m mp
join max_q_per_m mq on mp.m = mq.m;