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
上面累计乘法和累计除两列列举的公式是我所要表达的意思，实际中需要的是每一行按上述公式运算后的结果。
------解决方案--------------------
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