跪求经典问题 解法

Question

rowid   BEG_DATE H_COST          H_EVAL
----------------------------------------------------------------------------
19 2007-01-20 25000000 29798406.56
20 2007-01-21 47666585.51 56086281.28
21 2007-01-23 48267787.51 57909089.42
22 2007-01-24 25000000 28082241.43
23 2007-01-25 73267787.51 80221872.8现在需要求后一天的H_COST-后一天的H_EVAL)/前一天的H_COST,需要返回如下的DIVIDED字段：
如上表中的(47666585.51-56086281.28)/25000000=-0.336787831
BEG_DATE H_COST   H_EVAL         DIVIDED
2007-01-20 25000000 29798406.56
2007-01-21 47666585.51 56086281.28 -0.336787831
2007-01-23 48267787.51 57909089.42 -0.202265419
2007-01-24 25000000 28082241.43 -0.06385711
2007-01-25 73267787.51 80221872.8 -0.278163412请问高手门怎么写啊？最好能提供一存储过程。在线等啊

Answer 1

rowid     BEG_DATE    H_COST                 H_EVAL 
---------------------------------------------------------------------------- 
19       2007-01-20   25000000              29798406.56 
20       2007-01-21   47666585.51           56086281.28 
21       2007-01-23   48267787.51           57909089.42 
22       2007-01-24   25000000              28082241.43 
23       2007-01-25   73267787.51           80221872.8 
现在需要求（后一天的H_COST-后一天的H_EVAL)/前一天的H_COST,需要返回如下的DIVIDED字段： 
如上表中的(47666585.51-56086281.28)/25000000=-0.336787831 
BEG_DATE          H_COST                    H_EVAL                 DIVIDED 
2007-01-20        25000000                  29798406.56 
2007-01-21        47666585.51               56086281.28           -0.336787831 
2007-01-23        48267787.51               57909089.42           -0.202265419 
2007-01-24        25000000                  28082241.43           -0.06385711 
2007-01-25        73267787.51               80221872.8            -0.278163412 

Answer 2

用LEAD函数就是了呀。就是注意0除。

Answer 3

try select t.*,(H_COST-H_EVAL)/lag(H_COST,1,null) over(order by BEG_DATE ) from table t
lag函数