如何对每一行进行累乘和累除

[儒林外史]

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)

我想生成两个新列，一个是累计乘，一个是累计除，如下所示：
userid trading rt commulative_multiply commulative_division
0001 2012-12-01 00:00:00.000 0.3 0.3 0.3
0001 2012-12-02 00:00:00.000 0.6 0.3*0.6 0.3/0.6
0001 2012-12-03 00:00:00.000 0.4 0.3*0.6*0.4 0.3/0.6/0.4
0001 2012-12-04 00:00:00.000 0.8 0.3*0.6*0.4*0.8 0.3/0.6/0.4/0.8
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.5*0.5 0.5/0.5
0002 2012-11-03 00:00:00.000 0.6 0.5*0.5*0.6 0.5/0.5/0.6


上面累计乘法和累计除两列列举的公式是我所要表达的意思，实际中需要的是每一行按上述公式运算后的结果。

[lg314]

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

[--小F--]

引用 4 楼 ssp2009 的回复:

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

GOOD JOB..

[唐诗三百首]

4楼数学高手啊.学习了..

[spiritofdragon]

with t as ( select *,rn=ROW_NUMBER()over(partition by userid order by trading) from #tb ) ,cte as ( select *,rt commulative_multiply,rt commulative_division from t where rn=1 union all select tt2.* ,cte.commulative_multiply*tt2.rt,cte.commulative_division/tt2.rt from t tt2 join cte on tt2.userid=cte.userid and tt2.rn=cte.rn+1 ) select * from cte order by userid,rn

[spiritofdragon]

四楼学以致用啊：a*b=10^ lg(a) * 10^ lg(b)= 10 ^(lg(a)+lg(b))

[--小F--]

引用 7 楼 spiritofdragon 的回复:

with t as ( select *,rn=ROW_NUMBER()over(partition by userid order by trading) from #tb ) ,cte as ( select *,rt commulative_multiply,rt commulative_division from t where rn=1 union all select tt2.* ,cte.commulative_multiply*tt2.rt,cte.commulative_division/tt2.rt from t tt2 join cte on tt2.userid=cte.userid and tt2.rn=cte.rn+1 ) select * from cte order by userid,rn

我写了半天 原来是关联条件少了 tt2.rn=cte.rn+1 看来水平确实菜。

[唐诗三百首]

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)


-- 累计乘函数
create function dbo.fn_multiply
(@u varchar(4),
 @t datetime) returns float
as
begin
 declare @x float
 
 select @x=1.0
 
 select @x=@x*rt
  from tb
  where userid=@u and trading<=@t

 return @x
end


-- 累计除函数
create function dbo.fn_division
(@u varchar(4),
 @t datetime) returns float
as
begin
 declare @x float
 
 select @x=power((select top 1 rt
            from tb
            where userid=@u and trading<=@t 
            order by trading),2)
 
 select @x=@x/rt
  from tb
  where userid=@u and trading<=@t

 return @x
end


select userid,trading,rt,
       dbo.fn_multiply(userid,trading) 'commulative_multiply',
       dbo.fn_division(userid,trading) 'commulative_division'
 from tb

/*
userid trading                  rt                    commulative_multiply   commulative_division
------ ----------------------- ---------------------- ---------------------- ----------------------
0001   2012-12-01 00:00:00.000  0.3                    0.3                    0.3
0001   2012-12-02 00:00:00.000  0.6                    0.18                   0.5
0001   2012-12-03 00:00:00.000  0.4                    0.072                  1.25
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)
*/

[--小F--]

总感觉能用CTE写出来 但是没写出来 希望楼下的高手试试。