125-墨天轮SQL挑战赛第二期特别奖：一个业务的8种实现方法

老虎刘谈oracle性能优化 2025-06-30

175

可能很多读者还不太了解我，文章开头先自我介绍一下，：

本人老虎刘，曾经在oracle深圳研发中心real-world Performance(简称RWP）学习过一段时间，后来转到售后高服团队专职做oracle数据库优化，一共在oracle待了6年多（不包括后面的合作时间），在oracle性能优化，主要是SQL优化这个赛道上还算有一点心得。做过一些线下和线上培训，学员不算太多（很多是业内知名人士，有原厂ACS工程师，还有一些ACE和OCM，和我一样只有ocp认证的也挺多），没啥拿得出手的头衔，只有一个群友封的“国内SQL优化第一人”称号：

本人朋友圈经常会分享一些技术文章的点评，对优化感兴趣的朋友可以加微信 ora_service ：

下面步入本文正题：

这是2024年墨天轮社区搞的一个活动：

解锁SQL的无限可能 | 墨天轮SQL挑战赛第二期，等你来战！

（链接：https://www.modb.pro/db/1826549073320697856

）

看着题目比较简单，本人也凑了个热闹，参与了一下。在oracle数据数环境，用8种方法实现了题目的需求：

--建表并生成测试数据：
create table test_gen as
(
select 1 as seqno,1 as amount from dual
union all
select 2 as seqno,null as amount from dual
union all
select 3 as seqno,null as amount from dual
union all
select 4 as seqno,null as amount from dual
union all
select 5 as seqno,2 as amount from dual
union all
select 6 as seqno,null as amount from dual
union all
select 7 as seqno,null as amount from dual
union all
select 8 as seqno,3 as amount from dual
union all
select 9 as seqno,null as amount from dual
union all
select 10 as seqno,null as amount from dual
union all
select 11 as seqno,5 as amount from dual
);

方法1：last_value + ignore nulls 分析函数写法 ,实现方式最简洁，通用。

select seqno,amount
     ,last_value(amount) ignore nulls 
     over(order by seqno) as amount_all
from  test_gen a
order by seqno;

方法2：nvl+lag ignore nulls 分析函数写法, 与写法1不相上下。

select seqno,amount
      ,nvl(amount,lag(amount) ignore nulls 
      over(order by seqno)) as amount_all
from  test_gen a
order by seqno;

方法3：标量子查询写法,（其中fetch语法 12c+版本以上才支持）, 这种写法在数据量大时性能没办法接受。

select seqno,amount
     ,(select b.amount from test_gen b 
     where b.seqno<=a.seqno 
     and b.amount is not null 
     order by b.seqno desc 
     fetch first 1 rows only
     ) as amount_all
from  test_gen a
order by seqno;

方法4：outer apply写法,12c+版本支持，数据量大时效率跟方法3差不多。

select a.seqno,a.amount
      ,x.amount as amount_all
from  test_gen a
outer apply
(select * from test_gen b   
             where a.seqno>=b.seqno 
             and b.amount is not null 
             order by b.seqno desc
             fetch first 1 rows only
             )x 
order by 1;

方法5：match_recognize, 12c+ 版本(oracle特有语法).这个语法在实现第三期的SQL挑战赛时又获得了第一名（这次用了3种解法，这个语法是最简洁的 https://www.modb.pro/db/1917072884825075712）

select seqno,amount,amount_all
from
  test_gen a
match_recognize
(
order by seqno
measures
   first(amount) as amount_all
all rows per match
 pattern(a b*)
 define b as amount is null
);

方法6：model语法(也是oracle特有语法)

select seqno,amount as amount_all
from test_gen_2
model return updated rows
  dimension by (row_number() OVER (ORDER BY seqno) rn)
  measures( seqno,amount )
  rules (
        amount[any] = decode(amount[cv(rn)], null, NVL(amount[cv(rn) - 1], 0), amount[cv(rn)])
       )
order by seqno;

方法7：递归写法(这里没有把seqno字段当成连续的序列)

with cte0 as
(select row_number() over (order by seqno) as rn 
 , a.* from test_gen a 
)
,cte1(rn,seqno,amount,amount_all) as 
(
    select rn,seqno, amount, amount as amount_all
    from cte0
    where amount is not null
    union all
    select t.rn,t.seqno, t.amount, nvl(t.amount, x.amount_all) as amount_all
    from cte0 t,cte1 x
    where t.rn = x.rn + 1 and t.amount is null
)
select seqno, amount, amount_all
from cte1
order by seqno;

方法8：count+(min/max/first_value 任选一种) 两次分析函数实现

with mid1 as
(
select a.*
      ,count(amount) over (order by seqno) as group# 
from test_gen_2 a
)select a.seqno,a.amount
    ,first_value(amount) over (partition by group#) amount_all_1
    ,max(amount) over (partition by group#) amount_all_2
    ,min(amount) over (partition by group#) amount_all_3
from mid1 a
order by seqno;

感谢墨天轮的评委给了我一个特别奖！

文章转载自老虎刘谈oracle性能优化，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

125-墨天轮SQL挑战赛第二期特别奖：一个业务的8种实现方法

解锁SQL的无限可能 | 墨天轮SQL挑战赛第二期，等你来战！

评论