oracle 累除,怎么对每一行进行累乘和累除(2)

0001   2012-12-04 00:00:00.000  0.8                    0.0576                 1.5625

0002   2012-11-01 00:00:00.000  0.5                    0.5                    0.5

0002   2012-11-02 00:00:00.000  0.5                    0.25                   1

0002   2012-11-03 00:00:00.000  0.6                    0.15                   1.66666666666667

(7 row(s) affected)

*/

------解决方案--------------------

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb

GO

CREATE TABLE #tb

(

userid  VARCHAR(4),

trading  DATETIME,

rt  float

)

insert into #tb

values('0001','2012-12-01',0.3),

('0001','2012-12-02',0.6),

('0001','2012-12-03',0.4),

('0001','2012-12-04',0.8),

('0002','2012-11-01',0.5),

('0002','2012-11-02',0.5),

('0002','2012-11-03',0.6)

SELECT userid

,trading

,rt

,CONVERT(FLOAT, NULL) commulative_multiply

,CONVERT(FLOAT, NULL) commulative_division

INTO #tb2

FROM #tb

ORDER BY userid,trading

DECLARE @prevuserid VARCHAR(4)

,@userid VARCHAR(4)

,@rt FLOAT

,@commulative_multiply FLOAT

,@commulative_division FLOAT

DECLARE cc CURSOR

FOR

SELECT userid

,rt

FROM #tb2

FOR UPDATE

OPEN cc

WHILE 1 = 1

BEGIN

FETCH NEXT

FROM cc

INTO @userid

,@rt

IF @@fetch_status <> 0

BREAK

IF @prevuserid = @userid

BEGIN

SELECT @commulative_multiply = @commulative_multiply * @rt

,@commulative_division = CASE

WHEN @rt IS NULL

OR @rt = 0

THEN NULL

ELSE @commulative_division / @rt

END

END

ELSE

BEGIN

SELECT @commulative_multiply = @rt

,@commulative_division = @rt

END

UPDATE #tb2

SET commulative_multiply = @commulative_multiply

,commulative_division = @commulative_division

WHERE CURRENT OF cc

SELECT @prevuserid = @userid

END

DEALLOCATE cc

SELECT * FROM #tb2

DROP TABLE #tb

DROP TABLE #tb2

------解决方案--------------------

WITH cte AS

(

SELECT *,ROW_NUMBER() OVER(PARTITION BY userid ORDER BY trading) no FROM #tb

),

cte2 AS

(

SELECT *,num=CASE WHEN no=1 THEN Log10(rt) ELSE -Log10(rt) END FROM cte

)

SELECT userid,trading,rt,

(SELECT power(10.0000, Sum(Log10(rt))) FROM cte2 WHERE userid=a.userid AND trading<=a.trading),

(SELECT power(10.0000, Sum(num)) FROM cte2 WHERE userid=a.userid AND trading<=a.trading)

FROM cte2 a