[易飞]EXP(SUM(LOG(字段)))的实践

在写到一半代码的时候，需要统计指定行的乘积。想到SQL中有
EXP(SUM(LOG(字段)))的新函数试试。完全代码如下

-- =============================================   -- Author: <David Gong>   -- Create date: <2015-9-11>   -- Description: <统计COB品质状况表>    -- ============================================= ALTER Proc UP_Proc_COB(    @year  as char(4))asbegincreate table #tmpCOB(     项目 varchar(20),    月份 varchar(20),    百分比 decimal(8,4) default (0));--declare @year as char(4)--set @year='2015'declare @yearmonth as char(6)declare @i intset @i=1declare @month char(2)while @i<=12BEGIN   set @month=right(@i+100,2)    set @yearmonth =@year+right(@i+100,2)    if(@yearmonth<=CONVERT(char(6),getdate(),112))   begin       --统计当月绑定合格率        insert into #tmpCOB(项目,月份,百分比)        select '1.绑定合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as  numeric(8,4)) 合格率         from 制程COB绑测        where CONVERT(char(6),日期,112)=@yearmonth         --统计当月外观合格率        insert into #tmpCOB(项目,月份,百分比)        select '2.外观合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as  numeric(8,4)) 合格率         from 制程COB外观        where CONVERT(char(6),日期,112)=@yearmonth        --统计成测合格率        insert into #tmpCOB(项目,月份,百分比)        select '3.终测合格率' as 项目, @month 月份, cast(round(sum(测试数量-不良数量)*1.0/sum(测试数量),4) as  numeric(8,4)) 合格率         from 制程COB成测        where CONVERT(char(6),日期,112)=@yearmonth        --统计FQC合格率        insert into #tmpCOB(项目,月份,百分比)        select '4.FQC合格率' as 项目, @month 月份, cast(round(sum(检验数量-不合格数量)*1.0/sum(检验数量),4) as  numeric(8,4)) 合格率         from 制程FQC_COB_检验        where CONVERT(char(6),检验日期,112)=@yearmonth    end   set @i=@i+1ENDselect * into #COBfrom #tmpCOB pivot(max(百分比) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) aselect 项目,[01] 一月,[02] 二月,[03] 三月,[04] 四月,[05] 五月,[06] 六月,[07] 七月,[08] 八月,[09] 九月,[10] 十月,[11] 十一月,[12] 十二月FROM #COB union all---处理指定行的列乘积计算/*--方法一: CASE WHEN 行转列 之后PIVOT函数指定列select * from (select  '6.制程一次通过率' as 项目,月份,ROUND(sum(绑定百分比)*sum(外观百分比)*sum(终测百分比),4) as 百分比 from(select 项目,月份,CASE WHEN 项目='1.绑定合格率' then 百分比 else 0 end 绑定百分比 ,CASE WHEN 项目='2.外观合格率' then 百分比 else 0 end 外观百分比 , CASE WHEN 项目='3.终测合格率' then 百分比 else 0 end 终测百分比from #tmpCOBwhere  项目<>'4.FQC合格率' )Agroup by A.月份) as c pivot(max(百分比) for 月份 in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) b*/--方法二 直接采用EXP(SUM(LOG(字段)))SELECT '6.制程一次通过率',ROUND(EXP(SUM(LOG([01]))),4) as 一月,ROUND(EXP(SUM(LOG([02]))),4) as 二月,ROUND(EXP(SUM(LOG([03]))),4) as 三月, ROUND(EXP(SUM(LOG([04]))),4) as 四月,ROUND(EXP(SUM(LOG([05]))),4) as 五月,ROUND(EXP(SUM(LOG([06]))),4) as 六月,ROUND(EXP(SUM(LOG([07]))),4) as 七月,ROUND(EXP(SUM(LOG([08]))),4) as 八月,ROUND(EXP(SUM(LOG([09]))),4) as 九月,ROUND(EXP(SUM(LOG([10]))),4) as 十月,ROUND(EXP(SUM(LOG([11]))),4) as 十一月,ROUND(EXP(SUM(LOG([12]))),4) as 十二月FROM #COB where 项目<>'4.FQC合格率'union allselect case when 项目='FQC合格率目标' then '5.FQC合格率目标' else '7.一次直通率目标' end as 项目,一月,二月,三月,四月,五月,六月,七月 ,八月 ,九月 ,十月,十一月,十二月 from 制程FQC目标where 系列='COB' and 年份=@yeardrop table #tmpCOBdrop table #COBend

效果