SQL Server窗口化函数计算乘积

Question

原始数据如下：测试类型        总数量        通过数量       失败数量       通过率
Test_A           100             90                  10                   0.9
Test_B           100             80                  20                   0.8
Test_C           100             70                  30                   0.7
Test_D           100             60                  40                   0.6
Test_E            100            50                   50                   0.5需要输出结果：测试类型        总数量        通过数量       失败数量       通过率   累计通过率
Test_A           100             90                  10                   0.9
Test_B           100             80                  20                   0.8         0.72   
Test_C           100             70                  30                   0.7        0.504
Test_D           100             60                  40                   0.6        0.3024
Test_E            100            50                   50                   0.5       0.1512每个累计通过率是前面几个测试通过率的乘积。
请问各位专家，这个结果用现有的窗口函数能否实现（用游标方式已经实现）附测试代码：declare @mytable table(item varchar(50),test_name varchar(50), ThroughOut float, PassQty float,FailQty float, Yield float)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_a',100,90,10,0.90)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_b',100,80,20,0.80)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_c',100,70,30,0.70)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_d',100,60,40,0.60)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_e',100,50,50,0.50)select test_name,ThroughOut,PassQty,FailQty,Yield, 
sum(Yield) over (partition by item order by test_name rows between unbounded preceding and current row) as cum_yield
from @mytable 
-- sum可以用，但是现在需要的是累计的乘积，本来想用 exp(sum(LOG( Yield )))，但是因为exp不是窗口化函数，无法与over一起使用。select exp(sum(LOG( Yield ))) from @mytable -- 获得最终的cum_yield

Answer 1

分开两层写不就行了？
SELECT test_name,ThroughOut,PassQty,FailQty,Yield, EXP(S) AS cum_yield
FROM(
    Select test_name,ThroughOut,PassQty,FailQty,Yield, 
    sum(LOG( Yield )) over (partition by item order by test_name rows between unbounded preceding and current row) as S
    from @mytable
) AS T

Answer 2

确定是MSSQL?有些语法不像~能自定义 开窗聚合函数吗?还没试过这想法可不可行

Answer 3

恩 在外面再嵌套一层 或者用CTE.，临时表这些先记下结果 然后再查询。

Answer 4

还是熟悉的方式IF OBJECT_ID('tempdb..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable
(
item varchar(50)
,test_name varchar(50)
, ThroughOut FLOAT
, PassQty FLOAT
,FailQty FLOAT
, Yield float
)
INSERT INTO #mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
VALUES('aaaa','test_a',100,90,10,0.90)
INSERT INTO #mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
VALUES('aaaa','test_b',100,80,20,0.80)
INSERT INTO #mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
VALUES('aaaa','test_c',100,70,30,0.70)
INSERT INTO #mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
VALUES('aaaa','test_d',100,60,40,0.60)
INSERT INTO #mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
VALUES('aaaa','test_e',100,50,50,0.50);WITH t1 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS rn FROM #mytable
)
,t2 AS
(
SELECT *,CAST(0 AS FLOAT) AS cum_yield FROM t1 WHERE rn=1
UNION ALL
SELECT t1.*,CASE WHEN t2.cum_yield=0 THEN CAST(t2.Yield*t1.Yield AS FLOAT) 
ELSE t2.cum_yield*t1.Yield END AS cum_yield
FROM t1,t2
WHERE t1.rn=t2.rn+1
)
SELECT item
,test_name
,ThroughOut
,PassQty
,FailQty
,Yield
,cum_yield 
FROM t2/*
item test_name ThroughOut PassQty FailQty Yield cum_yield
aaaa test_a 100 90 10 0.9 0
aaaa test_b 100 80 20 0.8 0.72
aaaa test_c 100 70 30 0.7 0.504
aaaa test_d 100 60 40 0.6 0.3024
aaaa test_e 100 50 50 0.5 0.1512
*/

Answer 5

-- 凑个人数。
declare @mytable table
(
id  int identity,
item varchar(50),
test_name varchar(50), 
ThroughOut float, 
PassQty float,
FailQty float, 
Yield float  
)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_a',100,90,10,0.90)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_b',100,80,20,0.80)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_c',100,70,30,0.70)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_d',100,60,40,0.60)
insert into @mytable(item,test_name,ThroughOut,PassQty,FailQty,Yield)
values('aaaa','test_e',100,50,50,0.50)
;with m as (
select a.* , Yield as newcol from @mytable a where id = 1 
union all
select a.* , cast(a.Yield * m.newcol as float) from m , @mytable a where a.id = m.id  + 1 
)
select * from m

Answer 6

这样行吗：exp(  sum(Yield) over (partition by item order by test_name rows between unbounded preceding and current row)  )