（转）通过ORACLE分析函数LAG 过滤重复数据

研发的同事曾经遇到1个问题，看看他的问题，比较麻烦些。
X&z t$E8ad0     selectsbl.fee_terminal_id , sbl.status, smr.status, smr.report_timeIXPUB技术博客P jUP/d
   fromXXX_log sbl, XXX_report smr
/~ @P:_lAw:O3P0wheresbl.task_idin('8888','9999','1111','2222')IXPUB技术博客a#B@e)|$K
     andsbl.msg_id=smr.msg_id(+)
Oo"aE'y&o3QO0o0     andsbl.submit_time>='20080927'
Y,qc'c Hv({1r#D0     andsbl.submit_time<='20080929'
p:yZ5Y'inh0     andsmr.report_time>='20080927'
GC;yk"VJR1j3j D|0     andsmr.report_time<='20080929'IXPUB技术博客"G#X}f REynG#h0R
     and(fee_terminal_id, report_time)in(
G}7W@OOfu&V3I0            selectdistinct(sbl.fee_terminal_id),max(smr.report_time)IXPUB技术博客1{vFB n3L
                 fromXXX_log sbl, XXX_report smr
$y S1z| ~%|[b1o1f0               wheresbl.task_idin('8888','9999','1111','2222')IXPUB技术博客)yRUW$iIye!~
                    andsbl.msg_id=smr.msg_id(+)IXPUB技术博客5Z0_^1pu
                    andsbl.submit_time>='20080927'

q2i"@;v9uhl0                    andsbl.submit_time<='20080929'
_)d([j#L0                    andsmr.report_time>='20080927'
7bf/|;_6d2\#X0                    andsmr.report_time<='20080929'IXPUB技术博客 w[z y(d3]
               groupbyfee_terminal_id
ZK.k]YSY-`0     );IXPUB技术博客)B~H5d1QkJ"?
IXPUB技术博客
T aZ[IJ
IXPUB技术博客7O!il&G(h
t }
      IXPUB技术博客*\o;[er(H
     他需要提取某个时间段内，某个ID(fee_terminal_id)最新时间的状态。其实如果是在一张表中，直接MAX(report_time)就可以了。但是，恰恰这是两张表。每张表中一个状态，通常的情况就是我们首先得到ID及其最大的（最新）报告时间，然后再“自连接”自己进行WHERE条件的查询。IXPUB技术博客KEI ZtJ.e
      上面的SQL就是通过这种基本的想法来实现的。
9[ n-S C8}0      一般情况下，应该是没有问题的。但是，这次问题却出现了。主要就是数据太大，执行了30分钟没有看到有结果集返回。IXPUB技术博客ic,cnvAuO
       首先因为两张基本表（XXX_log sbl, XXX_report smr）都是分区表，不然数据也不会这么大呀。进行必要的分区工作，要知道，再精良的SQL，数据太多也会有问题。优化的第一步就是“少做”（片面一点讲，性能真的是设计出来的）。IXPUB技术博客#I!}2HSY9n
       完成后，直接执行原始SQL，基本上180秒左右，可以看到结果集返回。
1ntk+o;r/c Z0       至此，都有些“跑题”，还有过滤重复呢（其实就是找出最新时间的ID状态即可）。
/X:F(l&[_#q](QR0       看着这句SQL我总是有些不死心，看看其实这句SQL的两个部分的SQL语句基本相同，其实一部分就可以得到所有信息，就是因为数据无法得到ID和最新时间对应的值在一句话中。
C3@F;iy"n3B:}0       突然想起了ORACLE的分析函数。OK,使用IXPUB技术博客9tg6W){0Z1}
       LAG
_0ow*pT.wr0      selecta,b,c,d
5L1]7R9w1wA{!Hj0        from(selectsbl.fee_terminal_id a,IXPUB技术博客V1[_)?rn,?
                                                         sbl.status b,IXPUB技术博客S l'u%|x9~ L
                                                         smr.status c,IXPUB技术博客/aigona
                                                         smr.report_time d,
s3uR"NkU%Q1hd%q%U wT,g0                                                         lag(sbl.fee_terminal_id,1,0)over(partitionbysbl.fee_terminal_idorderbysmr.report_timedesc) flagIXPUB技术博客/}"?D P.cd*h
                                       fromxxx_log sbl, xxx_report smrIXPUB技术博客 ^TjiJA)y(t
                                 wheresbl.task_idin('8888','9999','1111','2222')
._5k,v5k:YT0                                         andsbl.msg_id = smr.msg_id(+)
5}D6P2iWty0                                         andsbl.submit_time >='20080927'IXPUB技术博客 Nm8H3O8G%NTx
                                         andsbl.submit_time <='20080929'IXPUB技术博客d~yu"wuM{
                                         andsmr.report_time >='20080927'
1ZP I'Sn"c0                                         andsmr.report_time <='20080929')
1c-r0` s/wlO0whereflag = 0
CNs_l0IXPUB技术博客P&h-D ?%\iEn

&~Y)U(kx1cx"f0      优化了原SQL语句。
$x+SD1xv-mys0      执行成本从19106下降到10139，结果集返回的时间下降到15秒左右。响应时间有大幅提高。
v-I2fn'S0      总结：IXPUB技术博客J-@4an jS!Y+V
      此次优化的核心思想，减少SQL的重复执行，能在一句SQL中执行完成的，就不要放到两句或者是多个子查询中执行。IXPUB技术博客y!WJ@gj$b[9]
       通过使用分析函数LAG，将分类和排序一次完成。一定要多多尝试使用分析函数，往往会给你带来非常的惊喜。